How to Execute \"SELECT ... WHERE ... IN ...\" Queries with MySQLdb in Python?

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

In Python, executing SQL queries involving the "IN" clause using MySQLdb can encounter challenges. Consider the following scenario:

Problem:

Attempting to execute a query like this:

<code class="sql">SELECT fooid FROM foo WHERE bar IN ('A','C');</code>

from within Python using the following code:

<code class="python">import MySQLdb
import config
connection=MySQLdb.connect(
    host=config.HOST,user=config.USER,passwd=config.PASS,db='test')
cursor=connection.cursor()

sql='SELECT fooid FROM foo WHERE bar IN %s'
args=[['A','C']]
cursor.execute(sql,args)
data=cursor.fetchall()
print(data)
# ()</code>

results in an empty dataset, despite the expectation of two rows.

Cause:

MySQLdb automatically adds single quotes around elements in the input list args, resulting in the query being executed as:

<code class="sql">SELECT fooid FROM foo WHERE bar IN ("'A'", "'C'")</code>

This query differs from the original intended query, which used a single-quoted list.

Solution:

To overcome this issue, you must manually construct the query parameters. Here's a modified version of the Python code that addresses the problem:

<code class="python">import MySQLdb
import config
connection=MySQLdb.connect(
    host=config.HOST,user=config.USER,passwd=config.PASS,db='test')
cursor=connection.cursor()

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)
data=cursor.fetchall()
print(data)
# (('1',), ('3',))</code>

By constructing the in_p string using map() and joining it with commas, you effectively create a series of placeholders for the elements in args. The use of the % operator ensures that the placeholders are correctly substituted with the actual values when the query is executed.

The above is the detailed content of How to Execute \"SELECT ... WHERE ... IN ...\" Queries with MySQLdb in Python?. For more information, please follow other related articles on the PHP Chinese website!