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

Edit: If you think this answer circumvents the built-in protections against SQL-injection attack you're mistaken; look more closely.

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)

Full 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'))

This may not be an answer to exactly the question you asked, but I think it may solve the problem you have.

Python's DB-API doesn't seem to give you a way to pass tuples as safely substituted parameters. The accepted answer from bernie is using the Python % operator for substitution, which is unsafe.

However, you may not have to pass tuples as parameters, particularly when the tuple you want is the result of another SQL query (as you indicated to Daniel). Instead, you can use SQL subqueries.

If the set of IDs you want in your IN clause is the result of SELECT id FROM other_table WHERE use=true, for example:

stmt = "SELECT * FROM table WHERE id IN (SELECT id FROM other_table WHERE use=true)"
db.execute(stmt)

And this can be parameterized (the safe way), too. If the IDs you want to select are the ones with a given parent_id:

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

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)