CODEWITHSUNDEEP
pythonsqlalchemypyramid
Anouar Mokhtari
Anouar Mokhtari

Reputation: 2183

session.execute() IN operator of SQLAlchemy

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.

Upvotes: 2

Views: 20362

Answers (3)

webjunkie
webjunkie

Reputation: 1184

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

Upvotes: 0

Azat Ibrakov
Azat Ibrakov

Reputation: 11019

  • 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)

Upvotes: 5

Dmitry
Dmitry

Reputation: 2096

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, ).

Upvotes: 0

Related Questions