出了什么问题
在检查下面你的评论中的链接并进行更多的研究和测试后,我成功地复制了MySQLdb版本1.2.4b4和1.2.5的错误。正如unubtu's answer所解释的那样,这与中出现的正则表达式的限制有关。每个版本的确切正则表达式略有不同,可能是因为人们不断发现它无法处理的情况并调整表达式,而不是寻找更好的方法。
正则表达式的作用是尝试匹配INSERT
语句的VALUES( ... )
子句,并识别其包含的元组表达式的开始和结束。如果匹配成功,executemany
会尝试将单行插入语句模板转换为多行插入语句,以便更快地运行。也就是说,代替为要插入的每一行执行以下操作:
INSERT INTO table
(foo, bar, ...)
VALUES
(%s, %s, ...);
它试图重写语句,以便只需执行一次:
INSERT INTO table
(foo, bar, ...)
VALUES
(1, 2, ...),
(3, 4, ...),
(5, 6, ...),
...;
你遇到的问题是
executemany
假设在
VALUES
后面的元组中只有参数占位符。当你还有其他占位符时,它会出现这种情况:
INSERT INTO table
(foo, bar, ...)
VALUES
(%s, %s, ...)
ON DUPLICATE KEY UPDATE baz=%s;
并尝试像这样重写它:
INSERT INTO table
(foo, bar, ...)
VALUES
(1, 2, ...),
(3, 4, ...),
(5, 6, ...),
...
ON DUPLICATE KEY UPDATE baz=%s;
这里的问题在于MySQLdb尝试在重写查询时进行字符串格式化。只有
VALUES (...)
子句需要被重写,因此MySQLdb试图将
所有参数放入匹配组
(%s,%s,...)
中,没有意识到一些参数需要放入
UPDATE
子句中。
如果你只向
executemany
发送
VALUES
子句的参数,你就可以避免
TypeError
,但会遇到另一个问题。注意,在重写的
INSERT ... ON DUPLICATE UPDATE
查询中,
VALUES
子句中有数字文字,但在
UPDATE
子句中仍然有
%s
占位符。当它到达MySQL服务器时,这将引发语法错误。
当我第一次测试你的示例代码时,我使用的是MySQLdb 1.2.3c1版本,无法重现你的问题。有趣的是,这个包的特定版本避免了这些问题的原因是正则表达式被破坏了,根本不匹配语句。由于它不匹配,
executemany
不会尝试重写查询,而只是循环遍历您的参数并重复调用
execute
。
要怎么处理呢?
首先,不要回到安装1.2.3c1以使其工作。您应该尽可能使用更新的代码。
如链接的 Q&A 中 unubtu 建议的,您可以切换到另一个包,但这可能需要进行一些调整,并可能对其他代码进行更改。
我建议的是以更简单直接的方式重写您的查询,并利用
UPDATE
子句中的
VALUES()
函数。该函数允许您通过列名引用在没有重复键违规的情况下插入的值(示例位于
MySQL文档中)。请不要解释,保留HTML标记。
基于此想法,以下是一种实现方法:
dData = [[u'Daniel', u'00-50-56-C0-00-12', u'Daniel']] # exact input you gave
sql = """
INSERT INTO app_network_white_black_list
(biz_id, shop_id, type, mac_phone, remarks, create_time)
VALUES
(%s, %s, %s, %s, %s, NOW())
ON DUPLICATE KEY UPDATE
type=VALUES(type), remarks=VALUES(remarks), create_time=VALUES(create_time);
""" # keep parameters in one part of the statement
# generator expression takes care of the repeated values
cur.executemany(sql, ((bsid, shop_id, dType, mac, rem) for mac, rem in dData))
这种方法应该有效,因为
UPDATE
子句中没有参数,这意味着MySQLdb将能够成功地将带有参数的单行插入模板转换为具有文字值的多行插入语句。
需要注意的一些事情:
executemany
不必提供元组;任何可迭代的对象都可以。
- 与隐式连接的字符串相比,在Python代码中使用多行字符串可以使SQL语句更易读;当您将语句与字符串分隔符分开时,很容易快速获取语句并将其复制到客户端应用程序中进行测试。
- 如果要将查询的一部分参数化,为什么不将查询的所有部分参数化?即使只有部分输入是用户输入,处理所有输入值的方式相同会更易读和可维护。
- 话虽如此,我没有将
NOW()
参数化。我在这里的首选方法是使用CURRENT_TIMESTAMP
作为列默认值,并利用语句中的DEFAULT
。其他人可能更喜欢在应用程序中生成此值并将其作为参数提供。如果您不担心版本兼容性,则现在这样做可能是可以的。
- 如果无法避免在
UPDATE
子句中具有参数占位符-例如,因为UPDATE
值不能在语句中硬编码或从VALUES
元组派生-则必须遍历execute
而不是使用executemany
。