How to On Duplicate Key Update

python mysql sql on-duplicate-key
mchruss03 picture mchruss03 · Aug 15, 2014 · Viewed 9.7k times · Source

I have this query that is executed in my Python script but when its inserting into the database and finds a duplicate of my unique column it causes it to error and stops. I know I need to use On Duplicate Key Update but I'm note sure how to properly add this.

My unique column 2.

cur.execute("""INSERT INTO logs (1,2,3) VALUES (%s,%s,%s) """,(line[0], line[1], line[2]))

If there is a duplicate to have it update that row/entry.

Answer

Tim Zimmermann picture Tim Zimmermann · Aug 15, 2014

When I understand you correctly, what you are looking for is this:

cur.execute(""" INSERT INTO logs (1, 2, 3) VALUES (%s, %s, %s) ON DUPLICATE KEY UPDATE 1=%s, 3=%s """, (line[0], line[1], line[2], line[0], line[2]))

Check also Insert on duplicate.

Related questions

How do I get a raw, compiled SQL query from a SQLAlchemy expression?

I have a SQLAlchemy query object and want to get the text of the compiled SQL statement, with all its parameters bound (e.g. no %s or other variables waiting to be bound by the statement compiler or MySQLdb dialect …

Read More
How can I allow django admin to set a field to NULL?

I've set my Model field to null=True, which allows NULL in MySQL, but I can't seem to assign NULL to the field through Django Admin. I've tried also setting blank=True, but that just sets the field to an …

Read More
IMDB to MySQL: Insert IMDB data into MySQL database

I’m looking for a solution to import all the IMDB data into my own MySQL database. I’ve downloaded all the IMDB data files from their homepage which are all in the file format *.list (in Windows). I want …

Read More