How to address a python list in MySQL query with "WHERE"? [duplicate]

Question

I have the below code in which I want my MySQL query select those values in the 'name' column that are identical to the value in 'filenames' python list. I keep get an empty () as for the result. Can anyone help me with this?

import MySQLdb
import fnmatch
import os
import pprint

connect = MySQLdb.connect(host = "123.5.4.4", port=3366, user = "me", passwd="*********", db="data2")

filenames=[]
maindict = {}  

for root, dirnames, filenames in os.walk('d:/data1'):  
    for filename in fnmatch.filter(filenames, '*.txt'):   
       filenames.append(filename)

with connect:            
    cur= connect.cursor()            
    cur.execute("SELECT version,name FROM data2.files WHERE name IN ('filenames'.join('')" )
    result = cur.fetchall()

pprint.pprint(result)

glglgl · Accepted Answer · 2013-04-03 10:01:26Z

1

You should use

cur.execute(
    "SELECT version,name FROM data2.files WHERE name IN (" +
    ",".join(("%s",) * len(filenames)) + 
    ")",
    tuple(filenames)
)

This creates a SQL string of the form

WHERE name IN (%s,%s,%s,%s)

with as many %ss you need and makes the MySQL module replace them with the filenames data, taking care of quoting as needed.

answered Apr 3, 2013 at 10:01

glglgl

91.5k13 gold badges157 silver badges230 bronze badges

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

1 Comment

UserYmY Over a year ago

the result still is as empty ()

st0ne · Accepted Answer · 2013-04-03 09:57:51Z

1

replace:

cur.execute("SELECT version,name FROM data2.files WHERE name IN ('filenames'.join('')" )

with:

cur.execute("SELECT version,name FROM data2.files WHERE name IN ('%s')" % "','".join(filenames) )

in python shell:

>>> filenames=['file1', 'file2']
>>> query = "SELECT version,name FROM data2.files WHERE name IN ('%s')" % "','".join(filenames)
>>> print query
SELECT version,name FROM data2.files WHERE name IN ('file1','file2')
>>>

edited Apr 3, 2013 at 9:57

answered Apr 3, 2013 at 9:43

st0ne

4,2752 gold badges26 silver badges25 bronze badges

5 Comments

UserYmY Over a year ago

It seems that there is an extra quotation in your comment right?It isnot working with this either

st0ne Over a year ago

may you can add a extra debug line which prints the resulting sql query. then fire the resulting sql in a mysql shell.

UserYmY Over a year ago

It is a syntax problem now. Is the syntax of the code you wrote correct?

st0ne Over a year ago

i didn't try against a db. but see the python shell example above.

glglgl Over a year ago

There are better ways. Yours is vulnerable to SQL injection.

Collectives™ on Stack Overflow

How to address a python list in MySQL query with "WHERE"? [duplicate]

2 Answers 2

1 Comment

5 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Linked

Related