Passing a Python List through a SQL Statement on a Loop

Question

I am trying to figure out how to pass a list as a parameters through a where statement in SQL, I can not program what I am looking for, but below is what I am looking for.

This is what I would do for one parameter.... x = 1 sql = """Select t1,t2,t3,t4 from database where t1= ? """ cur.execute(sql,x)

Example of what I need

X = [1,2,3,4]
Select t1,t2,t3,t4 from database where t1= 1
Select t1,t2,t3,t4 from database where t1= 2
Select t1,t2,t3,t4 from database where t1= 3
Select t1,t2,t3,t4 from database where t1= 4

Example of what I am trying that isn't working....

X = [1,2,3,4]
sql = """Select Select t1,t2,t3,t4 from database where t1= ? """
example=[]
i = 0
for item in X:
    while i < len(x)
        row = cur.execute(sql,item)
        i +=1
        example.append(row)

You also need to clarify "not working" and the `while` loop with the `i` counter is confusing me. — roganjosh, Jan 15 '19 at 18:49
Can't test; `cur.execute("SELECT t1, t2, t3, t4 FROM database WHERE t1 IN ({})".format(', '.join(['?' for item in X])), X)` — roganjosh, Jan 15 '19 at 18:52
What error exactly do you get? "not working" is a bit too much generic. — Valentino, Jan 15 '19 at 19:20
Possible duplicate of [SQL IN operator using pyodbc and SQL Server](https://stackoverflow.com/questions/4819356/sql-in-operator-using-pyodbc-and-sql-server) — Gord Thompson, Jan 16 '19 at 11:59

Gord Thompson · Answer 1 · 2020-09-27T14:33:49.237

1

If all you're doing is looping through a list of IDs and appending a row to a list named example then you can just build a dynamic IN clause and retrieve the rows all at once:

x = [1, 2, 3, 4]
qmarks = ','.join('?' * len(x))  
print(qmarks)  # ?,?,?,?
sql = f"SELECT * FROM tablename WHERE t1 IN ({qmarks})"  
print(sql)  # SELECT * FROM tablename WHERE t1 IN (?,?,?,?)
example = crsr.execute(sql, x).fetchall()

edited Sep 27 '20 at 14:33

answered Jan 16 '19 at 12:28

Gord Thompson

116,920
32
215
418

score 0 · Answer 2 · answered Jan 15 '19 at 19:17

0

I think it should be cursor.execute("""Select t1, t2, t3, t4 from database where t1 in ('1', '2', '3', '4')""")

answered Jan 15 '19 at 19:17

FatmaT

255
1
9

Passing a Python List through a SQL Statement on a Loop

2 Answers2