Why Does \'SELECT ... WHERE ... IN ...\' Fail in MySQLdb with Parameterized Queries?

Executing "SELECT ... WHERE ... IN ..." using MySQLdb

Executing a SQL query with the IN clause using MySQLdb can be challenging, particularly when SQL parameters are employed. While a similar query may function correctly from the mysql command-line, its execution in Python may encounter difficulties.

Case Study

Consider the following SQL query:

SELECT fooid FROM foo WHERE bar IN ('A', 'C')

When attempting to execute this query from within Python using the mysql command, the following SQL is generated:

<code class="python">sql = 'SELECT fooid FROM foo WHERE bar IN %s'
args = [['A', 'C']]</code>

However, upon execution, this query returns no rows, despite expecting two. Interestingly, by switching the roles of bar and fooid in the Python query, the expected rows are successfully retrieved:

<code class="python">sql = 'SELECT bar FROM foo WHERE fooid IN %s'
args = [[1, 3]]</code>

Root Cause

The discrepancy in behavior stems from the way MySQLdb converts the parameterized argument ['A', 'C'] to ("'''A''"', "'''''C''"'") while executing the query. The excessive quotation marks render the IN clause ineffective.

Solution

Since MySQLdb lacks a built-in method to bind a list to an IN clause, the query parameters must be manually constructed as follows:

<code class="python"># 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

# 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</code>

By manually constructing the IN clause with the correct number of placeholders, the query can be executed successfully, returning the expected rows.

The above is the detailed content of Why Does \'SELECT ... WHERE ... IN ...\' Fail in MySQLdb with Parameterized Queries?. For more information, please follow other related articles on the PHP Chinese website!