Executing "select ... Where ... In ..." Using Mysqldb

I'm having a problem executing some SQL from within Python, despite similar SQL working fine from the mysql command-line. The table looks like this: mysql> SELECT * FROM foo; +-

Solution 1:

Unfortunately, you need to manually construct the query parameters, because as far as I know, there is no built-in bind method for binding a list to an IN clause, similar to Hibernate's setParameterList(). However, you can accomplish the same with the following:

Python 3:

args=['A', 'C']
sql='SELECT fooid FROM foo WHERE bar IN (%s)' 
in_p=', '.join(list(map(lambda x: '%s', args)))
sql=sql% in_p
cursor.execute(sql, args)

Python 2:

args=['A', 'C']
sql='SELECT fooid FROM foo WHERE bar IN (%s)' 
in_p=', '.join(map(lambda x: '%s', args))
sql=sql% in_p
cursor.execute(sql, args)

Solution 2:

Here is a similar solution which I think is more efficient in building up the list of %s strings in the SQL:

Use the list_of_ids directly:

format_strings = ','.join(['%s'] * len(list_of_ids))
cursor.execute("DELETE FROM foo.bar WHERE baz IN (%s)" % format_strings,
                tuple(list_of_ids))

That way you avoid having to quote yourself, and avoid all kinds of sql injection.

Note that the data (list_of_ids) is going directly to mysql's driver, as a parameter (not in the query text) so there is no injection. You can leave any chars you want in the string, no need to remove or quote chars.

Solution 3:

If you have other parameters in the query, beyond the IN list, then the following extension to JG's answer may be useful.

ids = [1, 5, 7, 213]
sql = "select * from person where type=%s and id in (%s)"
in_ids = ', '.join(map(lambda x: '%s', ids))
sql = sql % ('%s', in_ids)
params = []
params.append(type)
params.extend(ids)
cursor.execute(sql, tuple(params))

That is, join all the params in a linear array, then pass it as a tuple to the execute method.

Solution 4:

this works for me:

myTuple= tuple(myList)
sql="select fooid from foo where bar in "+str(myTuple)
cursor.execute(sql)

Solution 5:

Maybe we can create a function to do what João proposed? Something like:

def cursor_exec(cursor, query, params):
    expansion_params= []
    real_params = []
    for p in params:
       if isinstance(p, (tuple, list)):
         real_params.extend(p)
         expansion_params.append( ("%s,"*len(p))[:-1] )
       else:
         real_params.append(p)
         expansion_params.append("%s")
    real_query = query % expansion_params
    cursor.execute(real_query, real_params)