[python] python list in sql query as parameter

I have a python list, say l

l = [1,5,8]

I want to write a sql query to get the data for all the elements of the list, say

select name from students where id = |IN THE LIST l|

How do I accomplish this?

Easiest way is to turn the list to tuple first

t = tuple(l)
query = "select name from studens where id IN {}".format(t)

This Will Work If Number of Values in List equals to 1 or greater than 1

t = str(tuple(l))
if t[-2] == ',':
   t= t.replace(t[-2],"")
query = "select name from studens where id IN {}".format(t)

placeholders= ', '.join("'{"+str(i)+"}'" for i in range(len(l)))
query="select name from students where id (%s)"%placeholders
query=query.format(*l)
cursor.execute(query)

This should solve your problem.

I like bobince's answer:

placeholder= '?' # For SQLite. See DBAPI paramstyle.
placeholders= ', '.join(placeholder for unused in l)
query= 'SELECT name FROM students WHERE id IN (%s)' % placeholders
cursor.execute(query, l)

But I noticed this:

placeholders= ', '.join(placeholder for unused in l)

Can be replaced with:

placeholders= ', '.join(placeholder*len(l))

I find this more direct if less clever and less general. Here l is required to have a length (i.e. refer to an object that defines a __len__ method), which shouldn't be a problem. But placeholder must also be a single character. To support a multi-character placeholder use:

placeholders= ', '.join([placeholder]*len(l))

For example, if you want the sql query:

select name from studens where id in (1, 5, 8)

What about:

my_list = [1, 5, 8]
cur.execute("select name from studens where id in %s" % repr(my_list).replace('[','(').replace(']',')') )

This uses parameter substitution and takes care of the single value list case:

l = [1,5,8]

get_operator = lambda x: '=' if len(x) == 1 else 'IN'
get_value = lambda x: int(x[0]) if len(x) == 1 else x

query = 'SELECT * FROM table where id ' + get_operator(l) + ' %s'

cursor.execute(query, (get_value(l),))

I like bobince's answer:

placeholder= '?' # For SQLite. See DBAPI paramstyle.
placeholders= ', '.join(placeholder for unused in l)
query= 'SELECT name FROM students WHERE id IN (%s)' % placeholders
cursor.execute(query, l)

But I noticed this:

placeholders= ', '.join(placeholder for unused in l)

Can be replaced with:

placeholders= ', '.join(placeholder*len(l))

I find this more direct if less clever and less general. Here l is required to have a length (i.e. refer to an object that defines a __len__ method), which shouldn't be a problem. But placeholder must also be a single character. To support a multi-character placeholder use:

placeholders= ', '.join([placeholder]*len(l))

This Will Work If Number of Values in List equals to 1 or greater than 1

t = str(tuple(l))
if t[-2] == ',':
   t= t.replace(t[-2],"")
query = "select name from studens where id IN {}".format(t)

l = [1] # or [1,2,3]

query = "SELECT * FROM table WHERE id IN :l"
params = {'l' : tuple(l)}
cursor.execute(query, params)

The :var notation seems simpler. (Python 3.7)

a simpler solution:

lst = [1,2,3,a,b,c]

query = f"""SELECT * FROM table WHERE IN {str(lst)[1:-1}"""

string.join the list values separated by commas, and use the format operator to form a query string.

myquery = "select name from studens where id in (%s)" % ",".join(map(str,mylist))

(Thanks, blair-conrad)

To run a select from where field is in list of strings (instead of int), as per this question use repr(tuple(map(str, l))). Full example:

l = ['a','b','c']
sql = f'''

select name 
from students 
where id in {repr(tuple(map(str, l)))}
'''
print(sql)

Returns: select name from students where id in ('a', 'b', 'c')

For a list of dates in Oracle, this worked

dates_str = ','.join([f'DATE {repr(s)}' for s in ['2020-11-24', '2020-12-28']])
dates_str = f'({dates_str})'

sql_cmd = f'''
select *
from students 
where 
and date in {dates_str}
'''

Returns: select * from students where and date in (DATE '2020-11-24',DATE '2020-12-28')

The SQL you want is

select name from studens where id in (1, 5, 8)

If you want to construct this from the python you could use

l = [1, 5, 8]
sql_query = 'select name from studens where id in (' + ','.join(map(str, l)) + ')'

The map function will transform the list into a list of strings that can be glued together by commas using the str.join method.

Alternatively:

l = [1, 5, 8]
sql_query = 'select name from studens where id in (' + ','.join((str(n) for n in l)) + ')'

if you prefer generator expressions to the map function.

UPDATE: S. Lott mentions in the comments that the Python SQLite bindings don't support sequences. In that case, you might want

select name from studens where id = 1 or id = 5 or id = 8

Generated by

sql_query = 'select name from studens where ' + ' or '.join(('id = ' + str(n) for n in l))

Solution for @umounted answer, because that broke with a one-element tuple, since (1,) is not valid SQL.:

>>> random_ids = [1234,123,54,56,57,58,78,91]
>>> cursor.execute("create table test (id)")
>>> for item in random_ids:
    cursor.execute("insert into test values (%d)" % item)
>>> sublist = [56,57,58]
>>> cursor.execute("select id from test where id in %s" % str(tuple(sublist)).replace(',)',')'))
>>> a = cursor.fetchall()
>>> a
[(56,), (57,), (58,)]

Other solution for sql string:

cursor.execute("select id from test where id in (%s)" % ('"'+'", "'.join(l)+'"'))

If you're using PostgreSQL with the Psycopg2 library you can let its tuple adaption do all the escaping and string interpolation for you, e.g:

ids = [1,2,3]
cur.execute(
  "SELECT * FROM foo WHERE id IN %s",
  [tuple(ids)])

i.e. just make sure that you're passing the IN parameter as a tuple. if it's a list you can use the = ANY array syntax:

cur.execute(
  "SELECT * FROM foo WHERE id = ANY (%s)",
  [list(ids)])

note that these both will get turned into the same query plan so you should just use whichever is easier. e.g. if your list comes in a tuple use the former, if they're stored in a list use the latter.

Dont complicate it, Solution for this is simple.

l = [1,5,8]

l = tuple(l)

params = {'l': l}

cursor.execute('SELECT * FROM table where id in %(l)s',params)

I hope this helped !!!

This uses parameter substitution and takes care of the single value list case:

l = [1,5,8]

get_operator = lambda x: '=' if len(x) == 1 else 'IN'
get_value = lambda x: int(x[0]) if len(x) == 1 else x

query = 'SELECT * FROM table where id ' + get_operator(l) + ' %s'

cursor.execute(query, (get_value(l),))

a simpler solution:

lst = [1,2,3,a,b,c]

query = f"""SELECT * FROM table WHERE IN {str(lst)[1:-1}"""

Easiest way is to turn the list to tuple first

t = tuple(l)
query = "select name from studens where id IN {}".format(t)

The SQL you want is

select name from studens where id in (1, 5, 8)

If you want to construct this from the python you could use

l = [1, 5, 8]
sql_query = 'select name from studens where id in (' + ','.join(map(str, l)) + ')'

The map function will transform the list into a list of strings that can be glued together by commas using the str.join method.

Alternatively:

l = [1, 5, 8]
sql_query = 'select name from studens where id in (' + ','.join((str(n) for n in l)) + ')'

if you prefer generator expressions to the map function.

UPDATE: S. Lott mentions in the comments that the Python SQLite bindings don't support sequences. In that case, you might want

select name from studens where id = 1 or id = 5 or id = 8

Generated by

sql_query = 'select name from studens where ' + ' or '.join(('id = ' + str(n) for n in l))

If you're using PostgreSQL with the Psycopg2 library you can let its tuple adaption do all the escaping and string interpolation for you, e.g:

ids = [1,2,3]
cur.execute(
  "SELECT * FROM foo WHERE id IN %s",
  [tuple(ids)])

i.e. just make sure that you're passing the IN parameter as a tuple. if it's a list you can use the = ANY array syntax:

cur.execute(
  "SELECT * FROM foo WHERE id = ANY (%s)",
  [list(ids)])

note that these both will get turned into the same query plan so you should just use whichever is easier. e.g. if your list comes in a tuple use the former, if they're stored in a list use the latter.

The SQL you want is

select name from studens where id in (1, 5, 8)

If you want to construct this from the python you could use

l = [1, 5, 8]
sql_query = 'select name from studens where id in (' + ','.join(map(str, l)) + ')'

The map function will transform the list into a list of strings that can be glued together by commas using the str.join method.

Alternatively:

l = [1, 5, 8]
sql_query = 'select name from studens where id in (' + ','.join((str(n) for n in l)) + ')'

if you prefer generator expressions to the map function.

UPDATE: S. Lott mentions in the comments that the Python SQLite bindings don't support sequences. In that case, you might want

select name from studens where id = 1 or id = 5 or id = 8

Generated by

sql_query = 'select name from studens where ' + ' or '.join(('id = ' + str(n) for n in l))

l = [1] # or [1,2,3]

query = "SELECT * FROM table WHERE id IN :l"
params = {'l' : tuple(l)}
cursor.execute(query, params)

The :var notation seems simpler. (Python 3.7)

For example, if you want the sql query:

select name from studens where id in (1, 5, 8)

What about:

my_list = [1, 5, 8]
cur.execute("select name from studens where id in %s" % repr(my_list).replace('[','(').replace(']',')') )

Solution for @umounted answer, because that broke with a one-element tuple, since (1,) is not valid SQL.:

>>> random_ids = [1234,123,54,56,57,58,78,91]
>>> cursor.execute("create table test (id)")
>>> for item in random_ids:
    cursor.execute("insert into test values (%d)" % item)
>>> sublist = [56,57,58]
>>> cursor.execute("select id from test where id in %s" % str(tuple(sublist)).replace(',)',')'))
>>> a = cursor.fetchall()
>>> a
[(56,), (57,), (58,)]

Other solution for sql string:

cursor.execute("select id from test where id in (%s)" % ('"'+'", "'.join(l)+'"'))

Just use inline if operation with tuple function:

query = "Select * from hr_employee WHERE id in " % tuple(employee_ids) if len(employee_ids) != 1 else "("+ str(employee_ids[0]) + ")"

To run a select from where field is in list of strings (instead of int), as per this question use repr(tuple(map(str, l))). Full example:

l = ['a','b','c']
sql = f'''

select name 
from students 
where id in {repr(tuple(map(str, l)))}
'''
print(sql)

Returns: select name from students where id in ('a', 'b', 'c')

For a list of dates in Oracle, this worked

dates_str = ','.join([f'DATE {repr(s)}' for s in ['2020-11-24', '2020-12-28']])
dates_str = f'({dates_str})'

sql_cmd = f'''
select *
from students 
where 
and date in {dates_str}
'''

Returns: select * from students where and date in (DATE '2020-11-24',DATE '2020-12-28')

Just use inline if operation with tuple function:

query = "Select * from hr_employee WHERE id in " % tuple(employee_ids) if len(employee_ids) != 1 else "("+ str(employee_ids[0]) + ")"

string.join the list values separated by commas, and use the format operator to form a query string.

myquery = "select name from studens where id in (%s)" % ",".join(map(str,mylist))

(Thanks, blair-conrad)