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
– Shadow, Commented Aug 7, 2019 at 23:12
You also seem to be missing a closing parenthesis after age, it should be age=values(age) — Shadow
– Shadow, Commented Aug 7, 2019 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
– nbk, Commented Aug 7, 2019 at 23:18
@nbk this is not a parametrized query, this is string interpolation. — Shadow
– Shadow, Commented Aug 7, 2019 at 23:19
You are also missing height from the field list, while you have it in the update section. — Shadow
– Shadow, Commented Aug 7, 2019 at 23:21

Bill Karwin · Accepted Answer · 2019-08-08 04:07:38Z

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

Collectives™ on Stack Overflow

Use ON DUPLICATE KEY UPDATE from python

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related