How to use a Python list in the WHERE clause of SQL

Question

I am trying to use a list in the WHERE clause of a SQL query. But it gives syntax error. Here is an example:

symbollist =['aa','bb'] 

SELECT symbol, protein_id FROM protein WHERE symbol in symbollist 

How to use list in the WHERE clause?

Answer 1

SQL does not accept Python list in the WHERE clause. That's why you are getting syntax error. You need to convert your list into a format that is allowed in the WHERE clause. Try the following code:

symbolList =['aa','bb','cc']

sql ='SELECT symbol, protein_id FROM protein WHERE symbol in ('

for symbol in symbolList[:-1]:
     sql = sql + "'" + symbol + "'"+ ','
sql = sql + "'" + symbolList[-1] + "')"
print (sql)

This will print:

SELECT symbol, protein_id FROM protein WHERE symbol in ('aa','bb','cc')