SqlAlchemy with list in where clause

Question

my table in a database is as follow

Username    city        Type 
 Anna       Paris        abc 
 Marc       london       abc
 erica      rome         AF 
 Sara       Newyork      cbd 
 silvia      paris        AD

I have a list contains string values

typelist = {'abc', 'cbd'}

and i want to query my database using sqlalchemy , to get data from a table where a column type equals the values in the list :

Username    city        Type 
 Anna       Paris        abc 
 Marc       london       abc
 Sara       Newyork      cbd

im trying this code

sql = "SELECT * FROM table WHERE data IN :values" 
query = sqlalchemy.text(sql).bindparams(values=tuple(typelist))
conn.engine.execute(query)

but it return just one value from the typelist not all the list values .

Username    city        Type 
 Sara       Newyork      cbd

What's your code of reading values from engine.execute? maybe you read only the first value from returning cursor — inf581, Jan 15 '21 at 17:43

score 6 · Answer 1 · answered Jan 15 '21 at 14:53

sql = "SELECT * FROM table WHERE data IN :values" 
query = sqlalchemy.text(sql).bindparams(sqlalchemy.bindparam("values", expanding=True))
conn.engine.execute(query, {"values": typelist})

Reference: https://docs.sqlalchemy.org/en/13/core/sqlelement.html#sqlalchemy.sql.expression.bindparam.params.expanding

score -1 · Answer 2 · answered Jan 15 '21 at 14:36

-1

My solution will work but you will need to format your string like this

sql = "SELECT * FROM table WHERE data IN ('data1', 'data2', 'data3')"

No need to use bind param here. Use this if you dont get any proper solution

answered Jan 15 '21 at 14:36

Nix

203
1
3
14

firstly , i don't know the values in the list because its changed every hour , secondly, i have a lot of values , this solution will dosent work – khaoula Jan 15 '21 at 14:44
use @inf581 answer. it works best – Nix Jan 18 '21 at 06:01

Steve G · Answer 3 · 2021-01-15T15:16:58.197

-1

You could use a dynamic SQL approach where you create a string from your list values and add the string to your SELECT statement.

queryList = ['abc', 'def']


def list_to_string(inList):
    strTemp = """'"""
    for x in inList:
        strTemp += str(x) + """','"""
    return strTemp[:-2]

sql = """SELECT * FROM table WHERE data in (""" + list_to_string(queryList) + """)"""

print(sql)

edited Jan 15 '21 at 15:16

answered Jan 15 '21 at 14:59

Steve G

39
1
7

2

You could, but you shouldn't. This is the road to SQL injection. – Ilja Everilä Jan 15 '21 at 23:18

SqlAlchemy with list in where clause

3 Answers3