python - IN子句中使用的mysql命名占位符在python中

Question

在破解 MySQL 绑定(bind)的 python 代码时，我更喜欢使用命名占位符，但似乎我无法用 IN 子句恰到好处。一个例子:

con = MySQLdb.connect(db='test', user='test')
cur = con.cursor()

三个太简单的例子:

# (#1) works fine, but I want placeholders.
cur.execute( """ update test
                 set    i = 999
                 where  SNO in (1, 2) """)

# (#2) works fine too, but still not enough placeholders.
cur.execute( """ update test
                 set    i = %(i)s
                 where  SNO in (1, 2) """, {'i' : 999})

# (#3) works, but did not pass the beauty check...
cur.execute( """ update test
                 set    i = %(i)s
                 where  SNO in ( %(a)s, %(b)s ) """, {'i' : 99,
                                                      'a' : 1,
                                                      'b' : 2})

这是我真正想要的，但它失败了:Operand should contain 1 column(s)

# (#4) This one fails with: _mysql_exceptions.OperationalError: (1241, 'Operand should contain 1 column(s)')
cur.execute( """ update test
                 set    i = %(i)s
                 where  SNO in ( %(foo)s ) """, {'i'   : 999,
                                                 'foo' : [1, 2]})

显然我需要更多魔法。简单地将问题转移到应用程序，在 python 中实现一个循环会很容易，但我宁愿避免这种情况。

是的，性能也很重要。

Answer 1

MySQLdb 已经为您处理了序列的转义:

>>> con = MySQLdb.connect(db='test')
>>> con.literal([1,2,3])
('1', '2', '3')
>>> cur = con.cursor()
>>> cur.execute("select * from test where id in %(foo)s", {'foo': [1,2,3]})
3L
>>> cur._executed
"select * from test where id in ('1', '2', '3')"

因此，通过删除占位符周围的括号，它应该可以工作 - 但仅适用于具有多个元素的序列，因为单个元素的格式如下:

>>> con.literal([1])
('1',)

插入到 SQL 查询中，尾随逗号使其成为非法 SQL。

要解决这个问题，您还可以定义自己的转换器以将自定义类型转换为您喜欢的表示形式:

import MySQLdb.converters
conv = MySQLdb.converters.conversions.copy()

class CustomList(list):
    def __init__(self, *items):
        super(CustomList, self).__init__(items)

conv[CustomList] = lambda lst, conv: "(%s)" % ', '.join(str(item) for item in lst)

con = MySQLdb.connect(db='test', conv=conv)
cur = con.cursor()
cur.execute('select * from test where id in %(foo)s', {'foo': CustomList(0, 1, 2)})
print cur._executed

select * from test where id in (0, 1, 2)

这样列表项周围的引号就消失了。

它也可以只替换 list 的转换器，但这会改变所有列表的行为并可能引入漏洞。上述格式化列表的方法对于包含字符串的列表是不安全的，因为它不会转义特殊字符。

为此，您必须递归地转义列表中的所有项目:

>>> ...
>>> conv[list] = lambda lst, cv: "(%s)" % ', '.join(cv[type(item)](item, cv) for item in lst)
>>> con = MySQLdb.connect(..., conv=conv)
>>> con.literal([1, "it's working...", 2])
"(1, 'it\\'s working...', 2)"