Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

mysql-connector python 'IN' operator stored as list

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 + "%")

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

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

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');

My question is, how do I write this query in my python code which will convert my variable 'dc' to a list?

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?

Thanks in advance

like image 699
wanab_geek Avatar asked Jul 25 '13 01:07

wanab_geek


People also ask

Which connector connects MySQL to Python?

Python needs a MySQL driver to access the MySQL database. In this tutorial we will use the driver "MySQL Connector". We recommend that you use PIP to install "MySQL Connector". PIP is most likely already installed in your Python environment.

What is Mycursor MYDB cursor ()?

mycursor = mydb.cursor() #cursor created. #creating database with name classdb. mycursor.execute( "CREATE DATABASE classdb;" ) Create New Database. After executing the above code, the above change can be seen in the database using phpMyAdmin.


2 Answers

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)
like image 129
unutbu Avatar answered Oct 10 '22 04:10

unutbu


As an alternative to @unutbu's answer which is specific to using mysql-connector:

cursor.execute(
    "SELECT thing "
    "    FROM table "
    "    WHERE some_col > %(example_param)s " 
    "    AND id IN ({li})".format(li=", ".join(list_of_values)),
    params={
        "example_param": 33
    })

If you try to move the joined list into a param (like example param) it may complain because mysql-connector interprets the values as strings.

If your list isn't made up of things that are string-format-able (like integers) by default then in your join statement replace list_of_values with:

[str(v) for v in list_of_values]   
like image 32
Chad Befus Avatar answered Oct 10 '22 03:10

Chad Befus