session.execute() IN operator of SQLAlchemy

Question

I have one problem when i try to execute that simple request :

params['_filter_items'] = (12345)

sql = """ SELECT * FROM items
          WHERE items.items IN %(_filter_items)s"""
# session is a db session of sqlAlchemy
query = session.execute(sql % params)

it will generate :

SELECT * FROM items
WHERE items.items IN 12345

without () when i have more than one item it's ok; i can touch the request; but i was wondered if there are another way to resolve it.

also (12345) is the same as 12345, parentheses are redundant, if you want to generate single element tuple you should write (12345,) (comma added) — Azat Ibrakov
– Azat Ibrakov, Commented Jun 9, 2017 at 9:38
and finally: you should avoid inserting of your parameters with %, pass them as argument to execute like session.execute(sql, params) — Azat Ibrakov
– Azat Ibrakov, Commented Jun 9, 2017 at 9:40
thanks I already try it but it doesn't work in PosgreSQL. items.item_id IN (142661089,) ProgrammingError: (psycopg2.ProgrammingError) syntax error at or near ")" exactly in comma — Anouar Mokhtari
– Anouar Mokhtari, Commented Jun 9, 2017 at 9:40

Azat Ibrakov · Accepted Answer · 2017-06-09 09:47:12Z

5

this object
```
(12345)
```
is the same as
```
12345
```
but it looks like you need tuple with single element 12345, it can be done with comma
```
(12345,)
```
we should avoid inserting of parameters by ourselves:
```
session.execute(sql % params)
```
let's delegate this work to SQLAlchemy & database drivers and pass parameters as argument to execute:
```
session.execute(sql, params)
```

try

params['_filter_items'] = (12345,)

sql = """ SELECT * FROM items
          WHERE items.items IN %(_filter_items)s"""
# session is a db session of sqlAlchemy
query = session.execute(sql, params)

edited Jun 9, 2017 at 9:47

answered Jun 9, 2017 at 9:41

Azat Ibrakov

11.1k9 gold badges43 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dmitry · Accepted Answer · 2017-06-09 09:26:54Z

0

Line of code params['_filter_items'] = (12345) resolves by Python interpreter into params['_filter_items'] = 12345. It means in your code (12345) it is int, not a tuple. To use a tuple you have to write params['_filter_items'] = (12345, ).

answered Jun 9, 2017 at 9:26

Dmitry

2,1161 gold badge19 silver badges22 bronze badges

Comments

webjunkie · Accepted Answer · 2017-06-09 17:17:37Z

0

You should take a look at this SQLAlchemy in clause for a more secure way of doing this.

answered Jun 9, 2017 at 17:17

webjunkie

1,1849 silver badges19 bronze badges

Collectives™ on Stack Overflow

session.execute() IN operator of SQLAlchemy

3 Answers 3

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Linked

Related