•

a small, expressive ORM

•

python 2.7+ and 3.4+

•

supports sqlite, mysql, mariadb, postgresql and cockroachdb

•

tons of extensions

•

Quickstart

•

Example twitter app

•

Using peewee interactively

•

Models and fields

•

Querying

•

Relationships and joins

•

Sqlite extensions, which includes Cython implementations of the SQLite date manipulation functions, the REGEXP operator, and full-text search result ranking algorithms.

•

Cython : used to expose additional functionality when using SQLite and to implement things like search result ranking in a performant manner. Since the generated C files are included with the package distribution, Cython is no longer required to use the C extensions.

•

apsw : an optional 3rd-party SQLite binding offering greater performance and comprehensive support for SQLite's C APIs. Use with APSWDatabase .

•

gevent is an optional dependency for SqliteQueueDatabase (though it works with threading just fine).

•

BerkeleyDB can be compiled with a SQLite frontend, which works with Peewee. Compiling can be tricky so here are instructions .

•

Lastly, if you use the Flask framework, there are helper extension modules available.

•

Model Definition

•

Storing data

•

Retrieving Data

•

before 1940 (grandma)

•

after 1959 (bob)

User :

Represents a user account and stores the username and password, an email address for generating avatars using gravatar , and a datetime field indicating when that account was created.

•

Strings (unicode or otherwise)

•

Integers, floats, and Decimal numbers.

•

Boolean values

•

Dates, times and datetimes

•

None (NULL)

•

Binary data

•

following() : who is this user following?

•

followers() : who is following this user?

•

Support for paginating lists of results is implemented in a simple function called object_list (after it's corollary in Django). This function is used by all the views that return lists of objects.

•

Simple authentication system with a login_required decorator. The first function simply adds user data into the current session when a user successfully logs in. The decorator login_required can be used to wrap view functions, checking for whether the session is authenticated and if not redirecting to the login page.

•

Return a 404 response instead of throwing exceptions when an object is not found in the database.

•

Example blog app using Flask and peewee. Also see accompanying blog post .

•

An encrypted command-line diary . There is a companion blog post you might enjoy as well.

•

Analytics web-service (like a lite version of Google Analytics). Also check out the companion blog post .

•

peewee.SqliteDatabase - to reference the "events.db"

•

playhouse.reflection.generate_models - to generate models from an existing database.

•

playhouse.reflection.print_model - to view the model definition.

•

playhouse.reflection.print_table_sql - to view the table SQL.

•

generate_models()

•

print_model()

•

print_table_sql()

•

Database.get_tables()

•

Database.get_indexes()

•

Database.get_columns() (for a given table)

•

Database.get_primary_keys() (for a given table)

•

Database.get_foreign_keys() (for a given table)

•

Unit tests.

•

Documentation, both prose form and general API documentation .

•

Code that conforms stylistically with the rest of the Peewee codebase.

•

Traceback and the error message (please format your code !)

•

Relevant portions of your code or code to reproduce the error

•

Peewee version: python -c "from peewee import __version__; print(__version__)"

•

Which database you're using

•

Ask on StackOverflow. I check SO just about every day for new peewee questions and try to answer them. This has the benefit also of preserving the question and answer for other people to find.

•

Ask on the mailing list, https://groups.google.com/group/peewee-orm

•

Open and close connections.

•

Execute queries.

•

Manage transactions (and savepoints).

•

Introspect tables, columns, indexes, and constraints.

•

Postgresql Extensions

•

SQLite Extensions

•

Cockroach Database

•

Sqlcipher backend (encrypted SQLite database).

•

apsw, an advanced sqlite driver

•

SqliteQ

•

Postgres: psycopg2

•

MySQL: pymysql

•

MySQL: mysqlclient

•

SQLite: sqlite3

•

CockroachDB: see psycopg2

•

Arrays

•

HStore

•

JSON

•

Server-side cursors

•

And more!

•

CRDB extension documentation

•

SSL configuration with CockroachDB

•

Arrays (postgres-specific, but applies to CRDB)

•

JSON (postgres-specific, but applies to CRDB)

The following settings are what I use with SQLite for a typical web application database.

•

Functions - which take any number of parameters and return a single value.

•

Aggregates - which aggregate parameters from multiple rows and return a single value.

•

Collations - which describe how to sort some value.

•

SqliteDatabase.func()

•

SqliteDatabase.aggregate()

•

SqliteDatabase.collation()

•

SqliteDatabase.table_function()

•

For even more SQLite extensions, see SQLite Extensions

•

Deferred ( default ) - only acquires lock when a read or write is performed. The first read creates a shared lock and the first write creates a reserved lock . Because the acquisition of the lock is deferred until actually needed, it is possible that another thread or process could create a separate transaction and write to the database after the BEGIN on the current thread has executed.

•

Immediate - a reserved lock is acquired immediately. In this mode, no other database may write to the database or open an immediate or exclusive transaction. Other processes can continue to read from the database, however.

•

Exclusive - opens an exclusive lock which prevents all (except for read uncommitted) connections from accessing the database until the transaction is complete.

•

Virtual tables, virtual file-systems, Blob I/O, backups and file control.

•

Connections can be shared across threads without any additional locking.

•

Transactions are managed explicitly by your code.

•

Unicode is handled correctly .

•

APSW is faster that the standard library sqlite3 module.

•

Exposes pretty much the entire SQLite C API to your Python app.

•

pymysql is a pure-python mysql client, works with python 2 and 3. Peewee will use attempt to use pymysql first.

•

mysqlclient uses a c extension and supports python 3. It exposes a MySQLdb module. Peewee will attempt to use this module if pymysql is not installed.

•

mysql-python is also called MySQLdb1 and is legacy and should not be used. Since this shares the same module name as mysqlclient, same applies.

•

mysql-connector python pure-python (I think??) supports python 3. To use this driver you can use MySQLConnectorDatabase from the playhouse.mysql_ext extension.

•

sqlite:///my_database.db will create a SqliteDatabase instance for the file my_database.db in the current directory.

•

sqlite:///:memory: will create an in-memory SqliteDatabase instance.

•

postgresql://postgres:my_password@localhost:5432/my_database will create a PostgresqlDatabase instance. A username and password are provided, as well as the host and port to connect to.

•

mysql://user:passwd@ip:port/my_db will create a MySQLDatabase instance for the local MySQL database my_db .

•

More examples in the db_url documentation .

•

Database.bind() and Model.bind() - bind one or more models to a database.

•

Database.bind_ctx() and Model.bind_ctx() - which are the same as their bind() counterparts, but return a context-manager and are useful when the database should only be changed temporarily.

•

Timeout after which connections will be recycled.

•

Upper bound on the number of open connections.

•

PooledPostgresqlDatabase

•

PooledPostgresqlExtDatabase

•

PooledMySQLDatabase

•

PooledSqliteDatabase

•

PooledSqliteExtDatabase

•

Database.bind_ctx() , which returns a context-manager that will bind the given models to the database instance for the duration of the wrapped block.

•

Model.bind_ctx() , which likewise returns a context-manager that binds the model (and optionally its dependencies) to the given database for the duration of the wrapped block.

•

Database.bind() , which is a one-time operation that binds the models (and optionally its dependencies) to the given database.

•

Model.bind() , which is a one-time operation that binds the model (and optionally its dependencies) to the given database.

•

No need for special-purpose "loop-aware" re-implementations of everything . Third-party libraries using asyncio usually have to re-implement layers and layers of code as well as re-implementing the protocols themselves.

•

Gevent allows you to write your application in normal, clean, idiomatic Python. No need to litter every line with "async", "await" and other noise. No callbacks, futures, tasks, promises. No cruft.

•

Gevent works with both Python 2 and Python 3.

•

Gevent is Pythonic . Asyncio is an un-pythonic abomination.

•

DatabaseError

•

DataError

•

IntegrityError

•

InterfaceError

•

InternalError

•

NotSupportedError

•

OperationalError

•

ProgrammingError

•

Connection.commit

•

Connection.execute

•

Connection.rollback

•

Cursor.description

•

Cursor.fetchone

•

last_insert_id() and rows_affected()

•

param and quote , which tell the SQL-generating code how to add parameter placeholders and quote entity names.

•

field_types for mapping data-types like INT or TEXT to their vendor-specific type names.

•

operations for mapping operations such as "LIKE/ILIKE" to their database equivalent

Model classes, Field instances and model instances all map to database concepts:

1.

Create an instance of a Database .

2.

Create a base model class which specifies our database.

3.

Define a model class.

NOTE:

Don't see the field you're looking for in the above table? It's easy to create custom field types and use them with your models.

Field initialization arguments

Parameters accepted by all field types and their default values:

Some fields take special parameters...

NOTE:

Both default and choices could be implemented at the database level as DEFAULT and CHECK CONSTRAINT respectively, but any application change would require a schema change. Because of this, default is implemented purely in python and choices are not validated but exist for metadata purposes only.

To add database (server-side) constraints, use the constraints parameter.

Default field values

Peewee can provide default values for fields when objects are created. For example to have an IntegerField default to zero rather than NULL , you could declare the field with a default value:

class Message(Model):
context = TextField()
read_count = IntegerField(default=0)

In some instances it may make sense for the default value to be dynamic. A common scenario is using the current date and time. Peewee allows you to specify a function in these cases, whose return value will be used when the object is created. Note we only provide the function, we do not actually call it:

class Message(Model):
context = TextField()
timestamp = DateTimeField(default=datetime.datetime.now)

NOTE:

If you are using a field that accepts a mutable type ( list , dict , etc), and would like to provide a default, it is a good idea to wrap your default value in a simple function so that multiple model instances are not sharing a reference to the same underlying object:

def house_defaults():
return {'beds': 0, 'baths': 0}

class House(Model):
number = TextField()
street = TextField()
attributes = JSONField(default=house_defaults)

The database can also provide the default value for a field. While peewee does not explicitly provide an API for setting a server-side default value, you can use the constraints parameter to specify the server default:

class Message(Model):
context = TextField()
timestamp = DateTimeField(constraints=[SQL('DEFAULT CURRENT_TIMESTAMP')])

NOTE:

Remember: when using the default parameter, the values are set by Peewee rather than being a part of the actual table and column definition.

ForeignKeyField

ForeignKeyField is a special field type that allows one model to reference another. Typically a foreign key will contain the primary key of the model it relates to (but you can specify a particular column by specifying a field ).

Foreign keys allow data to be normalized . In our example models, there is a foreign key from Tweet to User . This means that all the users are stored in their own table, as are the tweets, and the foreign key from tweet to user allows each tweet to point to a particular user object.

NOTE:

Refer to the Relationships and Joins document for an in-depth discussion of foreign keys, joins and relationships between models.

In peewee, accessing the value of a ForeignKeyField will return the entire related object, e.g.:

tweets = (Tweet
.select(Tweet, User)
.join(User)
.order_by(Tweet.created_date.desc()))
for tweet in tweets:
print(tweet.user.username, tweet.message)

NOTE:

In the example above the User data was selected as part of the query. For more examples of this technique, see the Avoiding N+1 document.

If we did not select the User , though, then an additional query would be issued to fetch the associated User data:

tweets = Tweet.select().order_by(Tweet.created_date.desc())
for tweet in tweets:
# WARNING: an additional query will be issued for EACH tweet
# to fetch the associated User data.
print(tweet.user.username, tweet.message)

Sometimes you only need the associated primary key value from the foreign key column. In this case, Peewee follows the convention established by Django, of allowing you to access the raw foreign key value by appending "_id" to the foreign key field's name:

tweets = Tweet.select()
for tweet in tweets:
# Instead of "tweet.user", we will just get the raw ID value stored
# in the column.
print(tweet.user_id, tweet.message)

To prevent accidentally resolving a foreign-key and triggering an additional query, ForeignKeyField supports an initialization parameter lazy_load which, when disabled, behaves like the "_id" attribute. For example:

class Tweet(Model):
# ... same fields, except we declare the user FK to have
# lazy-load disabled:
user = ForeignKeyField(User, backref='tweets', lazy_load=False)

for tweet in Tweet.select():
print(tweet.user, tweet.message)

# With lazy-load disabled, accessing tweet.user will not perform an extra
# query and the user ID value is returned instead.
# e.g.:
# 1 tweet from user1
# 1 another from user1
# 2 tweet from user2

# However, if we eagerly load the related user object, then the user
# foreign key will behave like usual:
for tweet in Tweet.select(Tweet, User).join(User):
print(tweet.user.username, tweet.message)

# user1 tweet from user1
# user1 another from user1
# user2 tweet from user1

ForeignKeyField Back-references

ForeignKeyField allows for a backreferencing property to be bound to the target model. Implicitly, this property will be named classname_set , where classname is the lowercase name of the class, but can be overridden using the parameter backref :

class Message(Model):
from_user = ForeignKeyField(User, backref='outbox')
to_user = ForeignKeyField(User, backref='inbox')
text = TextField()

for message in some_user.outbox:
# We are iterating over all Messages whose from_user is some_user.
print(message)

for message in some_user.inbox:
# We are iterating over all Messages whose to_user is some_user
print(message)

DateTimeField, DateField and TimeField

The three fields devoted to working with dates and times have special properties which allow access to things like the year, month, hour, etc.

DateField has properties for:

TimeField has properties for:

DateTimeField has all of the above.

These properties can be used just like any other expression. Let's say we have an events calendar and want to highlight all the days in the current month that have an event attached:

# Get the current time.
now = datetime.datetime.now()

# Get days that have events for the current month.
Event.select(Event.event_date.day.alias('day')).where(
(Event.event_date.year == now.year) &
(Event.event_date.month == now.month))

NOTE:

SQLite does not have a native date type, so dates are stored in formatted text columns. To ensure that comparisons work correctly, the dates need to be formatted so they are sorted lexicographically. That is why they are stored, by default, as YYYY-MM-DD HH:MM:SS .

BitField and BigBitField

The BitField and BigBitField are new as of 3.0.0. The former provides a subclass of IntegerField that is suitable for storing feature toggles as an integer bitmask. The latter is suitable for storing a bitmap for a large data-set, e.g. expressing membership or bitmap-type data.

As an example of using BitField , let's say we have a Post model and we wish to store certain True/False flags about how the post. We could store all these feature toggles in their own BooleanField objects, or we could use BitField instead:

class Post(Model):
content = TextField()
flags = BitField()

is_favorite = flags.flag(1)
is_sticky = flags.flag(2)
is_minimized = flags.flag(4)
is_deleted = flags.flag(8)

Using these flags is quite simple:

>>> p = Post()
>>> p.is_sticky = True
>>> p.is_minimized = True
>>> print(p.flags) # Prints 4 | 2 --> "6"
6
>>> p.is_favorite
False
>>> p.is_sticky
True

We can also use the flags on the Post class to build expressions in queries:

# Generates a WHERE clause that looks like:
# WHERE (post.flags & 1 != 0)
favorites = Post.select().where(Post.is_favorite)

# Query for sticky + favorite posts:
sticky_faves = Post.select().where(Post.is_sticky & Post.is_favorite)

Since the BitField is stored in an integer, there is a maximum of 64 flags you can represent (64-bits is common size of integer column). For storing arbitrarily large bitmaps, you can instead use BigBitField , which uses an automatically managed buffer of bytes, stored in a BlobField .

When bulk-updating one or more bits in a BitField , you can use bitwise operators to set or clear one or more bits:

# Set the 4th bit on all Post objects.
Post.update(flags=Post.flags | 8).execute()

# Clear the 1st and 3rd bits on all Post objects.
Post.update(flags=Post.flags & ˜(1 | 4)).execute()

For simple operations, the flags provide handy set() and clear() methods for setting or clearing an individual bit:

# Set the "is_deleted" bit on all posts.
Post.update(flags=Post.is_deleted.set()).execute()

# Clear the "is_deleted" bit on all posts.
Post.update(flags=Post.is_deleted.clear()).execute()

Example usage:

class Bitmap(Model):
data = BigBitField()

bitmap = Bitmap()

# Sets the ith bit, e.g. the 1st bit, the 11th bit, the 63rd, etc.
bits_to_set = (1, 11, 63, 31, 55, 48, 100, 99)
for bit_idx in bits_to_set:
bitmap.data.set_bit(bit_idx)

# We can test whether a bit is set using "is_set":
assert bitmap.data.is_set(11)
assert not bitmap.data.is_set(12)

# We can clear a bit:
bitmap.data.clear_bit(11)
assert not bitmap.data.is_set(11)

# We can also "toggle" a bit. Recall that the 63rd bit was set earlier.
assert bitmap.data.toggle_bit(63) is False
assert bitmap.data.toggle_bit(63) is True
assert bitmap.data.is_set(63)

# BigBitField supports item accessor by bit-number, e.g.:
assert bitmap.data[63]
bitmap.data[0] = 1
del bitmap.data[0]

# We can also combine bitmaps using bitwise operators, e.g.
b = Bitmap(data=b'\x01')
b.data |= b'\x02'
assert list(b.data) == [1, 1, 0, 0, 0, 0, 0, 0]
assert len(b.data) == 1

BareField

The BareField class is intended to be used only with SQLite. Since SQLite uses dynamic typing and data-types are not enforced, it can be perfectly fine to declare fields without any data-type. In those cases you can use BareField . It is also common for SQLite virtual tables to use meta-columns or untyped columns, so for those cases as well you may wish to use an untyped field (although for full-text search, you should use SearchField instead!).

BareField accepts a special parameter adapt . This parameter is a function that takes a value coming from the database and converts it into the appropriate Python type. For instance, if you have a virtual table with an un-typed column but you know that it will return int objects, you can specify adapt=int .

Example:

db = SqliteDatabase(':memory:')

class Junk(Model):
anything = BareField()

class Meta:
database = db

# Store multiple data-types in the Junk.anything column:
Junk.create(anything='a string')
Junk.create(anything=12345)
Junk.create(anything=3.14159)

Creating a custom field

It is easy to add support for custom field types in peewee. In this example we will create a UUID field for postgresql (which has a native UUID column type).

To add a custom field type you need to first identify what type of column the field data will be stored in. If you just want to add python behavior atop, say, a decimal field (for instance to make a currency field) you would just subclass DecimalField . On the other hand, if the database offers a custom column type you will need to let peewee know. This is controlled by the Field.field_type attribute.

NOTE:

Peewee ships with a UUIDField , the following code is intended only as an example.

Let's start by defining our UUID field:

class UUIDField(Field):
field_type = 'uuid'

We will store the UUIDs in a native UUID column. Since psycopg2 treats the data as a string by default, we will add two methods to the field to handle:

import uuid

class UUIDField(Field):
field_type = 'uuid'

def db_value(self, value):
return value.hex # convert UUID to hex string.

def python_value(self, value):
return uuid.UUID(value) # convert hex string to UUID

This step is optional. By default, the field_type value will be used for the columns data-type in the database schema. If you need to support multiple databases which use different data-types for your field-data, we need to let the database know how to map this uuid label to an actual uuid column type in the database. Specify the overrides in the Database constructor:

# Postgres, we use UUID data-type.
db = PostgresqlDatabase('my_db', field_types={'uuid': 'uuid'})

# Sqlite doesn't have a UUID type, so we use text type.
db = SqliteDatabase('my_db', field_types={'uuid': 'text'})

That is it! Some fields may support exotic operations, like the postgresql HStore field acts like a key/value store and has custom operators for things like contains and update . You can specify custom operations as well. For example code, check out the source code for the HStoreField , in playhouse.postgres_ext .

Field-naming conflicts

Model classes implement a number of class- and instance-methods, for example Model.save() or Model.create() . If you declare a field whose name coincides with a model method, it could cause problems. Consider:

class LogEntry(Model):
event = TextField()
create = TimestampField() # Uh-oh.
update = TimestampField() # Uh-oh.

To avoid this problem while still using the desired column name in the database schema, explicitly specify the column_name while providing an alternative name for the field attribute:

class LogEntry(Model):
event = TextField()
create_ = TimestampField(column_name='create')
update_ = TimestampField(column_name='update')

Creating model tables

In order to start using our models, its necessary to open a connection to the database and create the tables first. Peewee will run the necessary CREATE TABLE queries, additionally creating any constraints and indexes.

# Connect to our database.
db.connect()

# Create the tables.
db.create_tables([User, Tweet])

NOTE:

Strictly speaking, it is not necessary to call connect() but it is good practice to be explicit. That way if something goes wrong, the error occurs at the connect step, rather than some arbitrary time later.

NOTE:

By default, Peewee includes an IF NOT EXISTS clause when creating tables. If you want to disable this, specify safe=False .

After you have created your tables, if you choose to modify your database schema (by adding, removing or otherwise changing the columns) you will need to either:

Model options and table metadata

In order not to pollute the model namespace, model-specific configuration is placed in a special class called Meta (a convention borrowed from the django framework):

from peewee import *

contacts_db = SqliteDatabase('contacts.db')

class Person(Model):
name = CharField()

class Meta:
database = contacts_db

This instructs peewee that whenever a query is executed on Person to use the contacts database.

NOTE:

Take a look at the sample models - you will notice that we created a BaseModel that defined the database, and then extended. This is the preferred way to define a database and create models.

Once the class is defined, you should not access ModelClass.Meta , but instead use ModelClass._meta :

>>> Person.Meta
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
AttributeError: type object 'Person' has no attribute 'Meta'

>>> Person._meta
<peewee.ModelOptions object at 0x7f51a2f03790>

The ModelOptions class implements several methods which may be of use for retrieving model metadata (such as lists of fields, foreign key relationships, and more).

>>> Person._meta.fields
{'id': <peewee.AutoField object at 0x7f51a2e92750>,
'name': <peewee.CharField object at 0x7f51a2f0a510>}

>>> Person._meta.primary_key
<peewee.AutoField object at 0x7f51a2e92750>

>>> Person._meta.database
<peewee.SqliteDatabase object at 0x7f519bff6dd0>

Here is an example showing inheritable versus non-inheritable attributes:

>>> db = SqliteDatabase(':memory:')
>>> class ModelOne(Model):
... class Meta:
... database = db
... table_name = 'model_one_tbl'
...
>>> class ModelTwo(ModelOne):
... pass
...
>>> ModelOne._meta.database is ModelTwo._meta.database
True
>>> ModelOne._meta.table_name == ModelTwo._meta.table_name
False

Meta.primary_key

The Meta.primary_key attribute is used to specify either a CompositeKey or to indicate that the model has no primary key. Composite primary keys are discussed in more detail here: Composite primary keys .

To indicate that a model should not have a primary key, then set primary_key = False .

Examples:

class BlogToTag(Model):
"""A simple "through" table for many-to-many relationship."""
blog = ForeignKeyField(Blog)
tag = ForeignKeyField(Tag)

class Meta:
primary_key = CompositeKey('blog', 'tag')

class NoPrimaryKey(Model):
data = IntegerField()

class Meta:
primary_key = False

Table Names

By default Peewee will automatically generate a table name based on the name of your model class. The way the table-name is generated depends on the value of Meta.legacy_table_names . By default, legacy_table_names=True so as to avoid breaking backwards-compatibility. However, if you wish to use the new and improved table-name generation, you can specify legacy_table_names=False .

ATTENTION:

To preserve backwards-compatibility, the current release (Peewee 3.x) specifies legacy_table_names=True by default.

In the next major release (Peewee 4.0), legacy_table_names will have a default value of False .

To explicitly specify the table name for a model class, use the table_name Meta option. This feature can be useful for dealing with pre-existing database schemas that may have used awkward naming conventions:

class UserProfile(Model):
class Meta:
table_name = 'user_profile_tbl'

If you wish to implement your own naming convention, you can specify the table_function Meta option. This function will be called with your model class and should return the desired table name as a string. Suppose our company specifies that table names should be lower-cased and end with "_tbl", we can implement this as a table function:

def make_table_name(model_class):
model_name = model_class.__name__
return model_name.lower() + '_tbl'

class BaseModel(Model):
class Meta:
table_function = make_table_name

class User(BaseModel):
# table_name will be "user_tbl".

class UserProfile(BaseModel):
# table_name will be "userprofile_tbl".

Indexes and Constraints

Peewee can create indexes on single or multiple columns, optionally including a UNIQUE constraint. Peewee also supports user-defined constraints on both models and fields.

Single-column indexes and constraints

Single column indexes are defined using field initialization parameters. The following example adds a unique index on the username field, and a normal index on the email field:

class User(Model):
username = CharField(unique=True)
email = CharField(index=True)

To add a user-defined constraint on a column, you can pass it in using the constraints parameter. You may wish to specify a default value as part of the schema, or add a CHECK constraint, for example:

class Product(Model):
name = CharField(unique=True)
price = DecimalField(constraints=[Check('price < 10000')])
created = DateTimeField(
constraints=[SQL("DEFAULT (datetime('now'))")])

Multi-column indexes

Multi-column indexes may be defined as Meta attributes using a nested tuple. Each database index is a 2-tuple, the first part of which is a tuple of the names of the fields, the second part a boolean indicating whether the index should be unique.

class Transaction(Model):
from_acct = CharField()
to_acct = CharField()
amount = DecimalField()
date = DateTimeField()

class Meta:
indexes = (
# create a unique on from/to/date
(('from_acct', 'to_acct', 'date'), True),

# create a non-unique on from/to
(('from_acct', 'to_acct'), False),
)

NOTE:

Remember to add a trailing comma if your tuple of indexes contains only one item:

class Meta:
indexes = (
(('first_name', 'last_name'), True), # Note the trailing comma!
)

Advanced Index Creation

Peewee supports a more structured API for declaring indexes on a model using the Model.add_index() method or by directly using the ModelIndex helper class.

Examples:

class Article(Model):
name = TextField()
timestamp = TimestampField()
status = IntegerField()
flags = IntegerField()

# Add an index on "name" and "timestamp" columns.
Article.add_index(Article.name, Article.timestamp)

# Add a partial index on name and timestamp where status = 1.
Article.add_index(Article.name, Article.timestamp,
where=(Article.status == 1))

# Create a unique index on timestamp desc, status & 4.
idx = Article.index(
Article.timestamp.desc(),
Article.flags.bin_and(4),
unique=True)
Article.add_index(idx)

WARNING:

SQLite does not support parameterized CREATE INDEX queries. This means that when using SQLite to create an index that involves an expression or scalar value, you will need to declare the index using the SQL helper:

# SQLite does not support parameterized CREATE INDEX queries, so
# we declare it manually.
Article.add_index(SQL('CREATE INDEX ...'))

See add_index() for details.

For more information, see:

Table constraints

Peewee allows you to add arbitrary constraints to your Model , that will be part of the table definition when the schema is created.

For instance, suppose you have a people table with a composite primary key of two columns, the person's first and last name. You wish to have another table relate to the people table, and to do this, you will need to define a foreign key constraint:

class Person(Model):
first = CharField()
last = CharField()

class Meta:
primary_key = CompositeKey('first', 'last')

class Pet(Model):
owner_first = CharField()
owner_last = CharField()
pet_name = CharField()

class Meta:
constraints = [SQL('FOREIGN KEY(owner_first, owner_last) '
'REFERENCES person(first, last)')]

You can also implement CHECK constraints at the table level:

class Product(Model):
name = CharField(unique=True)
price = DecimalField()

class Meta:
constraints = [Check('price < 10000')]

Primary Keys, Composite Keys and other Tricks

The AutoField is used to identify an auto-incrementing integer primary key. If you do not specify a primary key, Peewee will automatically create an auto-incrementing primary key named "id".

To specify an auto-incrementing ID using a different field name, you can write:

class Event(Model):
event_id = AutoField() # Event.event_id will be auto-incrementing PK.
name = CharField()
timestamp = DateTimeField(default=datetime.datetime.now)
metadata = BlobField()

You can identify a different field as the primary key, in which case an "id" column will not be created. In this example we will use a person's email address as the primary key:

class Person(Model):
email = CharField(primary_key=True)
name = TextField()
dob = DateField()

WARNING:

I frequently see people write the following, expecting an auto-incrementing integer primary key:

class MyModel(Model):
id = IntegerField(primary_key=True)

Peewee understands the above model declaration as a model with an integer primary key, but the value of that ID is determined by the application. To create an auto-incrementing integer primary key, you would instead write:

class MyModel(Model):
id = AutoField() # primary_key=True is implied.

Composite primary keys can be declared using CompositeKey . Note that doing this may cause issues with ForeignKeyField , as Peewee does not support the concept of a "composite foreign-key". As such, I've found it only advisable to use composite primary keys in a handful of situations, such as trivial many-to-many junction tables:

class Image(Model):
filename = TextField()
mimetype = CharField()

class Tag(Model):
label = CharField()

class ImageTag(Model): # Many-to-many relationship.
image = ForeignKeyField(Image)
tag = ForeignKeyField(Tag)

class Meta:
primary_key = CompositeKey('image', 'tag')

In the extremely rare case you wish to declare a model with no primary key, you can specify primary_key = False in the model Meta options.

Non-integer primary keys

If you would like use a non-integer primary key (which I generally don't recommend), you can specify primary_key=True when creating a field. When you wish to create a new instance for a model using a non-autoincrementing primary key, you need to be sure you save() specifying force_insert=True .

from peewee import *

class UUIDModel(Model):
id = UUIDField(primary_key=True)

Auto-incrementing IDs are, as their name says, automatically generated for you when you insert a new row into the database. When you call save() , peewee determines whether to do an INSERT versus an UPDATE based on the presence of a primary key value. Since, with our uuid example, the database driver won't generate a new ID, we need to specify it manually. When we call save() for the first time, pass in force_insert = True :

# This works because .create() will specify `force_insert=True`.
obj1 = UUIDModel.create(id=uuid.uuid4())

# This will not work, however. Peewee will attempt to do an update:
obj2 = UUIDModel(id=uuid.uuid4())
obj2.save() # WRONG

obj2.save(force_insert=True) # CORRECT

# Once the object has been created, you can call save() normally.
obj2.save()

NOTE:

Any foreign keys to a model with a non-integer primary key will have a ForeignKeyField use the same underlying storage type as the primary key they are related to.

Composite primary keys

Peewee has very basic support for composite keys. In order to use a composite key, you must set the primary_key attribute of the model options to a CompositeKey instance:

class BlogToTag(Model):
"""A simple "through" table for many-to-many relationship."""
blog = ForeignKeyField(Blog)
tag = ForeignKeyField(Tag)

class Meta:
primary_key = CompositeKey('blog', 'tag')

WARNING:

Peewee does not support foreign-keys to models that define a CompositeKey primary key. If you wish to add a foreign-key to a model that has a composite primary key, replicate the columns on the related model and add a custom accessor (e.g. a property).

Manually specifying primary keys

Sometimes you do not want the database to automatically generate a value for the primary key, for instance when bulk loading relational data. To handle this on a one-off basis, you can simply tell peewee to turn off auto_increment during the import:

data = load_user_csv() # load up a bunch of data

User._meta.auto_increment = False # turn off auto incrementing IDs
with db.atomic():
for row in data:
u = User(id=row[0], username=row[1])
u.save(force_insert=True) # <-- force peewee to insert row

User._meta.auto_increment = True

Although a better way to accomplish the above, without resorting to hacks, is to use the Model.insert_many() API:

data = load_user_csv()
fields = [User.id, User.username]
with db.atomic():
User.insert_many(data, fields=fields).execute()

If you always want to have control over the primary key, simply do not use the AutoField field type, but use a normal IntegerField (or other column type):

class User(BaseModel):
id = IntegerField(primary_key=True)
username = CharField()

>>> u = User.create(id=999, username='somebody')
>>> u.id
999
>>> User.get(User.username == 'somebody').id
999

Models without a Primary Key

If you wish to create a model with no primary key, you can specify primary_key = False in the inner Meta class:

class MyData(BaseModel):
timestamp = DateTimeField()
value = IntegerField()

class Meta:
primary_key = False

This will yield the following DDL:

CREATE TABLE "mydata" (
"timestamp" DATETIME NOT NULL,
"value" INTEGER NOT NULL
)

WARNING:

Some model APIs may not work correctly for models without a primary key, for instance save() and delete_instance() (you can instead use insert() , update() and delete() ).

Self-referential foreign keys

When creating a hierarchical structure it is necessary to create a self-referential foreign key which links a child object to its parent. Because the model class is not defined at the time you instantiate the self-referential foreign key, use the special string 'self' to indicate a self-referential foreign key:

class Category(Model):
name = CharField()
parent = ForeignKeyField('self', null=True, backref='children')

As you can see, the foreign key points upward to the parent object and the back-reference is named children .

ATTENTION:

Self-referential foreign-keys should always be null=True .

When querying against a model that contains a self-referential foreign key you may sometimes need to perform a self-join. In those cases you can use Model.alias() to create a table reference. Here is how you might query the category and parent model using a self-join:

Parent = Category.alias()
GrandParent = Category.alias()
query = (Category
.select(Category, Parent)
.join(Parent, on=(Category.parent == Parent.id))
.join(GrandParent, on=(Parent.parent == GrandParent.id))
.where(GrandParent.name == 'some category')
.order_by(Category.name))

Circular foreign key dependencies

Sometimes it happens that you will create a circular dependency between two tables.

NOTE:

My personal opinion is that circular foreign keys are a code smell and should be refactored (by adding an intermediary table, for instance).

Adding circular foreign keys with peewee is a bit tricky because at the time you are defining either foreign key, the model it points to will not have been defined yet, causing a NameError .

class User(Model):
username = CharField()
favorite_tweet = ForeignKeyField(Tweet, null=True) # NameError!!

class Tweet(Model):
message = TextField()
user = ForeignKeyField(User, backref='tweets')

One option is to simply use an IntegerField to store the raw ID:

class User(Model):
username = CharField()
favorite_tweet_id = IntegerField(null=True)

By using DeferredForeignKey we can get around the problem and still use a foreign key field:

class User(Model):
username = CharField()
# Tweet has not been defined yet so use the deferred reference.
favorite_tweet = DeferredForeignKey('Tweet', null=True)

class Tweet(Model):
message = TextField()
user = ForeignKeyField(User, backref='tweets')

# Now that Tweet is defined, "favorite_tweet" has been converted into
# a ForeignKeyField.
print(User.favorite_tweet)
# <ForeignKeyField: "user"."favorite_tweet">

There is one more quirk to watch out for, though. When you call create_table we will again encounter the same issue. For this reason peewee will not automatically create a foreign key constraint for any deferred foreign keys.

To create the tables and the foreign-key constraint, you can use the SchemaManager.create_foreign_key() method to create the constraint after creating the tables:

# Will create the User and Tweet tables, but does *not* create a
# foreign-key constraint on User.favorite_tweet.
db.create_tables([User, Tweet])

# Create the foreign-key constraint:
User._schema.create_foreign_key(User.favorite_tweet)

NOTE:

Because SQLite has limited support for altering tables, foreign-key constraints cannot be added to a table after it has been created.

Querying

This section will cover the basic CRUD operations commonly performed on a relational database:

NOTE:

There is also a large collection of example queries taken from the - Postgresql Exercises website. Examples are listed on the query examples document.

Creating a new record

You can use Model.create() to create a new model instance. This method accepts keyword arguments, where the keys correspond to the names of the model's fields. A new instance is returned and a row is added to the table.

>>> User.create(username='Charlie')
<__main__.User object at 0x2529350>

This will INSERT a new row into the database. The primary key will automatically be retrieved and stored on the model instance.

Alternatively, you can build up a model instance programmatically and then call save() :

>>> user = User(username='Charlie')
>>> user.save() # save() returns the number of rows modified.
1
>>> user.id
1
>>> huey = User()
>>> huey.username = 'Huey'
>>> huey.save()
1
>>> huey.id
2

When a model has a foreign key, you can directly assign a model instance to the foreign key field when creating a new record.

>>> tweet = Tweet.create(user=huey, message='Hello!')

You can also use the value of the related object's primary key:

>>> tweet = Tweet.create(user=2, message='Hello again!')

If you simply wish to insert data and do not need to create a model instance, you can use Model.insert() :

>>> User.insert(username='Mickey').execute()
3

After executing the insert query, the primary key of the new row is returned.

NOTE:

There are several ways you can speed up bulk insert operations. Check out the Bulk inserts recipe section for more information.

Bulk inserts

There are a couple of ways you can load lots of data quickly. The naive approach is to simply call Model.create() in a loop:

data_source = [
{'field1': 'val1-1', 'field2': 'val1-2'},
{'field1': 'val2-1', 'field2': 'val2-2'},
# ...
]

for data_dict in data_source:
MyModel.create(**data_dict)

The above approach is slow for a couple of reasons:

You can get a significant speedup by simply wrapping this in a transaction with atomic() .

# This is much faster.
with db.atomic():
for data_dict in data_source:
MyModel.create(**data_dict)

The above code still suffers from points 2, 3 and 4. We can get another big boost by using insert_many() . This method accepts a list of tuples or dictionaries, and inserts multiple rows in a single query:

data_source = [
{'field1': 'val1-1', 'field2': 'val1-2'},
{'field1': 'val2-1', 'field2': 'val2-2'},
# ...
]

# Fastest way to INSERT multiple rows.
MyModel.insert_many(data_source).execute()

The insert_many() method also accepts a list of row-tuples, provided you also specify the corresponding fields:

# We can INSERT tuples as well...
data = [('val1-1', 'val1-2'),
('val2-1', 'val2-2'),
('val3-1', 'val3-2')]

# But we need to indicate which fields the values correspond to.
MyModel.insert_many(data, fields=[MyModel.field1, MyModel.field2]).execute()

It is also a good practice to wrap the bulk insert in a transaction:

# You can, of course, wrap this in a transaction as well:
with db.atomic():
MyModel.insert_many(data, fields=fields).execute()

NOTE:

SQLite users should be aware of some caveats when using bulk inserts. Specifically, your SQLite3 version must be 3.7.11.0 or newer to take advantage of the bulk insert API. Additionally, by default SQLite limits the number of bound variables in a SQL query to 999 for SQLite versions prior to 3.32.0 (2020-05-22) and 32766 for SQLite versions after 3.32.0.

Inserting rows in batches

Depending on the number of rows in your data source, you may need to break it up into chunks. SQLite in particular typically has a limit of 999 or 32766 variables-per-query (batch size would then be 999 // row length or 32766 // row length).

You can write a loop to batch your data into chunks (in which case it is strongly recommended you use a transaction):

# Insert rows 100 at a time.
with db.atomic():
for idx in range(0, len(data_source), 100):
MyModel.insert_many(data_source[idx:idx+100]).execute()

Peewee comes with a chunked() helper function which you can use for efficiently chunking a generic iterable into a series of batch -sized iterables:

from peewee import chunked

# Insert rows 100 at a time.
with db.atomic():
for batch in chunked(data_source, 100):
MyModel.insert_many(batch).execute()

Alternatives

The Model.bulk_create() method behaves much like Model.insert_many() , but instead it accepts a list of unsaved model instances to insert, and it optionally accepts a batch-size parameter. To use the bulk_create() API:

# Read list of usernames from a file, for example.
with open('user_list.txt') as fh:
# Create a list of unsaved User instances.
users = [User(username=line.strip()) for line in fh.readlines()]

# Wrap the operation in a transaction and batch INSERT the users
# 100 at a time.
with db.atomic():
User.bulk_create(users, batch_size=100)

NOTE:

If you are using Postgresql (which supports the RETURNING clause), then the previously-unsaved model instances will have their new primary key values automatically populated.

In addition, Peewee also offers Model.bulk_update() , which can efficiently update one or more columns on a list of models. For example:

# First, create 3 users with usernames u1, u2, u3.
u1, u2, u3 = [User.create(username='u%s' % i) for i in (1, 2, 3)]

# Now we'll modify the user instances.
u1.username = 'u1-x'
u2.username = 'u2-y'
u3.username = 'u3-z'

# Update all three users with a single UPDATE query.
User.bulk_update([u1, u2, u3], fields=[User.username])

This will result in executing the following SQL:

UPDATE "users" SET "username" = CASE "users"."id"
WHEN 1 THEN "u1-x"
WHEN 2 THEN "u2-y"
WHEN 3 THEN "u3-z" END
WHERE "users"."id" IN (1, 2, 3);

NOTE:

For large lists of objects, you should specify a reasonable batch_size and wrap the call to bulk_update() with Database.atomic() :

with database.atomic():
User.bulk_update(list_of_users, fields=['username'], batch_size=50)

WARNING:

Model.bulk_update() may not be the most efficient method for updating large numbers of records. This functionality is implemented such that we create a "mapping" of primary key to corresponding field values for all rows being updated using a SQL CASE statement.

Alternatively, you can use the Database.batch_commit() helper to process chunks of rows inside batch -sized transactions. This method also provides a workaround for databases besides Postgresql, when the primary-key of the newly-created rows must be obtained.

# List of row data to insert.
row_data = [{'username': 'u1'}, {'username': 'u2'}, ...]

# Assume there are 789 items in row_data. The following code will result in
# 8 total transactions (7x100 rows + 1x89 rows).
for row in db.batch_commit(row_data, 100):
User.create(**row)

Bulk-loading from another table

If the data you would like to bulk load is stored in another table, you can also create INSERT queries whose source is a SELECT query. Use the Model.insert_from() method:

res = (TweetArchive
.insert_from(
Tweet.select(Tweet.user, Tweet.message),
fields=[TweetArchive.user, TweetArchive.message])
.execute())

The above query is equivalent to the following SQL:

INSERT INTO "tweet_archive" ("user_id", "message")
SELECT "user_id", "message" FROM "tweet";

Updating existing records

Once a model instance has a primary key, any subsequent call to save() will result in an UPDATE rather than another INSERT . The model's primary key will not change:

>>> user.save() # save() returns the number of rows modified.
1
>>> user.id
1
>>> user.save()
>>> user.id
1
>>> huey.save()
1
>>> huey.id
2

If you want to update multiple records, issue an UPDATE query. The following example will update all Tweet objects, marking them as published , if they were created before today. Model.update() accepts keyword arguments where the keys correspond to the model's field names:

>>> today = datetime.today()
>>> query = Tweet.update(is_published=True).where(Tweet.creation_date < today)
>>> query.execute() # Returns the number of rows that were updated.
4

For more information, see the documentation on Model.update() , Update and Model.bulk_update() .

NOTE:

If you would like more information on performing atomic updates (such as incrementing the value of a column), check out the atomic update recipes.

Atomic updates

Peewee allows you to perform atomic updates. Let's suppose we need to update some counters. The naive approach would be to write something like this:

>>> for stat in Stat.select().where(Stat.url == request.url):
... stat.counter += 1
... stat.save()

Do not do this! Not only is this slow, but it is also vulnerable to race conditions if multiple processes are updating the counter at the same time.

Instead, you can update the counters atomically using update() :

>>> query = Stat.update(counter=Stat.counter + 1).where(Stat.url == request.url)
>>> query.execute()

You can make these update statements as complex as you like. Let's give all our employees a bonus equal to their previous bonus plus 10% of their salary:

>>> query = Employee.update(bonus=(Employee.bonus + (Employee.salary * .1)))
>>> query.execute() # Give everyone a bonus!

We can even use a subquery to update the value of a column. Suppose we had a denormalized column on the User model that stored the number of tweets a user had made, and we updated this value periodically. Here is how you might write such a query:

>>> subquery = Tweet.select(fn.COUNT(Tweet.id)).where(Tweet.user == User.id)
>>> update = User.update(num_tweets=subquery)
>>> update.execute()

Upsert

Peewee provides support for varying types of upsert functionality. With SQLite prior to 3.24.0 and MySQL, Peewee offers the replace() , which allows you to insert a record or, in the event of a constraint violation, replace the existing record. For Sqlite 3.24+ and Postgres, peewee provides full support for ON CONFLICT queries.

Example of using replace() and on_conflict_replace() :

class User(Model):
username = TextField(unique=True)
last_login = DateTimeField(null=True)

# Insert or update the user. The "last_login" value will be updated
# regardless of whether the user existed previously.
user_id = (User
.replace(username='the-user', last_login=datetime.now())
.execute())

# This query is equivalent:
user_id = (User
.insert(username='the-user', last_login=datetime.now())
.on_conflict_replace()
.execute())

NOTE:

In addition to replace , SQLite, MySQL and Postgresql provide an ignore action (see: on_conflict_ignore() ) if you simply wish to insert and ignore any potential constraint violation.

MySQL supports upsert via the ON DUPLICATE KEY UPDATE clause. For example:

class User(Model):
username = TextField(unique=True)
last_login = DateTimeField(null=True)
login_count = IntegerField()

# Insert a new user.
User.create(username='huey', login_count=0)

# Simulate the user logging in. The login count and timestamp will be
# either created or updated correctly.
now = datetime.now()
rowid = (User
.insert(username='huey', last_login=now, login_count=1)
.on_conflict(
preserve=[User.last_login], # Use the value we would have inserted.
update={User.login_count: User.login_count + 1})
.execute())

In the above example, we could safely invoke the upsert query as many times as we wanted. The login count will be incremented atomically, the last login column will be updated, and no duplicate rows will be created.

Postgresql and SQLite (3.24.0 and newer) provide a different syntax that allows for more granular control over which constraint violation should trigger the conflict resolution, and what values should be updated or preserved.

Example of using on_conflict() to perform a Postgresql-style upsert (or SQLite 3.24+):

class User(Model):
username = TextField(unique=True)
last_login = DateTimeField(null=True)
login_count = IntegerField()

# Insert a new user.
User.create(username='huey', login_count=0)

# Simulate the user logging in. The login count and timestamp will be
# either created or updated correctly.
now = datetime.now()
rowid = (User
.insert(username='huey', last_login=now, login_count=1)
.on_conflict(
conflict_target=[User.username], # Which constraint?
preserve=[User.last_login], # Use the value we would have inserted.
update={User.login_count: User.login_count + 1})
.execute())

In the above example, we could safely invoke the upsert query as many times as we wanted. The login count will be incremented atomically, the last login column will be updated, and no duplicate rows will be created.

NOTE:

The main difference between MySQL and Postgresql/SQLite is that Postgresql and SQLite require that you specify a conflict_target .

Here is a more advanced (if contrived) example using the EXCLUDED namespace. The EXCLUDED helper allows us to reference values in the conflicting data. For our example, we'll assume a simple table mapping a unique key (string) to a value (integer):

class KV(Model):
key = CharField(unique=True)
value = IntegerField()

# Create one row.
KV.create(key='k1', value=1)

# Demonstrate usage of EXCLUDED.
# Here we will attempt to insert a new value for a given key. If that
# key already exists, then we will update its value with the *sum* of its
# original value and the value we attempted to insert -- provided that
# the new value is larger than the original value.
query = (KV.insert(key='k1', value=10)
.on_conflict(conflict_target=[KV.key],
update={KV.value: KV.value + EXCLUDED.value},
where=(EXCLUDED.value > KV.value)))

# Executing the above query will result in the following data being
# present in the "kv" table:
# (key='k1', value=11)
query.execute()

# If we attempted to execute the query *again*, then nothing would be
# updated, as the new value (10) is now less than the value in the
# original row (11).

There are several important concepts to understand when using ON CONFLICT :

Full example:

class User(Model):
email = CharField(unique=True) # Unique identifier for user.
last_login = DateTimeField()
login_count = IntegerField(default=0)
ip_log = TextField(default='')

# Demonstrates the above 4 concepts.
def login(email, ip):
rowid = (User
.insert({User.email: email,
User.last_login: datetime.now(),
User.login_count: 1,
User.ip_log: ip})
.on_conflict(
# If the INSERT fails due to a constraint violation on the
# user email, then perform an UPDATE instead.
conflict_target=[User.email],

# Set the "last_login" to the value we would have inserted
# (our call to datetime.now()).
preserve=[User.last_login],

# Increment the user's login count and prepend the new IP
# to the user's ip history.
update={User.login_count: User.login_count + 1,
User.ip_log: fn.CONCAT(EXCLUDED.ip_log, ',', User.ip_log)})
.execute())

return rowid

# This will insert the initial row, returning the new row id (1).
print(login('test@example.com', '127.1'))

# Because test@example.com exists, this will trigger the UPSERT. The row id
# from above is returned again (1).
print(login('test@example.com', '127.2'))

u = User.get()
print(u.login_count, u.ip_log)

# Prints "2 127.2,127.1"

For more information, see Insert.on_conflict() and OnConflict .

Deleting records

To delete a single model instance, you can use the Model.delete_instance() shortcut. delete_instance() will delete the given model instance and can optionally delete any dependent objects recursively (by specifying recursive=True ).

>>> user = User.get(User.id == 1)
>>> user.delete_instance() # Returns the number of rows deleted.
1

>>> User.get(User.id == 1)
UserDoesNotExist: instance matching query does not exist:
SQL: SELECT t1."id", t1."username" FROM "user" AS t1 WHERE t1."id" = ?
PARAMS: [1]

To delete an arbitrary set of rows, you can issue a DELETE query. The following will delete all Tweet objects that are over one year old:

>>> query = Tweet.delete().where(Tweet.creation_date < one_year_ago)
>>> query.execute() # Returns the number of rows deleted.
7

For more information, see the documentation on:

Selecting a single record

You can use the Model.get() method to retrieve a single instance matching the given query. For primary-key lookups, you can also use the shortcut method Model.get_by_id() .

This method is a shortcut that calls Model.select() with the given query, but limits the result set to a single row. Additionally, if no model matches the given query, a DoesNotExist exception will be raised.

>>> User.get(User.id == 1)
<__main__.User object at 0x25294d0>

>>> User.get_by_id(1) # Same as above.
<__main__.User object at 0x252df10>

>>> User[1] # Also same as above.
<__main__.User object at 0x252dd10>

>>> User.get(User.id == 1).username
u'Charlie'

>>> User.get(User.username == 'Charlie')
<__main__.User object at 0x2529410>

>>> User.get(User.username == 'nobody')
UserDoesNotExist: instance matching query does not exist:
SQL: SELECT t1."id", t1."username" FROM "user" AS t1 WHERE t1."username" = ?
PARAMS: ['nobody']

For more advanced operations, you can use SelectBase.get() . The following query retrieves the latest tweet from the user named charlie :

>>> (Tweet
... .select()
... .join(User)
... .where(User.username == 'charlie')
... .order_by(Tweet.created_date.desc())
... .get())
<__main__.Tweet object at 0x2623410>

For more information, see the documentation on:

Create or get

Peewee has one helper method for performing "get/create" type operations: Model.get_or_create() , which first attempts to retrieve the matching row. Failing that, a new row will be created.

For "create or get" type logic, typically one would rely on a unique constraint or primary key to prevent the creation of duplicate objects. As an example, let's say we wish to implement registering a new user account using the example User model . The User model has a unique constraint on the username field, so we will rely on the database's integrity guarantees to ensure we don't end up with duplicate usernames:

try:
with db.atomic():
return User.create(username=username)
except peewee.IntegrityError:
# `username` is a unique column, so this username already exists,
# making it safe to call .get().
return User.get(User.username == username)

You can easily encapsulate this type of logic as a classmethod on your own Model classes.

The above example first attempts at creation, then falls back to retrieval, relying on the database to enforce a unique constraint. If you prefer to attempt to retrieve the record first, you can use get_or_create() . This method is implemented along the same lines as the Django function of the same name. You can use the Django-style keyword argument filters to specify your WHERE conditions. The function returns a 2-tuple containing the instance and a boolean value indicating if the object was created.

Here is how you might implement user account creation using get_or_create() :

user, created = User.get_or_create(username=username)

Suppose we have a different model Person and would like to get or create a person object. The only conditions we care about when retrieving the Person are their first and last names, but if we end up needing to create a new record, we will also specify their date-of-birth and favorite color:

person, created = Person.get_or_create(
first_name=first_name,
last_name=last_name,
defaults={'dob': dob, 'favorite_color': 'green'})

Any keyword argument passed to get_or_create() will be used in the get() portion of the logic, except for the defaults dictionary, which will be used to populate values on newly-created instances.

For more details read the documentation for Model.get_or_create() .

Selecting multiple records

We can use Model.select() to retrieve rows from the table. When you construct a SELECT query, the database will return any rows that correspond to your query. Peewee allows you to iterate over these rows, as well as use indexing and slicing operations:

>>> query = User.select()
>>> [user.username for user in query]
['Charlie', 'Huey', 'Peewee']

>>> query[1]
<__main__.User at 0x7f83e80f5550>

>>> query[1].username
'Huey'

>>> query[:2]
[<__main__.User at 0x7f83e80f53a8>, <__main__.User at 0x7f83e80f5550>]

Select queries are smart, in that you can iterate, index and slice the query multiple times but the query is only executed once.

In the following example, we will simply call select() and iterate over the return value, which is an instance of Select . This will return all the rows in the User table:

>>> for user in User.select():
... print(user.username)
...
Charlie
Huey
Peewee

NOTE:

Subsequent iterations of the same query will not hit the database as the results are cached. To disable this behavior (to reduce memory usage), call Select.iterator() when iterating.

When iterating over a model that contains a foreign key, be careful with the way you access values on related models. Accidentally resolving a foreign key or iterating over a back-reference can cause N+1 query behavior .

When you create a foreign key, such as Tweet.user , you can use the backref to create a back-reference ( User.tweets ). Back-references are exposed as Select instances:

>>> tweet = Tweet.get()
>>> tweet.user # Accessing a foreign key returns the related model.
<tw.User at 0x7f3ceb017f50>

>>> user = User.get()
>>> user.tweets # Accessing a back-reference returns a query.
<peewee.ModelSelect at 0x7f73db3bafd0>

You can iterate over the user.tweets back-reference just like any other Select :

>>> for tweet in user.tweets:
... print(tweet.message)
...
hello world
this is fun
look at this picture of my food

In addition to returning model instances, Select queries can return dictionaries, tuples and namedtuples. Depending on your use-case, you may find it easier to work with rows as dictionaries, for example:

>>> query = User.select().dicts()
>>> for row in query:
... print(row)

{'id': 1, 'username': 'Charlie'}
{'id': 2, 'username': 'Huey'}
{'id': 3, 'username': 'Peewee'}

See namedtuples() , tuples() , dicts() for more information.

Iterating over large result-sets

By default peewee will cache the rows returned when iterating over a Select query. This is an optimization to allow multiple iterations as well as indexing and slicing without causing additional queries. This caching can be problematic, however, when you plan to iterate over a large number of rows.

To reduce the amount of memory used by peewee when iterating over a query, use the iterator() method. This method allows you to iterate without caching each model returned, using much less memory when iterating over large result sets.

# Let's assume we've got 10 million stat objects to dump to a csv file.
stats = Stat.select()

# Our imaginary serializer class
serializer = CSVSerializer()

# Loop over all the stats and serialize.
for stat in stats.iterator():
serializer.serialize_object(stat)

For simple queries you can see further speed improvements by returning rows as dictionaries, namedtuples or tuples. The following methods can be used on any Select query to change the result row type:

Don't forget to append the iterator() method call to also reduce memory consumption. For example, the above code might look like:

# Let's assume we've got 10 million stat objects to dump to a csv file.
stats = Stat.select()

# Our imaginary serializer class
serializer = CSVSerializer()

# Loop over all the stats (rendered as tuples, without caching) and serialize.
for stat_tuple in stats.tuples().iterator():
serializer.serialize_tuple(stat_tuple)

When iterating over a large number of rows that contain columns from multiple tables, peewee will reconstruct the model graph for each row returned. This operation can be slow for complex graphs. For example, if we were selecting a list of tweets along with the username and avatar of the tweet's author, Peewee would have to create two objects for each row (a tweet and a user). In addition to the above row-types, there is a fourth method objects() which will return the rows as model instances, but will not attempt to resolve the model graph.

For example:

query = (Tweet
.select(Tweet, User) # Select tweet and user data.
.join(User))

# Note that the user columns are stored in a separate User instance
# accessible at tweet.user:
for tweet in query:
print(tweet.user.username, tweet.content)

# Using ".objects()" will not create the tweet.user object and assigns all
# user attributes to the tweet instance:
for tweet in query.objects():
print(tweet.username, tweet.content)

For maximum performance, you can execute queries and then iterate over the results using the underlying database cursor. Database.execute() accepts a query object, executes the query, and returns a DB-API 2.0 Cursor object. The cursor will return the raw row-tuples:

query = Tweet.select(Tweet.content, User.username).join(User)
cursor = database.execute(query)
for (content, username) in cursor:
print(username, '->', content)

Filtering records

You can filter for particular records using normal python operators. Peewee supports a wide variety of query operators .

>>> user = User.get(User.username == 'Charlie')
>>> for tweet in Tweet.select().where(Tweet.user == user, Tweet.is_published == True):
... print(tweet.user.username, '->', tweet.message)
...
Charlie -> hello world
Charlie -> this is fun

>>> for tweet in Tweet.select().where(Tweet.created_date < datetime.datetime(2011, 1, 1)):
... print(tweet.message, tweet.created_date)
...
Really old tweet 2010-01-01 00:00:00

You can also filter across joins:

>>> for tweet in Tweet.select().join(User).where(User.username == 'Charlie'):
... print(tweet.message)
hello world
this is fun
look at this picture of my food

If you want to express a complex query, use parentheses and python's bitwise or and and operators:

>>> Tweet.select().join(User).where(
... (User.username == 'Charlie') |
... (User.username == 'Peewee Herman'))

NOTE:

Note that Peewee uses bitwise operators ( & and | ) rather than logical operators ( and and or ). The reason for this is that Python coerces the return value of logical operations to a boolean value. This is also the reason why "IN" queries must be expressed using .in_() rather than the in operator.

Check out the table of query operations to see what types of queries are possible.

NOTE:

A lot of fun things can go in the where clause of a query, such as:

You can also nest queries, for example tweets by users whose username starts with "a":

# get users whose username starts with "a"
a_users = User.select().where(fn.Lower(fn.Substr(User.username, 1, 1)) == 'a')

# the ".in_()" method signifies an "IN" query
a_user_tweets = Tweet.select().where(Tweet.user.in_(a_users))

More query examples

NOTE:

For a wide range of example queries, see the Query Examples document, which shows how to implements queries from the PostgreSQL Exercises website.

Get active users:

User.select().where(User.active == True)

Get users who are either staff or superusers:

User.select().where(
(User.is_staff == True) | (User.is_superuser == True))

Get tweets by user named "charlie":

Tweet.select().join(User).where(User.username == 'charlie')

Get tweets by staff or superusers (assumes FK relationship):

Tweet.select().join(User).where(
(User.is_staff == True) | (User.is_superuser == True))

Get tweets by staff or superusers using a subquery:

staff_super = User.select(User.id).where(
(User.is_staff == True) | (User.is_superuser == True))
Tweet.select().where(Tweet.user.in_(staff_super))

Sorting records

To return rows in order, use the order_by() method:

>>> for t in Tweet.select().order_by(Tweet.created_date):
... print(t.pub_date)
...
2010-01-01 00:00:00
2011-06-07 14:08:48
2011-06-07 14:12:57

>>> for t in Tweet.select().order_by(Tweet.created_date.desc()):
... print(t.pub_date)
...
2011-06-07 14:12:57
2011-06-07 14:08:48
2010-01-01 00:00:00

You can also use + and - prefix operators to indicate ordering:

# The following queries are equivalent:
Tweet.select().order_by(Tweet.created_date.desc())

Tweet.select().order_by(-Tweet.created_date) # Note the "-" prefix.

# Similarly you can use "+" to indicate ascending order, though ascending
# is the default when no ordering is otherwise specified.
User.select().order_by(+User.username)

You can also order across joins. Assuming you want to order tweets by the username of the author, then by created_date:

query = (Tweet
.select()
.join(User)
.order_by(User.username, Tweet.created_date.desc()))

SELECT t1."id", t1."user_id", t1."message", t1."is_published", t1."created_date"
FROM "tweet" AS t1
INNER JOIN "user" AS t2
ON t1."user_id" = t2."id"
ORDER BY t2."username", t1."created_date" DESC

When sorting on a calculated value, you can either include the necessary SQL expressions, or reference the alias assigned to the value. Here are two examples illustrating these methods:

# Let's start with our base query. We want to get all usernames and the number of
# tweets they've made. We wish to sort this list from users with most tweets to
# users with fewest tweets.
query = (User
.select(User.username, fn.COUNT(Tweet.id).alias('num_tweets'))
.join(Tweet, JOIN.LEFT_OUTER)
.group_by(User.username))

You can order using the same COUNT expression used in the select clause. In the example below we are ordering by the COUNT() of tweet ids descending:

query = (User
.select(User.username, fn.COUNT(Tweet.id).alias('num_tweets'))
.join(Tweet, JOIN.LEFT_OUTER)
.group_by(User.username)
.order_by(fn.COUNT(Tweet.id).desc()))

Alternatively, you can reference the alias assigned to the calculated value in the select clause. This method has the benefit of being a bit easier to read. Note that we are not referring to the named alias directly, but are wrapping it using the SQL helper:

query = (User
.select(User.username, fn.COUNT(Tweet.id).alias('num_tweets'))
.join(Tweet, JOIN.LEFT_OUTER)
.group_by(User.username)
.order_by(SQL('num_tweets').desc()))

Or, to do things the "peewee" way:

ntweets = fn.COUNT(Tweet.id)
query = (User
.select(User.username, ntweets.alias('num_tweets'))
.join(Tweet, JOIN.LEFT_OUTER)
.group_by(User.username)
.order_by(ntweets.desc())

Getting random records

Occasionally you may want to pull a random record from the database. You can accomplish this by ordering by the random or rand function (depending on your database):

Postgresql and Sqlite use the Random function:

# Pick 5 lucky winners:
LotteryNumber.select().order_by(fn.Random()).limit(5)

MySQL uses Rand :

# Pick 5 lucky winners:
LotteryNumber.select().order_by(fn.Rand()).limit(5)

Paginating records

The paginate() method makes it easy to grab a page or records. paginate() takes two parameters, page_number , and items_per_page .

ATTENTION:

Page numbers are 1-based, so the first page of results will be page 1.

>>> for tweet in Tweet.select().order_by(Tweet.id).paginate(2, 10):
... print(tweet.message)
...
tweet 10
tweet 11
tweet 12
tweet 13
tweet 14
tweet 15
tweet 16
tweet 17
tweet 18
tweet 19

If you would like more granular control, you can always use limit() and offset() .

Counting records

You can count the number of rows in any select query:

>>> Tweet.select().count()
100
>>> Tweet.select().where(Tweet.id > 50).count()
50

Peewee will wrap your query in an outer query that performs a count, which results in SQL like:

SELECT COUNT(1) FROM ( ... your query ... );

Aggregating records

Suppose you have some users and want to get a list of them along with the count of tweets in each.

query = (User
.select(User, fn.Count(Tweet.id).alias('count'))
.join(Tweet, JOIN.LEFT_OUTER)
.group_by(User))

The resulting query will return User objects with all their normal attributes plus an additional attribute count which will contain the count of tweets for each user. We use a left outer join to include users who have no tweets.

Let's assume you have a tagging application and want to find tags that have a certain number of related objects. For this example we'll use some different models in a many-to-many configuration:

class Photo(Model):
image = CharField()

class Tag(Model):
name = CharField()

class PhotoTag(Model):
photo = ForeignKeyField(Photo)
tag = ForeignKeyField(Tag)

Now say we want to find tags that have at least 5 photos associated with them:

query = (Tag
.select()
.join(PhotoTag)
.join(Photo)
.group_by(Tag)
.having(fn.Count(Photo.id) > 5))

This query is equivalent to the following SQL:

SELECT t1."id", t1."name"
FROM "tag" AS t1
INNER JOIN "phototag" AS t2 ON t1."id" = t2."tag_id"
INNER JOIN "photo" AS t3 ON t2."photo_id" = t3."id"
GROUP BY t1."id", t1."name"
HAVING Count(t3."id") > 5

Suppose we want to grab the associated count and store it on the tag:

query = (Tag
.select(Tag, fn.Count(Photo.id).alias('count'))
.join(PhotoTag)
.join(Photo)
.group_by(Tag)
.having(fn.Count(Photo.id) > 5))

Retrieving Scalar Values

You can retrieve scalar values by calling Query.scalar() . For instance:

>>> PageView.select(fn.Count(fn.Distinct(PageView.url))).scalar()
100

You can retrieve multiple scalar values by passing as_tuple=True :

>>> Employee.select(
... fn.Min(Employee.salary), fn.Max(Employee.salary)
... ).scalar(as_tuple=True)
(30000, 50000)

Window functions

A Window function refers to an aggregate function that operates on a sliding window of data that is being processed as part of a SELECT query. Window functions make it possible to do things like:

peewee comes with support for SQL window functions, which can be created by calling Function.over() and passing in your partitioning or ordering parameters.

For the following examples, we'll use the following model and sample data:

class Sample(Model):
counter = IntegerField()
value = FloatField()

data = [(1, 10),
(1, 20),
(2, 1),
(2, 3),
(3, 100)]
Sample.insert_many(data, fields=[Sample.counter, Sample.value]).execute()

Ordered Windows

Let's calculate a running sum of the value field. In order for it to be a "running" sum, we need it to be ordered, so we'll order with respect to the Sample's id field:

query = Sample.select(
Sample.counter,
Sample.value,
fn.SUM(Sample.value).over(order_by=[Sample.id]).alias('total'))

for sample in query:
print(sample.counter, sample.value, sample.total)

# 1 10. 10.
# 1 20. 30.
# 2 1. 31.
# 2 3. 34.
# 3 100 134.

For another example, we'll calculate the difference between the current value and the previous value, when ordered by the id :

difference = Sample.value - fn.LAG(Sample.value, 1).over(order_by=[Sample.id])
query = Sample.select(
Sample.counter,
Sample.value,
difference.alias('diff'))

for sample in query:
print(sample.counter, sample.value, sample.diff)

# 1 10. NULL
# 1 20. 10. -- (20 - 10)
# 2 1. -19. -- (1 - 20)
# 2 3. 2. -- (3 - 1)
# 3 100 97. -- (100 - 3)

Partitioned Windows

Let's calculate the average value for each distinct "counter" value. Notice that there are three possible values for the counter field (1, 2, and 3). We can do this by calculating the AVG() of the value column over a window that is partitioned depending on the counter field:

query = Sample.select(
Sample.counter,
Sample.value,
fn.AVG(Sample.value).over(partition_by=[Sample.counter]).alias('cavg'))

for sample in query:
print(sample.counter, sample.value, sample.cavg)

# 1 10. 15.
# 1 20. 15.
# 2 1. 2.
# 2 3. 2.
# 3 100 100.

We can use ordering within partitions by specifying both the order_by and partition_by parameters. For an example, let's rank the samples by value within each distinct counter group.

query = Sample.select(
Sample.counter,
Sample.value,
fn.RANK().over(
order_by=[Sample.value],
partition_by=[Sample.counter]).alias('rank'))

for sample in query:
print(sample.counter, sample.value, sample.rank)

# 1 10. 1
# 1 20. 2
# 2 1. 1
# 2 3. 2
# 3 100 1

Bounded windows

By default, window functions are evaluated using an unbounded preceding start for the window, and the current row as the end. We can change the bounds of the window our aggregate functions operate on by specifying a start and/or end in the call to Function.over() . Additionally, Peewee comes with helper-methods on the Window object for generating the appropriate boundary references:

To examine how boundaries work, we'll calculate a running total of the value column, ordered with respect to id , but we'll only look the running total of the current row and it's two preceding rows:

query = Sample.select(
Sample.counter,
Sample.value,
fn.SUM(Sample.value).over(
order_by=[Sample.id],
start=Window.preceding(2),
end=Window.CURRENT_ROW).alias('rsum'))

for sample in query:
print(sample.counter, sample.value, sample.rsum)

# 1 10. 10.
# 1 20. 30. -- (20 + 10)
# 2 1. 31. -- (1 + 20 + 10)
# 2 3. 24. -- (3 + 1 + 20)
# 3 100 104. -- (100 + 3 + 1)

NOTE:

Technically we did not need to specify the end=Window.CURRENT because that is the default. It was shown in the example for demonstration.

Let's look at another example. In this example we will calculate the "opposite" of a running total, in which the total sum of all values is decreased by the value of the samples, ordered by id . To accomplish this, we'll calculate the sum from the current row to the last row.

query = Sample.select(
Sample.counter,
Sample.value,
fn.SUM(Sample.value).over(
order_by=[Sample.id],
start=Window.CURRENT_ROW,
end=Window.following()).alias('rsum'))

# 1 10. 134. -- (10 + 20 + 1 + 3 + 100)
# 1 20. 124. -- (20 + 1 + 3 + 100)
# 2 1. 104. -- (1 + 3 + 100)
# 2 3. 103. -- (3 + 100)
# 3 100 100. -- (100)

Filtered Aggregates

Aggregate functions may also support filter functions (Postgres and Sqlite 3.25+), which get translated into a FILTER (WHERE...) clause. Filter expressions are added to an aggregate function with the Function.filter() method.

For an example, we will calculate the running sum of the value field with respect to the id , but we will filter-out any samples whose counter=2 .

query = Sample.select(
Sample.counter,
Sample.value,
fn.SUM(Sample.value).filter(Sample.counter != 2).over(
order_by=[Sample.id]).alias('csum'))

for sample in query:
print(sample.counter, sample.value, sample.csum)

# 1 10. 10.
# 1 20. 30.
# 2 1. 30.
# 2 3. 30.
# 3 100 130.

NOTE:

The call to filter() must precede the call to over() .

Reusing Window Definitions

If you intend to use the same window definition for multiple aggregates, you can create a Window object. The Window object takes the same parameters as Function.over() , and can be passed to the over() method in-place of the individual parameters.

Here we'll declare a single window, ordered with respect to the sample id , and call several window functions using that window definition:

win = Window(order_by=[Sample.id])
query = Sample.select(
Sample.counter,
Sample.value,
fn.LEAD(Sample.value).over(win),
fn.LAG(Sample.value).over(win),
fn.SUM(Sample.value).over(win)
).window(win) # Include our window definition in query.

for row in query.tuples():
print(row)

# counter value lead() lag() sum()
# 1 10. 20. NULL 10.
# 1 20. 1. 10. 30.
# 2 1. 3. 20. 31.
# 2 3. 100. 1. 34.
# 3 100. NULL 3. 134.

Multiple window definitions

In the previous example, we saw how to declare a Window definition and re-use it for multiple different aggregations. You can include as many window definitions as you need in your queries, but it is necessary to ensure each window has a unique alias:

w1 = Window(order_by=[Sample.id]).alias('w1')
w2 = Window(partition_by=[Sample.counter]).alias('w2')
query = Sample.select(
Sample.counter,
Sample.value,
fn.SUM(Sample.value).over(w1).alias('rsum'), # Running total.
fn.AVG(Sample.value).over(w2).alias('cavg') # Avg per category.
).window(w1, w2) # Include our window definitions.

for sample in query:
print(sample.counter, sample.value, sample.rsum, sample.cavg)

# counter value rsum cavg
# 1 10. 10. 15.
# 1 20. 30. 15.
# 2 1. 31. 2.
# 2 3. 34. 2.
# 3 100 134. 100.

Similarly, if you have multiple window definitions that share similar definitions, it is possible to extend a previously-defined window definition. For example, here we will be partitioning the data-set by the counter value, so we'll be doing our aggregations with respect to the counter. Then we'll define a second window that extends this partitioning, and adds an ordering clause:

w1 = Window(partition_by=[Sample.counter]).alias('w1')

# By extending w1, this window definition will also be partitioned
# by "counter".
w2 = Window(extends=w1, order_by=[Sample.value.desc()]).alias('w2')

query = (Sample
.select(Sample.counter, Sample.value,
fn.SUM(Sample.value).over(w1).alias('group_sum'),
fn.RANK().over(w2).alias('revrank'))
.window(w1, w2)
.order_by(Sample.id))

for sample in query:
print(sample.counter, sample.value, sample.group_sum, sample.revrank)

# counter value group_sum revrank
# 1 10. 30. 2
# 1 20. 30. 1
# 2 1. 4. 2
# 2 3. 4. 1
# 3 100. 100. 1

Frame types: RANGE vs ROWS vs GROUPS

Depending on the frame type, the database will process ordered groups differently. Let's create two additional Sample rows to visualize the difference:

>>> Sample.create(counter=1, value=20.)
<Sample 6>
>>> Sample.create(counter=2, value=1.)
<Sample 7>

Let's examine the difference by calculating a "running sum" of the samples, ordered with respect to the counter and value fields. To specify the frame type, we can use either:

The behavior of RANGE , when there are logical duplicates, may lead to unexpected results:

query = Sample.select(
Sample.counter,
Sample.value,
fn.SUM(Sample.value).over(
order_by=[Sample.counter, Sample.value],
frame_type=Window.RANGE).alias('rsum'))

for sample in query.order_by(Sample.counter, Sample.value):
print(sample.counter, sample.value, sample.rsum)

# counter value rsum
# 1 10. 10.
# 1 20. 50.
# 1 20. 50.
# 2 1. 52.
# 2 1. 52.
# 2 3. 55.
# 3 100 155.

With the inclusion of the new rows we now have some rows that have duplicate category and value values. The RANGE frame type causes these duplicates to be evaluated together rather than separately.

The more expected result can be achieved by using ROWS as the frame-type:

query = Sample.select(
Sample.counter,
Sample.value,
fn.SUM(Sample.value).over(
order_by=[Sample.counter, Sample.value],
frame_type=Window.ROWS).alias('rsum'))

for sample in query.order_by(Sample.counter, Sample.value):
print(sample.counter, sample.value, sample.rsum)

# counter value rsum
# 1 10. 10.
# 1 20. 30.
# 1 20. 50.
# 2 1. 51.
# 2 1. 52.
# 2 3. 55.
# 3 100 155.

Peewee uses these rules for determining what frame-type to use:

The Window.GROUPS frame type looks at the window range specification in terms of groups of rows, based on the ordering term(s). Using GROUPS , we can define the frame so it covers distinct groupings of rows. Let's look at an example:

query = (Sample
.select(Sample.counter, Sample.value,
fn.SUM(Sample.value).over(
order_by=[Sample.counter, Sample.value],
frame_type=Window.GROUPS,
start=Window.preceding(1)).alias('gsum'))
.order_by(Sample.counter, Sample.value))

for sample in query:
print(sample.counter, sample.value, sample.gsum)

# counter value gsum
# 1 10 10
# 1 20 50
# 1 20 50 (10) + (20+0)
# 2 1 42
# 2 1 42 (20+20) + (1+1)
# 2 3 5 (1+1) + 3
# 3 100 103 (3) + 100

As you can hopefully infer, the window is grouped by its ordering term, which is (counter, value) . We are looking at a window that extends between one previous group and the current group.

NOTE:

For information about the window function APIs, see:

For general information on window functions, read the postgres - window functions tutorial

Additionally, the postgres docs and the sqlite docs contain a lot of good information.

Retrieving row tuples / dictionaries / namedtuples

Sometimes you do not need the overhead of creating model instances and simply want to iterate over the row data without needing all the APIs provided Model . To do this, use:

stats = (Stat
.select(Stat.url, fn.Count(Stat.url))
.group_by(Stat.url)
.tuples())

# iterate over a list of 2-tuples containing the url and count
for stat_url, stat_count in stats:
print(stat_url, stat_count)

Similarly, you can return the rows from the cursor as dictionaries using dicts() :

stats = (Stat
.select(Stat.url, fn.Count(Stat.url).alias('ct'))
.group_by(Stat.url)
.dicts())

# iterate over a list of 2-tuples containing the url and count
for stat in stats:
print(stat['url'], stat['ct'])

Returning Clause

PostgresqlDatabase supports a RETURNING clause on UPDATE , INSERT and DELETE queries. Specifying a RETURNING clause allows you to iterate over the rows accessed by the query.

By default, the return values upon execution of the different queries are:

When a returning clause is used the return value upon executing a query will be an iterable cursor object.

Postgresql allows, via the RETURNING clause, to return data from the rows inserted or modified by a query.

For example, let's say you have an Update that deactivates all user accounts whose registration has expired. After deactivating them, you want to send each user an email letting them know their account was deactivated. Rather than writing two queries, a SELECT and an UPDATE , you can do this in a single UPDATE query with a RETURNING clause:

query = (User
.update(is_active=False)
.where(User.registration_expired == True)
.returning(User))

# Send an email to every user that was deactivated.
for deactivate_user in query.execute():
send_deactivation_email(deactivated_user.email)

The RETURNING clause is also available on Insert and Delete . When used with INSERT , the newly-created rows will be returned. When used with DELETE , the deleted rows will be returned.

The only limitation of the RETURNING clause is that it can only consist of columns from tables listed in the query's FROM clause. To select all columns from a particular table, you can simply pass in the Model class.

As another example, let's add a user and set their creation-date to the server-generated current timestamp. We'll create and retrieve the new user's ID, Email and the creation timestamp in a single query:

query = (User
.insert(email='foo@bar.com', created=fn.now())
.returning(User)) # Shorthand for all columns on User.

# When using RETURNING, execute() returns a cursor.
cursor = query.execute()

# Get the user object we just inserted and log the data:
user = cursor[0]
logger.info('Created user %s (id=%s) at %s', user.email, user.id, user.created)

By default the cursor will return Model instances, but you can specify a different row type:

data = [{'name': 'charlie'}, {'name': 'huey'}, {'name': 'mickey'}]
query = (User
.insert_many(data)
.returning(User.id, User.username)
.dicts())

for new_user in query.execute():
print('Added user "%s", id=%s' % (new_user['username'], new_user['id']))

Just as with Select queries, you can specify various result row types .

Common Table Expressions

Peewee supports the inclusion of common table expressions (CTEs) in all types of queries. CTEs may be useful for:

To declare a Select query for use as a CTE, use cte() method, which wraps the query in a CTE object. To indicate that a CTE should be included as part of a query, use the Query.with_cte() method, passing a list of CTE objects.

Simple Example

For an example, let's say we have some data points that consist of a key and a floating-point value. Let's define our model and populate some test data:

class Sample(Model):
key = TextField()
value = FloatField()

data = (
('a', (1.25, 1.5, 1.75)),
('b', (2.1, 2.3, 2.5, 2.7, 2.9)),
('c', (3.5, 3.5)))

# Populate data.
for key, values in data:
Sample.insert_many([(key, value) for value in values],
fields=[Sample.key, Sample.value]).execute()

Let's use a CTE to calculate, for each distinct key, which values were above-average for that key.

# First we'll declare the query that will be used as a CTE. This query
# simply determines the average value for each key.
cte = (Sample
.select(Sample.key, fn.AVG(Sample.value).alias('avg_value'))
.group_by(Sample.key)
.cte('key_avgs', columns=('key', 'avg_value')))

# Now we'll query the sample table, using our CTE to find rows whose value
# exceeds the average for the given key. We'll calculate how far above the
# average the given sample's value is, as well.
query = (Sample
.select(Sample.key, Sample.value)
.join(cte, on=(Sample.key == cte.c.key))
.where(Sample.value > cte.c.avg_value)
.order_by(Sample.value)
.with_cte(cte))

We can iterate over the samples returned by the query to see which samples had above-average values for their given group:

>>> for sample in query:
... print(sample.key, sample.value)

# 'a', 1.75
# 'b', 2.7
# 'b', 2.9

Complex Example

For a more complete example, let's consider the following query which uses multiple CTEs to find per-product sales totals in only the top sales regions. Our model looks like this:

class Order(Model):
region = TextField()
amount = FloatField()
product = TextField()
quantity = IntegerField()

Here is how the query might be written in SQL. This example can be found in the postgresql documentation .

WITH regional_sales AS (
SELECT region, SUM(amount) AS total_sales
FROM orders
GROUP BY region
), top_regions AS (
SELECT region
FROM regional_sales
WHERE total_sales > (SELECT SUM(total_sales) / 10 FROM regional_sales)
)
SELECT region,
product,
SUM(quantity) AS product_units,
SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

With Peewee, we would write:

reg_sales = (Order
.select(Order.region,
fn.SUM(Order.amount).alias('total_sales'))
.group_by(Order.region)
.cte('regional_sales'))

top_regions = (reg_sales
.select(reg_sales.c.region)
.where(reg_sales.c.total_sales > (
reg_sales.select(fn.SUM(reg_sales.c.total_sales) / 10)))
.cte('top_regions'))

query = (Order
.select(Order.region,
Order.product,
fn.SUM(Order.quantity).alias('product_units'),
fn.SUM(Order.amount).alias('product_sales'))
.where(Order.region.in_(top_regions.select(top_regions.c.region)))
.group_by(Order.region, Order.product)
.with_cte(reg_sales, top_regions))

Recursive CTEs

Peewee supports recursive CTEs. Recursive CTEs can be useful when, for example, you have a tree data-structure represented by a parent-link foreign key. Suppose, for example, that we have a hierarchy of categories for an online bookstore. We wish to generate a table showing all categories and their absolute depths, along with the path from the root to the category.

We'll assume the following model definition, in which each category has a foreign-key to its immediate parent category:

class Category(Model):
name = TextField()
parent = ForeignKeyField('self', backref='children', null=True)

To list all categories along with their depth and parents, we can use a recursive CTE:

# Define the base case of our recursive CTE. This will be categories that
# have a null parent foreign-key.
Base = Category.alias()
level = Value(1).alias('level')
path = Base.name.alias('path')
base_case = (Base
.select(Base.id, Base.name, Base.parent, level, path)
.where(Base.parent.is_null())
.cte('base', recursive=True))

# Define the recursive terms.
RTerm = Category.alias()
rlevel = (base_case.c.level + 1).alias('level')
rpath = base_case.c.path.concat('->').concat(RTerm.name).alias('path')
recursive = (RTerm
.select(RTerm.id, RTerm.name, RTerm.parent, rlevel, rpath)
.join(base_case, on=(RTerm.parent == base_case.c.id)))

# The recursive CTE is created by taking the base case and UNION ALL with
# the recursive term.
cte = base_case.union_all(recursive)

# We will now query from the CTE to get the categories, their levels, and
# their paths.
query = (cte
.select_from(cte.c.name, cte.c.level, cte.c.path)
.order_by(cte.c.path))

# We can now iterate over a list of all categories and print their names,
# absolute levels, and path from root -> category.
for category in query:
print(category.name, category.level, category.path)

# Example output:
# root, 1, root
# p1, 2, root->p1
# c1-1, 3, root->p1->c1-1
# c1-2, 3, root->p1->c1-2
# p2, 2, root->p2
# c2-1, 3, root->p2->c2-1

Data-Modifying CTE

Peewee supports data-modifying CTE's.

Example of using a data-modifying CTE to move data from one table to an archive table, using a single query:

class Event(Model):
name = CharField()
timestamp = DateTimeField()

class Archive(Model):
name = CharField()
timestamp = DateTimeField()

# Move rows older than 24 hours from the Event table to the Archive.
cte = (Event
.delete()
.where(Event.timestamp < (datetime.now() - timedelta(days=1)))
.returning(Event)
.cte('moved_rows'))

# Create a simple SELECT to get the resulting rows from the CTE.
src = Select((cte,), (cte.c.id, cte.c.name, cte.c.timestamp))

# Insert into the archive table whatever data was returned by the DELETE.
res = (Archive
.insert_from(src, (Archive.id, Archive.name, Archive.timestamp))
.with_cte(cte)
.execute())

The above corresponds to, roughly, the following SQL:

WITH "moved_rows" AS (
DELETE FROM "event" WHERE ("timestamp" < XXXX-XX-XXTXX:XX:XX)
RETURNING "id", "name", "timestamp")
INSERT INTO "archive" ("id", "name", "timestamp")
SELECT "moved_rows"."id", "moved_rows"."name", "moved_rows"."timestamp"
FROM "moved_rows";

For additional examples, refer to the tests in models.py and sql.py :

Foreign Keys and Joins

This section has been moved into its own document: Relationships and Joins .

Query operators

The following types of comparisons are supported by peewee:

Because I ran out of operators to override, there are some additional query operations available as methods:

To combine clauses using logical operators, use:

Here is how you might use some of these query operators:

# Find the user whose username is "charlie".
User.select().where(User.username == 'charlie')

# Find the users whose username is in [charlie, huey, mickey]
User.select().where(User.username.in_(['charlie', 'huey', 'mickey']))

# Find users whose salary is between 50k and 60k (inclusive).
Employee.select().where(Employee.salary.between(50000, 60000))

Employee.select().where(Employee.name.startswith('C'))

Blog.select().where(Blog.title.contains(search_string))

Here is how you might combine expressions. Comparisons can be arbitrarily complex.

NOTE:

Note that the actual comparisons are wrapped in parentheses. Python's operator precedence necessitates that comparisons be wrapped in parentheses.

# Find any users who are active administrations.
User.select().where(
(User.is_admin == True) &
(User.is_active == True))

# Find any users who are either administrators or super-users.
User.select().where(
(User.is_admin == True) |
(User.is_superuser == True))

# Alternatively, use the boolean values directly. Here we query users who
# are admins and NOT superusers.
User.select().where(User.is_admin & ˜User.is_superuser)

# Find any Tweets by users who are not admins (NOT IN).
admins = User.select().where(User.is_admin == True)
non_admin_tweets = Tweet.select().where(Tweet.user.not_in(admins))

# Find any users who are not my friends (strangers).
friends = User.select().where(User.username.in_(['charlie', 'huey', 'mickey']))
strangers = User.select().where(User.id.not_in(friends))

WARNING:

Although you may be tempted to use python's in , and , or , is , and not operators in your query expressions, these will not work. The return value of an in expression is always coerced to a boolean value. Similarly, and , or and not all treat their arguments as boolean values and cannot be overloaded.

So just remember:

For more examples, see the Expressions section.

NOTE:

LIKE and ILIKE with SQLite

Because SQLite's LIKE operation is case-insensitive by default, peewee will use the SQLite GLOB operation for case-sensitive searches. The glob operation uses asterisks for wildcards as opposed to the usual percent-sign. If you are using SQLite and want case-sensitive partial string matching, remember to use asterisks for the wildcard.

Three valued logic

Because of the way SQL handles NULL , there are some special operations available for expressing:

While it would be possible to use the IS NULL and IN operators with the negation operator ( ˜ ), sometimes to get the correct semantics you will need to explicitly use IS NOT NULL and NOT IN .

The simplest way to use IS NULL and IN is to use the operator overloads:

# Get all User objects whose last login is NULL.
User.select().where(User.last_login >> None)

# Get users whose username is in the given list.
usernames = ['charlie', 'huey', 'mickey']
User.select().where(User.username << usernames)

If you don't like operator overloads, you can call the Field methods instead:

# Get all User objects whose last login is NULL.
User.select().where(User.last_login.is_null(True))

# Get users whose username is in the given list.
usernames = ['charlie', 'huey', 'mickey']
User.select().where(User.username.in_(usernames))

To negate the above queries, you can use unary negation, but for the correct semantics you may need to use the special IS NOT and NOT IN operators:

# Get all User objects whose last login is *NOT* NULL.
User.select().where(User.last_login.is_null(False))

# Using unary negation instead.
User.select().where(˜(User.last_login >> None))

# Get users whose username is *NOT* in the given list.
usernames = ['charlie', 'huey', 'mickey']
User.select().where(User.username.not_in(usernames))

# Using unary negation instead.
usernames = ['charlie', 'huey', 'mickey']
User.select().where(˜(User.username << usernames))

Adding user-defined operators

Because I ran out of python operators to overload, there are some missing operators in peewee, for instance modulo . If you find that you need to support an operator that is not in the table above, it is very easy to add your own.

Here is how you might add support for modulo in SQLite:

from peewee import *
from peewee import Expression # The building block for expressions.

def mod(lhs, rhs):
# Note: this works with Sqlite, but some drivers may use string-
# formatting before sending the query to the database, so you may
# need to use '%%' instead here.
return Expression(lhs, '%', rhs)

Now you can use these custom operators to build richer queries:

# Users with even ids.
User.select().where(mod(User.id, 2) == 0)

For more examples check out the source to the playhouse.postgresql_ext module, as it contains numerous operators specific to postgresql's hstore.

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:

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')

# User is active and not a superuser.
(User.is_active & ˜User.is_superuser)

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 occurred 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 occurred 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 .

SQL Functions

SQL functions, like COUNT() or SUM() , can be expressed using the fn() helper:

# Get all users and the number of tweets they've authored. Sort the
# results from most tweets -> fewest tweets.
query = (User
.select(User, fn.COUNT(Tweet.id).alias('tweet_count'))
.join(Tweet, JOIN.LEFT_OUTER)
.group_by(User)
.order_by(fn.COUNT(Tweet.id).desc()))
for user in query:
print('%s -- %s tweets' % (user.username, user.tweet_count))

The fn helper exposes any SQL function as if it were a method. The parameters can be fields, values, subqueries, or even nested functions.

Nesting function calls

Suppose you need to want to get a list of all users whose username begins with a . There are a couple ways to do this, but one method might be to use some SQL functions like LOWER and SUBSTR . To use arbitrary SQL functions, use the special fn() object to construct queries:

# Select the user's id, username and the first letter of their username, lower-cased
first_letter = fn.LOWER(fn.SUBSTR(User.username, 1, 1))
query = User.select(User, first_letter.alias('first_letter'))

# Alternatively we could select only users whose username begins with 'a'
a_users = User.select().where(first_letter == 'a')

>>> for user in a_users:
... print(user.username)

SQL Helper

There are times when you may want to simply pass in some arbitrary sql. You can do this using the special SQL class. One use-case is when referencing an alias:

# We'll query the user table and annotate it with a count of tweets for
# the given user
query = (User
.select(User, fn.Count(Tweet.id).alias('ct'))
.join(Tweet)
.group_by(User))

# Now we will order by the count, which was aliased to "ct"
query = query.order_by(SQL('ct'))

# You could, of course, also write this as:
query = query.order_by(fn.COUNT(Tweet.id))

There are two ways to execute hand-crafted SQL statements with peewee:

Security and SQL Injection

By default peewee will parameterize queries, so any parameters passed in by the user will be escaped. The only exception to this rule is if you are writing a raw SQL query or are passing in a SQL object which may contain untrusted data. To mitigate this, ensure that any user-defined data is passed in as a query parameter and not part of the actual SQL query:

# Bad! DO NOT DO THIS!
query = MyModel.raw('SELECT * FROM my_table WHERE data = %s' % (user_data,))

# Good. `user_data` will be treated as a parameter to the query.
query = MyModel.raw('SELECT * FROM my_table WHERE data = %s', user_data)

# Bad! DO NOT DO THIS!
query = MyModel.select().where(SQL('Some SQL expression %s' % user_data))

# Good. `user_data` will be treated as a parameter.
query = MyModel.select().where(SQL('Some SQL expression %s', user_data))

NOTE:

MySQL and Postgresql use '%s' to denote parameters. SQLite, on the other hand, uses '?' . Be sure to use the character appropriate to your database. You can also find this parameter by checking Database.param .

Relationships and Joins

In this document we'll cover how Peewee handles relationships between models.

Model definitions

We'll use the following model definitions for our examples:

import datetime
from peewee import *

db = SqliteDatabase(':memory:')

class BaseModel(Model):
class Meta:
database = db

class User(BaseModel):
username = TextField()

class Tweet(BaseModel):
content = TextField()
timestamp = DateTimeField(default=datetime.datetime.now)
user = ForeignKeyField(User, backref='tweets')

class Favorite(BaseModel):
user = ForeignKeyField(User, backref='favorites')
tweet = ForeignKeyField(Tweet, backref='favorites')

Peewee uses ForeignKeyField to define foreign-key relationships between models. Every foreign-key field has an implied back-reference, which is exposed as a pre-filtered Select query using the provided backref attribute.

Creating test data

To follow along with the examples, let's populate this database with some test data:

def populate_test_data():
db.create_tables([User, Tweet, Favorite])

data = (
('huey', ('meow', 'hiss', 'purr')),
('mickey', ('woof', 'whine')),
('zaizee', ()))
for username, tweets in data:
user = User.create(username=username)
for tweet in tweets:
Tweet.create(user=user, content=tweet)

# Populate a few favorites for our users, such that:
favorite_data = (
('huey', ['whine']),
('mickey', ['purr']),
('zaizee', ['meow', 'purr']))
for username, favorites in favorite_data:
user = User.get(User.username == username)
for content in favorites:
tweet = Tweet.get(Tweet.content == content)
Favorite.create(user=user, tweet=tweet)

ATTENTION:

In the following examples we will be executing a number of queries. If you are unsure how many queries are being executed, you can add the following code, which will log all queries to the console:

import logging
logger = logging.getLogger('peewee')
logger.addHandler(logging.StreamHandler())
logger.setLevel(logging.DEBUG)

NOTE:

In SQLite, foreign keys are not enabled by default. Most things, including the Peewee foreign-key API, will work fine, but ON DELETE behaviour will be ignored, even if you explicitly specify on_delete in your ForeignKeyField . In conjunction with the default AutoField behaviour (where deleted record IDs can be reused), this can lead to subtle bugs. To avoid problems, I recommend that you enable foreign-key constraints when using SQLite, by setting pragmas={'foreign_keys': 1} when you instantiate SqliteDatabase .

# Ensure foreign-key constraints are enforced.
db = SqliteDatabase('my_app.db', pragmas={'foreign_keys': 1})

Performing simple joins

As an exercise in learning how to perform joins with Peewee, let's write a query to print out all the tweets by "huey". To do this we'll select from the Tweet model and join on the User model, so we can then filter on the User.username field:

>>> query = Tweet.select().join(User).where(User.username == 'huey')
>>> for tweet in query:
... print(tweet.content)
...
meow
hiss
purr

NOTE:

We did not have to explicitly specify the join predicate (the "ON" clause), because Peewee inferred from the models that when we joined from Tweet to User, we were joining on the Tweet.user foreign-key.

The following code is equivalent, but more explicit:

query = (Tweet
.select()
.join(User, on=(Tweet.user == User.id))
.where(User.username == 'huey'))

If we already had a reference to the User object for "huey", we could use the User.tweets back-reference to list all of huey's tweets:

>>> huey = User.get(User.username == 'huey')
>>> for tweet in huey.tweets:
... print(tweet.content)
...
meow
hiss
purr

Taking a closer look at huey.tweets , we can see that it is just a simple pre-filtered SELECT query:

>>> huey.tweets
<peewee.ModelSelect at 0x7f0483931fd0>

>>> huey.tweets.sql()
('SELECT "t1"."id", "t1"."content", "t1"."timestamp", "t1"."user_id"
FROM "tweet" AS "t1" WHERE ("t1"."user_id" = ?)', [1])

Joining multiple tables

Let's take another look at joins by querying the list of users and getting the count of how many tweet's they've authored that were favorited. This will require us to join twice: from user to tweet, and from tweet to favorite. We'll add the additional requirement that users should be included who have not created any tweets, as well as users whose tweets have not been favorited. The query, expressed in SQL, would be:

SELECT user.username, COUNT(favorite.id)
FROM user
LEFT OUTER JOIN tweet ON tweet.user_id = user.id
LEFT OUTER JOIN favorite ON favorite.tweet_id = tweet.id
GROUP BY user.username

NOTE:

In the above query both joins are LEFT OUTER, since a user may not have any tweets or, if they have tweets, none of them may have been favorited.

Peewee has a concept of a join context , meaning that whenever we call the join() method, we are implicitly joining on the previously-joined model (or if this is the first call, the model we are selecting from). Since we are joining straight through, from user to tweet, then from tweet to favorite, we can simply write:

query = (User
.select(User.username, fn.COUNT(Favorite.id).alias('count'))
.join(Tweet, JOIN.LEFT_OUTER) # Joins user -> tweet.
.join(Favorite, JOIN.LEFT_OUTER) # Joins tweet -> favorite.
.group_by(User.username))

Iterating over the results:

>>> for user in query:
... print(user.username, user.count)
...
huey 3
mickey 1
zaizee 0

For a more complicated example involving multiple joins and switching join contexts, let's find all the tweets by Huey and the number of times they've been favorited. To do this we'll need to perform two joins and we'll also use an aggregate function to calculate the favorite count.

Here is how we would write this query in SQL:

SELECT tweet.content, COUNT(favorite.id)
FROM tweet
INNER JOIN user ON tweet.user_id = user.id
LEFT OUTER JOIN favorite ON favorite.tweet_id = tweet.id
WHERE user.username = 'huey'
GROUP BY tweet.content;

NOTE:

We use a LEFT OUTER join from tweet to favorite since a tweet may not have any favorites, yet we still wish to display it's content (along with a count of zero) in the result set.

With Peewee, the resulting Python code looks very similar to what we would write in SQL:

query = (Tweet
.select(Tweet.content, fn.COUNT(Favorite.id).alias('count'))
.join(User) # Join from tweet -> user.
.switch(Tweet) # Move "join context" back to tweet.
.join(Favorite, JOIN.LEFT_OUTER) # Join from tweet -> favorite.
.where(User.username == 'huey')
.group_by(Tweet.content))

Note the call to switch() - that instructs Peewee to set the join context back to Tweet . If we had omitted the explicit call to switch, Peewee would have used User (the last model we joined) as the join context and constructed the join from User to Favorite using the Favorite.user foreign-key, which would have given us incorrect results.

If we wanted to omit the join-context switching we could instead use the join_from() method. The following query is equivalent to the previous one:

query = (Tweet
.select(Tweet.content, fn.COUNT(Favorite.id).alias('count'))
.join_from(Tweet, User) # Join tweet -> user.
.join_from(Tweet, Favorite, JOIN.LEFT_OUTER) # Join tweet -> favorite.
.where(User.username == 'huey')
.group_by(Tweet.content))

We can iterate over the results of the above query to print the tweet's content and the favorite count:

>>> for tweet in query:
... print('%s favorited %d times' % (tweet.content, tweet.count))
...
meow favorited 1 times
hiss favorited 0 times
purr favorited 2 times

Selecting from multiple sources

If we wished to list all the tweets in the database, along with the username of their author, you might try writing this:

>>> for tweet in Tweet.select():
... print(tweet.user.username, '->', tweet.content)
...
huey -> meow
huey -> hiss
huey -> purr
mickey -> woof
mickey -> whine

There is a big problem with the above loop: it executes an additional query for every tweet to look up the tweet.user foreign-key. For our small table the performance penalty isn't obvious, but we would find the delays grew as the number of rows increased.

If you're familiar with SQL, you might remember that it's possible to SELECT from multiple tables, allowing us to get the tweet content and the username in a single query:

SELECT tweet.content, user.username
FROM tweet
INNER JOIN user ON tweet.user_id = user.id;

Peewee makes this quite easy. In fact, we only need to modify our query a little bit. We tell Peewee we wish to select Tweet.content as well as the User.username field, then we include a join from tweet to user. To make it a bit more obvious that it's doing the correct thing, we can ask Peewee to return the rows as dictionaries.

>>> for row in Tweet.select(Tweet.content, User.username).join(User).dicts():
... print(row)
...
{'content': 'meow', 'username': 'huey'}
{'content': 'hiss', 'username': 'huey'}
{'content': 'purr', 'username': 'huey'}
{'content': 'woof', 'username': 'mickey'}
{'content': 'whine', 'username': 'mickey'}

Now we'll leave off the call to ".dicts()" and return the rows as Tweet objects. Notice that Peewee assigns the username value to tweet.user.username -- NOT tweet.username ! Because there is a foreign-key from tweet to user, and we have selected fields from both models, Peewee will reconstruct the model-graph for us:

>>> for tweet in Tweet.select(Tweet.content, User.username).join(User):
... print(tweet.user.username, '->', tweet.content)
...
huey -> meow
huey -> hiss
huey -> purr
mickey -> woof
mickey -> whine

If we wish to, we can control where Peewee puts the joined User instance in the above query, by specifying an attr in the join() method:

>>> query = Tweet.select(Tweet.content, User.username).join(User, attr='author')
>>> for tweet in query:
... print(tweet.author.username, '->', tweet.content)
...
huey -> meow
huey -> hiss
huey -> purr
mickey -> woof
mickey -> whine

Conversely, if we simply wish all attributes we select to be attributes of the Tweet instance, we can add a call to objects() at the end of our query (similar to how we called dicts() ):

>>> for tweet in query.objects():
... print(tweet.username, '->', tweet.content)
...
huey -> meow
(etc)

More complex example

As a more complex example, in this query, we will write a single query that selects all the favorites, along with the user who created the favorite, the tweet that was favorited, and that tweet's author.

In SQL we would write:

SELECT owner.username, tweet.content, author.username AS author
FROM favorite
INNER JOIN user AS owner ON (favorite.user_id = owner.id)
INNER JOIN tweet ON (favorite.tweet_id = tweet.id)
INNER JOIN user AS author ON (tweet.user_id = author.id);

Note that we are selecting from the user table twice - once in the context of the user who created the favorite, and again as the author of the tweet.

With Peewee, we use Model.alias() to alias a model class so it can be referenced twice in a single query:

Owner = User.alias()
query = (Favorite
.select(Favorite, Tweet.content, User.username, Owner.username)
.join(Owner) # Join favorite -> user (owner of favorite).
.switch(Favorite)
.join(Tweet) # Join favorite -> tweet
.join(User)) # Join tweet -> user

We can iterate over the results and access the joined values in the following way. Note how Peewee has resolved the fields from the various models we selected and reconstructed the model graph:

>>> for fav in query:
... print(fav.user.username, 'liked', fav.tweet.content, 'by', fav.tweet.user.username)
...
huey liked whine by mickey
mickey liked purr by huey
zaizee liked meow by huey
zaizee liked purr by huey

Subqueries

Peewee allows you to join on any table-like object, including subqueries or common table expressions (CTEs). To demonstrate joining on a subquery, let's query for all users and their latest tweet.

Here is the SQL:

SELECT tweet.*, user.*
FROM tweet
INNER JOIN (
SELECT latest.user_id, MAX(latest.timestamp) AS max_ts
FROM tweet AS latest
GROUP BY latest.user_id) AS latest_query
ON ((tweet.user_id = latest_query.user_id) AND (tweet.timestamp = latest_query.max_ts))
INNER JOIN user ON (tweet.user_id = user.id)

We'll do this by creating a subquery which selects each user and the timestamp of their latest tweet. Then we can query the tweets table in the outer query and join on the user and timestamp combination from the subquery.

# Define our subquery first. We'll use an alias of the Tweet model, since
# we will be querying from the Tweet model directly in the outer query.
Latest = Tweet.alias()
latest_query = (Latest
.select(Latest.user, fn.MAX(Latest.timestamp).alias('max_ts'))
.group_by(Latest.user)
.alias('latest_query'))

# Our join predicate will ensure that we match tweets based on their
# timestamp *and* user_id.
predicate = ((Tweet.user == latest_query.c.user_id) &
(Tweet.timestamp == latest_query.c.max_ts))

# We put it all together, querying from tweet and joining on the subquery
# using the above predicate.
query = (Tweet
.select(Tweet, User) # Select all columns from tweet and user.
.join(latest_query, on=predicate) # Join tweet -> subquery.
.join_from(Tweet, User)) # Join from tweet -> user.

Iterating over the query, we can see each user and their latest tweet.

>>> for tweet in query:
... print(tweet.user.username, '->', tweet.content)
...
huey -> purr
mickey -> whine

There are a couple things you may not have seen before in the code we used to create the query in this section:

Common-table Expressions

In the previous section we joined on a subquery, but we could just as easily have used a common-table expression (CTE) . We will repeat the same query as before, listing users and their latest tweets, but this time we will do it using a CTE.

Here is the SQL:

WITH latest AS (
SELECT user_id, MAX(timestamp) AS max_ts
FROM tweet
GROUP BY user_id)
SELECT tweet.*, user.*
FROM tweet
INNER JOIN latest
ON ((latest.user_id = tweet.user_id) AND (latest.max_ts = tweet.timestamp))
INNER JOIN user
ON (tweet.user_id = user.id)

This example looks very similar to the previous example with the subquery:

# Define our CTE first. We'll use an alias of the Tweet model, since
# we will be querying from the Tweet model directly in the main query.
Latest = Tweet.alias()
cte = (Latest
.select(Latest.user, fn.MAX(Latest.timestamp).alias('max_ts'))
.group_by(Latest.user)
.cte('latest'))

# Our join predicate will ensure that we match tweets based on their
# timestamp *and* user_id.
predicate = ((Tweet.user == cte.c.user_id) &
(Tweet.timestamp == cte.c.max_ts))

# We put it all together, querying from tweet and joining on the CTE
# using the above predicate.
query = (Tweet
.select(Tweet, User) # Select all columns from tweet and user.
.join(cte, on=predicate) # Join tweet -> CTE.
.join_from(Tweet, User) # Join from tweet -> user.
.with_cte(cte))

We can iterate over the result-set, which consists of the latest tweets for each user:

>>> for tweet in query:
... print(tweet.user.username, '->', tweet.content)
...
huey -> purr
mickey -> whine

NOTE:

For more information about using CTEs, including information on writing recursive CTEs, see the Common Table Expressions section of the "Querying" document.

Multiple foreign-keys to the same Model

When there are multiple foreign keys to the same model, it is good practice to explicitly specify which field you are joining on.

Referring back to the example app's models , consider the Relationship model, which is used to denote when one user follows another. Here is the model definition:

class Relationship(BaseModel):
from_user = ForeignKeyField(User, backref='relationships')
to_user = ForeignKeyField(User, backref='related_to')

class Meta:
indexes = (
# Specify a unique multi-column index on from/to-user.
(('from_user', 'to_user'), True),
)

Since there are two foreign keys to User , we should always specify which field we are using in a join.

For example, to determine which users I am following, I would write:

(User
.select()
.join(Relationship, on=Relationship.to_user)
.where(Relationship.from_user == charlie))

On the other hand, if I wanted to determine which users are following me, I would instead join on the from_user column and filter on the relationship's to_user :

(User
.select()
.join(Relationship, on=Relationship.from_user)
.where(Relationship.to_user == charlie))

Joining on arbitrary fields

If a foreign key does not exist between two tables you can still perform a join, but you must manually specify the join predicate.

In the following example, there is no explicit foreign-key between User and ActivityLog , but there is an implied relationship between the ActivityLog.object_id field and User.id . Rather than joining on a specific Field , we will join using an Expression .

user_log = (User
.select(User, ActivityLog)
.join(ActivityLog, on=(User.id == ActivityLog.object_id), attr='log')
.where(
(ActivityLog.activity_type == 'user_activity') &
(User.username == 'charlie')))

for user in user_log:
print(user.username, user.log.description)

#### Print something like ####
charlie logged in
charlie posted a tweet
charlie retweeted
charlie posted a tweet
charlie logged out

NOTE:

Recall that we can control the attribute Peewee will assign the joined instance to by specifying the attr parameter in the join() method. In the previous example, we used the following join :

join(ActivityLog, on=(User.id == ActivityLog.object_id), attr='log')

Then when iterating over the query, we were able to directly access the joined ActivityLog without incurring an additional query:

for user in user_log:
print(user.username, user.log.description)

Self-joins

Peewee supports constructing queries containing a self-join.

Using model aliases

To join on the same model (table) twice, it is necessary to create a model alias to represent the second instance of the table in a query. Consider the following model:

class Category(Model):
name = CharField()
parent = ForeignKeyField('self', backref='children')

What if we wanted to query all categories whose parent category is Electronics . One way would be to perform a self-join:

Parent = Category.alias()
query = (Category
.select()
.join(Parent, on=(Category.parent == Parent.id))
.where(Parent.name == 'Electronics'))

When performing a join that uses a ModelAlias , it is necessary to specify the join condition using the on keyword argument. In this case we are joining the category with its parent category.

Using subqueries

Another less common approach involves the use of subqueries. Here is another way we might construct a query to get all the categories whose parent category is Electronics using a subquery:

Parent = Category.alias()
join_query = Parent.select().where(Parent.name == 'Electronics')

# Subqueries used as JOINs need to have an alias.
join_query = join_query.alias('jq')

query = (Category
.select()
.join(join_query, on=(Category.parent == join_query.c.id)))

This will generate the following SQL query:

SELECT t1."id", t1."name", t1."parent_id"
FROM "category" AS t1
INNER JOIN (
SELECT t2."id"
FROM "category" AS t2
WHERE (t2."name" = ?)) AS jq ON (t1."parent_id" = "jq"."id")

To access the id value from the subquery, we use the .c magic lookup which will generate the appropriate SQL expression:

Category.parent == join_query.c.id
# Becomes: (t1."parent_id" = "jq"."id")

Implementing Many to Many

Peewee provides a field for representing many-to-many relationships, much like Django does. This feature was added due to many requests from users, but I strongly advocate against using it, since it conflates the idea of a field with a junction table and hidden joins. It's just a nasty hack to provide convenient accessors.

To implement many-to-many correctly with peewee, you will therefore create the intermediary table yourself and query through it:

class Student(Model):
name = CharField()

class Course(Model):
name = CharField()

class StudentCourse(Model):
student = ForeignKeyField(Student)
course = ForeignKeyField(Course)