Home > Database > Mysql Tutorial > Why is \'SELECT ... WHERE ... IN ...\' Query Failing in MySQLdb?

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

Barbara Streisand
Release: 2024-11-01 17:32:02
Original
896 people have browsed it

Why is

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>
Copy after login

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>
Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template