Python MySQL Syntax Error - DUPLICATE KEY UPDATE

Question

I have the following MySQL + Python code:

data = json.loads(decoded_response)

insert_values = []
cursor = cnx.cursor()
add_data = """INSERT INTO pb_ya_camps (camp_id,camp_name) VALUES (%s,%s) ON DUPLICATE KEY UPDATE VALUES (%s,%s)"""

for jsonData in data["data"]:
        if "No" in jsonData["StatusArchive"]:
                print("...processing campaign ",jsonData["Name"],"into the database.")
                insert_values.append((jsonData["CampaignID"],jsonData["Name"]))

try:
        cursor.executemany(add_data,(insert_values,insert_values))

Which at the moment produces the following error:

MYSQL ERROR: Failed processing format-parameters; 'MySQLConverter' object has no attribute '_tuple_to_mysql'

As far as I understand it is not liking the following:

cursor.executemany(add_data,(insert_values,insert_values))

I believe you can't do that with Python...but my problem probably derives from improper MySQL syntax. Could you please take a look at it?

INSERT INTO pb_ya_camps (camp_id,camp_name) VALUES (%s,%s) ON DUPLICATE KEY UPDATE VALUES (%s,%s)

I am not sure how to properly use the ON DUPLICATE KEY UPDATE without having to re-specify all the values... <<<--- that is the main problem.

I have read the following: LINK TO PREVIOUS EXAMPLE however I don't want to rely on KEY UPDATE col1 = VALUES(col1) because in a further part of my script I have too many columns to keep listing as part of col = value for each column...

Thank you!

Instead of (insert_values,insert_values)), try insert_values * 2. — Blender
– Blender, Commented May 27, 2013 at 1:20
MySQLConverter' object has no attribute '_tuple_to_mysql' exception with mysql-connector — Antti Haapala
– Antti Haapala, Commented Mar 10, 2016 at 7:53

GregD · Accepted Answer · 2013-05-27 01:24:33Z

3

Following MySQL Reference Manual, MySQL syntax for INSERT ... ON DUPLICATE KEY UPDATE is:

INSERT INTO table (`a`, `b`, `c`) 
VALUES (1, 2, 3)
    ON DUPLICATE KEY UPDATE `c` = `c` + 1;

So in your case (please note that writing either camp_id = %s or camp_id = VALUES(%s) is the same:

INSERT INTO `pb_ya_camps` (`camp_id`, `camp_name`) 
VALUES (%s,%s) 
    ON DUPLICATE KEY UPDATE `camp_id` = VALUES(%s), `camp_name` = VALUES(%s)

More information about the syntax at the MySQL Reference Manual.

answered May 27, 2013 at 1:24

GregD

2,8773 gold badges31 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Aivoric Over a year ago

Thanks GregD! so for every column I want to update I would I have specify: "column_name = value"?

Collectives™ on Stack Overflow

Python MySQL Syntax Error - DUPLICATE KEY UPDATE

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related