执行"SELECT ... WHERE ... IN ...".使用MySQLdb [英] Executing "SELECT ... WHERE ... IN ..." using MySQLdb

问题描述

尽管在mysql命令行中可以使用类似的SQL，但在Python中执行某些SQL还是有问题.

该表如下所示:

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

我可以从mysql命令行执行以下SQL查询，而不会出现问题:

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)

但是，当我尝试在Python中执行相同操作时，我没有任何行，而我希望有2行:

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

所以问题是:应该如何修改python代码以选择('A','C')在('A','C')中的那些fooid?

顺便说一句，我注意到，如果我切换bar和fooid的角色，我可以获取代码来成功选择fooid在(1,3)中的那些bar.我不明白为什么一个这样的查询(下面)有效，而另一个(上面)却无效.

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

完全清楚地说，这是创建foo表的方式:

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

---

编辑:当我使用mysqld -l /tmp/myquery.log启用常规查询日志时 我明白了

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

实际上，似乎在A和C周围放置了过多的引号.

由于@Amber的评论，我更好地了解出了什么问题. MySQLdb将参数化的参数['A','C']转换为("'A'","'C'").

是否有一种使用IN SQL语法进行参数化查询的方法?还是必须手动构造SQL字符串?

解决方案

这是同类解决方案我认为在SQL中建立％s字符串列表更有效:

直接使用list_of_ids:

format_strings = ','.join(['%s'] * len(list_of_ids))
cursor.execute("DELETE FROM foo.bar WHERE baz IN (%s)" % format_strings,
                tuple(list_of_ids))

这样，您就不必引用自己的名字，并避免各种SQL注入.

请注意，数据(list_of_ids)作为参数(不在查询文本中)直接传递到mysql的驱动程序，因此没有注入.您可以在字符串中保留所需的任何字符，而无需删除或引用字符.

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?

解决方案

Here is a similar solution which I think is more efficient in building up the list of %s strings in the SQL:

Use the list_of_ids directly:

format_strings = ','.join(['%s'] * len(list_of_ids))
cursor.execute("DELETE FROM foo.bar WHERE baz IN (%s)" % format_strings,
                tuple(list_of_ids))

That way you avoid having to quote yourself, and avoid all kinds of sql injection.

Note that the data (list_of_ids) is going directly to mysql's driver, as a parameter (not in the query text) so there is no injection. You can leave any chars you want in the string, no need to remove or quote chars.

这篇关于执行"SELECT ... WHERE ... IN ...".使用MySQLdb的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！