python list in sql query as parameter [duplicate]

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)

Easiest way is to turn the list to tuple first

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

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

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

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)