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

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)

st0ne · Answer 1 · 2013-04-03T09:57:51.210

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 03 '13 at 09:57

answered Apr 03 '13 at 09:43

st0ne

4,165
2
23
24

It seems that there is an extra quotation in your comment right?It isnot working with this either – UserYmY Apr 03 '13 at 09:47
may you can add a extra debug line which prints the resulting sql query. then fire the resulting sql in a mysql shell. – st0ne Apr 03 '13 at 09:52
It is a syntax problem now. Is the syntax of the code you wrote correct? – UserYmY Apr 03 '13 at 09:54
i didn't try against a db. but see the python shell example above. – st0ne Apr 03 '13 at 09:58
3

There are better ways. Yours is vulnerable to SQL injection. – glglgl Apr 03 '13 at 09:59

score 1 · Accepted Answer · answered Apr 03 '13 at 10:01

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 03 '13 at 10:01

glglgl

89,107
13
149
217

the result still is as empty () – UserYmY Apr 03 '13 at 11:04

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

2 Answers2