Peewee: Upsert support

[coleifer]

I'd like to consolidate the discussions in #942 and #1067 as they both concern extensions to basic INSERT and UPDATE queries. In this ticket I'll describe the various features and syntaxes Peewee will need to support -- please let me know if I've missed something!

---

SQLite

Docs.

• INSERT OR <REPLACE|ROLLBACK|ABORT|FAIL|IGNORE> INTO ...
• REPLACE INTO ... (as a shortcut for INSERT OR REPLACE).

These extra clauses are referred to in the docs as the ON CONFLICT CLAUSE. We are only interested in the IGNORE and REPLACE strategies. Here's how REPLACE works:

When a UNIQUE or PRIMARY KEY constraint violation occurs, the REPLACE algorithm deletes pre-existing rows that are causing the constraint violation prior to inserting or updating the current row and the command continues executing normally. If a NOT NULL constraint violation occurs, the REPLACE conflict resolution replaces the NULL value with the default value for that column, or if the column has no default value, then the ABORT algorithm is used. If a CHECK constraint violation occurs, the REPLACE conflict resolution algorithm always works like ABORT.

MySQL

Docs.

• INSERT IGNORE
• REPLACE INTO ...
• INSERT INTO ... ON DUPLICATE KEY UPDATE ...
  The last one is different from SQLite as it allows the user to specify a different query in the event of a conflict. Here's an example:

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

No special syntax seems to be needed to reference the column value of the conflicting row. To reference the value from the INSERT portion of the query you can use the VALUES() function. I believe the following would be equivalent to a REPLACE if the only constraint on the table was on column a:

INSERT INTO table (a,b,c) VALUES (1, 2, 3)
   ON DUPLICATE KEY UPDATE b = VALUES(b), c = VALUES(c)

Postgresql

Docs.

• INSERT INTO ... ON CONFLICT DO NOTHING
• INSERT INTO ... ON CONFLICT (conflict_target) DO UPDATE SET ...

Postgresql's upsert looks to be the most sophisticated, as you need to specify a conflict target to do an update. The conflict target description in the docs is very confusing but it seems to work fine if you just pass in the list of columns that have the appropriate constraint. You can also reference the value of the conflicting row using EXCLUDED.

Examples:

INSERT INTO distributors (did, dname)
    VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc')
    ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname;

-- Don't update existing distributors based in a certain ZIP code
INSERT INTO distributors AS d (did, dname) VALUES (8, 'Anvil Distribution')
    ON CONFLICT (did) DO UPDATE
    SET dname = EXCLUDED.dname || ' (formerly ' || d.dname || ')'
    WHERE d.zipcode <> '21201';

-- Name a constraint directly in the statement (uses associated
-- index to arbitrate taking the DO NOTHING action)
INSERT INTO distributors (did, dname) VALUES (9, 'Antwerp Design')
    ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING;

-- This statement could infer a partial unique index on "did"
-- with a predicate of "WHERE is_active", but it could also
-- just use a regular unique constraint on "did"
INSERT INTO distributors (did, dname) VALUES (10, 'Conrad International')
    ON CONFLICT (did) WHERE is_active DO NOTHING;

[coleifer]

Implementing INSERT/IGNORE for all databases should be straightforward.

For SQLite and for MySQL, in the simple case of a REPLACE query, the semantics are the same just slightly different SQL syntax.

For MySQL and Postgresql, in the not-so-simple case, it seems like the following things will need to be in-place in Peewee's APIs:

• Referencing the values that would have been inserted (VALUES(x) in MySQL, EXCLUDED.x in Postgres).
• Referencing the pre-existing value of a column in the event of an update.
• Specify which columns / constraint the conflict applies to (Postgres only).

[huangzhw]

In MySQL, Replace will delete the conflict row and insert a new row, but on duplicate key update just update the old row. So if the only constraint on the table was on column a, they are still different.

[coleifer]

@hzweveryday -- thank you for letting me know of that distinction, that's quite important!

[coleifer]

Roughing out some APIs / implementation: 500910da7f358c62b42ddd968b78f0352d7f49b3

[coleifer]

Implemented for SQLite, MySQL and Postgres: 8de7d16f022a92daae2d8c0b9ea798b375e09bff

Here are some examples (I'm open to suggestions on the API):

# Stupid simple example.
Person.insert(name='huey').on_conflict('ignore')

# Generates:
# sqlite: INSERT OR IGNORE INTO "person" ("name") VALUES ('huey')
# mysql: INSERT IGNORE INTO "person" ("name") VALUES ('huey')
# postgres: INSERT INTO "person" ("name") VALUES ('huey') ON CONFLICT DO NOTHING

# Slightly more interesting.
(Person
 .insert({Person.name: 'huey', Person.dob: some_date})
 .on_conflict(
     preserve=(Person.dob,),
     update={Person.name: Person.name.concat(' (updated)')}))

# Generates:
# sqlite: not supported
# mysql: INSERT INTO "person" ("name", "dob") VALUES ('huey', ...) 
#        ON DUPLICATE KEY UPDATE "dob" = VALUES("dob"), "name" = "name" || ' (updated)'
# postgres: requires a conflict target, see next example

(Person
 .insert(name='huey', dob=some_date)
 .on_conflict(
     conflict_target=(Person.name,),
     preserve=(Person.dob,),
     update={Person.name: Person.name.concat(' (updated)')))

# postgres: INSERT INTO "person" ("name", "dob") VALUES ('huey', ...) 
#           ON CONFLICT ("name")
#           DO UPDATE SET "dob" = EXCLUDED."dob", "name" = "name" || ' (updated)'

[c17r]

sqlite's INSERT OR REPLACE is not analogous to UPSERT. If you have a table with 4 fields -- 3 come from the outside and the 4th comes from elsewhere -- then INSERT OR REPLACE on that record loses the existing value in the 4th column.

[mikemill]

For MySQL I've found that REPLACE INTO is hardly ever the right thing to do. So the preference would be to use INSERT INTO ... ON DUPLICATE KEY.

That also allows you to basically do a mass update of the table with only partial fields.

Take a table with fields: id, a, b, c, d, e
You could do

INSERT INTO table (id, c) VALUES
  (1, 'something'),
  (2, 'somethingelse')
ON DUPLICATE KEY UPDATE
  c=VALUES(c)

and not lose the values in a, b, d, e (provided that each id already existed).

(And yes, I just leveraged that for a real life case with the exception being I used an INSERT INTO ... SELECT ... ON DUPLICATE KEY UPDATE instead of suppling the values manually)

[coleifer]

The upsert API for MySQL supports both REPLACE and ON DUPLICATE KEY.

[kootenpv]

The postgres example does not seem to work:

TypeError: on_conflict() got an unexpected keyword argument 'conflict_target'

[coleifer]

Postgres example from tests:

class Emp(TestModel):
    first = CharField()
    last = CharField()
    empno = CharField(unique=True)

    class Meta:
        indexes = (
            (('first', 'last'), True),
        )

test_data = (
        ('huey', 'cat', '123'),
        ('zaizee', 'cat', '124'),
        ('mickey', 'dog', '125'),
    )

for first, last, empno in self.test_data:
    Emp.create(first=first, last=last, empno=empno)

    def test_update(self):
        res = (Emp
               .insert(first='foo', last='bar', empno='125')
               .on_conflict(
                   conflict_target=(Emp.empno,),
                   preserve=(Emp.first, Emp.last),
                   update={Emp.empno: '125.1'})
               .execute())
        self.assertData([
            ('huey', 'cat', '123'),
            ('zaizee', 'cat', '124'),
            ('foo', 'bar', '125.1')])

        res = (Emp
               .insert(first='foo', last='bar', empno='126')
               .on_conflict(
                   conflict_target=(Emp.first, Emp.last),
                   preserve=(Emp.first,),
                   update={Emp.last: 'baze'})
               .execute())
        self.assertData([
            ('huey', 'cat', '123'),
            ('zaizee', 'cat', '124'),
            ('foo', 'baze', '125.1')])

[foxx]

Hey @coleifer, long time no speak :) Just ran into an issue with this, I'm trying to use bulk_insert() and would like it to ignore any duplicate entries. This was also raised in https://github.com/coleifer/peewee/issues/1067 . Tried using on_conflict() but didn't seem to do anything when using bulk_insert(). Any ideas?

EDIT: Ahh never mind, it was a bug on my own code, on_conflict('IGNORE') seems to work beautifully!

[coleifer]

Oh hell yeah I'm glad to hear! I've been using it myself but only with sqlite. Good to hear from you @foxx

[willyhakim]

This project is so amazing! @coleifer Just wanted to say that you're amazing. One the best API designs I've seen. Thanks

[coleifer]

Glad you're enjoying the project!

[joe-urciuoli]

Any advice on how to get this to work with bulk operations? I'm trying to get it to function with bulk_insert_mappings. on_conflict('ignore') doesn't seem to have any effect.

session.bulk_insert_mappings(
            Event,
            event_list
        ).on_conflict('ignore')

[coleifer]

Session and bulk insert mapping are not peewee apis.

[coleifer]

Peewee would use:

Event.insert_many(event_list).on_conflict('ignore')

Assuming event_list was a list of dictionaries.