Hacks

Collected hacks using peewee. Have a cool hack you’d like to share? Open an issue on GitHub or contact me.

Optimistic Locking

Optimistic locking is useful in situations where you might ordinarily use a SELECT FOR UPDATE (or in SQLite, BEGIN IMMEDIATE). For example, you might fetch a user record from the database, make some modifications, then save the modified user record. Typically this scenario would require us to lock the user record for the duration of the transaction, from the moment we select it, to the moment we save our changes.

In optimistic locking, on the other hand, we do not acquire any lock and instead rely on an internal version column in the row we’re modifying. At read time, we see what version the row is currently at, and on save, we ensure that the update takes place only if the version is the same as the one we initially read. If the version is higher, then some other process must have snuck in and changed the row – to save our modified version could result in the loss of important changes.

It’s quite simple to implement optimistic locking in Peewee, here is a base class that you can use as a starting point:

  1. from peewee import *
  3. class ConflictDetectedException(Exception): pass
  5. class BaseVersionedModel(Model):
  6.     version = IntegerField(default=1, index=True)
  8.     def save_optimistic(self):
  9.         if not self.id:
  10.             # This is a new record, so the default logic is to perform an
  11.             # INSERT. Ideally your model would also have a unique
  12.             # constraint that made it impossible for two INSERTs to happen
  13.             # at the same time.
  14.             return self.save()
  16.         # Update any data that has changed and bump the version counter.
  17.         field_data = dict(self.__data__)
  18.         current_version = field_data.pop('version', 1)
  19.         self._populate_unsaved_relations(field_data)
  20.         field_data = self._prune_fields(field_data, self.dirty_fields)
  21.         if not field_data:
  22.             raise ValueError('No changes have been made.')
  24.         ModelClass = type(self)
  25.         field_data['version'] = ModelClass.version + 1  # Atomic increment.
  27.         query = ModelClass.update(**field_data).where(
  28.             (ModelClass.version == current_version) &
  29.             (ModelClass.id == self.id))
  30.         if query.execute() == 0:
  31.             # No rows were updated, indicating another process has saved
  32.             # a new version. How you handle this situation is up to you,
  33.             # but for simplicity I'm just raising an exception.
  34.             raise ConflictDetectedException()
  35.         else:
  36.             # Increment local version to match what is now in the db.
  37.             self.version += 1
  38.             return True

Here’s an example of how this works. Let’s assume we have the following model definition. Note that there’s a unique constraint on the username – this is important as it provides a way to prevent double-inserts.

  1. class User(BaseVersionedModel):
  2.     username = CharField(unique=True)
  3.     favorite_animal = CharField()

Example:

  1. >>> u = User(username='charlie', favorite_animal='cat')
  2. >>> u.save_optimistic()
  3. True
  5. >>> u.version
  6. 1
  8. >>> u.save_optimistic()
  9. Traceback (most recent call last):
  10.   File "<stdin>", line 1, in <module>
  11.   File "x.py", line 18, in save_optimistic
  12.     raise ValueError('No changes have been made.')
  13. ValueError: No changes have been made.
  15. >>> u.favorite_animal = 'kitten'
  16. >>> u.save_optimistic()
  17. True
  19. # Simulate a separate thread coming in and updating the model.
  20. >>> u2 = User.get(User.username == 'charlie')
  21. >>> u2.favorite_animal = 'macaw'
  22. >>> u2.save_optimistic()
  23. True
  25. # Now, attempt to change and re-save the original instance:
  26. >>> u.favorite_animal = 'little parrot'
  27. >>> u.save_optimistic()
  28. Traceback (most recent call last):
  29.   File "<stdin>", line 1, in <module>
  30.   File "x.py", line 30, in save_optimistic
  31.     raise ConflictDetectedException()
  32. ConflictDetectedException: current version is out of sync

Top object per group

These examples describe several ways to query the single top item per group. For a thorough discuss of various techniques, check out my blog post Querying the top item by group with Peewee ORM. If you are interested in the more general problem of querying the top N items, see the section below Top N objects per group.

In these examples we will use the User and Tweet models to find each user and their most-recent tweet.

The most efficient method I found in my testing uses the MAX() aggregate function.

We will perform the aggregation in a non-correlated subquery, so we can be confident this method will be performant. The idea is that we will select the posts, grouped by their author, whose timestamp is equal to the max observed timestamp for that user.

  1. # When referencing a table multiple times, we'll call Model.alias() to create
  2. # a secondary reference to the table.
  3. TweetAlias = Tweet.alias()
  5. # Create a subquery that will calculate the maximum Tweet created_date for each
  6. # user.
  7. subquery = (TweetAlias
  8.             .select(
  9.                 TweetAlias.user,
  10.                 fn.MAX(TweetAlias.created_date).alias('max_ts'))
  11.             .group_by(TweetAlias.user)
  12.             .alias('tweet_max_subquery'))
  14. # Query for tweets and join using the subquery to match the tweet's user
  15. # and created_date.
  16. query = (Tweet
  17.          .select(Tweet, User)
  18.          .join(User)
  19.          .switch(Tweet)
  20.          .join(subquery, on=(
  21.              (Tweet.created_date == subquery.c.max_ts) &
  22.              (Tweet.user == subquery.c.user_id))))

SQLite and MySQL are a bit more lax and permit grouping by a subset of the columns that are selected. This means we can do away with the subquery and express it quite concisely:

  1. query = (Tweet
  2.          .select(Tweet, User)
  3.          .join(User)
  4.          .group_by(Tweet.user)
  5.          .having(Tweet.created_date == fn.MAX(Tweet.created_date)))

Top N objects per group

These examples describe several ways to query the top N items per group reasonably efficiently. For a thorough discussion of various techniques, check out my blog post Querying the top N objects per group with Peewee ORM.

In these examples we will use the User and Tweet models to find each user and their three most-recent tweets.

Postgres lateral joins

Lateral joins are a neat Postgres feature that allow reasonably efficient correlated subqueries. They are often described as SQL for each loops.

The desired SQL is:

  1. SELECT * FROM
  2.   (SELECT id, username FROM user) AS uq
  3.    LEFT JOIN LATERAL
  4.   (SELECT message, created_date
  5.    FROM tweet
  6.    WHERE (user_id = uq.id)
  7.    ORDER BY created_date DESC LIMIT 3)
  8.   AS pq ON true

To accomplish this with peewee is quite straightforward:

  1. subq = (Tweet
  2.         .select(Tweet.message, Tweet.created_date)
  3.         .where(Tweet.user == User.id)
  4.         .order_by(Tweet.created_date.desc())
  5.         .limit(3))
  7. query = (User
  8.          .select(User, subq.c.content, subq.c.created_date)
  9.          .join(subq, JOIN.LEFT_LATERAL)
  10.          .order_by(User.username, subq.c.created_date.desc()))
  12. # We queried from the "perspective" of user, so the rows are User instances
  13. # with the addition of a "content" and "created_date" attribute for each of
  14. # the (up-to) 3 most-recent tweets for each user.
  15. for row in query:
  16.     print(row.username, row.content, row.created_date)

To implement an equivalent query from the “perspective” of the Tweet model, we can instead write:

  1. # subq is the same as the above example.
  2. subq = (Tweet
  3.         .select(Tweet.message, Tweet.created_date)
  4.         .where(Tweet.user == User.id)
  5.         .order_by(Tweet.created_date.desc())
  6.         .limit(3))
  8. query = (Tweet
  9.          .select(User.username, subq.c.content, subq.c.created_date)
  10.          .from_(User)
  11.          .join(subq, JOIN.LEFT_LATERAL)
  12.          .order_by(User.username, subq.c.created_date.desc()))
  14. # Each row is a "tweet" instance with an additional "username" attribute.
  15. # This will print the (up-to) 3 most-recent tweets from each user.
  16. for tweet in query:
  17.     print(tweet.username, tweet.content, tweet.created_date)

Window functions

Window functions, which are supported by peewee, provide scalable, efficient performance.

The desired SQL is:

  1. SELECT subq.message, subq.username
  2. FROM (
  3.     SELECT
  4.         t2.message,
  5.         t3.username,
  6.         RANK() OVER (
  7.             PARTITION BY t2.user_id
  8.             ORDER BY t2.created_date DESC
  9.         ) AS rnk
  10.     FROM tweet AS t2
  11.     INNER JOIN user AS t3 ON (t2.user_id = t3.id)
  12. ) AS subq
  13. WHERE (subq.rnk <= 3)

To accomplish this with peewee, we will wrap the ranked Tweets in an outer query that performs the filtering.

  1. TweetAlias = Tweet.alias()
  3. # The subquery will select the relevant data from the Tweet and
  4. # User table, as well as ranking the tweets by user from newest
  5. # to oldest.
  6. subquery = (TweetAlias
  7.             .select(
  8.                 TweetAlias.message,
  9.                 User.username,
  10.                 fn.RANK().over(
  11.                     partition_by=[TweetAlias.user],
  12.                     order_by=[TweetAlias.created_date.desc()]).alias('rnk'))
  13.             .join(User, on=(TweetAlias.user == User.id))
  14.             .alias('subq'))
  16. # Since we can't filter on the rank, we are wrapping it in a query
  17. # and performing the filtering in the outer query.
  18. query = (Tweet
  19.          .select(subquery.c.message, subquery.c.username)
  20.          .from_(subquery)
  21.          .where(subquery.c.rnk <= 3))

Other methods

If you’re not using Postgres, then unfortunately you’re left with options that exhibit less-than-ideal performance. For a more complete overview of common methods, check out this blog post. Below I will summarize the approaches and the corresponding SQL.

Using COUNT, we can get all tweets where there exist less than N tweets with more recent timestamps:

  1. TweetAlias = Tweet.alias()
  3. # Create a correlated subquery that calculates the number of
  4. # tweets with a higher (newer) timestamp than the tweet we're
  5. # looking at in the outer query.
  6. subquery = (TweetAlias
  7.             .select(fn.COUNT(TweetAlias.id))
  8.             .where(
  9.                 (TweetAlias.created_date >= Tweet.created_date) &
  10.                 (TweetAlias.user == Tweet.user)))
  12. # Wrap the subquery and filter on the count.
  13. query = (Tweet
  14.          .select(Tweet, User)
  15.          .join(User)
  16.          .where(subquery <= 3))

We can achieve similar results by doing a self-join and performing the filtering in the HAVING clause:

  1. TweetAlias = Tweet.alias()
  3. # Use a self-join and join predicates to count the number of
  4. # newer tweets.
  5. query = (Tweet
  6.          .select(Tweet.id, Tweet.message, Tweet.user, User.username)
  7.          .join(User)
  8.          .switch(Tweet)
  9.          .join(TweetAlias, on=(
  10.              (TweetAlias.user == Tweet.user) &
  11.              (TweetAlias.created_date >= Tweet.created_date)))
  12.          .group_by(Tweet.id, Tweet.content, Tweet.user, User.username)
  13.          .having(fn.COUNT(Tweet.id) <= 3))

The last example uses a LIMIT clause in a correlated subquery.

  1. TweetAlias = Tweet.alias()
  3. # The subquery here will calculate, for the user who created the
  4. # tweet in the outer loop, the three newest tweets. The expression
  5. # will evaluate to `True` if the outer-loop tweet is in the set of
  6. # tweets represented by the inner query.
  7. query = (Tweet
  8.          .select(Tweet, User)
  9.          .join(User)
  10.          .where(Tweet.id << (
  11.              TweetAlias
  12.              .select(TweetAlias.id)
  13.              .where(TweetAlias.user == Tweet.user)
  14.              .order_by(TweetAlias.created_date.desc())
  15.              .limit(3))))

Writing custom functions with SQLite

SQLite is very easy to extend with custom functions written in Python, that are then callable from your SQL statements. By using the SqliteExtDatabase and the func() decorator, you can very easily define your own functions.

Here is an example function that generates a hashed version of a user-supplied password. We can also use this to implement login functionality for matching a user and password.

  1. from hashlib import sha1
  2. from random import random
  3. from playhouse.sqlite_ext import SqliteExtDatabase
  5. db = SqliteExtDatabase('my-blog.db')
  7. def get_hexdigest(salt, raw_password):
  8.     data = salt + raw_password
  9.     return sha1(data.encode('utf8')).hexdigest()
  11. @db.func()
  12. def make_password(raw_password):
  13.     salt = get_hexdigest(str(random()), str(random()))[:5]
  14.     hsh = get_hexdigest(salt, raw_password)
  15.     return '%s$%s' % (salt, hsh)
  17. @db.func()
  18. def check_password(raw_password, enc_password):
  19.     salt, hsh = enc_password.split('$', 1)
  20.     return hsh == get_hexdigest(salt, raw_password)

Here is how you can use the function to add a new user, storing a hashed password:

  1. query = User.insert(
  2.     username='charlie',
  3.     password=fn.make_password('testing')).execute()

If we retrieve the user from the database, the password that’s stored is hashed and salted:

  1. >>> user = User.get(User.username == 'charlie')
  2. >>> print(user.password)
  3. b76fa$88be1adcde66a1ac16054bc17c8a297523170949

To implement login-type functionality, you could write something like this:

  1. def login(username, password):
  2.     try:
  3.         return (User
  4.                 .select()
  5.                 .where(
  6.                     (User.username == username) &
  7.                     (fn.check_password(password, User.password) == True))
  8.                 .get())
  9.     except User.DoesNotExist:
  10.         # Incorrect username and/or password.
  11.         return False

Date math

Each of the databases supported by Peewee implement their own set of functions and semantics for date/time arithmetic.

This section will provide a short scenario and example code demonstrating how you might utilize Peewee to do dynamic date manipulation in SQL.

Scenario: we need to run certain tasks every X seconds, and both the task intervals and the task themselves are defined in the database. We need to write some code that will tell us which tasks we should run at a given time:

  1. class Schedule(Model):
  2.     interval = IntegerField()  # Run this schedule every X seconds.
  5. class Task(Model):
  6.     schedule = ForeignKeyField(Schedule, backref='tasks')
  7.     command = TextField()  # Run this command.
  8.     last_run = DateTimeField()  # When was this run last?

Our logic will essentially boil down to:

  1. # e.g., if the task was last run at 12:00:05, and the associated interval
  2. # is 10 seconds, the next occurrence should be 12:00:15. So we check
  3. # whether the current time (now) is 12:00:15 or later.
  4. now >= task.last_run + schedule.interval

So we can write the following code:

  1. next_occurrence = something  # ??? how do we define this ???
  3. # We can express the current time as a Python datetime value, or we could
  4. # alternatively use the appropriate SQL function/name.
  5. now = Value(datetime.datetime.now())  # Or SQL('current_timestamp'), e.g.
  7. query = (Task
  8.          .select(Task, Schedule)
  9.          .join(Schedule)
  10.          .where(now >= next_occurrence))

For Postgresql we will multiple a static 1-second interval to calculate the offsets dynamically:

  1. second = SQL("INTERVAL '1 second'")
  2. next_occurrence = Task.last_run + (Schedule.interval * second)

For MySQL we can reference the schedule’s interval directly:

  1. from peewee import NodeList  # Needed to construct sql entity.
  3. interval = NodeList((SQL('INTERVAL'), Schedule.interval, SQL('SECOND')))
  4. next_occurrence = fn.date_add(Task.last_run, interval)

For SQLite, things are slightly tricky because SQLite does not have a dedicated datetime type. So for SQLite, we convert to a unix timestamp, add the schedule seconds, then convert back to a comparable datetime representation:

  1. next_ts = fn.strftime('%s', Task.last_run) + Schedule.interval
  2. next_occurrence = fn.datetime(next_ts, 'unixepoch')