Python Prepared Statements. Problems With Select In

I'm having an issue with a prepared statement in Python I can't solve so far. The Query, which should be execute is e.g.: SELECT md5 FROM software WHERE software_id IN (1, 2, 4)

Solution 1:

You need one placeholder for each item in your parameter list. You can use string operations to get that part done:

1. Create one %s for each parameter, and
2. Join those together with a comma.

In the next step you can pass your two arguments to execute() as recommended in the DB-API documentation.

software_id_string = (1,2,4)
qry = '''SELECT md5 
           FROM software 
          WHERE software_id IN (%s)''' % ','.join(['%s']*len(software_id_string))
# // 'SELECT md5 FROM software WHERE software_id IN (%s,%s,%s)'
cursor.execute(qry, software_id_string)

Solution 2:

I recommend creating a type converter for explicit handling of IN clauses. This is the way the psycopg2 module handles it:

from MySQLdb.converters import conversions

classSQL_IN(object):
    def__init__(self, seq):
        self.seq = seq

    @classmethoddefescape(cls, obj, d):
        return'(' + ','.join((d[type(o)](o, d) for o in obj.seq)) + ')'# add this before you call MySQLdb.connect()
conversions[SQL_IN] = SQL_IN.escape

Real example:

db = MySQLdb.connect()
cursor = db.cursor()

SQL = "SELECT * FROM emp WHERE emp_id IN %s"
in_values = (1, 2, 3)
cursor.execute(SQL, (SQL_IN(in_values),))
print cursor._last_executed
print cursor.fetchall()

SQL = "SELECT * FROM emp WHERE name IN %s"
in_values = ("bob", "fred")
cursor.execute(SQL, (SQL_IN(in_values),))
print cursor._last_executed
print cursor.fetchall()

Output:

SELECT*FROM emp WHERE emp_id IN (1,2,3)
((1L, 'bob'), (2L, 'larry'), (3L, 'fred'))
SELECT*FROM emp WHERE name IN ('bob','fred')
((1L, 'bob'), (3L, 'fred'))

Solution 3:

You want

cursor.execute("SELECT md5 FROM software WHERE software_id IN %s" % software_id_string)

i.e. a % instead of a comma