MySQLdb不会返回所有用“重复键更新"转换的参数. [英] MySQLdb returns not all arguments converted with "on duplicate key update"

问题描述

使用python中的MySQLdb软件包，我想插入检查某些唯一键的记录.我使用的方法是executemany.参数是sql语句和元组.但是当我执行它时，它引发了一个错误，指出不是所有参数都转换了". 代码如下:

With MySQLdb package in python, I want to insert records with checking some unique keys. The method I used is executemany. The arguments are sql sentence and a tuple. But when I executed it, it raised an error which said "not all argument converted". The codes are as following:

dData = [[u'Daniel', u'00-50-56-C0-00-12', u'Daniel']]
sql = "INSERT INTO app_network_white_black_list (biz_id, shop_id, type, mac_phone, remarks, create_time) " \
      "VALUES ({bsid}, {shop_id}, {type}, %s, %s, NOW()) " \
      "ON DUPLICATE KEY UPDATE type={type}, remarks=%s, create_time=NOW()".format(bsid=bsid, shop_id=shop_id, type=dType)
cur.executemany(sql, tuple(dData))

有人说这是一个错误.但是他们没有给我一条跳过它的道路.如果存在错误，请提供一种方法.

Someone said this is a bug. But they didn't give me a path to jump over it. Please provide a method if this is a bug.

推荐答案

出了什么问题

在检查中的链接后您在下面的评论中，并进行了更多的研究和测试，我能够使用MySQLdb 1.2.4b4和1.2.5版本来重现该错误.如 unubtu的答案中所述，这与出现在cursors.py中的正则表达式的局限性有关.每个版本中确切的正则表达式略有不同，这可能是因为人们一直在寻找它无法处理的案例并调整表达式，而不是完全寻找更好的方法.

What's going wrong

After checking the link in your comment below and doing some more research and testing, I was able to reproduce the error with MySQLdb versions 1.2.4b4 and 1.2.5. As explained in unubtu's answer, this has to do with the limitations of a regular expression that appears in cursors.py. The exact regular expression is slightly different in each release, probably because people keep finding cases it doesn't handle and adjusting the expression instead of looking for a better approach entirely.

正则表达式的作用是尝试匹配INSERT语句的VALUES ( ... )子句，并标识其包含的元组表达式的开始和结束.如果匹配成功，则executemany尝试将单行插入语句模板转换为多行插入语句，以使其运行更快.即，您不必对要插入的每一行都执行此操作:

What the regular expression does is try to match the VALUES ( ... ) clause of the INSERT statement and identify the beginning and end of the tuple expression it contains. If the match succeeds, executemany tries to convert the single-row insert statement template into a multiple-row insert statement so that it runs faster. I.e., instead of executing this for every row you want to insert:

INSERT INTO table
  (foo, bar, ...)
VALUES
  (%s, %s, ...);

它将尝试重写该语句，使其仅执行一次:

It tries to rewrite the statement so that it only has to execute once:

INSERT INTO table
  (foo, bar, ...)
VALUES
  (1, 2, ...),
  (3, 4, ...),
  (5, 6, ...),
  ...;

您遇到的问题是executemany假定您紧随VALUES之后在元组中仅具有参数占位符.以后当您还有占位符时，它将执行以下操作:

The problem you're running into is that executemany assumes you only have parameter placeholders in the tuple immediately after VALUES. When you also have placeholders later on, it takes this:

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尝试将您的参数 all 放入匹配组(%s, %s, ...)中，而没有意识到有些参数需要放入UPDATE子句中.

The problem here is that MySQLdb is trying to do string formatting at the same time that it's rewriting the query. Only the VALUES ( ... ) clause needs to be rewritten, so MySQLdb tries to put all your parameters into the matching group (%s, %s, ...), not realizing that some parameters need to go into the UPDATE clause instead.

如果仅将VALUES子句的参数发送给executemany，则可以避免TypeError，但会遇到其他问题.请注意，重写的INSERT ... ON DUPLICATE UPDATE查询在VALUES子句中具有数字文字，但是在UPDATE子句中仍然有%s占位符.当它到达MySQL服务器时，将引发语法错误.

If you only send parameters for the VALUES clause to executemany, you'll avoid the TypeError but run into a different problem. Notice that the rewritten INSERT ... ON DUPLICATE UPDATE query has numeric literals in the VALUES clause, but there's still a %s placeholder in the UPDATE clause. That's going to throw a syntax error when it reaches the MySQL server.

当我第一次测试您的示例代码时，我使用的是MySQLdb 1.2.3c1，无法重现您的问题.有趣的是，特定版本的软件包避免了这些问题的原因是正则表达式已损坏并且根本与语句不匹配.由于不匹配，因此executemany不会尝试重写查询，而只是循环遍历您的参数，反复调用execute.

When I first tested your sample code, I was using MySQLdb 1.2.3c1 and couldn't reproduce your problem. Amusingly, the reason that particular version of the package avoids these problems is that the regular expression is broken and doesn't match the statement at all. Since it doesn't match, executemany doesn't attempt to rewrite the query, and instead just loops through your parameters calling execute repeatedly.

首先，不要回过头来安装1.2.3c1来完成此工作.您希望尽可能使用更新的代码.

First of all, don't go back and install 1.2.3c1 to make this work. You want to be using updated code where possible.

您可以转到unpackage中在链接的问答"中建议的其他程序包，但这将涉及一些调整，并且可能需要更改其他代码.

You could move to another package, as unubtu suggests in the linked Q&A, but that would involve some amount of adjustment and possibly changes to other code.

我所建议的是以更直接的方式重写查询，并利用UPDATE子句中的VALUES()函数.此功能允许您按列名引用在没有重复键冲突的情况下将要插入的值(示例在

What I would recommend instead is to rewrite your query in a way that is more straightforward and takes advantage of the VALUES() function in your UPDATE clause. This function allows you to refer back to the values that you would have inserted in the absence of a duplicate key violation, by column name (examples are in the MySQL docs).

考虑到这一点，这是一种实现方法:

With that in mind, here's one way to do it:

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将能够成功地将带有参数的单行插入模板转换为具有文字值的多行插入语句.

This approach should work because there are no parameters in the UPDATE clause, meaning MySQLdb will be able to successfully convert the single-line insert template with parameters into a multi-line insert statement with literal values.

一些注意事项:

• 您不必为executemany提供元组；任何迭代都可以.
• 与隐式连接的字符串相比，多行字符串在Python代码中提供了更具可读性的SQL语句；当您将语句与字符串定界符分开时，很容易快速抓取该语句并将其复制到客户端应用程序中进行测试.
• 如果要参数化部分查询，为什么不参数化所有查询?即使只有一部分是用户输入，以相同的方式处理所有输入值也更具可读性和可维护性.
• 也就是说，我没有参数化NOW().我在这里的首选方法是使用CURRENT_TIMESTAMP作为列的默认值，并在语句中利用DEFAULT的优势.其他人可能更喜欢在应用程序中生成此值并将其作为参数提供.如果您不担心版本兼容性，则可以保持原样.
• 如果您不能避免在UPDATE子句中使用参数占位符–例如，因为UPDATE值不能在语句中进行硬编码或从VALUES元组派生–您可以将不得不遍历execute而不是使用executemany.

• You don't have to supply a tuple to executemany; any iterable is fine.
• Multiline strings make for much more readable SQL statements in your Python code than implicitly concatenated strings; when you separate the statement from the string delimiters, it's easy to quickly grab the statement and copy it into a client application for testing.
• If you're going to parameterize part of your query, why not parameterize all of your query? Even if only part of it is user input, it's more readable and maintainable to handle all your input values the same way.
• That said, I didn't parameterize NOW(). My preferred approach here would be to use CURRENT_TIMESTAMP as the column default and take advantage of DEFAULT in the statement. Others might prefer to generate this value in the application and supply it as a parameter. If you're not worried about version compatibility, it's probably fine as-is.
• If you can't avoid having parameter placeholders in the UPDATE clause – e.g., because the UPDATE value(s) can't be hard-coded in the statement or derived from the VALUES tuple – you'll have to iterate over execute instead of using executemany.

这篇关于MySQLdb不会返回所有用“重复键更新"转换的参数.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！