Atomic updates

Peewee allows you to perform atomic updates. Let’s suppose we need to update some 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 race conditions 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 our employees 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 a denormalized column on the User model that stored the number of tweets a user had made, and we updated this value periodically. Here is how you might write 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 SQLite prior to 3.24.0 and MySQL, Peewee offers the replace(), which allows 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 to insert and ignore any potential constraint violation.

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

Example of using on_conflict() to perform a Postgresql-style upsert (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 as we wanted. The login count will be incremented atomically, the last login column will be updated, and no duplicate rows will be created.

For more information, see Insert.on_conflict() and OnConflict.