问题描述
我正在将mysql-connector与python结合使用，并有这样的查询:
I am using mysql-connector with python and have a query like this:
SELECT avg(downloadtime) FROM tb_npp where date(date) between %s and %s and host like %s",(s_date,e_date,"%" + dc + "%")
现在，如果我的变量"dc"是这样的列表:
NOw, if my variable 'dc' is a list like this:
dc = ['sjc','iad','las']
然后我有一个如下所示的mysql查询:
Then I have a mysql query like below:
SELECT avg(downloadtime) FROM tb_npp where date(date) = '2013-07-01' and substring(host,6,3) in ('sjc','las');
我的问题是，如何在我的python代码中编写此查询，该查询会将变量"dc"转换为列表?
My question is, how do I write this query in my python code which will convert my variable 'dc' to a list?
我尝试了以下查询，但出现错误:处理格式参数失败； "MySQLConverter"对象没有属性"_list_to_mysql"
I tried the below query but getting error: Failed processing format-parameters; 'MySQLConverter' object has no attribute '_list_to_mysql'
cursor3.execute("SELECT avg(downloadtime) FROM tb_npp where date(date) between %s and %s and substring(host,6,3) in %s",(s_date,e_date,dc))
有人可以告诉我我在做什么错吗?
Can somebody please tell me what I am doing wrong?
预先感谢
推荐答案
我不熟悉mysql-connector，但是它的行为似乎是.如果是这样，则需要使用一些字符串格式:
I'm not familiar with mysql-connector, but its behavior appears to be similar to MySQLdb in this regard. If that's true, you need to use a bit of string formatting:
sql = """SELECT avg(downloadtime) FROM tb_npp where date(date) = %s
and substring(host,6,3) in ({c})""".format(
c=', '.join(['%s']*len(dc)))
args = ['2013-07-01'] + dc
cursor3.execute(sql, args)
这篇关于mysql-connector python'IN'运算符存储为列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！