Python, MySql ON Duplicate Key Syntax Error

Question

All,

I am using mysql.connector to connect and perform DB operation from my python code. I started getting the below error the moment I added ON DUPLICATE KEY UPDATE query.

Failed to insert into MySQL table 1064 (42000): 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 '''), Assigned_to=values(''), Assignment_group=values('Updated'), Company=values(' at line 1

My Code that performs the operation is

mycursor = connection.cursor()
                sql = "INSERT INTO server(Asset_tag,Assigned_to,Assignment_group,Company,CPU_core_count,CPU_count,CPU_manufacturer,CPU_speed,CPU_type,Diskspace,DNS_Domain,Manufacturer,Name,Operating_System,OS_Domain,OS_Service_Pack,OS_Version,RAM,Serial_number,Description) VALUES (%s %s %s %s %s %s %s %s %s %s %s %s %s %s %s %s %s %s %s %s) \
                ON DUPLICATE KEY \
                UPDATE  Asset_tag=values(%s), Assigned_to=values(%s), Assignment_group=values(%s), Company=values(%s), Serial_number=values(%s)"
                val = (
                    data[0]["Asset tag"],
                    data[0]["Assigned to"],
                    "Temp",
                    data[0]["Company"],
                    data[0]["CPU core count"],
                    data[0]["CPU count"],
                    data[0]["CPU manufacturer"],
                    data[0]["CPU speed (MHz)"],
                    data[0]["CPU type"],
                    data[0]["Disk space (GB)"],
                    data[0]["DNS Domain"],
                    data[0]["Manufacturer"],
                    data[0]["Name"],
                    data[0]["Operating System"],
                    data[0]["OS Domain"],
                    data[0]["OS Service Pack"],
                    data[0]["OS Version"],
                    data[0]["RAM (MB)"],
                    data[0]["Serial number"],
                    data[0]["Description"],
                    data[0]["Asset tag"],
                    data[0]["Assigned to"],
                    "Updated",
                    data[0]["Company"],
                    data[0]["Serial number"],
                )
                print(val)
                mycursor.execute(sql, (val))
                connection.commit()

Update:

The below also doesn't work

sql = "INSERT INTO server(Asset_tag,Assigned_to,Assignment_group,Company,CPU_core_count,CPU_count,CPU_manufacturer,CPU_speed,CPU_type,Diskspace,DNS_Domain,Manufacturer,Name,Operating_System,OS_Domain,OS_Service_Pack,OS_Version,RAM,Serial_number,Description) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) \
                ON DUPLICATE KEY \
                UPDATE  Asset_tag=VALUES(%s), Assigned_to=VALUES(%s), Assignment_group=VALUES(%s), Company=VALUES(%s), Serial_number=VALUES(%s)"

Referred to these link but didn't help:

https://topherpedersen.blog/2019/12/10/how-to-use-on-duplicate-key-update-with-python-mysql-and-mysql-connector/

MySQL Python ON DUPLICATE KEY UPDATE VALUES

Answer 1

There is an issue with the way VALUES function is used in ON DUPLICATE KEY UPDATE. Here is a snippet from documentation explaining the same.

In assignment value expressions in the ON DUPLICATE KEY UPDATE clause, you can use the VALUES(col_name) function to refer to column values from the INSERT portion of the INSERT ... ON DUPLICATE KEY UPDATE statement. In other words, VALUES(col_name) in the ON DUPLICATE KEY UPDATE clause refers to the value of col_name that would be inserted, had no duplicate-key conflict occurred. This function is especially useful in multiple-row inserts.

Link to documentation

Solutions:

1. We can either avoid the usage of VALUES function and update the values directly as below:

    sql = "INSERT INTO server(Asset_tag,Assigned_to,Assignment_group,Company,CPU_core_count,CPU_count,CPU_manufacturer,CPU_speed,CPU_type,Diskspace,DNS_Domain,Manufacturer,Name,Operating_System,OS_Domain,OS_Service_Pack,OS_Version,RAM,Serial_number,Description) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) \
            ON DUPLICATE KEY \
            UPDATE  Asset_tag=%s, Assigned_to=%s, Assignment_group=%s, Company=%s, Serial_number=%s"
   

2. Pass on the exact field names from insert statement as below:

    sql = "INSERT INTO server(Asset_tag,Assigned_to,Assignment_group,Company,CPU_core_count,CPU_count,CPU_manufacturer,CPU_speed,CPU_type,Diskspace,DNS_Domain,Manufacturer,Name,Operating_System,OS_Domain,OS_Service_Pack,OS_Version,RAM,Serial_number,Description) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) \
            ON DUPLICATE KEY \
            UPDATE  Asset_tag=VALUES(Asset_tag), Assigned_to=VALUES(Assigned_to), Assignment_group=%s, Company=VALUES(Company), Serial_number=VALUES(Serial_number)"
   

Answer 2

try this in sql:

sql = "INSERT INTO server(Asset_tag,Assigned_to,Assignment_group,Company,CPU_core_count,CPU_count,CPU_manufacturer,CPU_speed,CPU_type,Diskspace,DNS_Domain,Manufacturer,Name,Operating_System,OS_Domain,OS_Service_Pack,OS_Version,RAM,Serial_number,Description) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) \
            ON DUPLICATE KEY \
            UPDATE  Asset_tag=VALUES(Asset_tag), Assigned_to=VALUES(Assigned_to), Assignment_group=VALUES(Assignment_group), Company=VALUES(Company), Serial_number=VALUES(Serial_number);"

Note: Remove extra keys you are sending in val or you can skip use of VALUES(col_name) and directly assign value..

Refer to this for proper use of ON DUPLICATE KEY: https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html

In assignment value expressions in the ON DUPLICATE KEY UPDATE clause, you can use the VALUES(col_name)

Answer 3

The value placeholders need to be separated with commas too, not just spaces.