Use ON DUPLICATE KEY UPDATE from python

Question

I'm trying to use this code to update a table on mySQL, but I'm getting error with the update part

table_name = 'my_table'
sql_select_Query = """ 
INSERT {0}  (age, city, gender,UniqueId)
VALUES ({1},{2},{3},{4})
ON DUPLICATE KEY UPDATE 
  age=VALUES(age,city=VALUES(city),gender=VALUES(gender),height=VALUES(height)
""".format(table_name,  '877','2','1','2898989')

    cursor = mySQLconnection .cursor()
    cursor.execute(sql_select_Query)
    mySQLconnection.commit()

For example, to update the city I get:

Unknow columns '877'

Hence it seems it is taking the value as a column name and search for then in my_table.

As a start I would print out the final sql query after the substitutions have been made. — Shadow, Aug 07 '19 at 23:12
You also seem to be missing a closing parenthesis after `age`, it should be `age=values(age)` — Shadow, Aug 07 '19 at 23:17
age=VALUES(age must be age=VALUES(age) but your string doesn't look like the parameterized mysql i come to know — nbk, Aug 07 '19 at 23:18
@nbk this is not a parametrized query, this is string interpolation. — Shadow, Aug 07 '19 at 23:19
You are also missing `height` from the field list, while you have it in the update section. — Shadow, Aug 07 '19 at 23:21
i know, but the nonsense on this page is to use parametrized statement sql imnjection and all that. besides it is easier to read — nbk, Aug 07 '19 at 23:21
You can't use `VALUES(height)` if `height` isn't one of the columns you're inserting into. — Barmar, Aug 08 '19 at 00:13

Bill Karwin · Accepted Answer · 2019-08-08T04:07:38.257

The correct way to use parameters with Python is to pass the values in the execute() call, not to interpolate the values into the SQL query.

Except for identifiers like the table name in your case.

sql_select_Query = """
INSERT `{0}`  (age, city, gender, UniqueId)
VALUES (%s, %s, %s, %s)
ON DUPLICATE KEY UPDATE
  age=VALUES(age), city=VALUES(city), gender=VALUES(gender)
""".format(table_name)

cursor.execute(sql_select_Query, ('877', '2', '1', '2898989'))

Use ON DUPLICATE KEY UPDATE from python

1 Answers1