CODEWITHSUNDEEP
pythonmysql
Z.Mack
Z.Mack

Reputation: 53

MySQL Python ON DUPLICATE KEY UPDATE VALUES

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

Upvotes: 2

Views: 11679

Answers (2)

Saifullah khan
Saifullah khan

Reputation: 778

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)

Upvotes: 0

Nae
Nae

Reputation: 15325

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.

Upvotes: 4

Related Questions