+2 votes
in Databases by (14.2k points)

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?

1 Answer

0 votes
by (22k points)
edited by

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

...