ZetCode

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.

field_types.py
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.

models.py
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.

create_table.py
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.

insert_many.py
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.

fetch_all.py
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.

where_clause.py
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.

where_ops.py
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.

single_instance.py
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.

columns.py
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.

count.py
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.

show_sql.py
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.

log_sql.py
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.

order_by.py
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.

paging.py
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.

group_by.py
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.

update.py
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.

delete.py
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.

transaction.py
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.

shop.py
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.

shop_data.py
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.

one2many.py
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.

join.py
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.

prefetch.py
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.

cascade.py
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.

many2many.py
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.

async_notes.py
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 documentation

Peewee – Querying

Peewee – Relationships and joins

Peewee – asyncio

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

I am Jan Bodnar, a programmer and technical writer. I have been writing programming tutorials at ZetCode since 2007 and have published eight e-books. See about the author.

List all Python tutorials.