执行“SELECT ... WHERE ... IN ..."使用 MySQL 数据库

2021-11-20 00:00:00 python mysql

我在 Python 中执行某些 SQL 时遇到问题，尽管类似的 SQL 在 mysql 命令行中运行良好.

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

表格如下所示:

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

我可以从 mysql 命令行执行以下 SQL 查询，没有任何问题:

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)

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

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

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

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

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

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',))

为了绝对清楚，这就是 foo 表的创建方式:

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

<小时>

编辑:当我使用 mysqld -l/tmp/myquery.log 启用一般查询日志时看到了

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

确实，A 和 C 周围的引号似乎太多了.

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

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

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

有没有办法使用 IN SQL 语法进行参数化查询?还是必须手动构造 SQL 字符串?

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

推荐答案

这里有一个类似的解决方案 我认为在 SQL 中构建 %s 字符串列表更有效:

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

直接使用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注入.

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

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

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.

相关文章