Sql query to python list

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?

User12345

4,71512 gold badges49 silver badges102 bronze badges

asked Nov 12, 2008 at 11:18

Mohit RankaMohit Ranka

3,98312 gold badges40 silver badges41 bronze badges

2

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)

answered Nov 12, 2008 at 12:30

16

Easiest way is to turn the list to tuple first

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

answered Apr 5, 2018 at 18:51

Amir ImaniAmir Imani

2,7012 gold badges20 silver badges23 bronze badges

13

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

answered Nov 22, 2016 at 9:01

ALLSYEDALLSYED

1,43517 silver badges14 bronze badges

9

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

answered Nov 12, 2008 at 11:52

Blair ConradBlair Conrad

224k25 gold badges132 silver badges111 bronze badges

3

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)

answered Nov 12, 2008 at 11:49

gimelgimel

80.1k10 gold badges74 silver badges104 bronze badges

1

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

answered Nov 20, 2010 at 14:35

jimharkjimhark

4,8581 gold badge25 silver badges26 bronze badges

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.

answered Dec 18, 2019 at 20:56

Sam MasonSam Mason

13.6k1 gold badge35 silver badges51 bronze badges

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

answered Nov 26, 2014 at 9:16

XimixXimix

3912 silver badges9 bronze badges

1

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

answered Sep 23, 2020 at 9:13

Greed RulerGreed Ruler

1491 silver badge14 bronze badges

1

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

answered Nov 25, 2020 at 4:27

citynormancitynorman

4,4202 gold badges34 silver badges38 bronze badges

1

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.

answered Feb 12, 2019 at 9:21

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)

answered May 26, 2020 at 14:23

user13476428user13476428

611 gold badge1 silver badge3 bronze badges

a simpler solution:

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

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

answered Apr 22, 2019 at 22:59

Omar OmeiriOmar Omeiri

1,0651 gold badge12 silver badges26 bronze badges

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)

answered Aug 27, 2020 at 16:48

rajraj

1661 silver badge9 bronze badges

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

answered Aug 18, 2016 at 18:02

pgalileapgalilea

2412 silver badges5 bronze badges

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

answered Jun 28, 2018 at 22:08

How do I pass a list of SQL queries in Python?

Can you query SQL with Python?

How do I add a query to a SQL list?

How do I pass a list of values in SQL?