How can I do upsert (update and insert) query in MySQL Python?

Question

I'm looking for a simple upsert (Update/Insert).

I have table in which I am inserting rows for books table but next time when I want to insert row I don't want to insert again data for that table just want to update with required columns if it exits there if not then create new row.

How can I do this in Mysql-python?

cursor.execute("""INSERT INTO books (book_code,book_name,created_at,updated_at) VALUES (%s,%s,%s,%s)""", (book_code,book_name,curr_time,curr_time,))

falsetru · Accepted Answer · 2017-11-13 17:21:49Z

11

MySQL has REPLACE statement:

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

cursor.execute("""
    REPLACE INTO books (book_code,book_name,created_at,updated_at)
    VALUES (%s,%s,%s,%s)""",
    (book_code,book_name,curr_time,curr_time,)
)

UPDATE According to comment of @Yo-han, REPLACE is like DELETE and INSERT, not UPSERT. Here's alternative using INSERT ... ON DUPLICATE KEY UPDATE:

cursor.execute("""
    INSERT INTO books (book_code,book_name,created_at,updated_at)
    VALUES (%s,%s,%s,%s)
    ON DUPLICATE KEY UPDATE book_name=%s, created_at=%s, updated_at=%s
""", (book_code, book_name, curr_time, curr_time, book_name, curr_time, curr_time))

edited Nov 13, 2017 at 17:21

answered Feb 18, 2015 at 3:43

falsetru

371k69 gold badges769 silver badges659 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Yo-han Over a year ago

This is not a upsert. This is a DELETE and INSERT. To do a upsert you should use the INSERT ... ON DUPLICATE KEY UPDATE syntax.

falsetru Over a year ago

@Yo-han, Thank you for the information. I updated to answer according to your comment.

Collectives™ on Stack Overflow

How can I do upsert (update and insert) query in MySQL Python?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related