Run a SQL query using a Python list

Question

I have a list with strings:

list = ['abc', 'cbd', 'bdc']

I want to use it as an input within an SQL query like:

query=('SELECT * from table where column1 in list')

What is the best way to do it in order for the SQL to run for these 3 items of the list?

Possible duplicate of [python list in sql query as parameter](https://stackoverflow.com/questions/283645/python-list-in-sql-query-as-parameter) — DannyMoshe, Mar 20 '19 at 15:54

score 2 · Accepted Answer · answered Mar 20 '19 at 16:06

It is generally a bad idea to generate SQL text like this (this is a brief explanation why). Common database access libraries will do the query parameter substitution for you (psycopg for PostgreSQL and MySQLdb for MySQL both do this, not sure about others).

That said, if you are very-very sure that the values you pass in the array are safe, you could do something like

my_list =['abc', 'cbd', 'bdc'] # do not call your variable list, list is a type
query = 'SELECT * from table where column1 in {}'.format(tuple(my_list))

score 0 · Answer 2 · answered Mar 20 '19 at 15:54

0

First, you could transform the list in a string and then you can add it to your query:

My_list =['abc', 'cbd', 'bdc']
slist=" '"
for i in My_list:
      slist+= i.strip()+"', '"
slist=slist[:-3]
query='''SELECT * from table where column1 in (%s)'''%slist

answered Mar 20 '19 at 15:54

Nate

856
6
5

Thanks but it stills reads the last comma, so it gives an error. – Alexis Mar 20 '19 at 16:04

Run a SQL query using a Python list

2 Answers2