Python 使用 MySQLdb 执行“SELECT ... WHERE ... IN ...”

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

提问by unutbu

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

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

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 命令行执行以下 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)

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

但是，当我尝试在 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)
# ()

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

所以问题是：应该如何修改 python 代码来选择那些fooids where barin ('A','C')？

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

顺便说一下，我注意到如果我切换barand的角色fooid，我可以得到代码来选择那些bars where fooidis in(1,3)成功。我不明白为什么一个这样的查询（下面）有效，而另一个（上面）却没有。

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 footable was created:

为了绝对清楚，这就是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

---

---

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

编辑：当我启用一般查询日志时，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

Indeed, it looks like too many quotes are being placed around Aand 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'").

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

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

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

采纳答案by Robert Bergs

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

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

Use the list_of_idsdirectly:

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.

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 的驱动程序，因此没有注入。您可以在字符串中保留任何您想要的字符，无需删除或引用字符。

回答by Jo?o Silva

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

不幸的是，您需要手动构造查询参数，因为据我所知，没有bind将 a 绑定list到IN子句的内置方法，类似于 Hibernate 的setParameterList(). 但是，您可以通过以下方式完成相同的操作：

Python 3:

蟒蛇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:

蟒蛇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)

回答by michael

Why not just this in that case?

在那种情况下为什么不只是这个？

args = ['A', 'C']
sql = 'SELECT fooid FROM foo WHERE bar IN (%s)' 
in_p  =', '.join(list(map(lambda arg:  "'%s'" % arg, args)))
sql = sql % in_p
cursor.execute(sql)

results in:

结果是：

SELECT fooid FROM foo WHERE bar IN ('A', 'C')

回答by Guglielmo Celata

If you have other parameters in the query, beyond the IN list, then the following extension to JG's answer may be useful.

如果查询中有其他参数，超出 IN 列表，那么 JG 答案的以下扩展可能有用。

ids = [1, 5, 7, 213]
sql = "select * from person where type=%s and id in (%s)"
in_ids = ', '.join(map(lambda x: '%s', ids))
sql = sql % ('%s', in_ids)
params = []
params.append(type)
params.extend(ids)
cursor.execute(sql, tuple(params))

That is, join all the params in a linear array, then pass it as a tuple to the execute method.

也就是说，将所有参数连接到一个线性数组中，然后将其作为元组传递给 execute 方法。

回答by satru

Maybe we can create a function to do what Jo?o proposed? Something like:

也许我们可以创建一个函数来执行 Jo?o 的建议？就像是：

def cursor_exec(cursor, query, params):
    expansion_params= []
    real_params = []
    for p in params:
       if isinstance(p, (tuple, list)):
         real_params.extend(p)
         expansion_params.append( ("%s,"*len(p))[:-1] )
       else:
         real_params.append(p)
         expansion_params.append("%s")
    real_query = query % expansion_params
    cursor.execute(real_query, real_params)

回答by Smooth Customer

Have been trying every variation on Jo?o's solution to get an IN List query to work with Tornado's mysql wrapper, and was still getting the accursed "TypeError: not enough arguments for format string" error. Turns out adding "*" to the list var "*args" did the trick.

一直在尝试 Jo?o 解决方案的所有变体，以使 IN 列表查询与 Tornado 的 mysql 包装器一起使用，但仍然收到该诅咒的“TypeError: not enough arguments for format string”错误。结果证明将 "*" 添加到列表 var "*args" 就可以了。

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
db.query(sql, *args)

回答by Luci Stanescu

Improving on Jo?o's and satru's code, I suggest creating a cursor mixin that can be used to build a cursor with an execute that accepts nested iterables and handles them correctly. A better name would be nice, though... For Python3, use strinstead of basestring.

改进 Jo?o 和 satru 的代码，我建议创建一个游标 mixin，它可用于构建一个带有执行的游标，该游标接受嵌套的可迭代对象并正确处理它们。一个更好的名字将是很好，虽然...对于Python3，使用str代替basestring。

from MySQLdb.cursors import Cursor

class BetterExecuteMixin(object):
    """
    This mixin class provides an implementation of the execute method
    that properly handles sequence arguments for use with IN tests.
    Examples:
    execute('SELECT * FROM foo WHERE id IN (%s) AND type=%s', ([1,2,3], 'bar'))
    # Notice that when the sequence is the only argument, you still need
    # a surrounding tuple:
    execute('SELECT * FROM foo WHERE id IN (%s)', ([1,2,3],))
    """

    def execute(self, query, args=None):
        if args is not None:
            try:
                iter(args)
            except TypeError:
                args = (args,)
            else:
                if isinstance(args, basestring):
                    args = (args,)
            real_params = []
            placeholders = []
            for arg in args:
                # sequences that we treat as a single argument
                if isinstance(arg, basestring):
                    real_params.append(arg)
                    placeholders.append('%s')
                    continue
                try:
                    real_params.extend(arg)
                    placeholders.append(','.join(['%s']*len(arg)))
                except TypeError:
                    real_params.append(arg)
                    placeholders.append('%s')
            args = real_params
            query = query % tuple(placeholders)
        return super(BetterExecuteMixin, self).execute(query, args)

class BetterCursor(BetterExecuteMixin, Cursor):
    pass

This can then be used as follows (and it's still backwards compatible!):

然后可以按如下方式使用它（它仍然向后兼容！）：

import MySQLdb
conn = MySQLdb.connect(user='user', passwd='pass', db='dbname', host='host',
                       cursorclass=BetterCursor)
cursor = conn.cursor()
cursor.execute('SELECT * FROM foo WHERE id IN (%s) AND type=%s', ([1,2,3], 'bar'))
cursor.execute('SELECT * FROM foo WHERE id IN (%s)', ([1,2,3],))
cursor.execute('SELECT * FROM foo WHERE type IN (%s)', (['bar', 'moo'],))
cursor.execute('SELECT * FROM foo WHERE type=%s', 'bar')
cursor.execute('SELECT * FROM foo WHERE type=%s', ('bar',))

回答by shanal

this works for me:

这对我有用：

myTuple= tuple(myList)
sql="select fooid from foo where bar in "+str(myTuple)
cursor.execute(sql)

回答by aman2357

args should be tuple.

args 应该是元组。

eg:

例如：

args = ('A','B')

args = ('A',) # in case of single

回答by Mizanur Rahman

Very simple:

很简单：

Just use the below formation###

只需使用以下格式###

rules_id = ["9","10"]

sql2 = "SELECT * FROM attendance_rules_staff WHERE id in"+str(tuple(rules_id))

note the str(tuple(rules_id)).

请注意str(tuple(rules_id)).