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

Question

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 1

The error is coming from the comma after the 3. Just leave it off for the single values and you're set.

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

Answer 2

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

Edit: 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'))