在循环中通过 SQL 语句传递 Python 列表 [英] Passing a Python List through a SQL Statement on a Loop

问题描述

我想弄清楚如何通过 SQL 中的 where 语句将列表作为参数传递，我无法编写我要查找的内容，但下面是我要查找的内容.

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.

这就是我对一个参数所做的......<代码>x = 1sql = """Select t1,t2,t3,t4 from database where t1= ? """cur.execute(sql,x)

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)

我需要的例子

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)

推荐答案

如果您所做的只是遍历 ID 列表并将一行附加到名为 example 的列表中，那么您只需构建一个动态 IN 子句并一次检索所有行:

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

这篇关于在循环中通过 SQL 语句传递 Python 列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！