Mysql-connector python 'IN' statement saved as a list

Question

I am using a 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 + "%") 

NOw if my variable 'dc' is a list like this:

  dc = ['sjc','iad','las'] 

Then I have a mysql query as shown below:

 SELECT avg(downloadtime) FROM tb_npp where date(date) = '2013-07-01' and substring(host,6,3) in ('sjc','las'); 

My question is: how do I write this query in my Python code that converts my 'dc' variable to a list?

I tried the following query but got an error: Failed processing format-parameters; The MySQLConverter object does not have the _list_to_mysql attribute

 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 someone please tell me what I am doing wrong?

Thanks in advance

Answer 1

I am not familiar with the mysql connector, but its behavior looks similar to MySQLdb in this regard. If this is 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)