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, Jun 09 '17 at 09:38
and finally: you should avoid inserting of your parameters with `%`, pass them as argument to `execute` like `session.execute(sql, params)` — Azat Ibrakov, Jun 09 '17 at 09: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, Jun 09 '17 at 09:40
btw why are you not using ORM features and executing raw SQL? — Azat Ibrakov, Jun 09 '17 at 09:53
indeed , you are right , the request is an update and it does many things with more than 40 lines. i will use ORM in the next time =)) — Anouar Mokhtari, Jun 09 '17 at 10:10
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/146243/discussion-between-anouar-mokhtari-and-azat-ibrakov). — Anouar Mokhtari, Jun 09 '17 at 10:30

Azat Ibrakov · Accepted Answer · 2017-06-09T09:47:12.863

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)

score 0 · Answer 2 · answered Jun 09 '17 at 09:26

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 09 '17 at 09:26

Dmitry

2,026
1
18
22

score 0 · Answer 3 · answered Jun 09 '17 at 17:17

0

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

answered Jun 09 '17 at 17:17

webjunkie

1,184
9
19

session.execute() IN operator of SQLAlchemy

3 Answers3