Models and Fields¶

Models and Fields allow Peewee applications to declare the tables and columns they will use, and issue queries using Python. This document explains how to use Peewee to express database tables and columns.

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

Python construct	Database concept
Model class	Table
Field instance	Column
Model instance	Row

Tip

If you are connecting Peewee to an existing database rather than defining a schema from scratch, the pwiz tool can generate model definitions automatically by introspecting the database.

The following code shows the typical way you will define your database connection and model classes.

import datetime
from peewee import *

db = SqliteDatabase('my_app.db')

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

class User(BaseModel):
    username = CharField(unique=True)

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

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

Three things to note:

Create an instance of a Database.
```
db = SqliteDatabase('my_app.db')
```
The db object will be used to manage the connections to the Sqlite database. In this example we’re using SqliteDatabase, but you could also use one of the other database engines.
Create a base model class which specifies our database.
```
class BaseModel(Model):
    class Meta:
        database = db
```
BaseModel exists only to specify the database setting in its Meta class. Because Meta.database is inheritable, every model that extends BaseModel will automatically use the same database. This pattern avoids repeating the database assignment on every model class.

Model configuration is kept namespaced in a special class called Meta. Meta configuration is passed on to subclasses, so our project’s models will all subclass BaseModel. There are many different attributes you can configure using Model.Meta.
Declare model classes and fields.
```
class User(BaseModel):
    username = CharField(unique=True)
```
Model definition uses the declarative style seen in other popular ORMs. Note that we are extending the BaseModel class so the User model will inherit the database connection.

We have explicitly defined a single username column with a unique constraint. Because we have not specified a primary key, Peewee will automatically add an auto-incrementing integer primary key field named id.

Model Inheritance¶

Model subclasses inherit the Meta configuration of their parent as well as the parent’s fields. Inherited Meta attributes (such as database) are shared; non-inheritable attributes (such as table_name) are re-derived for each subclass.

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

class TimestampedModel(BaseModel):
    """Adds created/updated timestamps to any subclass."""
    created = DateTimeField(default=datetime.datetime.now)
    updated = DateTimeField(default=datetime.datetime.now)

class Article(TimestampedModel):
    title = TextField()
    body = TextField()
    # Article.created and Article.updated are inherited.
    # Article._meta.database is inherited from BaseModel.

Peewee uses a separate table for each concrete model class. There is no notion of inheritance spanning multiple tables. If you subclass a model, both the parent and the child have their own tables.

Fields¶

The Field class is used to describe the mapping of Model attributes to database columns. Each field type has a corresponding SQL storage class (varchar, int, etc). Fields handle conversion between python data types and underlying storage transparently.

When creating a Model class, fields are defined as class attributes:

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

In the above example, no field specifies primary_key=True. As a result, Peewee will create an auto-incrementing integer primary key named id. Peewee uses AutoField to signify an auto-incrementing integer primary key.

Field types¶

Field Type	Sqlite	Postgresql	MySQL
`AutoField`	integer	serial	integer
`BigAutoField`	integer	bigserial	bigint
`IntegerField`	integer	integer	integer
`BigIntegerField`	integer	bigint	bigint
`SmallIntegerField`	integer	smallint	smallint
`IdentityField`	not supported	int identity	not supported
`FloatField`	real	real	real
`DoubleField`	real	double precision	double precision
`DecimalField`	decimal	numeric	numeric
`CharField`	varchar	varchar	varchar
`FixedCharField`	char	char	char
`TextField`	text	text	text
`BlobField`	blob	bytea	blob
`BitField`	integer	bigint	bigint
`BigBitField`	blob	bytea	blob
`UUIDField`	text	uuid	varchar(40)
`BinaryUUIDField`	blob	bytea	varbinary(16)
`DateTimeField`	datetime	timestamp	datetime
`DateField`	date	date	date
`TimeField`	time	time	time
`TimestampField`	integer	integer	integer
`IPField`	integer	bigint	bigint
`BooleanField`	integer	boolean	bool
`BareField`	untyped	not supported	not supported
`ForeignKeyField`	integer	integer	integer

See also

SQLite fields for JSON, Full-Text Search: SQLite
Postgresql fields for Arrays, JSON, Full-Text Search, HStore: Postgresql
MySQL fields for JSON: MySQL and MariaDB
Extra fields (extension): Extra Field Types
Custom fields

Common field parameters¶

All field types accept the following keyword arguments:

Parameter	Default	Description
`null`	`False`	allow null values
`index`	`False`	create an index on this column
`unique`	`False`	create a unique index on this column. See also adding composite indexes.
`column_name`	`None`	explicitly specify the column name in the database.
`default`	`None`	any value or callable to use as a default for uninitialized models
`primary_key`	`False`	primary key for the table
`constraints`	`None`	one or more constraints, e.g. `[Check('price > 0')]`
`sequence`	`None`	sequence name (if backend supports it)
`collation`	`None`	collation to use for ordering the field / index
`unindexed`	`False`	indicate field on virtual table should be unindexed (SQLite-only)
`choices`	`None`	optional iterable containing 2-tuples of `value`, `display`
`help_text`	`None`	string representing any helpful text for this field
`verbose_name`	`None`	string representing the “user-friendly” name of this field
`index_type`	`None`	specify a custom index-type, e.g. for Postgres you might specify a `'BRIN'` or `'GIN'` index.

Special parameters¶

Field type	Special Parameters
`ForeignKeyField`	`model`, `field`, `backref`, `on_delete`, `on_update`, `deferrable` `lazy_load`
`CharField`	`max_length`
`FixedCharField`	`max_length`
`DateTimeField`	`formats`
`DateField`	`formats`
`TimeField`	`formats`
`TimestampField`	`resolution`, `utc`
`DecimalField`	`max_digits`, `decimal_places`, `auto_round`, `rounding`
`BareField`	`adapt`

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:

class Product(Model):
    price = DecimalField(max_digits=8, decimal_places=2,
                         constraints=[Check('price >= 0')])
    added = DateTimeField(constraints=[Default('CURRENT_TIMESTAMP')])
    status = IntegerField(constraints=[Check('status in (0, 1, 2)')])

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)
    created = DateTimeField(default=datetime.datetime.now)

For read_count, Peewee uses the literal value 0. For created, Peewee calls datetime.datetime.now at the moment of instantiation - note that the function itself is passed, not its return value.

Mutable defaults require a factory function. If a default value is a mutable object such as a list or dict, passing it directly means every model instance shares the same object. Wrap it in a function instead:

# Wrong: all instances share one dict.
class Config(BaseModel):
    settings = JSONField(default={})

# Correct: each instance gets a fresh dict.
def default_settings():
    return {}

class Config(BaseModel):
    settings = JSONField(default=default_settings)

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 and Default() to specify the server default:

class Message(Model):
    content = TextField()
    timestamp = DateTimeField(constraints=[Default('CURRENT_TIMESTAMP')])

This produces a DEFAULT CURRENT_TIMESTAMP clause in the CREATE TABLE statement. Peewee’s own default parameter produces no DDL; it only operates during Python-side model instantiation.

A consequence of using server-generated defaults is that newly-inserted models will not automatically retrieve the new value. This requires a separate query to read back the defaults added by the server.

ForeignKeyField¶

ForeignKeyField links a model to another model. It stores the related row’s primary key as an integer column and provides a Python-level descriptor that resolves it to a full model instance on access.

class Tweet(BaseModel):
    user = ForeignKeyField(User, backref='tweets')
    content = TextField()

The backref parameter creates a reverse accessor on the target model. With backref='tweets', every User instance gains a tweets attribute that returns a pre-filtered Select query of that user’s tweets.

ForeignKeyField accepts referential action parameters:

on_delete - action to take when the referenced row is deleted. Common values: 'CASCADE', 'SET NULL', 'RESTRICT'.
on_update - action to take when the referenced row’s primary key changes.
deferrable - defers constraint checking to transaction commit (Postgresql and SQLite only).

Warning

SQLite does not enforce foreign key constraints by default. Enable enforcement by setting the foreign_keys pragma on connection:

db = SqliteDatabase('my_app.db', pragmas={'foreign_keys': 1})

See also

Relationships and Joins covers how foreign keys behave at runtime, including lazy loading, back-references, and avoiding N+1 query problems.

Typically a foreign key will reference the primary key of the related model, but you can specify a particular column by specifying field=.

In Peewee, accessing the value of a ForeignKeyField will return the entire related object:

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

for tweet in tweets:
    print(tweet.user.username, tweet.message)

In the example above the User data was selected efficiently. If we did not select the User, then an additional query would be needed 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. Peewee allows 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 paramater lazy_load which, when disabled, behaves like the "_id" attribute:

class Tweet(Model):
    # 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 back-reference property to be bound to the target model. This property will be named <classname>_set by default, where classname is the lowercase name of the model class. This name can be overridden by specifying 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)

Back-references are just pre-filtered select queries, so we can add additional behavior like order_by():

for message in some_user.inbox.order_by(Message.id):
    # Iterate over all Messages whose to_user is some_user.
    print(message)

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

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

For deeply nested hierarchies, recursive CTEs are more efficient than repeated self-joins. See Common Table Expressions.

See also

Relationships and Joins

Date and Time Fields¶

The three fields devoted to working with dates and times have properties to access date attributes like year, month, hour, etc.

DateField: Properties for: year, month, day
TimeField: Properties for: hour, minute, second
DateTimeField:: Properties for: year, month, day, hour, minute, second

These properties can be used as an expression in a query. Let’s say we have an events table and want to list all the days in the current month which have at least one event:

 # Get the current date.
 today = datetime.date.today()

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

# Group activity by hour of day.
query = (PageView
         .select(
             PageView.timestamp.hour.alias('hour'),
             fn.COUNT(PageView.id).alias('n'))
         .group_by(PageView.timestamp.hour)
         .order_by(PageView.timestamp.hour))

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.

TimestampField stores a datetime as a Unix timestamp integer. The resolution parameter controls sub-second precision (default: seconds); utc=True instructs Peewee to treat stored values as UTC.

BitField and BigBitField¶

The BitField and BigBitField are suitable for storing bitmap data. BitField 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 a single 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

Model Settings¶

Model-specific configuration is placed in a special Metadata class called Meta:

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.

Once the class is defined metadata settings are accessible at 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.Metadata object at 0x7f51a2f03790>

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

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

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

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

There are several options you can specify as Meta attributes. While most options are inheritable, some are table-specific and will not be inherited by subclasses.

Option	Purpose	Inheritable
`database`	Database instance for this model.	Yes
`table_name`	Explicit table name.	No
`table_function`	Callable that returns a table name from the class.	Yes
`indexes`	Tuple of multi-column index definitions.	Yes
`primary_key`	`CompositeKey` or `False`.	Yes
`constraints`	List of table-level constraint expressions.	Yes
`schema`	Database schema name.	Yes
`only_save_dirty`	Only emit changed fields on `save()`.	Yes
`options`	Extra options for `CREATE TABLE` extensions.	Yes
`table_settings`	Strings appended after the closing parenthesis in DDL.	Yes
`temporary`	Mark as a temporary table.	Yes
`legacy_table_names`	Use legacy (non-snake-case) table name generation.	Yes
`depends_on`	Declare a dependency on another table for ordering.	No
`without_rowid`	SQLite `WITHOUT ROWID` tables.	No
`strict_tables`	SQLite strict typing (3.37+).	Yes

Example of inheritable vs non-inheritable settings:

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

Table naming¶

By default Peewee derives the table name from the model class name. The exact transformation depends on Meta.legacy_table_names:

Model class name	legacy (default)	non-legacy
`User`	`user`	`user`
`UserProfile`	`userprofile`	`user_profile`
`APIResponse`	`apiresponse`	`api_response`
`WebHTTPRequest`	`webhttprequest`	`web_http_request`

New projects should opt into non-legacy naming by setting legacy_table_names = False on BaseModel. The legacy default exists only for backwards compatibility with existing deployments.

class BaseModel(Model):
    class Meta:
        database = db
        legacy_table_names = False   # Recommended for new projects.

To override the table name entirely, use table_name:

class UserProfile(BaseModel):
    class Meta:
        table_name = 'acct_user_profile'   # Maps to pre-existing table.

To apply a naming convention programmatically across all models, use table_function:

def prefixed_table_name(model_class):
    return 'myapp_' + model_class.__name__.lower()

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

class User(BaseModel):
    pass   # Table name: "myapp_user"

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 by specifying index=True or unique=True when declaring the Field.

Add a unique index on username and a normal b-tree index on email:

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

To add a user-defined constraint on a column, you can specify it 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=[Default('CURRENT_TIMESTAMP')])

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

Partial and expression indexes¶

Partial indexes, indexes with expressions, and more complex indexes can use the Model.add_index() API:

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

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

Note

SQLite does not support parameterized CREATE INDEX queries. Partial indexes and expression indexes on SQLite must be written using SQL:

Article.add_index(SQL('CREATE INDEX ... WHERE status = 1'))

If the above is cumbersome, you can also pass a SQL instance to Meta.indexes:

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

    class Meta:
        indexes = [
            SQL('CREATE INDEX article_published_lookup ON '
                'article (name, timestamp) WHERE status = 1'),
        ]

Primary Keys¶

Auto-incrementing integer primary key¶

If a model declares no primary key, Peewee automatically adds an auto-incrementing integer field named id:

class Article(BaseModel):
    title = TextField()
    # Peewee implicitly adds: id = AutoField()

To use a different name for the auto-incrementing primary key, declare an AutoField explicitly:

class Article(BaseModel):
    article_id = AutoField()
    title = TextField()

Warning

A common mistake is writing id = IntegerField(primary_key=True) when intending an auto-incrementing primary key. This declares a plain integer column whose value the application must supply - the database will not generate it. Use AutoField for auto-increment behavior.

Non-integer primary keys¶

Any field can serve as the primary key by passing primary_key=True:

class Country(BaseModel):
    code = CharField(max_length=2, primary_key=True)   # e.g. 'US', 'DE'
    name = TextField()

When using a non-auto-incrementing primary key, Peewee cannot distinguish between a new row (needs INSERT) and an existing row (needs UPDATE) by checking whether the primary key is None. On the first save, pass force_insert=True explicitly:

country = Country(code='DE', name='Germany')
country.save(force_insert=True)   # First save: must force INSERT.
country.name = 'Deutschland'
country.save()                    # Subsequent saves: UPDATE as normal.

Model.create() handles this automatically, so it is the simpler option for one-step creation:

country = Country.create(code='DE', name='Germany')

Composite primary keys¶

Use CompositeKey in Meta.primary_key to designate two or more columns as a composite primary key:

class TweetTag(BaseModel):
    tweet = ForeignKeyField(Tweet)
    tag = TextField()

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

Composite primary keys are most appropriate for junction tables in many-to-many relationships. Peewee has limited support for foreign keys to models with composite primary keys; avoid them in models that other models will reference.

Models without a primary key¶

To create a table with no primary key, set primary_key = False:

class LogEntry(BaseModel):
    timestamp = DateTimeField()
    event = TextField()

    class Meta:
        primary_key = False

Note that Model.save() and Model.delete_instance() do not work on keyless models, since both require a primary key to target a specific row. Use Model.insert(), Model.update(), and Model.delete() (the class-level query methods) instead.

Table Constraints¶

Peewee allows arbitrary constraints to Model classes.

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. To do this define a multi-column 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)')]

CHECK constraints can be specified at the table level:

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

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

Creating Tables¶

Once models are defined, create their corresponding tables with Database.create_tables():

db.create_tables([User, Tweet, Favorite])

To create a single table, use Model.create_table():

Tweet.create_table()

See also

Schema Management for documentation on table creation and other schema management tasks.

Advanced Topics¶

The following sections cover scenarios that arise less frequently. New users can skip this section and return to it when the need arises.

Circular foreign key dependencies¶

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

Note

Circular foreign keys 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(BaseModel):
    username = TextField()
    favorite_tweet = DeferredForeignKey('Tweet', null=True)

class Tweet(BaseModel):
    user = ForeignKeyField(User, backref='tweets')
    content = TextField()

db.create_tables([User, Tweet])

# Add the constraint that could not be created at table-creation time.
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.

Field naming conflicts¶

Several names are reserved by Model for built-in methods and attributes (for example save, create, delete, update, get). Declaring a field with one of these names overwrites the method.

When the desired column name conflicts with a model method, supply an alternative attribute name and set column_name explicitly:

class LogEntry(BaseModel):
    timestamp = DateTimeField()
    # "create" and "update" would conflict with Model.create / Model.update.
    created_at = DateTimeField(column_name='create')
    updated_at = DateTimeField(column_name='update')

The database column is still named create and update; the Python attributes are created_at and updated_at.

BareField (SQLite only)¶

BareField declares a column with no type affinity. It is only meaningful with SQLite, which permits untyped columns and virtual table columns.

class FTSEntry(BaseModel):
    content = BareField()

The optional adapt parameter specifies a callable that converts values coming from the database into a Python type:

class RawData(BaseModel):
    value = BareField(adapt=float)

For full-text search virtual tables, use SearchField rather than BareField. See Full-Text Search.

Custom fields¶

A custom field is a subclass of an existing field that overrides the Python-to-database and database-to-Python conversion methods. This is most useful when a database offers a column type that has no built-in Peewee equivalent, or when a standard column type should carry application-specific Python behavior.

The two conversion hooks are:

db_value(self, value) - converts a Python value to the format the database driver expects.
python_value(self, value) - converts a value from the database driver into the desired Python type.

The following example implements a field that stores a pathlib.Path value as a TEXT column:

from pathlib import Path

class PathField(TextField):
    def db_value(self, value):
        return str(value) if value is not None else None

    def python_value(self, value):
        return Path(value) if value is not None else None

class Document(BaseModel):
    path = PathField()

doc = Document.create(path=Path('/var/data/report.pdf'))
assert isinstance(doc.path, Path)

When the database requires a completely new storage type (not a variant of an existing one), set field_type to the type label and register the label with each database that will use it:

class PointField(Field):
    field_type = 'point'   # Custom type label.

    def db_value(self, value):
        if value is not None:
            return f'{value[0]},{value[1]}'

    def python_value(self, value):
        if value is not None:
            x, y = value.split(',')
            return (float(x), float(y))

# Tell Peewee what DDL type to emit for each database.
sq_db  = SqliteDatabase('mydb', field_types={'point': 'text'})

See also

Field API reference.

Models and Fields¶

Model Inheritance¶

Fields¶

Field types¶

Common field parameters¶

Special parameters¶

Default field values¶

ForeignKeyField¶

ForeignKeyField Back-references¶

Self-referential foreign keys¶

Date and Time Fields¶

BitField and BigBitField¶

Model Settings¶

Table naming¶

Indexes and Constraints¶

Single-column indexes and constraints¶

Multi-column indexes¶

Partial and expression indexes¶

Primary Keys¶

Auto-incrementing integer primary key¶

Non-integer primary keys¶

Composite primary keys¶

Models without a primary key¶

Table Constraints¶

Creating Tables¶

Advanced Topics¶

Circular foreign key dependencies¶

Field naming conflicts¶

BareField (SQLite only)¶

Custom fields¶

peewee

Navigation

Related Topics