Expressions

Peewee is designed to provide a simple, expressive, and pythonic way of constructing SQL queries. This section will provide a quick overview of some common types of expressions.

There are two primary types of objects that can be composed to create expressions:

• Field instances
• SQL aggregations and functions using fn

We will assume a simple “User” model with fields for username and other things. It looks like this:

class User(Model):
    username = CharField()
    is_admin = BooleanField()
    is_active = BooleanField()
    last_login = DateTimeField()
    login_count = IntegerField()
    failed_logins = IntegerField()

Comparisons use the Query operators:

# username is equal to 'charlie'
User.username == 'charlie'
# user has logged in less than 5 times
User.login_count < 5

Comparisons can be combined using bitwise and and or. Operator precedence is controlled by python and comparisons can be nested to an arbitrary depth:

# User is both and admin and has logged in today
(User.is_admin == True) & (User.last_login >= today)
# User's username is either charlie or charles
(User.username == 'charlie') | (User.username == 'charles')

Comparisons can be used with functions as well:

# user's username starts with a 'g' or a 'G':
fn.Lower(fn.Substr(User.username, 1, 1)) == 'g'

We can do some fairly interesting things, as expressions can be compared against other expressions. Expressions also support arithmetic operations:

# users who entered the incorrect more than half the time and have logged
# in at least 10 times
(User.failed_logins > (User.login_count * .5)) & (User.login_count > 10)

Expressions allow us to do atomic updates:

# when a user logs in we want to increment their login count:
User.update(login_count=User.login_count + 1).where(User.id == user_id)

Expressions can be used in all parts of a query, so experiment!

Row values

Many databases support row values, which are similar to Python tuple objects. In Peewee, it is possible to use row-values in expressions via Tuple. For example,

# If for some reason your schema stores dates in separate columns ("year",
# "month" and "day"), you can use row-values to find all rows that happened
# in a given month:
Tuple(Event.year, Event.month) == (2019, 1)

The more common use for row-values is to compare against multiple columns from a subquery in a single expression. There are other ways to express these types of queries, but row-values may offer a concise and readable approach.

For example, assume we have a table “EventLog” which contains an event type, an event source, and some metadata. We also have an “IncidentLog”, which has incident type, incident source, and metadata columns. We can use row-values to correlate incidents with certain events:

class EventLog(Model):
    event_type = TextField()
    source = TextField()
    data = TextField()
    timestamp = TimestampField()
class IncidentLog(Model):
    incident_type = TextField()
    source = TextField()
    traceback = TextField()
    timestamp = TimestampField()
# Get a list of all the incident types and sources that have occured today.
incidents = (IncidentLog
             .select(IncidentLog.incident_type, IncidentLog.source)
             .where(IncidentLog.timestamp >= datetime.date.today()))
# Find all events that correlate with the type and source of the
# incidents that occured today.
events = (EventLog
          .select()
          .where(Tuple(EventLog.event_type, EventLog.source).in_(incidents))
          .order_by(EventLog.timestamp))

Other ways to express this type of query would be to use a join or to join on a subquery. The above example is there just to give you and idea how Tuple might be used.

You can also use row-values to update multiple columns in a table, when the new data is derived from a subquery. For an example, see here.