Why Doesn\'t My MySQLdb SELECT ... WHERE ... IN ... Query Return Results?

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:

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

Python 2:

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

The above is the detailed content of Why Doesn\'t My MySQLdb SELECT ... WHERE ... IN ... Query Return Results?. For more information, please follow other related articles on the PHP Chinese website!