python execute many with "on duplicate key update"?

Question

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 error happens when the number of items do not match what you are supposed to fill in and what actually you are passing. >>> a = 1 >>> b = 2 >>> print "%s" % (a,b) Traceback (most recent call last): File "<stdin>", line 1, in <module> TypeError: not all arguments converted during string formatting >>> — ronak
– ronak, Commented Oct 10, 2012 at 18:00

unutbu · Accepted Answer · 2012-10-10 19:40:11Z

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.

MatejMecka · Accepted Answer · 2018-05-25 09:59:43Z

8

When you write sql like following:

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.

So when you use "ON DUPLICATE KEY UPDATE" in python, you need to write sql like this:

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)'

edited May 25, 2018 at 9:59

MatejMecka

1,4862 gold badges26 silver badges39 bronze badges

answered May 25, 2018 at 9:24

devo chen

891 silver badge1 bronze badge

Comments

whi · Accepted Answer · 2018-01-13 05:26:55Z

4

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/

answered Jan 13, 2018 at 5:26

whi

2,7606 gold badges35 silver badges41 bronze badges

Comments

backing · Accepted Answer · 2015-06-05 03:19:52Z

0

It is a bug of mysqldb as ubuntu said, sightly change the sql then it works:

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

answered Jun 5, 2015 at 3:19

backing

1274 bronze badges

Collectives™ on Stack Overflow

python execute many with "on duplicate key update"?

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related