Why is \'SELECT ... WHERE ... IN ...\' Query Failing in MySQLdb?

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

Despite having a successful execution of SQL queries from the MySQL command-line, you may encounter issues executing queries with the "WHERE ... IN ..." syntax from within Python using MySQLdb. Let's investigate the problem and explore a solution.

In your case, you are unable to retrieve rows with a query that filters the "bar" column with an "IN" clause, while a similar query that filters the "fooid" column with "IN" succeeds. This inconsistency can be attributed to the way MySQLdb handles parametrized queries.

When you specify an array of values as an argument to MySQLdb, it automatically converts it into a tuple of strings with single quotes, resulting in incorrect syntax for the "IN" clause. For instance, your code converts ['A','C'] to ("'A'","'C'").

 args=[['A','C']] 

To resolve this, you must manually construct the SQL string and use Python to concatenate the values in the "IN" clause:

<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>

Alternatively, for Python 2, you can use the following code:

<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>

This code dynamically generates the SQL string with the correct number of placeholders ('%s') by iterating over the values in the array and joining them with a comma. By executing this manually constructed query, you will obtain the expected result, selecting fooids where the "bar" column is in ('A','C')

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