Insert or update a peewee record in python

Question

Is there a simple one-line way using peewee in Python of either inserting a record if the primary key does not already exist, or updating the record if it does already exist.

Currently i am using the code:

try:
    sql_database.create(record_key = record_key, record_data_2 = record_data_2)
except IntegrityError:  ## Occurs if primary_key already exists
    sql_database.update(record_key = record_key, record_data_2 = record_data_2)

I couldn't see a "create or update" command, but maybe i am missing something.

coleifer · Accepted Answer · 2021-09-27T12:10:28.307

30

Depends on the database.

For Sqlite and MySQL, peewee 3.x supports INSERT OR REPLACE. See docs: http://docs.peewee-orm.com/en/latest/peewee/api.html#Model.replace

For Postgresql and SQLite 3.24 and newer, peewee 3.x offers complete support of the ON CONFLICT clause. Note that you can use the "on_conflict" APIs with MySQL -- the restriction is that it doesn't support the "UPDATE" action. See docs: http://docs.peewee-orm.com/en/latest/peewee/api.html#OnConflict

Examples:

# Works with MySQL (which use "REPLACE")
result = (Emp
          .insert(first='mickey', last='dog', empno='1337')
          .on_conflict('replace')
          .execute())

# Works with Postgresql and SQLite (which supports ON CONFLICT ... UPDATE).
result = (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())

You can also use the get_or_create method: http://docs.peewee-orm.com/en/latest/peewee/api.html?highlight=get_or_create#Model.get_or_create

edited Sep 27 '21 at 12:10

answered Nov 20 '15 at 05:22

coleifer

24,887
6
60
75

1

can you use a create_or_get , then change the non-key elements (user.insert_date= datetime.now) and then save ? I get an error sometimes.. – maugch Aug 18 '16 at 14:57
3

the problem with upsert is that REPLACE can't be used for example when you have fields like insert_date, update_date on the record. You don't know the original insert_date to be successful with it. – maugch Aug 19 '16 at 08:31
note that peewee v3 does not offer upsert anymore. updated the answer & link to reflect this. – Greg Sadetsky Apr 24 '18 at 22:39
@GregSadetsky -- that's not even remotely correct. 3.x has more robust support for upsert than ever. See "on_conflict" for http://docs.peewee-orm.com/en/latest/peewee/api.html#Insert and http://docs.peewee-orm.com/en/latest/peewee/api.html#OnConflict – coleifer Apr 26 '18 at 00:31
1

Thanks @coleifer, and thanks for updating your answer to better reflect the new peewee API. My only point in saying that "peewee v3 does not offer upsert" is that "upsert", as a method, does not seem to exist anymore as part of v3 -- unlike in v2. That was my only point. Sorry for the miscommunication on my part -- I did not mean to say that upsert, as a functionality, did not exist in v3. Cheers – Greg Sadetsky Apr 26 '18 at 04:28
2

Note that `on conflict replace` will delete the old row and insert a new row, which means the id will change for that record. – ospider Sep 05 '19 at 00:47
I used `.get_or_create` and still get `IntegrityError: (1062` when a primary key matches the inserted document. – Marc Maxmeister Oct 23 '19 at 19:55

Insert or update a peewee record in python

1 Answers1