How to specify the quote to use for strings in a python list for mysql?

Question

My list is as follows:

['1.0.176.20:14386', '1.0.179.115:20208', '1.0.218.241:14699', '1.0.242.3:27376', '1.1.218.165:24513']

from:

sessions = [session[0].replace("'", '"') for session in sessions]

But in mysql:

cur.execute("""
        SELECT *
        FROM logs
        WHERE logs.session IN %s
        """, (
            sessions,
        ))

it returns nothing because of query being:

SELECT *
        FROM logs
        WHERE logs.session IN ("'1.0.176.20:14386'", "'1.0.179.115:20208'")

How do I fix this in mysql/list?

Salem · Accepted Answer · 2014-07-30 18:27:56Z

1

When you format that string what you will get is

... WHERE logs.session IN ['1.0.176.20:14386', '1.0.179.115:20208', (...)]

because that is the string representation of a list ([...]).

You need to do something like this:

sessions_in = ",".join(map(lambda x: "'{0}'".format(x), sessions))
cur.execute("""
    SELECT *
    FROM logs
    WHERE logs.session IN ( {0} )
    """.format(sessions_in))

It will create a string by joining all the list items with a , between them. As this would only create a single string with all the elements inside it ("1,2,3,(...)"), you need to transform each string into a quoted string before joining them. That's what that map will do, giving you:

"'1.0.176.20:14386','1.0.179.115:20208','1.0.218.241:14699','1.0.242.3:27376','1.1.218.165:24513'"

edited Jul 30, 2014 at 18:27

answered Jul 29, 2014 at 13:59

Salem

13k4 gold badges37 silver badges56 bronze badges

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

1 Comment

Tjorriemorrie Over a year ago

Can you perhaps elaborate a bit more?

Collectives™ on Stack Overflow

How to specify the quote to use for strings in a python list for mysql?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related