我必须传递多个id到我的SQL原始查询,这些id将作为参数传递。我是这样做的:
itlist=[6009,[5989,5796,5793],5788,5750]
for it in itlist:
cursor.execute("select branch_tag from builds where id in (%d);"%[it])
data1=cursor.fetchall()
for record in data1:
print record[0]
这给了我一个错误,我不知道如何传递该列表的SQL查询。任何帮助都将不胜感激……提前感谢
将列表平展为newlist并将其传递给您的查询:
def flatten(foo):
newl = []
for x in foo:
if hasattr(x, '__iter__'):
for y in flatten(x):
newl.append(y)
else:
newl.append(x)
return newl
itlist=[6009,[5989,5796,5793],5788,5750]
newitlist = flatten(itlist)
for it in newitlist:
cursor.execute("select branch_tag from builds where id in (%d);"%[it])
data1=cursor.fetchall()
for record in data1:
print record[0]
改编自Haim引用的答案,但适用于您有嵌套列表的特殊情况:
itlist=[6009,[5989,5796,5793],5788,5750]
for it in itlist:
# This ensures that 'it' is a list even if it is just a single element
if type(it) != list:
it = [it]
format_strings = ','.join(['%s'] * len(it))
cursor.execute("select branch_tag from builds where id in (%s)" % format_strings,
tuple(it))
data1=cursor.fetchall()
for record in data1:
print record[0]
查询字符串中有%s
而不是%d
的原因是因为您实际上要将其替换为一堆%s
's(因此%s
或%s,%s,%s
),以适应您试图传递的可变数量的id。
在你的例子中,第一个查询字符串是:
"select branch_tag from builds where id in (6009)"
下一个是:
"select branch_tag from builds where id in (5989,5796,5793)"
当然,假设itlist
是嵌套的,就像你的问题一样。如果不是,那么这个答案的大部分仍然适用:
itlist=[6009,5989,5796,5793,5788,5750]
format_strings = ','.join(['%s'] * len(itlist))
cursor.execute("select branch_tag from builds where id in (%s)" % format_strings,
tuple(itlist))
data1=cursor.fetchall()
for record in data1:
print record[0]
将每个元素转换为一个列表,然后按照以下方式使用python基础知识:
itlist=[[6009],[5989,5796,5793],[5750]]
for it in itlist:
cursor.execute("select branch_tag from builds where id=(select max(id) from builds where id in ("+ str(it)[1:-1] +"));")
data1=cursor.fetchall()
for record in data1:
print record[0]