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

Have you tried using multiline string with multiple quotation marks for your SQL query instead of one quotation mark and backslash? Something like """ query string """. — Leon Markes
– Leon Markes, Commented Feb 11, 2021 at 14:02

Vineet Yadav · Accepted Answer · 2021-02-15 08:40:58Z

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)

AKX · Accepted Answer · 2021-02-06 06:31:27Z

0

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

answered Feb 6, 2021 at 6:31

AKX

171k16 gold badges146 silver badges228 bronze badges

1 Comment

Tokyo Over a year ago

I tired with commas as well but didnt work

Ramesh · Accepted Answer · 2021-02-15 15:56:52Z

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:

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"

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)"

Collectives™ on Stack Overflow

Python, MySql ON Duplicate Key Syntax Error

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related