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

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

Answer 2

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 3

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)

Answer 4

As the question said, the following will fail:

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

Following the pg8000 docs the IN can be replaced with an ANY() to give the same result:

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

This sends the query and parameters separately to the server, avoiding SQL injection attacks.

Answer 5

A solution with f-string.

params = [...]
stmt = f"SELECT * FROM table WHERE id IN ({','.join(['%s']*len(params ),)})"
db.execute(stmt, params)

If there is another param placeholder it will be like this

age = 18
params = [...]
stmt = f"SELECT * FROM table WHERE age>%s AND id IN ({','.join(['%s']*len(params ),)})"
db.execute(stmt, tuple([age] + params))

Answer 6

The accepted answer risks SQL injection; you should never ever pass user input directly to the database. Instead, generate a query with the correct number of placeholders, then let pg8000 do the escaping:

params = [3,2,1]
# SELECT * from table where id in (%s,%s,%s)
stmt = 'SELECT * FROM table WHERE id IN ({})'.format(','.join(['%s']*len(params)))
cursor.execute(stmt, tuple(params))