Does Python do many with "duplicate key updates"?

Question

I am trying to execute in python with re-updating a key 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 get 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 cycle?

Answer 1

This is a bug in MySQLdb due to the regular expression that MySQLdb uses to parse expressions INSERT:

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

restr = (r"\svalues\s*"
        r"(\(((?<!\\)'[^\)]*?\)[^\)]*(?<!\\)?'"
        r"|[^\(\)]|"
        r"(?:\([^\)]*\))"
        r")+\))")

insert_values= re.compile(restr)

, , , MySQLdb 1.2.3. ( , MySQLdb - 1.2.4b4.)

, , . , - , . , , INSERT ... SELECT SELECT WHERE , ... , .

oursql; . .

Answer 2

mysqldb, ubuntu, sql, :

insert into tb_name(col1, col2) select 1,2 on duplicate key update col1=1

Answer 3

found:

on duplicate key update col1=VALUES(col1), col2=VALUES(col2)

https://hardforum.com/threads/python-mysql-not-all-arguments-converted-during-string-formatting.1367039/

Answer 4

When you write sql as below:

sql = insert into A (id, last_date, count) values(%s, %s, %s) on duplicate key update last_date=%s, count=count+%s'

You will get the following error: TypeError: not all arguments converted during string formatting.

Therefore, when you use "ON DUPLICATE KEY UPDATE"in python, you need to write sql as follows:

sql = 'insert into A (id, last_date, count) values(%s, %s, %s) on duplicate key update last_date=values(last_date),count=count+values(count)'