MySQL Python ON DUPLICATE KEY UPDATE VALUES

Question

I am working on using python to upload JSON data to MySQL. I need to include "ON DUPLICATE KEY UPDATE VALUES" in the insert statement, but am running into issues in Python.

If I run the following code, everything works...

import json
import mysql.connector

mydb = mysql.connector.connect(host=x,user=x,password=x,database=x)
cursor = mydb.cursor()

json_data = json.loads(file)

for item in json_data['data']:
   cursor.execute(
       "INSERT INTO input_1 (a,b,c,d) VALUES (%s,%s,%s,%s)",
        (item['a'],item['b'],item['c'],item['d'])
   )

However, when I tack on the "ON DUPLICATE KEY" to the end...

import json
import mysql.connector

mydb = mysql.connector.connect(host=x,user=x,password=x,database=x)
cursor = mydb.cursor()

json_data = json.loads(file)

for item in json_data['data']:
   cursor.execute(
       "INSERT INTO input_1 (a,b,c,d) VALUES (%s,%s,%s,%s) ON DUPLICATE KEY UPDATE VALUES a=%s,b=%s,c=%s", 
       (item['a'],item['b'],item['c'],item['d'])
   )

I Get the Following Message:

ProgrammingError: Not enough parameters for the SQL statement

Columns a,b,c are the primary key for a unique row of information. Column d is what would be updated if a duplicate primary key was found, so it shouldn't be listed within the duplicate value keys. — Z.Mack
– Z.Mack, Commented Feb 26, 2019 at 20:29
You have 7 parameters in the sql code, but provide only 4 in python. Remember, the prepared statement will not know that it should use the first three parameters twice! — Shadow
– Shadow, Commented Feb 26, 2019 at 20:47

Nae · Accepted Answer · 2019-02-26 20:46:33Z

4

Replace:

cursor.execute("INSERT INTO input_1 (a,b,c,d) VALUES (%s,%s,%s,%s) ON DUPLICATE KEY UPDATE VALUES a=%s,b=%s,c=%s", (item['a'],item['b'],item['c'],item['d']))

with:

cursor.execute("INSERT INTO input_1 (a,b,c,d) VALUES (%s,%s,%s,%s) ON DUPLICATE KEY UPDATE VALUES a=%s,b=%s,c=%s", (item['a'],item['b'],item['c'],item['d'],item['a'],item['b'],item['c']))

The sql statetement requires you to have 7 parameters not 4.

answered Feb 26, 2019 at 20:46

Nae

15.5k8 gold badges62 silver badges89 bronze badges

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

1 Comment

Mike_K Over a year ago

This didn't work for me with python 3. The answer here did: stackoverflow.com/questions/52260069/…

Saifullah khan · Accepted Answer · 2023-06-21 11:24:47Z

All in one function:

def insert_row(table, db, row, **kwargs):
    placeholders = ', '.join(['%s'] * len(row))
    columns = ', '.join(row.keys())
    ignore = 'IGNORE' if kwargs.get('insertIgnore') else ''
    onDuplicate = ''
    onDuplicateUpdate = kwargs.get('onDuplicateUpdate', [])
    values = list(row.values())
    if len(onDuplicateUpdate) > 0:
        updates = {}
        for k in onDuplicateUpdate:
            updates[k] = row[k]
        updatePlaceholders = ('=%s, '.join(updates.keys())) + '=%s'
        values.extend(list(updates.values()))
        onDuplicate = f'  ON DUPLICATE KEY UPDATE {updatePlaceholders}'

    sql = "INSERT %s INTO %s ( %s ) VALUES ( %s ) %s ;" % (ignore, table, columns, placeholders, onDuplicate)
    # print('sql', sql)
    rowInserted = db.cursor().execute(sql, values)
    db.commit()
    return rowInserted == 1

Usage Example:

user_details = {
    "id": "1",
    "name": "Saifullah Khan",
    "city": "Rawalpindi",
    "address": "Bahria Town Phase 8",
    "timestamp": datetime.now().strftime("%Y-%m-%d %H:%M:%S")
}
onDuplicateUpdate = ['city', 'address', 'timestamp']
insert_row('users', db, user_details, onDuplicateUpdate=onDuplicateUpdate, insertIgnore=True)

Collectives™ on Stack Overflow

MySQL Python ON DUPLICATE KEY UPDATE VALUES

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related