gpt4 book ai didi

python - Python MySQLdb 子句的多个参数

转载 作者:行者123 更新时间:2023-11-29 13:29:40 24 4
gpt4 key购买 nike

下面是我正在使用的数据的一小部分。我可以按照我喜欢的方式格式化数据。变量“dc”内的数据由值“id1”和“id2”组成。我想要做的是能够为“dc”中的所有值发出一个 SELECT 语句。由于某种原因,无论我在“cursor.execute”语句或“format_strings”变量中尝试什么,我似乎都无法获得能够将两个变量传递给 MySQL 的正确代码。

有关如何格式化数据(“dc”)或代码以执行一条 SELECT 语句的评论/建议将非常有帮助。

results = ()

dc = ['103,4770634', '42,427752', '64,10122045', '42,13603629', '42,25516425', '103,2748102', '42,1966402', '42,30262834', '42,6667711', '18,13737683', '42,28921168', '42,26076925', '103,3733654', '42,23313527', '64,3307344', '103,3973533', '42,6360982', '48,11846077', '103,3775309', '64,10122050', '42,1965119', '103,4265810', '103,3971645', '103,4962583', '103,689615', '42,22834366', '103,761655', '95,1184', '64,9594482', '42,22855603', '48,8654764', '103,4226756', '42,23366982', '103,3897036', '42,11339650', '101,6369', '42,25830920', '103,5009291', '42,29238961', '59,6299475', '42,22931663', '42,25839056', '43,11864458', '43,41346192', '103,4261645', '42,3747082', '103,4795050', '42,9417503', '103,4245623', '42,61431911']

try:
format_strings = ','.join(['%s%s'] * len(dc))
cursor.execute("SELECT * FROM tbl1 WHERE id1=(%s) AND id2=(%s)" % format_strings, (dc))
res = cursor.fetchall()
results = results + res
except Exception, e:
print e

更新

根据@lecumia和@beroe在下面发布的内容,我想出了以下内容,虽然不那么优雅,可能也不是 super 高效,但它确实有效。

results = ()
id1 = []
id2 = []

dc = ['103,4770634', '42,427752', '64,10122045', '42,13603629', '42,25516425']
for d in dc:
id1.append(d.split(',')[0])
id2.append(d.split(',')[1])

try:
sql = "SELECT * FROM DomainEmails WHERE email_id IN (%s) AND domain_id IN (%s)"
in_id1 = "'" + "', '".join(id1) + "'"
in_id2 = "'" + "', '".join(id2) + "'"
sql = sql % (in_id1, in_id2)
cursor.execute(sql)
res = cursor.fetchall()
results = results + res
except Exception, e:
print e

实际查询

SELECT * FROM tbl1 WHERE id1 IN ('103', '42', '64', '42', '42') AND id2 IN ('4770634', '427752', '10122045', '13603629', '25516425')

查询结果

这些符合我的预期:

{'id1': 42L, 'id2': 427752L, 'firstseen': datetime.date(2010, 5, 6)}
{'id1': 42L, 'id2': 427752L, 'firstseen': datetime.date(2011, 5, 2)}
{'id1': 42L, 'id2': 13603629L, 'firstseen': datetime.date(2011, 3, 21)}
{'id1': 42L, 'id2': 13603629L, 'firstseen': datetime.date(2011, 4, 17)}

最佳答案

基于

Executing "SELECT ... WHERE ... IN ..." using MySQLdb

results = ()

dc = ['103,4770634', '42,427752', '64,10122045', '42,13603629', '42,25516425', '103,2748102', '42,1966402', '42,30262834', '42,6667711', '18,13737683', '42,28921168', '42,26076925', '103,3733654', '42,23313527', '64,3307344', '103,3973533', '42,6360982', '48,11846077', '103,3775309', '64,10122050', '42,1965119', '103,4265810', '103,3971645', '103,4962583', '103,689615', '42,22834366', '103,761655', '95,1184', '64,9594482', '42,22855603', '48,8654764', '103,4226756', '42,23366982', '103,3897036', '42,11339650', '101,6369', '42,25830920', '103,5009291', '42,29238961', '59,6299475', '42,22931663', '42,25839056', '43,11864458', '43,41346192', '103,4261645', '42,3747082', '103,4795050', '42,9417503', '103,4245623', '42,61431911']

try:
sql = "SELECT * FROM tbl1 WHERE id1 in (%s) AND id2 in (%s)"
in_ids = ', '.join(map(lambda x: '%s', dc))
in_ids = in_ids % tuple(dc)
sql = sql % (in_ids, in_ids)
cursor.execute(sql)
res = cursor.fetchall()
results = results + res
except Exception, e:
print e

结果

SELECT * FROM tbl1 WHERE id1 in (103,4770634, 42,427752, 64,10122045, 42,13603629, 42,25516425, 103,2748102, 42,1966402, 42,30262834, 42,6667711, 18,13737683, 42,28921168, 42,26076925, 103,3733654, 42,23313527, 64,3307344, 103,3973533, 42,6360982, 48,11846077, 103,3775309, 64,10122050, 42,1965119, 103,4265810, 103,3971645, 103,4962583, 103,689615, 42,22834366, 103,761655, 95,1184, 64,9594482, 42,22855603, 48,8654764, 103,4226756, 42,23366982, 103,3897036, 42,11339650, 101,6369, 42,25830920, 103,5009291, 42,29238961, 59,6299475, 42,22931663, 42,25839056, 43,11864458, 43,41346192, 103,4261645, 42,3747082, 103,4795050, 42,9417503, 103,4245623, 42,61431911) AND id2 in (103,4770634, 42,427752, 64,10122045, 42,13603629, 42,25516425, 103,2748102, 42,1966402, 42,30262834, 42,6667711, 18,13737683, 42,28921168, 42,26076925, 103,3733654, 42,23313527, 64,3307344, 103,3973533, 42,6360982, 48,11846077, 103,3775309, 64,10122050, 42,1965119, 103,4265810, 103,3971645, 103,4962583, 103,689615, 42,22834366, 103,761655, 95,1184, 64,9594482, 42,22855603, 48,8654764, 103,4226756, 42,23366982, 103,3897036, 42,11339650, 101,6369, 42,25830920, 103,5009291, 42,29238961, 59,6299475, 42,22931663, 42,25839056, 43,11864458, 43,41346192, 103,4261645, 42,3747082, 103,4795050, 42,9417503, 103,4245623, 42,61431911)

关于python - Python MySQLdb 子句的多个参数,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/19647786/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com