Variables in SQL statement in Python

Question

I have list of integer containing ids and a string variable. How can use these variables in an SQL statement? If I use this:

list_of_ids = [1,2,3]
s_date = '2015-01-01'

cursor.execute("""
   SELECT * FROM foo WHERE id IN (%s)
   AND start_date=%s
   """, (list_of_ids,s_date))

The list_of_ids will be enclosed in quotes which shouldn't be.

This question is related to this imploding a list for use in a python MySQLDB IN clause but only the IN statement part.

I'm using psycopg2 connection -- in case that helps.

See the top answer on the [related question](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1) which has a detailed description of the _correct_ way to do it. — Turophile, Dec 09 '15 at 04:21

score 0 · Answer 1 · answered Dec 09 '15 at 03:57

Build parameters as a sequence (a list in the following example). You need to adjust sql part accordingly.

in_part = ','.join('%s' for _ in list_of_ids)
sql = "SELECT * FROM foo WHERE id IN (%s) AND start_date=%%s" % (in_part,)
params = list_of_ids + [s_date]  # [1, 2, 3, '2015-01-01']
cursor.execute(sql, params)

Clodoaldo Neto · Answer 2 · 2015-12-09T11:09:45.413

Adaptation of Python values to SQL types

To use the in syntax cast the list to a tuple:

list_of_ids = [1,2,3]
s_date = '2015-01-01'

query = """
    select *
    from foo
    where id in %s and start_date = %s
"""
print cursor.mogrify(query, (tuple(list_of_ids), s_date))
#cursor.execute(query, (tuple(list_of_ids), s_date))

Output:

select *
from foo
where id in (1, 2, 3) and start_date = '2015-01-01'

To pass the list without casting use the = any syntax:

query = """
    select *
    from foo
    where id = any (%s) and start_date = %s
"""
print cursor.mogrify(query, (list_of_ids, s_date))
#cursor.execute(query, (list_of_ids, s_date))

Output:

select *
from foo
where id = any (ARRAY[1, 2, 3]) and start_date = '2015-01-01'

Variables in SQL statement in Python

2 Answers2