session.execute() IN operator of SQLAlchemy

python sqlalchemy pyramid

10,220

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)

10,220

Author by

Anouar Mokhtari

Developer Full Stack Python & Angular Open Source

Updated on June 04, 2022

Comments

Anouar Mokhtari almost 2 years
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.
- Azat Ibrakov almost 7 years
  
  12345 supposed to be a string or integer?
- Azat Ibrakov almost 7 years
  
  also (12345) is the same as 12345, parentheses are redundant, if you want to generate single element tuple you should write (12345,) (comma added)
- Anouar Mokhtari almost 7 years
  
  integer it s primary key of the table
- Azat Ibrakov almost 7 years
  
  and finally: you should avoid inserting of your parameters with %, pass them as argument to execute like session.execute(sql, params)
- Anouar Mokhtari almost 7 years
  
  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
- Azat Ibrakov almost 7 years
  
  btw why are you not using ORM features and executing raw SQL?
- Anouar Mokhtari almost 7 years
  
  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 almost 7 years
  
  Let us continue this discussion in chat.