Query Expressions
The where clause is where a Peewee query becomes specific, and Peewee's expression language is the part of the ORM that takes the most practice: field operators build SQL conditions, logical combinators (&, |, ~) compose them, and the string operators (LIKE, IN, BETWEEN) cover the pattern-matching queries. The payoff is that the expression reads like the condition itself - Author.name == "Ada" & Author.is_active is the AND of two conditions - and print(q) shows the exact SQL before it runs. This chapter is the reference for that language.
Field Operators: The Condition Vocabulary
from peewee import *
# equality and comparison - these build SQL, they are not Python truth tests
q1 = Author.select().where(Author.id == 7)
q2 = Article.select().where(Article.views > 1000)
q3 = Article.select().where(Article.rating < 3.5)
q4 = Author.select().where(Author.created_at >= datetime(2026, 1, 1))
# != is distinct, None maps to IS NULL (never == None in SQL)
q5 = Article.select().where(Article.rating != None)
q6 = Author.select().where(Author.bio.is_null())
q7 = Author.select().where(Author.bio.is_not_null())
print(q2) # SELECT ... FROM article WHERE ("views" > 1000)The operators on a field (==, !=, >, >=, <, <=) do not compare Python values - they build Expression objects that render as SQL. That distinction is the whole mental model: Author.name == "Ada" is not a boolean, it is a piece of a query. The null cases deserve special care because Python's == None is not SQL's IS NULL - a column equal to NULL is never true in SQL, so the field's is_null()/is_not_null() methods are the correct spelling, and using field == None silently produces WHERE col = NULL that matches nothing. The print(q) habit catches this in seconds.
Logical Combinators: AND, OR, NOT
from peewee import *
# & is AND, | is OR, ~ is NOT - the SQL logic as Python operators
q = Article.select().where(
(Article.published == True) & (Article.views > 100)
)
# OR must be parenthesized - & binds tighter than |, like * vs +
q2 = Article.select().where(
(Article.rating > 4) | (Article.views > 10000)
)
# ~ negates a condition
q3 = Author.select().where(~(Author.is_active == True))
# .alias() prevents the same field appearing twice in one expression
# (required by some backends when a field repeats)
q4 = Article.select().where(
Article.views.alias("v1") > 100 & Article.views.alias("v2") < 5000
)The three combinators map directly to SQL: & to AND, | to OR, ~ to NOT. The one rule that bites everyone is precedence - & binds tighter than |, exactly like multiplication over addition - so a | b & c is a | (b & c), and an OR that should apply to a whole group needs explicit parentheses. The .alias() form is the escape hatch for expressions where a column legitimately appears twice (PostgreSQL rejects a bare repeated column in some positions); aliasing gives each occurrence a distinct name in the SQL. Build the condition as a named variable when it gets long - readability of the query is readability of the SQL.
String and Set Operators
from peewee import *
# IN: membership in a set of values
q = Author.select().where(Author.email.in_([
"[email protected]", "[email protected]",
]))
# LIKE: the ** operator is LIKE, **= is case-insensitive (postgres)
q2 = Article.select().where(Article.title ** "%python%")
q3 = Article.select().where(Article.title.startswith("Intro to"))
# BETWEEN: a range condition
q4 = Article.select().where(Article.views.between(100, 10000))
# the negated forms
q5 = Author.select().where(~Author.email.in_(["[email protected]"]))
q6 = Article.select().where(Article.title.not_like("%draft%"))The string and set operators cover the pattern queries: field.in_([...]) renders IN (...), the ** operator renders LIKE (with % wildcards in the pattern), field.between(a, b) renders BETWEEN, and the helper methods (startswith, not_like) spell the common cases readably. The negated forms are ~ over the operator (~Author.email.in_(...)) or the not_-prefixed helpers. For a list of values from user input, in_ is the safe, parameterized spelling of a multi-value equality - never build it by string-concatenating the values, because the parameterization is what keeps the values out of the SQL text.
Ordering, Limiting, and the Full Query
from peewee import *
# the full shape of a read query:
# select fields -> where -> order_by -> limit/offset -> execute
rows = (Article.select()
.where(Article.published == True)
.order_by(Article.views.desc())
.limit(10)
.offset(20)
.execute())
# select specific fields (a projection, not the whole row)
light = (Article.select(Article.id, Article.title, Article.views)
.where(Article.views > 100)
.execute())
# distinct: collapse duplicate rows
distinct_authors = (Article.select(Article.author)
.distinct()
.execute())
print(rows[0].title, rows[0].views)The complete read query chains four refinements after select(): where (the filter), order_by (with .desc()/.asc() on the field for direction), limit/offset (pagination), and distinct() (deduplication). The select(fields...) form projects a subset of columns - the right call when the caller only needs a few of the ten columns the table has, because it is both faster and a clearer statement of what the query is for. The order is conventional: filter, then order, then page, and each step returns the query so the chain reads top-to-bottom like the SQL it becomes. print(q) on the assembled chain is the final inspection before .execute().
Summary
Field operators (==, !=, >, the null checks is_null()/is_not_null()) build SQL conditions rather than testing Python values; the combinators &/|/~ compose them with AND/OR/NOT, with parentheses where OR meets tighter-binding AND; and the set and string operators (in_, ** for LIKE, between, the not_ forms) cover the pattern queries in parameterized form. The full read query chains where, order_by, limit/offset, and distinct() over a field projection, and the standing habit is print(q) before .execute() - the expression language is only safe to use at speed once seeing the generated SQL is as routine as writing the expression.