Python Peewee
last modified November 17, 2026 Python 3.15
In this article we show how to work with databases in Python using the Peewee ORM.
Peewee is a small and expressive object-relational mapper (ORM)
for Python. It maps database tables to Python classes, so that we can work
with rows as with ordinary objects and build SQL queries from Python
expressions. Peewee supports SQLite, MySQL, MariaDB, and PostgreSQL. It is a
single module without required dependencies; SQLite works out of the box
thanks to the built-in sqlite3 module.
The examples were tested with Python 3.15 and Peewee 4.5 on Linux. Peewee 4 comes with asyncio support; the basic API is the same as in Peewee 3.
$ pip install peewee
Peewee mapping
A Model class maps to a database table, a Field to
a table column, and a model instance to a table row.
Peewee uses SqliteDatabase for SQLite,
MySQLDatabase for MySQL and MariaDB, and
PostgresqlDatabase for PostgreSQL. For MySQL and PostgreSQL, we
need to install a database driver, such as pymysql or
psycopg2. In this article we work with SQLite.
Peewee field types
Field types in a Peewee model define the storage type of the model attributes. They are translated to the column types of the particular database. Instead of listing the types from memory, we ask Peewee for its mapping.
import peewee as pw
fields = [pw.AutoField, pw.BigAutoField, pw.IntegerField, pw.BigIntegerField,
pw.SmallIntegerField, pw.FloatField, pw.DoubleField, pw.DecimalField,
pw.CharField, pw.FixedCharField, pw.TextField, pw.BlobField,
pw.UUIDField, pw.BooleanField, pw.DateTimeField, pw.DateField,
pw.TimeField]
dbs = [pw.SqliteDatabase(None), pw.PostgresqlDatabase(None),
pw.MySQLDatabase(None)]
types = [db.get_context_options()['field_types'] for db in dbs]
print(f'{"Field":<18} {"SQLite":<10} {"PostgreSQL":<17} MySQL')
for field in fields:
sqlite, pg, mysql = (t[field.field_type] for t in types)
print(f'{field.__name__:<18} {sqlite:<10} {pg:<17} {mysql}')
Each field class has a field_type attribute, such as
INT or BOOL. The field_types
dictionary of a database maps it to a column type. The databases are created
with None instead of a name; such a database is not
initialized, which is enough for our purpose, and no connection is
needed.
The primary key of a model is an AutoField named
id, unless we define another one. In SQLite, booleans are stored
as integers, and UUIDs as text.
$ python field_types.py Field SQLite PostgreSQL MySQL AutoField INTEGER SERIAL INTEGER AUTO_INCREMENT BigAutoField INTEGER BIGSERIAL BIGINT AUTO_INCREMENT IntegerField INTEGER INTEGER INTEGER BigIntegerField INTEGER BIGINT BIGINT SmallIntegerField INTEGER SMALLINT SMALLINT FloatField REAL REAL FLOAT DoubleField REAL DOUBLE PRECISION DOUBLE PRECISION DecimalField DECIMAL NUMERIC NUMERIC CharField VARCHAR VARCHAR VARCHAR FixedCharField CHAR CHAR CHAR TextField TEXT TEXT TEXT BlobField BLOB BYTEA BLOB UUIDField TEXT UUID VARCHAR(40) BooleanField INTEGER BOOLEAN BOOL DateTimeField DATETIME TIMESTAMP DATETIME DateField DATE DATE DATE TimeField TIME TIME TIME
Peewee model definition
All examples about notes share a models.py module with the
database and the model definition.
import datetime
from peewee import CharField, DateField, Model, SqliteDatabase
db = SqliteDatabase('notes.db')
class BaseModel(Model):
class Meta:
database = db
class Note(BaseModel):
text = CharField()
created = DateField(default=datetime.date.today)
class Meta:
table_name = 'notes'
def __str__(self):
return f'{self.id} {self.text} on {self.created}'
The module defines the Note model for a notes
table in SQLite.
db = SqliteDatabase('notes.db')
We create a SqliteDatabase object for the notes.db
file. The file is created when we first connect.
class BaseModel(Model):
class Meta:
database = db
It is a common practice to define a base model that specifies the database; all models that inherit from it use the same database.
class Note(BaseModel):
text = CharField()
created = DateField(default=datetime.date.today)
We specify the model fields. The CharField stores strings and
the DateField stores dates. If the date is not given, the
default callable provides the current date.
class Meta:
table_name = 'notes'
In the inner Meta class, we set the table name; by default,
Peewee derives it from the class name, note. Older Peewee
versions used the db_table option, which is deprecated.
Creating a table
The create_tables method creates tables for the given models.
New rows can be inserted with the create class method or by
creating an instance and calling save.
import datetime from models import Note, db db.connect() db.drop_tables([Note]) db.create_tables([Note]) note1 = Note.create(text='Went to the cinema', created=datetime.date(2026, 10, 5)) print(note1.id, note1) note2 = Note(text='Listened to music') print(note2.id) note2.save() print(note2.id, note2) db.close()
The example creates the notes table and inserts two
notes.
db.connect() db.drop_tables([Note]) db.create_tables([Note])
We connect to the database, drop the table if it exists, and create it
again, so that we can run the script repeatedly. Calling
connect is optional; Peewee opens a connection automatically
when it is needed. Opening and closing it explicitly makes the lifetime of
the connection clear.
note1 = Note.create(text='Went to the cinema', created=datetime.date(2026, 10, 5))
The create method inserts a row and returns the new instance;
its id is set by the database.
note2 = Note(text='Listened to music') print(note2.id) note2.save()
An instance created with the constructor is not saved until we call
save; until then, its id is None. The
date is not given, so the default, today's date, is used.
$ python create_table.py 1 1 Went to the cinema on 2026-10-05 None 2 2 Listened to music on 2026-11-17
Verifying the data
We look at the created table with the sqlite3 command line
tool. The table has an id primary key; the
CharField is a VARCHAR(255) column, and both
columns are NOT NULL, since Peewee fields are required unless
created with null=True.
$ sqlite3 notes.db '.schema notes'
CREATE TABLE IF NOT EXISTS "notes" ("id" INTEGER NOT NULL PRIMARY KEY, "text" VARCHAR(255) NOT NULL, "created" DATE NOT NULL);
$ sqlite3 notes.db 'SELECT * FROM notes'
1|Went to the cinema|2026-10-05
2|Listened to music|2026-11-17
Peewee insert_many
The insert_many method inserts multiple rows in one
statement.
import datetime
from models import Note, db
data = [
{'text': 'Tai chi in the morning', 'created': datetime.date(2026, 10, 20)},
{'text': 'Visited friend', 'created': datetime.date(2026, 10, 12)},
{'text': 'Watched TV all day', 'created': datetime.date(2026, 10, 14)},
{'text': 'Worked in the garden', 'created': datetime.date(2026, 10, 22)},
{'text': 'Walked for an hour', 'created': datetime.date(2026, 10, 28)},
{'text': 'Read a book', 'created': datetime.date(2026, 11, 3)},
{'text': 'Cooked a dinner', 'created': datetime.date(2026, 11, 9)},
]
with db.atomic():
n = Note.insert_many(data).execute()
print(f'last inserted id: {n}')
print(f'number of notes: {Note.select().count()}')
We add seven notes to the table.
data = [
{'text': 'Tai chi in the morning', 'created': datetime.date(2026, 10, 20)},
{'text': 'Visited friend', 'created': datetime.date(2026, 10, 12)},
...
]
The data is defined in a list of dictionaries. A list of tuples can be used
too; then we pass the corresponding fields in the fields
argument.
with db.atomic():
n = Note.insert_many(data).execute()
The insert_many method creates an insert query, which is run by
execute. With SQLite, execute returns the id of the
last inserted row. The atomic method runs the operation in a
transaction. A single statement is atomic anyway, but when we insert large
data in several batches, for instance with the chunked helper,
the transaction guarantees that either all or none of the rows are saved,
and it speeds up the insertion considerably.
$ python insert_many.py last inserted id: 9 number of notes: 9
Peewee select all instances
The select method creates a SELECT query. If no
fields are given, the query selects all the fields defined on the
model.
from models import Note
notes = Note.select()
for note in notes:
print(note.id, note.text, note.created, type(note.created).__name__)
The example fetches and displays all Note instances. The query
is executed lazily, when we start iterating over it. Note that the
created attribute is a datetime.date object, not a
string: Peewee converts the values between Python and database types.
$ python fetch_all.py 1 Went to the cinema 2026-10-05 date 2 Listened to music 2026-11-17 date 3 Tai chi in the morning 2026-10-20 date 4 Visited friend 2026-10-12 date 5 Watched TV all day 2026-10-14 date 6 Worked in the garden 2026-10-22 date 7 Walked for an hour 2026-10-28 date 8 Read a book 2026-11-03 date 9 Cooked a dinner 2026-11-09 date
Peewee filter with where
The where method filters data based on a given condition. The
condition is written as a Python expression with the model fields.
import datetime
from models import Note
notes = Note.select().where(Note.created >= datetime.date(2026, 10, 22))
for note in notes:
print(note)
The example retrieves the notes created on October 22, 2026 or later. The
Note.created >= ... expression does not evaluate to a
boolean; Peewee overloads the operators so that they build an SQL
expression.
$ python where_clause.py 2 Listened to music on 2026-11-17 6 Worked in the garden on 2026-10-22 7 Walked for an hour on 2026-10-28 8 Read a book on 2026-11-03 9 Cooked a dinner on 2026-11-09
Combining conditions
Conditions are combined with the & (and), |
(or), and ~ (not) operators. The Python and,
or, and not keywords cannot be overloaded and must
not be used. Fields also have methods for common SQL operators, such as
in_, between, startswith, and
contains.
import datetime
from models import Note
def show(title, query):
print(f'{title}:')
for note in query:
print(f' {note}')
show('id between 3 and 5', Note.select().where((Note.id >= 3) & (Note.id <= 5)))
show('id 1 or text starts with W',
Note.select().where((Note.id == 1) | Note.text.startswith('W')))
show('not in October', Note.select().where(
~Note.created.between(datetime.date(2026, 10, 1), datetime.date(2026, 10, 31))))
show('id in (2, 4, 6)', Note.select().where(Note.id.in_([2, 4, 6])))
show('text contains "the"', Note.select().where(Note.text.contains('the')))
The show helper prints a title and the notes returned by a
query.
Note.select().where((Note.id >= 3) & (Note.id <= 5))
The & and | operators have a higher precedence
than comparisons, so each comparison must be enclosed in parentheses.
~Note.created.between(datetime.date(2026, 10, 1), datetime.date(2026, 10, 31))
The between method produces SQL BETWEEN; the
~ operator negates it.
Note.text.contains('the')
The contains, startswith, and
endswith methods produce LIKE expressions. In
SQLite, they are case-insensitive for ASCII letters.
$ python where_ops.py id between 3 and 5: 3 Tai chi in the morning on 2026-10-20 4 Visited friend on 2026-10-12 5 Watched TV all day on 2026-10-14 id 1 or text starts with W: 1 Went to the cinema on 2026-10-05 5 Watched TV all day on 2026-10-14 6 Worked in the garden on 2026-10-22 7 Walked for an hour on 2026-10-28 not in October: 2 Listened to music on 2026-11-17 8 Read a book on 2026-11-03 9 Cooked a dinner on 2026-11-09 id in (2, 4, 6): 2 Listened to music on 2026-11-17 4 Visited friend on 2026-10-12 6 Worked in the garden on 2026-10-22 text contains "the": 1 Went to the cinema on 2026-10-05 3 Tai chi in the morning on 2026-10-20 6 Worked in the garden on 2026-10-22
Peewee retrieve single instance
The get method returns a single instance. If no row matches the
query, it raises the DoesNotExist exception of the model. The
get_or_none method returns None instead.
from models import Note
note = Note.select().where(Note.text == 'Visited friend').get()
print(note)
note = Note.get(Note.text == 'Read a book')
print(note)
note = Note.get_by_id(2)
print(note)
note = Note.get_or_none(Note.text == 'Played chess')
print(note)
try:
Note.get(Note.id == 100)
except Note.DoesNotExist as e:
print(f'{type(e).__name__}: {e}')
The example shows several ways to retrieve a single instance.
note = Note.select().where(Note.text == 'Visited friend').get()
We can call get on a query. It adds LIMIT 1 to the
query and returns the first row.
note = Note.get(Note.text == 'Read a book') note = Note.get_by_id(2)
The Note.get class method is a shortcut for the same thing; the
get_by_id method looks up the instance by its primary key.
try:
Note.get(Note.id == 100)
except Note.DoesNotExist as e:
print(f'{type(e).__name__}: {e}')
There is no note with id 100. Each model has its own
DoesNotExist exception class; its message contains the SQL
query and its parameters.
$ python single_instance.py
4 Visited friend on 2026-10-12
8 Read a book on 2026-11-03
2 Listened to music on 2026-11-17
None
NoteDoesNotExist: <Model: Note> instance matching query does not exist:
SQL: SELECT "t1"."id", "t1"."text", "t1"."created" FROM "notes" AS "t1" WHERE ("t1"."id" = ?) LIMIT ? OFFSET ?
Params: [100, 1, 0]
Peewee selecting specific columns
We can pass the fields to be selected to the select method.
Instead of model instances, the query can return tuples, dictionaries, or
named tuples.
from playhouse.shortcuts import model_to_dict
from models import Note
query = Note.select(Note.text, Note.created).limit(2)
print(query[0].id)
print(list(query.tuples()))
print(list(query.dicts()))
for row in query.namedtuples():
print(row.text, row.created)
texts = [note.text for note in Note.select(Note.text).where(Note.id > 6)]
print(texts)
print(model_to_dict(Note.get_by_id(3)))
The example selects the text and creation date of two notes.
print(query[0].id)
The id field was not selected, so the attribute is
None. Queries support indexing; the query is executed and the
rows are cached.
print(list(query.tuples())) print(list(query.dicts()))
The tuples, dicts, and namedtuples
methods change the type of the returned rows. They are faster than creating
model instances.
print(model_to_dict(Note.get_by_id(3)))
The model_to_dict function from the
playhouse.shortcuts module converts a model instance to a
dictionary, for instance before we serialize it to JSON.
$ python columns.py
None
[('Went to the cinema', datetime.date(2026, 10, 5)), ('Listened to music', datetime.date(2026, 11, 17))]
[{'text': 'Went to the cinema', 'created': datetime.date(2026, 10, 5)}, {'text': 'Listened to music', 'created': datetime.date(2026, 11, 17)}]
Went to the cinema 2026-10-05
Listened to music 2026-11-17
['Walked for an hour', 'Read a book', 'Cooked a dinner']
{'id': 3, 'text': 'Tai chi in the morning', 'created': datetime.date(2026, 10, 20)}
Peewee count instances
The count method returns the number of rows a query would
return, and exists tells whether there is at least one. SQL
functions are available through the fn object.
import datetime
from peewee import fn
from models import Note
print(Note.select().count())
print(Note.select().where(Note.created >= datetime.date(2026, 11, 1)).count())
print(Note.select().where(Note.text.contains('chess')).exists())
first, last = Note.select(fn.MIN(Note.created), fn.MAX(Note.created)).scalar(as_tuple=True)
print(f'from {first} to {last}')
The example counts all notes and the notes created in November, checks whether a note about chess exists, and finds the oldest and newest dates.
first, last = Note.select(fn.MIN(Note.created), fn.MAX(Note.created)).scalar(as_tuple=True)
Any attribute of fn becomes an SQL function call, so
fn.MIN produces MIN. The scalar method
returns the value of the first column of the first row; with
as_tuple=True, it returns all columns of the row.
$ python count.py 9 3 False from 2026-10-05 to 2026-11-17
Peewee show SQL statements
The sql method returns the SQL statement of a query and its
parameters, without executing it.
from models import Note query = Note.select().where(Note.id == 3) print(query.sql()) query = Note.update(text='Ran ten kilometres').where(Note.id == 3) print(query.sql())
The example shows the SQL to which a select and an update query are translated. The values are passed as parameters, never inserted into the SQL string, which protects us from SQL injection.
$ python show_sql.py
('SELECT "t1"."id", "t1"."text", "t1"."created" FROM "notes" AS "t1" WHERE ("t1"."id" = ?)', [3])
('UPDATE "notes" SET "text" = ? WHERE ("notes"."id" = ?)', ['Ran ten kilometres', 3])
Logging queries
Peewee logs every executed query to the peewee logger at the
DEBUG level. This is useful to see what queries an application
actually runs.
import logging
from models import Note
logger = logging.getLogger('peewee')
logger.addHandler(logging.StreamHandler())
logger.setLevel(logging.DEBUG)
note = Note.get_by_id(4)
print(note)
n = Note.select().where(Note.text.startswith('W')).count()
print(n)
We add a handler that writes to the standard error output and set the level
of the logger. The count method wraps the query in a
subquery.
$ python log_sql.py
('SELECT "t1"."id", "t1"."text", "t1"."created" FROM "notes" AS "t1" WHERE ("t1"."id" = ?) LIMIT ? OFFSET ?', [4, 1, 0])
4 Visited friend on 2026-10-12
('SELECT COUNT(1) FROM (SELECT 1 FROM "notes" AS "t1" WHERE ("t1"."text" LIKE ?)) AS "_wrapped" LIMIT ?', ['W%', 1])
4
Peewee ordering
The retrieved instances can be ordered with order_by. The
desc method of a field sorts in descending order; the
asc method, which is the default, in ascending order.
from models import Note
print('ascending:')
for note in Note.select().order_by(Note.created):
print(f' {note.created} {note.text}')
print('descending, then by text:')
for note in Note.select().order_by(Note.created.desc(), Note.text).limit(4):
print(f' {note.created} {note.text}')
The code example orders the notes by the date of creation.
Note.select().order_by(Note.created.desc(), Note.text).limit(4)
We can sort by multiple fields; the second field decides the order of rows
with equal values of the first. The limit method restricts the
number of returned rows.
$ python order_by.py ascending: 2026-10-05 Went to the cinema 2026-10-12 Visited friend 2026-10-14 Watched TV all day 2026-10-20 Tai chi in the morning 2026-10-22 Worked in the garden 2026-10-28 Walked for an hour 2026-11-03 Read a book 2026-11-09 Cooked a dinner 2026-11-17 Listened to music descending, then by text: 2026-11-17 Listened to music 2026-11-09 Cooked a dinner 2026-11-03 Read a book 2026-10-28 Walked for an hour
Peewee offset, limit, and paginate
With the offset and limit methods we define how
many rows are skipped and how many rows are returned. The
paginate method computes them from a page number and a page
size.
from models import Note
query = Note.select().order_by(Note.id)
for note in query.offset(2).limit(3):
print(note)
print()
for page in range(1, 4):
notes = query.paginate(page, 4)
print(page, [note.id for note in notes])
The first query skips two rows and returns the next three. Then we display the notes in pages of four items; page numbers start from 1. Paging should always be combined with an explicit ordering; otherwise the database may return the rows in any order.
$ python paging.py 3 Tai chi in the morning on 2026-10-20 4 Visited friend on 2026-10-12 5 Watched TV all day on 2026-10-14 1 [1, 2, 3, 4] 2 [5, 6, 7, 8] 3 [9]
Grouping
The group_by method groups rows for aggregate functions.
from peewee import fn
from models import Note
month = fn.strftime('%Y-%m', Note.created).alias('month')
count = fn.COUNT(Note.id).alias('count')
query = (Note
.select(month, count)
.group_by(month)
.order_by(month))
for row in query.dicts():
print(row)
We count the notes per month.
month = fn.strftime('%Y-%m', Note.created).alias('month')
count = fn.COUNT(Note.id).alias('count')
The strftime function is specific to SQLite; in PostgreSQL, we
would use to_char or date_trunc. The
alias method names the columns, so we can refer to them in
group_by and in the returned dictionaries.
$ python group_by.py
{'month': '2026-10', 'count': 6}
{'month': '2026-11', 'count': 3}
Peewee update
The update class method creates an update query, which
modifies all rows that match the where condition and returns
their number. A single instance can also be modified by changing its
attributes and calling save.
import datetime
from models import Note
query = Note.update(created=datetime.date(2026, 10, 27)).where(Note.id == 1)
n = query.execute()
print(f'rows updated: {n}')
note = Note.get_by_id(5)
note.text = 'Watched a film'
note.save()
n = Note.update(text=Note.text + '!').where(Note.created > datetime.date(2026, 11, 1)).execute()
print(f'rows updated: {n}')
for note in Note.select().where(Note.id.in_([1, 5, 8, 9])):
print(note)
The example modifies several notes.
note = Note.get_by_id(5) note.text = 'Watched a film' note.save()
For an instance with a primary key, save runs an
UPDATE instead of an INSERT. By default, it writes
all fields; with only=[Note.text] it writes only the given
ones.
Note.update(text=Note.text + '!').where(Note.created > datetime.date(2026, 11, 1))
New values can be expressions with fields. This query appends an exclamation mark to the texts of notes from November in the database, without loading them into Python.
$ python update.py rows updated: 1 rows updated: 3 1 Went to the cinema on 2026-10-27 5 Watched a film on 2026-10-14 8 Read a book! on 2026-11-03 9 Cooked a dinner! on 2026-11-09
Peewee delete
The delete_by_id class method deletes a row by its primary key,
delete_instance deletes a loaded instance, and the
delete class method creates a delete query. All of them return
the number of deleted rows.
from models import Note
n = Note.delete_by_id(9)
print(f'deleted by id: {n}')
note = Note.get(Note.text == 'Visited friend')
n = note.delete_instance()
print(f'deleted instance: {n}')
n = Note.delete().where(Note.id > 6).execute()
print(f'deleted by query: {n}')
print([note.id for note in Note.select()])
We delete notes in three different ways and print the ids of the remaining notes.
n = Note.delete().where(Note.id > 6).execute()
Always remember the where condition; without it, the query
deletes all rows of the table.
$ python delete.py deleted by id: 1 deleted instance: 1 deleted by query: 2 [1, 2, 3, 5, 6]
Transactions
The atomic method runs a block of code in a transaction. If an
exception escapes from the block, the transaction is rolled back; otherwise
it is committed. Nested atomic blocks use savepoints.
from models import Note, db
print(f'notes before: {Note.select().count()}')
try:
with db.atomic():
Note.create(text='Went swimming')
Note.create(text='Visited parents')
raise RuntimeError('something went wrong')
except RuntimeError as e:
print(f'error: {e}')
print(f'notes after failed transaction: {Note.select().count()}')
with db.atomic() as txn:
Note.create(text='Went swimming')
txn.rollback()
Note.create(text='Visited parents')
print(f'notes after partial rollback: {Note.select().count()}')
print(Note.select().order_by(Note.id.desc()).get())
We insert notes in two transactions.
with db.atomic():
Note.create(text='Went swimming')
Note.create(text='Visited parents')
raise RuntimeError('something went wrong')
The exception rolls back the whole transaction, so neither note is saved.
with db.atomic() as txn:
Note.create(text='Went swimming')
txn.rollback()
Note.create(text='Visited parents')
We can also roll back explicitly. The rollback method discards
the changes made so far and starts a new transaction; the second note is
committed at the end of the block.
$ python transaction.py notes before: 5 error: something went wrong notes after failed transaction: 5 notes after partial rollback: 6 7 Visited parents on 2026-11-17
Peewee one-to-many relationship
Relationships between models are created with
ForeignKeyField. In the following examples, we work with
customers and their reservations. One customer can have many
reservations.
import datetime
from peewee import CharField, DateField, ForeignKeyField, Model, SqliteDatabase
db = SqliteDatabase('shop.db', pragmas={'foreign_keys': 1})
class BaseModel(Model):
class Meta:
database = db
class Customer(BaseModel):
name = CharField()
class Reservation(BaseModel):
customer = ForeignKeyField(Customer, backref='reservations', on_delete='CASCADE')
created = DateField(default=datetime.date.today)
The shop.py module defines the two models.
db = SqliteDatabase('shop.db', pragmas={'foreign_keys': 1})
SQLite does not enforce foreign keys by default. We turn them on with the
foreign_keys pragma, which Peewee sets on each new
connection.
customer = ForeignKeyField(Customer, backref='reservations', on_delete='CASCADE')
The field creates a customer_id column that references the
customer table. The backref parameter names the
attribute through which a customer accesses its reservations. With
on_delete='CASCADE', the reservations are deleted together with
their customer.
import datetime
from shop import Customer, Reservation, db
db.drop_tables([Customer, Reservation])
db.create_tables([Customer, Reservation])
with db.atomic():
paul = Customer.create(name='Paul Novak')
terry = Customer.create(name='Terry Neils')
jack = Customer.create(name='Jack Fonda')
Customer.create(name='Tom Willis')
Reservation.insert_many([
(paul, datetime.date(2026, 11, 22)),
(terry, datetime.date(2026, 11, 28)),
(terry, datetime.date(2026, 11, 29)),
(paul, datetime.date(2026, 11, 29)),
(jack, datetime.date(2026, 12, 2)),
], fields=[Reservation.customer, Reservation.created]).execute()
print(Customer.select().count(), Reservation.select().count())
The script creates the tables and inserts four customers and five
reservations. In the insert_many call, the rows are tuples and
the fields argument says which columns they fill. A model
instance can be used as a value of a foreign key field.
$ python shop_data.py 4 5
Accessing related objects
A foreign key attribute returns the related object, and the backref returns a query of the objects that refer to it.
from shop import Customer, Reservation
paul = Customer.get(Customer.name == 'Paul Novak')
for res in paul.reservations:
print(res.id, res.created)
res = Reservation.get_by_id(5)
print(res.customer.name)
We list Paul's reservations and print the customer of reservation 5. Each
attribute access runs a query: paul.reservations selects the
reservations, and res.customer loads the customer the first
time it is accessed.
$ python one2many.py 1 2026-11-22 4 2026-11-29 Jack Fonda
Joins
The join method joins another model to a query. Peewee infers
the join condition from the foreign key.
from peewee import JOIN, fn
from shop import Customer, Reservation
query = (Reservation
.select(Reservation, Customer)
.join(Customer)
.where(Reservation.created >= '2026-11-28')
.order_by(Reservation.created))
for res in query:
print(res.created, res.customer.name)
print()
query = (Customer
.select(Customer.name, fn.COUNT(Reservation.id).alias('total'))
.join(Reservation, JOIN.LEFT_OUTER)
.group_by(Customer.name)
.order_by(Customer.name))
for customer in query:
print(customer.name, customer.total)
The first query selects reservations together with their customers; the second counts the reservations of each customer.
Reservation.select(Reservation, Customer).join(Customer)
Because we select the customer columns too, res.customer is
filled from the same row and does not run another query.
.join(Reservation, JOIN.LEFT_OUTER)
The default join is an inner join, which would skip customers without reservations. With a left outer join, Tom Willis is included with zero reservations.
$ python join.py 2026-11-28 Terry Neils 2026-11-29 Terry Neils 2026-11-29 Paul Novak 2026-12-02 Jack Fonda Jack Fonda 1 Paul Novak 2 Terry Neils 2 Tom Willis 0
Prefetching
Iterating over customers and accessing the reservations of each customer
runs one query for the customers and one query per customer. This is the
N+1 query problem. The prefetch function loads the
related rows with one extra query per model.
import logging
from peewee import prefetch
from shop import Customer, Reservation
class QueryCounter(logging.Handler):
count = 0
def emit(self, record):
QueryCounter.count += 1
logger = logging.getLogger('peewee')
logger.addHandler(QueryCounter())
logger.setLevel(logging.DEBUG)
for customer in Customer.select():
dates = [str(res.created) for res in customer.reservations]
print(customer.name, dates)
print(f'queries: {QueryCounter.count}')
QueryCounter.count = 0
for customer in prefetch(Customer.select(), Reservation.select()):
dates = [str(res.created) for res in customer.reservations]
print(customer.name, dates)
print(f'queries: {QueryCounter.count}')
We count the executed queries with a logging handler attached to the
peewee logger. Without prefetching, the loop runs five queries
for four customers; with prefetch, it runs two.
$ python prefetch.py Paul Novak ['2026-11-22', '2026-11-29'] Terry Neils ['2026-11-28', '2026-11-29'] Jack Fonda ['2026-12-02'] Tom Willis [] queries: 5 Paul Novak ['2026-11-22', '2026-11-29'] Terry Neils ['2026-11-28', '2026-11-29'] Jack Fonda ['2026-12-02'] Tom Willis [] queries: 2
Cascading delete
Thanks to the ON DELETE CASCADE clause of the foreign key, the
database deletes the reservations of a deleted customer.
from shop import Customer, Reservation terry = Customer.get(Customer.name == 'Terry Neils') terry.delete_instance() print(Customer.select().count(), Reservation.select().count())
After we delete Terry Neils, three customers and three reservations remain.
$ python cascade.py 3 3
Peewee many-to-many relationship
A many-to-many relationship needs a junction table. The
ManyToManyField creates it for us and provides methods to add
and remove relations.
from peewee import CharField, ManyToManyField, Model, SqliteDatabase
db = SqliteDatabase(':memory:')
class BaseModel(Model):
class Meta:
database = db
class Course(BaseModel):
name = CharField()
class Student(BaseModel):
name = CharField()
courses = ManyToManyField(Course, backref='students')
StudentCourse = Student.courses.get_through_model()
db.create_tables([Student, Course, StudentCourse])
math, physics, history = (Course.create(name=n) for n in ('math', 'physics', 'history'))
john = Student.create(name='John')
lucy = Student.create(name='Lucy')
peter = Student.create(name='Peter')
john.courses.add([math, physics])
lucy.courses.add([math, history])
peter.courses.add(physics)
for student in Student.select():
print(student.name, [c.name for c in student.courses])
print([s.name for s in math.students])
lucy.courses.remove(math)
print([s.name for s in math.students])
print(StudentCourse._meta.table_name)
Students can attend many courses, and courses have many students. We use an in-memory database, which disappears when the program ends.
StudentCourse = Student.courses.get_through_model() db.create_tables([Student, Course, StudentCourse])
The junction model, called the through model, must be created together with the other tables.
john.courses.add([math, physics])
The add method inserts rows into the junction table; the
remove method deletes them. Through the students
backref, we see the relation from the other side.
$ python many2many.py John ['math', 'physics'] Lucy ['math', 'history'] Peter ['physics'] ['John', 'Lucy'] ['John'] student_course_through
Peewee and asyncio
Peewee 4 supports asyncio through the playhouse.pwasyncio
module. It uses the asynchronous drivers aiosqlite, asyncpg, and aiomysql
and the greenlet library, which lets the synchronous Peewee code run inside
coroutines. We install the extra for SQLite.
$ pip install "peewee[aiosqlite]"
Asynchronous queries
The asynchronous database classes have methods such as list,
get, count, and scalar, which execute
a query and are awaited.
import asyncio
from peewee import CharField, DateField, Model
from playhouse.pwasyncio import AsyncSqliteDatabase
db = AsyncSqliteDatabase('notes.db')
class Note(Model):
text = CharField()
created = DateField()
class Meta:
database = db
table_name = 'notes'
async def main():
async with db:
n = await db.count(Note.select())
print(f'{n} notes')
query = Note.select().where(Note.text.startswith('W'))
for note in await db.list(query):
print(note.id, note.text)
note = await db.get(Note.select().where(Note.id == 3))
print(note.text, note.created)
async with db.atomic():
await db.run(Note.create, text='Played chess', created='2026-11-17')
print(await db.scalar(Note.select(Note.text).order_by(Note.id.desc())))
await db.close_pool()
asyncio.run(main())
We read and insert notes in the notes.db database
asynchronously.
db = AsyncSqliteDatabase('notes.db')
The AsyncSqliteDatabase class keeps a pool of aiosqlite
connections. The models are defined as usual.
async with db:
n = await db.count(Note.select())
The async with statement acquires a connection for the block.
Queries are built with the regular API and executed with the awaitable
methods of the database.
async with db.atomic():
await db.run(Note.create, text='Played chess', created='2026-11-17')
The run method runs any synchronous Peewee code, here the
create method, inside the asynchronous context. Transactions
work with async with.
await db.close_pool()
At the end, we close the connection pool.
$ python async_notes.py 6 notes 1 Went to the cinema 5 Watched a film 6 Worked in the garden Tai chi in the morning 2026-10-20 Played chess
Source
Peewee – Relationships and joins
In this article we have worked with databases using the Python Peewee ORM. See also Python SQLite, Python PyMySQL, Python PostgreSQL, and Python asyncio.
Author
List all Python tutorials.