John Giotta John Giotta - 1 year ago 97
Python Question

Passing param to DB .execute for WHERE IN... INT list

With Python's DB API spec you can pass an argument of parameters to the execute() method. Part of my statement is a WHERE IN clause and I've been using a tuple to populate the IN. For example:

params = ((3, 2, 1), )
stmt = "SELECT * FROM table WHERE id IN %s"
db.execute(stmt, params)

But when I run into a situation where the parameter tuple is only a tuple of 1 item, the execute fails.

ProgrammingError: ERROR: syntax error at or near ")"

LINE 13: WHERE id IN (3,)

How can I get the tuple to work with clause properly?

Answer Source

Edit: Please, as @rspeer mentions in a comment, do take precautions to protect yourself from SQL injection attack.

Testing with pg8000 (a DB-API 2.0 compatible Pure-Python interface to the PostgreSQL database engine):

This is the recommended way to pass multiple parameters to an "IN" clause.

params = [3,2,1]
stmt = 'SELECT * FROM table WHERE id IN (%s)' % ','.join('%s' for i in params)
cursor.execute(stmt, params)

Another edit (fully tested and working example):

>>> from pg8000 import DBAPI
>>> conn = DBAPI.connect(user="a", database="d", host="localhost", password="p")
>>> c = conn.cursor()
>>> prms = [1,2,3]
>>> stmt = 'SELECT * FROM table WHERE id IN (%s)' % ','.join('%s' for i in prms)
>>> c.execute(stmt,prms)
>>> c.fetchall()
((1, u'myitem1'), (2, u'myitem2'), (3, u'myitem3'))
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download