Python list in SQL query as parameter [duplicate]

Question

This question already has answers here:

Imploding a list for use in a Python MySQL IN clause (11 answers)

Closed 4 years ago.

I have a Python list, say

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?

• David Wei: l1 = ['a'], l2 = ['a', 'b'], How do I generate a statement like this, "SELECT * FROM table_name WHERE col_name IN ['a'];" , I've tried the answer and it doesn't work
• David Wei: l = [1,5,8] l_str = str(l).replace( '[', '(' ).replace( ']', ')' )

Answer 1

Answers so far have been templating the values into a plain SQL string. That's absolutely fine for integers, but if we wanted to do it for strings we get the escaping issue.

Here's a variant using a parameterised query that would work for both:

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)

• ThiefMaster: ','.join(placeholder * len(l)) would be a bit shorter while still readable imho
• bobince: @Thiefmaster: yes, would have to be ([placeholder]*len(l)) in the general case as the placeholder may be multiple characters.
• Alessandro Dentella: Why do you want to join "by hand" rather than leave this work to the dbapi? the key is to use a tuple rather than a list...
• toto_tico: Based on this answerm here is a single line solution for Python 3.6 cursor.execute(f'SELECT name FROM students WHERE id IN ({','.join('?' for _ in l)})', l). @bobince, you should also remark in your solution that using ? is the safe way to go in terms of avoid SQL injection. There are a lot of answers here that are vulnerable, basically any that concatenates strings in python.
• Paul Coccoli: Reading these comments and the other answers, I see why there have been so many SQL injection vulnerabilities. Almost no one seems to care (until they're pwned).

Answer 2

Easiest way is to turn the list to tuple first

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

• Doubledown: As stated by @renstrm, this doesn't work if l just contains one element, you'll end up with id IN (1,). Which is a syntax error.
• Boris: @Amirhos Imani and what if you don't know how many elements you have?
• Amir Imani: @Boris if you can't guarantee that your input is always a list, you can do something similar to this one liner stackoverflow.com/questions/38821586/… before passing the argument to your query
• Bacon Bits: This, like most answers here other than the accepted one, is vulnerable to SQL injection and should not be used.
• irene: @BaconBits Fair warning, but for some applications where SQL injection is not an issue (for example, analysis of a database where I am the only user), this will do.

Answer 3

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)

enter image description here

I hope this helped !!!

• Peter Lithammer: This doesn't work if l just contains one element, you'll end up with id IN (1,). Which is a syntax error.
• Alessandro Dentella: If l only contains 1 element be sure it's a tuple and it WILL work. This solution is clearer in my opinion than the accepted one.
• Никита Конин: But it still won't work if tuple is empty, so there must be additional check before query execution.
• Nick Chammas: This doesn't work for me with sqlite3. What library did you test this against?
• Anish Sana: Good solution but @renstrm and Никита-Конин are right about requiring additional checks for when the tuple has a single element or no elements.

Answer 4

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

• S.Lott: :-( Python SQLite binding doesn't support sequences.
• Blair Conrad: Oh? I didn't realize. Then again, I didn't realize we were going for a SQLite binding solution.
• S.Lott: Sorry, not suggesting or implying SQLite -- just sad that bindings for lists don't work there.

Answer 5

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)

• Nick Chammas: Since this approach doesn't use database parameter substitution, it exposes you to SQL injection attacks. The % being used here is just plain Python string formatting.

Answer 6

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

Answer 7

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.

Answer 8

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)

Answer 9

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]) + ")"

• theruss: My problem was with parameterised queries. The injected list param, always ended up having its values surrounded by quotes in the resulting SQL IN clauses which was confusing as hell. The tuple() idea worked. It works because DJango translates that into a string format suited to an SQL IN() clause. It was this that didn't click for me when several answers suggested using the tuple.

Answer 10

a simpler solution - convert list to string, remove square brackets and add round brackets:

lst = [1,2,3,a,b,c]
query = f"""SELECT * FROM table WHERE IN ({str(lst)[1:-1]})"""

Answer 11

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 ({str(l)[1:-1]})
'''
print(sql)

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

For a list of dates in Oracle, this worked

l = ['2020-11-24', '2020-12-28']
dates_str = ','.join([f'DATE {repr(s)}' for s in l])
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')

If you need to get the list of dates from a pandas df, it's df['date'].dt.strftime('%Y-%m-%d').unique()

And since I often needed it too, adding columns from a list

# single list
f'select {','.join(l)}'
# multi list in different tables
sql_cmd = f'''
select {','.join(f't1.{s}' for s in l1)},
{','.join(f't1.{s}' for s in l2)},
{','.join(f't2.{s}' for s in l3)} 
'''

• Abdul Aziz Barkat: WARNING: Don't use this it's vulnerable to SQL injection.
• citynorman: Fine don't put it in a web app with arbitrary user input, i just use it for my own data pipelines where i control the input
• citynorman: @Abdul got a better solution that works for strings but is not vulnerable to SQL injection?
• Abdul Aziz Barkat: @citynorman there are answers on this very question that are safe, see this one for example.

Answer 12

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

• Anurag: This is not better solution because of sql injection.
• Abdul Aziz Barkat: WARNING: Don't use this it's vulnerable to SQL injection. (Restating above comment since it's not stated clearly)

Answer 13

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.

Answer 14

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

Answer 15

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(']',')') )

Answer 16

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)