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

Question

I'm having a problem executing some SQL from within Python, despite similar SQL working fine from the mysql command-line.

The table looks like this:

mysql> SELECT * FROM foo; +-------+-----+ | fooid | bar | +-------+-----+ |     1 | A   |  |     2 | B   |  |     3 | C   |  |     4 | D   |  +-------+-----+ 4 rows in set (0.00 sec) 

I can execute the following SQL query from the mysql command-line, without a problem:

mysql> SELECT fooid FROM foo WHERE bar IN ('A','C'); SELECT fooid FROM foo WHERE bar IN ('A','C'); +-------+ | fooid | +-------+ |     1 |  |     3 |  +-------+ 2 rows in set (0.00 sec) 

However, when I try to do the same from within Python, I get no rows, while I expected 2 rows:

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) # () 

So the question is: how should the python code be modified to select those fooids where bar is in ('A','C')?

By the way, I noticed that if I switch the roles of bar and fooid, I can get the code to select those bars where fooid is in (1,3) successfully. I don't understand why one such query (below) works, while the other one (above) doesn't.

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

And just to be absolutely clear, this is how the foo table was created:

mysql> DROP TABLE IF EXISTS foo; Query OK, 0 rows affected (0.00 sec)  mysql> CREATE TABLE `foo` (           `fooid` int(11) NOT NULL AUTO_INCREMENT,           `bar` varchar(10) NOT NULL,           PRIMARY KEY (`fooid`)); Query OK, 0 rows affected (0.01 sec)  mysql> INSERT into foo (bar) values ('A'),('B'),('C'),('D'); Query OK, 4 rows affected (0.00 sec) Records: 4  Duplicates: 0  Warnings: 0 

---

Edit: When I enable the general query log with mysqld -l /tmp/myquery.log I see

mysqld, Version: 5.1.37-1ubuntu5.5-log ((Ubuntu)). started with: Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock Time                 Id Command    Argument 110101 11:45:41     1 Connect   unutbu@localhost on test             1 Query set autocommit=0             1 Query SELECT fooid FROM foo WHERE bar IN ("'A'", "'C'")             1 Query SELECT bar FROM foo WHERE fooid IN ('1', '3')             1 Quit 

Indeed, it looks like too many quotes are being placed around A and C.

Thanks to @Amber's comment, I understand better what is going wrong. MySQLdb converts the parametrized argument ['A','C'] to ("'A'","'C'").

Is there a way to make a parametrized query using the IN SQL syntax? Or must one manually construct the SQL string?

Answer 1

Unfortunately, you need to manually construct the query parameters, because as far as I know, there is no built-in bind method for binding a list to an IN clause, similar to Hibernate's setParameterList(). However, you can accomplish the same with the following:

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 cursor.execute(sql, args) 

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 cursor.execute(sql, args)