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

Accepted Answer

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

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

Solution

Problem

Related problems