Expressions

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

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

• 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 bitwiseand and or. Operatorprecedence is controlled by python and comparisons can be nested to anarbitrary 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 comparedagainst 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 userow-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 froma subquery in a single expression. There are other ways to express these typesof queries, but row-values may offer a concise and readable approach.

For example, assume we have a table “EventLog” which contains an event type, anevent source, and some metadata. We also have an “IncidentLog”, which hasincident type, incident source, and metadata columns. We can use row-values tocorrelate 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 joinor to join on a subquery. The above example is therejust 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 newdata is derived from a subquery. For an example, see here.