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)

So I tried to execute a Query like this:

software_id_string = "(2, 3, 4)"
cursor.execute("SELECT md5 FROM software WHERE software_id IN %s", 
                software_id开发者_StackOverflow社区_string)

The Problem is that there are '' added to the string --> '(2, 3, 4)', so that the Query will be:

SELECT md5 FROM software WHERE software_id IN ''(2, 3, 4)''

I've also tried to rebuild the Script like this:

software_id_string = " 1 OR software_id = 2"
cursor.execute("SELECT md5 FROm software WHERE software_id = %s", 
              software_id_string)

This works only for the first id, which will be submitted (in this case 1), because the OR-part won't be interpeted as an SQL Statement...

Is there any possibility to fix the issues with the prepared statements?

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)

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

class SQL_IN(object):
    def __init__(self, seq):
        self.seq = seq

    @classmethod
    def escape(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'))

You want

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

i.e. a % instead of a comma