2

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,))
Brisi
  • 1,653
  • 6
  • 24
  • 38

1 Answers1

6

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))
falsetru
  • 314,667
  • 49
  • 610
  • 551
  • 3
    This is not a upsert. This is a DELETE and INSERT. To do a upsert you should use the [INSERT ... ON DUPLICATE KEY UPDATE](https://stackoverflow.com/a/4205207/808097) syntax. – Yo-han Nov 13 '17 at 08:55
  • @Yo-han, Thank you for the information. I updated to answer according to your comment. – falsetru Nov 13 '17 at 17:22