Atomic updates

Peewee allows you to perform atomic updates. Let’s suppose we need to updatesome counters. The naive approach would be to write something like this:

>>> for stat in Stat.select().where(Stat.url == request.url):
...     stat.counter += 1
...     stat.save()

Do not do this! Not only is this slow, but it is also vulnerable to raceconditions if multiple processes are updating the counter at the same time.

Instead, you can update the counters atomically using update():

>>> query = Stat.update(counter=Stat.counter + 1).where(Stat.url == request.url)
>>> query.execute()

You can make these update statements as complex as you like. Let’s give all ouremployees a bonus equal to their previous bonus plus 10% of their salary:

>>> query = Employee.update(bonus=(Employee.bonus + (Employee.salary * .1)))
>>> query.execute()  # Give everyone a bonus!

We can even use a subquery to update the value of a column. Suppose we had adenormalized column on the User model that stored the number of tweets auser had made, and we updated this value periodically. Here is how you mightwrite such a query:

>>> subquery = Tweet.select(fn.COUNT(Tweet.id)).where(Tweet.user == User.id)
>>> update = User.update(num_tweets=subquery)
>>> update.execute()

Upsert

Peewee provides support for varying types of upsert functionality. With SQLiteprior to 3.24.0 and MySQL, Peewee offers the replace(), whichallows you to insert a record or, in the event of a constraint violation,replace the existing record.

Example of using replace() and on_conflict_replace():

class User(Model):
    username = TextField(unique=True)
    last_login = DateTimeField(null=True)
 
# Insert or update the user. The "last_login" value will be updated
# regardless of whether the user existed previously.
user_id = (User
           .replace(username='the-user', last_login=datetime.now())
           .execute())
 
# This query is equivalent:
user_id = (User
           .insert(username='the-user', last_login=datetime.now())
           .on_conflict_replace()
           .execute())

Note

In addition to replace, SQLite, MySQL and Postgresql provide an _ignore_action (see: on_conflict_ignore()) if you simply wish toinsert and ignore any potential constraint violation.

MySQL supports upsert via the ON DUPLICATE KEY UPDATE clause. Forexample:

class User(Model):
    username = TextField(unique=True)
    last_login = DateTimeField(null=True)
    login_count = IntegerField()
 
# Insert a new user.
User.create(username='huey', login_count=0)
 
# Simulate the user logging in. The login count and timestamp will be
# either created or updated correctly.
now = datetime.now()
rowid = (User
         .insert(username='huey', last_login=now, login_count=1)
         .on_conflict(
             preserve=[User.last_login],  # Use the value we would have inserted.
             update={User.login_count: User.login_count + 1})
         .execute())

In the above example, we could safely invoke the upsert query as many times aswe wanted. The login count will be incremented atomically, the last logincolumn will be updated, and no duplicate rows will be created.

Postgresql and SQLite (3.24.0 and newer) provide a different syntax thatallows for more granular control over which constraint violation should triggerthe conflict resolution, and what values should be updated or preserved.

Example of using on_conflict() to perform a Postgresql-styleupsert (or SQLite 3.24+):

class User(Model):
    username = TextField(unique=True)
    last_login = DateTimeField(null=True)
    login_count = IntegerField()
 
# Insert a new user.
User.create(username='huey', login_count=0)
 
# Simulate the user logging in. The login count and timestamp will be
# either created or updated correctly.
now = datetime.now()
rowid = (User
         .insert(username='huey', last_login=now, login_count=1)
         .on_conflict(
             conflict_target=[User.username],  # Which constraint?
             preserve=[User.last_login],  # Use the value we would have inserted.
             update={User.login_count: User.login_count + 1})
         .execute())

In the above example, we could safely invoke the upsert query as many times aswe wanted. The login count will be incremented atomically, the last logincolumn will be updated, and no duplicate rows will be created.

Note

The main difference between MySQL and Postgresql/SQLite is that Postgresqland SQLite require that you specify a conflict_target.

Here is a more advanced (if contrived) example using the EXCLUDEDnamespace. The EXCLUDED helper allows us to reference values in theconflicting data. For our example, we’ll assume a simple table mapping a uniquekey (string) to a value (integer):

class KV(Model):
    key = CharField(unique=True)
    value = IntegerField()
 
# Create one row.
KV.create(key='k1', value=1)
 
# Demonstrate usage of EXCLUDED.
# Here we will attempt to insert a new value for a given key. If that
# key already exists, then we will update its value with the *sum* of its
# original value and the value we attempted to insert -- provided that
# the new value is larger than the original value.
query = (KV.insert(key='k1', value=10)
         .on_conflict(conflict_target=[KV.key],
                      update={KV.value: KV.value + EXCLUDED.value},
                      where=(EXCLUDED.value > KV.value)))
 
# Executing the above query will result in the following data being
# present in the "kv" table:
# (key='k1', value=11)
query.execute()
 
# If we attempted to execute the query *again*, then nothing would be
# updated, as the new value (10) is now less than the value in the
# original row (11).

For more information, see Insert.on_conflict() andOnConflict.