2

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!

Community
  • 1
  • 1
Aivoric
  • 838
  • 2
  • 10
  • 24

1 Answers1

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.

GregD
  • 2,797
  • 3
  • 28
  • 39
  • Thanks GregD! so for every column I want to update I would I have specify: "column_name = value"? – Aivoric May 27 '13 at 02:09