Executing "SELECT ... WHERE ... IN ..." Using MySQLdb

When executing an SQL query with an IN clause using MySQLdb, it's important to pay attention to how the query parameters are constructed.

Problem

A user encountered an issue while trying to select fooids where bar was in ('A','C') using Python and MySQLdb. Despite the identical query working from the mysql command-line, it returned no rows in Python.

Root Cause

The issue arose because MySQLdb converted the parametrized argument ['A','C'] to ("'A'","'C'"), which resulted in too many quotes around the values in the IN clause.

Solution

To execute the query correctly, the query parameters must be manually constructed. The following Python code demonstrates how to achieve this:

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)