python list in sql query as parameter

Question

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?

Answer 1

Following is a variant using the parameterized query, this will work fine:

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)

Note: If you want to do it for strings then you'll get the escaping issue.