<tfoot id='pFVaj'></tfoot>
    1. <legend id='pFVaj'><style id='pFVaj'><dir id='pFVaj'><q id='pFVaj'></q></dir></style></legend>

      <small id='pFVaj'></small><noframes id='pFVaj'>

        <bdo id='pFVaj'></bdo><ul id='pFVaj'></ul>
      1. <i id='pFVaj'><tr id='pFVaj'><dt id='pFVaj'><q id='pFVaj'><span id='pFVaj'><b id='pFVaj'><form id='pFVaj'><ins id='pFVaj'></ins><ul id='pFVaj'></ul><sub id='pFVaj'></sub></form><legend id='pFVaj'></legend><bdo id='pFVaj'><pre id='pFVaj'><center id='pFVaj'></center></pre></bdo></b><th id='pFVaj'></th></span></q></dt></tr></i><div id='pFVaj'><tfoot id='pFVaj'></tfoot><dl id='pFVaj'><fieldset id='pFVaj'></fieldset></dl></div>

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

        时间:2023-06-02
        <tfoot id='ZSWDM'></tfoot>
            <bdo id='ZSWDM'></bdo><ul id='ZSWDM'></ul>

            • <small id='ZSWDM'></small><noframes id='ZSWDM'>

              <i id='ZSWDM'><tr id='ZSWDM'><dt id='ZSWDM'><q id='ZSWDM'><span id='ZSWDM'><b id='ZSWDM'><form id='ZSWDM'><ins id='ZSWDM'></ins><ul id='ZSWDM'></ul><sub id='ZSWDM'></sub></form><legend id='ZSWDM'></legend><bdo id='ZSWDM'><pre id='ZSWDM'><center id='ZSWDM'></center></pre></bdo></b><th id='ZSWDM'></th></span></q></dt></tr></i><div id='ZSWDM'><tfoot id='ZSWDM'></tfoot><dl id='ZSWDM'><fieldset id='ZSWDM'></fieldset></dl></div>
            • <legend id='ZSWDM'><style id='ZSWDM'><dir id='ZSWDM'><q id='ZSWDM'></q></dir></style></legend>

                  <tbody id='ZSWDM'></tbody>
                • 本文介绍了执行“SELECT ... WHERE ... IN ..."使用 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')?

                  顺便说一句,我注意到如果我切换barfooid的角色,我可以得到代码来选择那些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
                  

                  确实,AC 周围的引号似乎太多了.

                  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.

                  这篇关于执行“SELECT ... WHERE ... IN ..."使用 MySQL 数据库的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                  上一篇:使用索引,使用临时,使用文件排序 - 如何解决这个问题? 下一篇:如何重新编号主索引

                  相关文章

                    <legend id='47k0B'><style id='47k0B'><dir id='47k0B'><q id='47k0B'></q></dir></style></legend>
                      <bdo id='47k0B'></bdo><ul id='47k0B'></ul>
                  1. <tfoot id='47k0B'></tfoot>

                    <small id='47k0B'></small><noframes id='47k0B'>

                  2. <i id='47k0B'><tr id='47k0B'><dt id='47k0B'><q id='47k0B'><span id='47k0B'><b id='47k0B'><form id='47k0B'><ins id='47k0B'></ins><ul id='47k0B'></ul><sub id='47k0B'></sub></form><legend id='47k0B'></legend><bdo id='47k0B'><pre id='47k0B'><center id='47k0B'></center></pre></bdo></b><th id='47k0B'></th></span></q></dt></tr></i><div id='47k0B'><tfoot id='47k0B'></tfoot><dl id='47k0B'><fieldset id='47k0B'></fieldset></dl></div>