mysql-connector python 'IN' operator stored as list

Question

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

if you just run the query without using python does it work? — Drewdin
– Drewdin, Commented Jul 25, 2013 at 1:06

Community · Accepted Answer · 2017-05-23 12:33:18Z

14

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)

edited May 23, 2017 at 12:33

CommunityBot

11 silver badge

answered Jul 25, 2013 at 1:16

unutbu

886k197 gold badges1.9k silver badges1.7k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

wanab_geek Over a year ago

Ha.... THanks a lot.. :) it works, but if i have many args, can i just concatenate all of them to the args variable like: args = ['2013-07-01'] + ['2013-07-07'] + dc ?

unutbu Over a year ago

Yes, or: args = ['2013-07-01', '2013-07-07'] + dc

Stephen.lin Over a year ago

I don't agree with this answer，In fact, we don't have to do anything, pymsql has already done it。For example: SELECT avg(downloadtime) FROM tb_npp where date(date) = %s and substring(host,6,3) %s

Chad Befus · Accepted Answer · 2021-02-11 23:52:01Z

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]

Stephen.lin · Accepted Answer · 2023-02-16 11:23:33Z

0

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

That's it, that's it

edited Feb 16, 2023 at 11:23

answered Feb 16, 2023 at 7:37

Stephen.lin

1761 silver badge13 bronze badges

Collectives™ on Stack Overflow

mysql-connector python 'IN' operator stored as list

3 Answers 3

3 Comments

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Linked

Related