executemany with ON DUPLICATE KEY ...

[Kael Fischer]

Hey Andy at al,

versions:
MySQLdb.version_info: (1, 2, 1, 'final', 2)
Python 2.4.3
FreeBSD 6.1-RELEASE
Mysql Server Version: 5.0.17

Problem:
--code-- [sqlText wrapped here but is a single line in python]
sqlText = """INSERT INTO NCBI.Gi_Info
(Gi, Title, Tax_ID, Length, Family_Tax_ID,
Genus_Tax_ID, Species_Tax_ID)
VALUES (%s,%s,%s,%s,%s,%s,%s)
ON DUPLICATE KEY UPDATE Timestamp=NOW()
"""
tempCursor.executemany(sqlText,parameters)
--end code--

This works if len(parameters) == 1
If len(parameters)>1, this breaks with:

_mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(169257208,'Phage Gifsy-1, complete genome',129861,48491,NULL,NULL,129861) ON DU' at line 2")

The data in the error is always the second tuple in parameters.

If I wrap it in a loop and send one at a time it's fine (exact same code in the loop).

It also works in all cases when I remove "ON DUPLICATE KEY UPDATE Timestamp=NOW()"

Is this a bug or a usage error? Google and SF-forum search were not helpful.

Best Regards and thanks,
Kael

[Kael Fischer]

note to self... check closed bugs. This was bug 1556443 and has been fixed.