unutbu unutbu - 2 months ago 21
Python Question

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

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
fooid
s 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
bar
s 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

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)