python键不能重复_python使用“重复键更新”执行许多操作？

I am trying to executemany in python with on duplicate key update, with the following script:

# data from a previous query (returns 4 integers in each row)

rows = first_cursor.fetchall()

query="""

INSERT INTO data (a, b, c)

VALUES (%s,%s,%s) ON DUPLICATE KEY UPDATE a=%s

"""

second_cursor.executemany(query,rows)

I'm getting this error:

File "/usr/lib/pymodules/python2.6/MySQLdb/cursors.py", line 212, in executemany

self.errorhandler(self, TypeError, msg)

File "/usr/lib/pymodules/python2.6/MySQLdb/connections.py", line 35, in defaulterrorhandler

raise errorclass, errorvalue

TypeError: not all arguments converted during string formatting

Is this even possible without creating my own loop?

解决方案

This is a bug in MySQLdb due to the regex that MySQLdb uses to parse INSERT statements:

In /usr/lib/pymodules/python2.7/MySQLdb/cursors.py:

restr = (r"\svalues\s*"

r"(\(((?

r"|[^\(\)]|"

r"(?:\([^\)]*\))"

r")+\))")

insert_values= re.compile(restr)

Although there have been numerous bug reports about this problem that have been closed as fixed, I was able to reproduce the error in MySQLdb version 1.2.3. (Note the latest version of MySQLdb at the moment is 1.2.4b4.)

Maybe this bug is fixable, I don't really know. But I think it is just the tip of the iceberg -- it points to much more trouble lurking just a little deeper. You could have for instance an INSERT ... SELECT statement with nested SELECT statements with WHERE conditions and parameters sprinkled all about... Making the regex more and more complicated to handle these cases seems to me like a losing battle.

You could use oursql; it does not use regex or string formating. It passes parametrized queries and arguments to the server separately.