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

Solution

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

Comments

[Yo-han]

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]

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