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
– roganjosh, Commented Jan 15, 2019 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
– roganjosh, Commented Jan 15, 2019 at 18:52
What error exactly do you get? "not working" is a bit too much generic. — Valentino
– Valentino, Commented Jan 15, 2019 at 19:20

Gord Thompson · Accepted Answer · 2020-09-27 14:33:49Z

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, 2020 at 14:33

answered Jan 16, 2019 at 12:28

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

FatmaT · Accepted Answer · 2019-01-15 19:17:05Z

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, 2019 at 19:17

FatmaT

2551 silver badge9 bronze badges

Collectives™ on Stack Overflow

Passing a Python List through a SQL Statement on a Loop

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related