Why?¶

peewee began when I was working on a small app in flask and found myself writing lots of queries and wanting a very simple abstraction on top of the sql. I had so much fun working on it that I kept adding features. My goal has always been, though, to keep the implementation incredibly simple. I’ve made a couple dives into django’s orm but have never come away with a deep understanding of its implementation. peewee is small enough that its my hope anyone with an interest in orms will be able to understand the code without too much trouble.

Installing with git¶

You can pip install the git clone:

pip install -e git+https://github.com/coleifer/peewee.git

If you don’t want to use pip:

git clone https://github.com/coleifer/peewee.git
cd peewee
python setup.py install

You can test your installation by running the test suite.

python setup.py test

Feel free to check out the Example app which ships with the project.

Goals for the new API¶

• consistent: there is one way of doing things
• expressive: things can be done that I never thought of

Changes from version 1.0¶

The biggest changes between 1.0 and 2.0 are in the syntax used for constructing queries. The first iteration of peewee I threw up on github was about 600 lines. I was passing around strings and dictionaries and as time went on and I added features, those strings turned into tuples and objects. This meant, though, that I needed code to handle all the possible ways of expressing something. Look at the code for parse_select.

I learned a valuable lesson: keep data in datastructures until the absolute last second.

With the benefit of hindsight and experience, I decided to rewrite and unify the API a bit. The result is a tradeoff. The newer syntax may be a bit more verbose at times, but at least it will be consistent.

Since seeing is believing, I will show some side-by-side comparisons. Let’s pretend we’re using the models from the cookbook, good ol’ user and tweet:

class User(Model):
    username = CharField()

class Tweet(Model):
    user = ForeignKeyField(User, related_name='tweets')
    message = TextField()
    created_date = DateTimeField(default=datetime.datetime.now)
    is_published = BooleanField(default=True)

Get me a list of all tweets by a user named “charlie”:

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

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

Get me a list of tweets ordered by the authors username, then newest to oldest:

# 1.0 -- this is one where there are like 10 ways to express it
Tweet.select().join(User).order_by('username', (Tweet, 'created_date', 'desc'))

# 2.0
Tweet.select().join(User).order_by(User.username, Tweet.created_date.desc())

Get me a list of tweets created by users named “charlie” or “peewee herman”, and which were created in the last week.

last_week = datetime.datetime.now() - datetime.timedelta(days=7)

# 1.0
Tweet.select().where(created_date__gt=last_week).join(User).where(
    Q(username='charlie') | Q(username='peewee herman')
)

# 2.0
Tweet.select().join(User).where((Tweet.created_date > last_week) & (
    (User.username == 'charlie') | (User.username == 'peewee herman')
))

Get me a list of users and when they last tweeted (if ever):

# 1.0
User.select({
    User: ['*'],
    Tweet: [Max('created_date', 'last_date')]
}).join(Tweet, 'LEFT OUTER').group_by(User)

# 2.0
User.select(
    User, fn.Max(Tweet.created_date).alias('last_date')
).join(Tweet, JOIN_LEFT_OUTER).group_by(User)

Let’s do an atomic update on a counter model (you’ll have to use your imagination):

# 1.0
Counter.update(count=F('count') + 1).where(url=request.url)

# 2.0
Counter.update(count=Counter.count + 1).where(Counter.url == request.url)

Let’s find all the users whose username starts with ‘a’ or ‘A’:

# 1.0
User.select().where(R('LOWER(SUBSTR(username, 1, 1)) = %s', 'a'))

# 2.0
User.select().where(fn.Lower(fn.Substr(User.username, 1, 1)) == 'a')

I hope a couple things jump out at you from these examples. What I see is that the 1.0 API is sometimes a bit less verbose, but it relies on strings in many places (which may be fields, aliases, selections, join types, functions, etc). In the where clause stuff gets crazy as there are args being combined with bitwise operators (“Q” expressions) and also kwargs being used with django-style “double-underscore” lookups. The crazy thing is, there are so many different ways I could have expressed some of the above queries using peewee 1.0 that I had a hard time deciding which to even write.

The 2.0 API is hopefully more consistent. Selections, groupings, functions, joins and orderings all pretty much conform to the same API. Likewise, where and having clauses are handled the same way (in 1.0 the having clause is simply a raw string). The new fn object actually is a wrapper – whatever appears to the right of the dot (i.e. fn.*Lower*) – is treated as a function that can take any arbitrary parameters.

If you’re feeling froggy and want to get coding, you might want to check out:

• the cookbook, which contains many practical examples
• the example app documentation, which shows how to build a simple twitter-like site
• using “fn”
• the querying docs, which contain an in-depth overview of the query apis

Changes in fields and columns¶

Well, for one, columns are gone. They were a shim that I used to hack in non-integer primary keys. I always thought the field SQL generation was one of the grosser parts of the module and even worse was the back-and-forth that happened between the field and column classes. So, columns are gone - its just fields - and they’re hopefully a bit smaller and saner. I also cleaned up the primary key business. Basically it works like this:

• if you don’t specify a primary key, one will be created named “id”
• if you do specify a primary key and it is a PrimaryKeyField (or subclass), it will be an automatically incrementing integer
• if you specify a primary key and it is anything else peewee assumes you are in control and will stay out of the way.

The API for specifying a non-auto-incrementing primary key changed:

# 1.0
class OldSchool(Model):
    uuid = PrimaryKeyField(column_class=VarCharColumn)

# 2.0
class NewSchool(Model):
    uuid = CharField(primary_key=True)

The kwargs for the Field constructor changed slightly, the biggest probably being that db_index was renamed to index.

Changes in database and adapter¶

In peewee 1.0 there were two classes that controlled access to the database – the Database subclass and an Adapter. The adapter’s job was to say what features a database backend provided, what operations were valid, what column types were supported, and how to open a connection. The database was a bit higher-level and its main job was to execute queries and provide metadata about the database, like lists of tables, last insert id, etc.

I chose to consolidate these two classes, since inevitably they always went in pairs (e.g. SqliteDatabase/SqliteAdapter). The database class now encapsulates all this functionality.

How the SQL gets made¶

The first thing I started with is the QueryCompiler and the data structures it uses. You can see it start to take shape in my first commit. It takes the data structures from peewee and spits out SQL. It works recursively and knows about a few types of expressions:

• the query tree
• comparison statements like ‘==’, ‘IN’, ‘LIKE’ which comprise the leaves of the tree
• expressions like addition, substraction, bitwise operations
• sql functions like substr and lower
• aggregate functions like count and max
• columns, which may be selected, joined on, grouped by, ordered by, used as parameters for functions and aggregates, etc.
• python objects to use as query parameters

At the heart of it is the Expr object, which is for “expression”. It can be anything that can validly be translated into part of a SQL query.

Expressions can be nested, giving way to interesting possibilities like the following example I love which selects users whose username starts with “a”:

User.select().where(fn.Substr(fn.Lower(User.username, 1, 1)) == 'a')

The “where” clause now contains a tree with one leaf. The leaf represents the nested function expression on the left-hand-side and the scalar value ‘a’ on the right hand side. Peewee will recursively evaluate the expressions on either side of the operation and generate the correct SQL.

Another aspect is that Field objects are also expressions, which makes it possible to write things like:

Employee.select().where(Employee.salary < (Employee.tenure * 1000) + 40000)

If you’re interested in looking, the QueryCompiler.parse_expr method is where the bulk of the code lives.

Database and Connection Recipes¶

>>> database = SqliteDatabase('stats.db')
>>> database.connect()

class MyModel(Model):
    some_field = CharField()

    class Meta:
        database = database

>>> custom_db = SqliteDatabase('custom.db')

>>> class CustomModel(Model):
...     whatev = CharField()
...
...     class Meta:
...         database = custom_db
...

>>> custom_db.connect()
>>> CustomModel.create_table()

custom_db = SqliteDatabase('custom.db')

class CustomModel(Model):
    class Meta:
        database = custom_db

class User(CustomModel):
    username = CharField()

class Tweet(CustomModel):
    # etc, etc

psql_db = PostgresqlDatabase('my_database', user='code')


class PostgresqlModel(Model):
    """A base model that will use our Postgresql database"""
    class Meta:
        database = psql_db

class User(PostgresqlModel):
    username = CharField()
    # etc, etc

mysql_db = MySQLDatabase('my_database', user='code')


class MySQLModel(Model):
    """A base model that will use our MySQL database"""
    class Meta:
        database = mysql_db

class User(MySQLModel):
    username = CharField()
    # etc, etc


# when you're ready to start querying, remember to connect
mysql_db.connect()

concurrent_db = SqliteDatabase('stats.db', threadlocals=True)

native_concurrent_db = SqliteDatabase('stats.db', check_same_thread=False)

deferred_db = SqliteDatabase(None)

class SomeModel(Model):
    class Meta:
        database = deferred_db

>>> deferred_db.connect()
Exception: Error, database not properly initialized before opening connection

database_name = raw_input('What is the name of the db? ')
deferred_db.init(database_name)

Creating, Reading, Updating and Deleting¶

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

>>> user = User()
>>> user.username = 'Charlie'
>>> user.save()
>>> user.id
1

>>> user.save()
>>> user.id
1
>>> user.save()
>>> user.id
1

>>> update_query = Tweet.update(is_published=True).where(Tweet.creation_date < datetime.today())
>>> update_query.execute()
4 # <--- number of rows updated

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

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

>>> delete_query = Tweet.delete().where(Tweet.pub_date < one_year_ago)
>>> delete_query.execute()
7 # <--- number of rows deleted

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

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

>>> User.get(User.username == 'Charlie')
<__main__.Blog 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 user in User.select():
...     print user.username
...
Charlie
Peewee Herman

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

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

>>> user = User.get(User.username == 'Charlie')
>>> for tweet in Tweet.select().where(Tweet.user == user, Tweet.is_published == True):
...     print '%s: %s (%s)' % (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

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

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

# the "<<" operator signifies an "IN" query
Tweet.select().where(
    Tweet.user << User.select().where(fn.Lower(fn.Substr(User.username, 1, 1)) == 'a')
)

>>> for tweet in Tweet.filter(user__username='Charlie'):
...     print tweet.message
hello world
this is fun
look at this picture of my food

>>> User.filter(DQ(username='Charlie') | DQ(username='Peewee Herman'))

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

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

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

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

# let's assume we've got 1M stat objects to dump to csv
stats_qr = Stat.select().execute()

# our imaginary serializer class
serializer = CSVSerializer()

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

stats_query = Stat.select().naive() # note we are calling "naive()"
stats_qr = stats_query.execute()

for stat in stats_qr.iterator():
    serializer.serialize_object(stat)

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

query = User.select().annotate(Tweet)

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

query = User.select().join(Tweet, JOIN_LEFT_OUTER).annotate(Tweet)

query = User.select().annotate(Tweet, fn.Max(Tweet.created_date).alias('latest'))

class Photo(Model):
    image = CharField()

class Tag(Model):
    name = CharField()

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

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

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

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

# select the users' id, username and the first letter of their username, lower-cased
query = User.select(User, fn.Lower(fn.Substr(User.username, 1, 1)).alias('first_letter'))

# alternatively we could select only users whose username begins with 'a'
a_users = User.select().where(fn.Lower(fn.Substr(User.username, 1, 1)) == 'a')

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

# 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(R('ct'))

Working with transactions¶

db = SqliteDatabase(':memory:')

with db.transaction():
    user.delete_instance(recursive=True) # delete user and associated tweets

db = SqliteDatabase(':memory:')

@db.commit_on_success
def delete_user(user):
    user.delete_instance(recursive=True)

db.set_autocommit(False)
try:
    user.delete_instance(recursive=True)
except:
    db.rollback()
    raise
else:
    db.commit()
finally:
    db.set_autocommit(True)

db = SqliteDatabase(':memory:', autocommit=False)

User.create(username='somebody')
db.commit()

Non-integer Primary Keys and other Tricks¶

from peewee import *

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


inst = UUIDModel(id=str(uuid.uuid4()))
inst.save() # <-- WRONG!!  this will try to do an update

inst.save(force_insert=True) # <-- CORRECT

# to update the instance after it has been saved once
inst.save()

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

User._meta.auto_increment = False # turn off auto incrementing IDs
with db.transaction():
    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

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

Introspecting databases¶

If you’d like to generate some models for an existing database, you can try out the database introspection tool “pwiz” that comes with peewee.

Usage:

python pwiz.py my_postgresql_database

It works with postgresql, mysql and sqlite:

python pwiz.py test.db --engine=sqlite

pwiz will generate code for:

• database connection object
• a base model class to use this connection
• models that were introspected from the database tables

The generated code is written to stdout.

Schema migrations¶

Currently peewee does not have support for automatic schema migrations.

Running the example¶

After ensuring that flask, jinja2, werkzeug and sqlite3 are all installed, switch to the example directory and execute the run_example.py script:

python run_example.py

Diving into the code¶

Models¶

In the spirit of the ur-python framework, django, peewee uses declarative model definitions. If you’re not familiar with django, the idea is that you declare a class with some members which map directly to the database schema. For the twitter clone, there are just three models:

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

Relationship:

this is a “utility model” that contains two foreign-keys to the User model and represents “following”.

Message:

analagous to a tweet. this model stores the text content of the message, when it was created, and who posted it (foreign key to User).

If you like UML, this is basically what it looks like:

Here is what the code looks like:

# create a peewee database instance -- our models will use this database to
# persist information
database = SqliteDatabase(DATABASE)

# model definitions -- the standard "pattern" is to define a base model class
# that specifies which database to use.  then, any subclasses will automatically
# use the correct storage. for more information, see:
# http://charlesleifer.com/docs/peewee/peewee/models.html#model-api-smells-like-django
class BaseModel(Model):
    class Meta:
        database = database

# the user model specifies its fields (or columns) declaratively, like django
class User(BaseModel):
    username = CharField()
    password = CharField()
    email = CharField()
    join_date = DateTimeField()

    class Meta:
        order_by = ('username',)

    # it often makes sense to put convenience methods on model instances, for
    # example, "give me all the users this user is following":
    def following(self):
        # query other users through the "relationship" table
        return User.select().join(
            Relationship, on=Relationship.to_user,
        ).where(Relationship.from_user == self)

    def followers(self):
        return User.select().join(
            Relationship, on=Relationship.from_user,
        ).where(Relationship.to_user == self)

    def is_following(self, user):
        return Relationship.select().where(
            (Relationship.from_user == self) &
            (Relationship.to_user == user)
        ).count() > 0

    def gravatar_url(self, size=80):
        return 'http://www.gravatar.com/avatar/%s?d=identicon&s=%d' % \
            (md5(self.email.strip().lower().encode('utf-8')).hexdigest(), size)


# this model contains two foreign keys to user -- it essentially allows us to
# model a "many-to-many" relationship between users.  by querying and joining
# on different columns we can expose who a user is "related to" and who is
# "related to" a given user
class Relationship(BaseModel):
    from_user = ForeignKeyField(User, related_name='relationships')
    to_user = ForeignKeyField(User, related_name='related_to')


# a dead simple one-to-many relationship: one user has 0..n messages, exposed by
# the foreign key.  because we didn't specify, a users messages will be accessible
# as a special attribute, User.message_set
class Message(BaseModel):
    user = ForeignKeyField(User)
    content = TextField()
    pub_date = DateTimeField()

    class Meta:
        order_by = ('-pub_date',)

peewee supports a handful of field types which map to different column types in sqlite. Conversion between python and the database is handled transparently, including the proper handling of None/NULL.

Note

You might have noticed that we created a BaseModel which sets the database, and then all the other models extend the BaseModel. This is a good way to make sure all your models are talking to the right database.

>>> from app import *
>>> create_tables()

def create_tables():
    User.create_table()
    Relationship.create_table()
    Message.create_table()

# config
DATABASE = 'tweepee.db'

# ... more config here, omitted

database = SqliteDatabase(DATABASE) # tell our models to use "tweepee.db"

@app.before_request
def before_request():
    g.db = database
    g.db.connect()

@app.after_request
def after_request(response):
    g.db.close()
    return response

def following(self):
    # query other users through the "relationship" table
    return User.select().join(
        Relationship, on=Relationship.to_user,
    ).where(Relationship.from_user == self)

def followers(self):
    return User.select().join(
        Relationship, on=Relationship.from_user,
    ).where(Relationship.to_user == self)

# following:
SELECT t1."id", t1."username", t1."password", t1."email", t1."join_date"
FROM "user" AS t1
INNER JOIN "relationship" AS t2
    ON t1."id" = t2."to_user_id"  # <-- joining on to_user_id
WHERE t2."from_user_id" = ?
ORDER BY t1."username" ASC

# followers
SELECT t1."id", t1."username", t1."password", t1."email", t1."join_date"
FROM user AS t1
INNER JOIN relationship AS t2
    ON t1."id" = t2."from_user_id"  # <-- joining on from_user_id
WHERE t2."to_user_id" = ?
ORDER BY t1."username" ASC

Relationship.get_or_create(
    from_user=session['user'], # <-- the logged-in user
    to_user=user, # <-- the user they want to follow
)

# python code
messages = Message.select().where(
    Message.user << user.following()
)

SELECT t1."id", t1."user_id", t1."content", t1."pub_date"
FROM "message" AS t1
WHERE t1."user_id" IN (
    SELECT t2."id"
    FROM "user" AS t2
    INNER JOIN "relationship" AS t3
        ON t2."id" = t3."to_user_id"
    WHERE t3."from_user_id" = ?
    ORDER BY t1."username" ASC
)

Creating tables¶

In order to start using these models, its necessary to open a connection to the database and create the tables first:

# connect to our database
db.connect()

# create the tables
User.create_table()
Tweet.create_table()

Model instances¶

Assuming you’ve created the tables and connected to the database, you are now free to create models and execute queries.

Creating models in the interactive interpreter is a snap.

1. Use the Model.create() classmethod:

   >>> user = User.create(username='charlie')
   >>> tweet = Tweet.create(
   ...     message='http://www.youtube.com/watch?v=xdhLQCYQ-nQ',
   ...     user=user
   ... )
   
   >>> tweet.user.username
   'charlie'
   

2. Build up the instance programmatically:

   >>> user = User()
   >>> user.username = 'charlie'
   >>> user.save()
   

>>> tweet.user.username
'charlie'

>>> for tweet in user.tweets:
...     print tweet.message
...
http://www.youtube.com/watch?v=xdhLQCYQ-nQ

>>> user.tweets
<peewee.SelectQuery object at 0x151f510>

Model options¶

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

from peewee import *

custom_db = SqliteDatabase('custom.db')

class CustomModel(Model):
    class Meta:
        database = custom_db

This instructs peewee that whenever a query is executed on CustomModel to use the custom database.

There are several options you can specify as Meta attributes:

• database: specifies a Database instance to use with this model
• db_table: the name of the database table this model maps to
• indexes: a list of fields to index
• order_by: a sequence of columns to use as the default ordering for this model

Specifying indexes:

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

Example of ordering:

class Tweet(Model):
    message = TextField()
    created = DateTimeField()

    class Meta:
        # order by created date descending
        ordering = ('-created',)

my_db = PostgresqlDatabase('my_db')

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

class SomeModel(BaseModel):
    field1 = CharField()

    class Meta:
        ordering = ('field1',)
        # no need to define database again since it will be inherited from
        # the BaseModel

Model methods¶

class Model¶

save([force_insert=False])¶

Save the given instance, creating or updating depending on whether it has a primary key. If force_insert=True an INSERT will be issued regardless of whether or not the primary key exists.

example:

>>> some_obj.title = 'new title' # <-- does not touch the database
>>> some_obj.save() # <-- change is persisted to the db

classmethod create(**attributes)¶

Parameters:	attributes – key/value pairs of model attributes

Create an instance of the Model with the given attributes set.

example:

>>> user = User.create(username='admin', password='test')

delete_instance([recursive=False[, delete_nullable=False]])¶

Parameters:	recursive – Delete this instance and anything that depends on it, optionally updating those that have nullable dependencies delete_nullable – If doing a recursive delete, delete all dependent objects regardless of whether it could be updated to NULL

Delete the given instance. Any foreign keys set to cascade on delete will be deleted automatically. For more programmatic control, you can call with recursive=True, which will delete any non-nullable related models (those that are nullable will be set to NULL). If you wish to delete all dependencies regardless of whether they are nullable, set delete_nullable=True.

example:

>>> some_obj.delete_instance() # <-- it is gone forever

classmethod get(*args, **kwargs)¶

Parameters:	args – a list of query expressions, e.g. Usre.username == 'foo' kwargs – a mapping of column + lookup to value, e.g. “age__gt=55”
Return type:	Model instance or raises DoesNotExist exception

Get a single row from the database that matches the given query. Raises a <model-class>.DoesNotExist if no rows are returned:

>>> user = User.get(User.username == username, User.password == password)

This method is also expose via the SelectQuery, though it takes no parameters:

>>> active = User.select().where(User.active == True)
>>> try:
...     users = active.where(User.username == username, User.password == password)
...     user = users.get()
... except User.DoesNotExist:
...     user = None

Note

the “kwargs” style syntax is provided for compatibility with version 1.0. The expression-style syntax is preferable.

classmethod get_or_create(**attributes)¶

Parameters:	attributes – key/value pairs of model attributes
Return type:	a Model instance

Get the instance with the given attributes set. If the instance does not exist it will be created.

example:

>>> CachedObj.get_or_create(key=key, val=some_val)

classmethod select(*selection)¶

Parameters:	selection – a list of model classes, field instances, functions or expressions
Return type:	a SelectQuery for the given Model

example:

>>> User.select().where(User.active == True).order_by(User.username)
>>> Tweet.select(Tweet, User).join(User).order_by(Tweet.created_date.desc())

classmethod update(**query)¶

Return type:	an UpdateQuery for the given Model

example:

>>> q = User.update(active=False).where(User.registration_expired == True)
>>> q.execute() # <-- execute it

classmethod delete()¶

Return type:	a DeleteQuery for the given Model

example:

>>> q = User.delete().where(User.active == False)
>>> q.execute() # <-- execute it

Warning

Assume you have a model instance – calling model_instance.delete() does not delete it.

classmethod insert(**query)¶

Return type:	an InsertQuery for the given Model

example:

>>> q = User.insert(username='admin', active=True, registration_expired=False)
>>> q.execute()
1

classmethod raw(sql, *params)¶

Return type:	a RawQuery for the given Model

example:

>>> q = User.raw('select id, username from users')
>>> for user in q:
...     print user.id, user.username

classmethod filter(*args, **kwargs)¶

Parameters:	args – a list of DQ or Node objects kwargs – a mapping of column + lookup to value, e.g. “age__gt=55”
Return type:	SelectQuery with appropriate WHERE clauses

Provides a django-like syntax for building a query. The key difference between filter() and SelectQuery.where() is that filter() supports traversing joins using django’s “double-underscore” syntax:

>>> sq = Entry.filter(blog__title='Some Blog')

This method is chainable:

>>> base_q = User.filter(active=True)
>>> some_user = base_q.filter(username='charlie')

Note

this method is provided for compatibility with peewee 1.0

classmethod create_table([fail_silently=False])¶

Parameters:	fail_silently – If set to True, the method will check for the existence of the table before attempting to create.

Create the table for the given model.

example:

>>> database.connect()
>>> SomeModel.create_table() # <-- creates the table for SomeModel

classmethod drop_table([fail_silently=False])¶

Parameters:	fail_silently – If set to True, the query will check for the existence of the table before attempting to remove.

Drop the table for the given model.

Note

Cascading deletes are not handled by this method, nor is the removal of any constraints.

classmethod table_exists()¶

Return type:	Boolean whether the table for this model exists in the database

Field types table¶

Parameters accepted by all field types and their default values:

• null = False – boolean indicating whether null values are allowed to be stored
• index = False – boolean indicating whether to create an index on this column
• unique = False – boolean indicating whether to create a unique index on this column
• verbose_name = None – string representing the “user-friendly” name of this field
• help_text = None – string representing any helpful text for this field
• db_column = None – string representing the underlying column to use if different, useful for legacy databases
• default = None – any value to use as a default for uninitialized models
• choices = None – an optional iterable containing 2-tuples of value, display
• primary_key = False – whether this field is the primary key for the table
• sequence = None – sequence to populate field (if backend supports it)

Self-referential Foreign Keys¶

Since the class is not available at the time the field is declared, when creating a self-referential foreign key pass in ‘self’ as the “to” relation:

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

Implementing Many to Many¶

Peewee does not provide a “field” for many to many relationships the way that django does – this is because the “field” really is hiding an intermediary table. To implement many-to-many 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)

To query, let’s say we want to find students who are enrolled in math class:

for student in Student.select().join(StudentCourse).join(Course).where(Course.name == 'math'):
    print student.name

To query what classes a given student is enrolled in:

for course in Course.select().join(StudentCourse).join(Student).where(Student.name == 'da vinci'):
    print course.name

To efficiently iterate over a many-to-many relation, i.e., list all students and their respective courses, we will query the “through” model StudentCourse and “precompute” the Student and Course:

query = StudentCourse.select(
    StudentCourse, Student, Course)
).join(Course).switch(StudentCourse).join(Student)

To print a list of students and their courses you might do the following:

last = None
for student_course in query:
    student = student_course.student
    if student != last:
        last = student
        print 'Student: %s' % student.name
    print '    - %s' % student_course.course.name

Since we selected all fields from Student and Course in the select clause of the query, these foreign key traversals are “free” and we’ve done the whole iteration with just 1 query.

Non-integer Primary Keys¶

First of all, let me say that I do not think using non-integer primary keys is a good idea. The cost in storage is higher, the index lookups will be slower, and foreign key joins will be more expensive. That being said, here is how you can use non-integer pks in peewee.

from peewee import Model, PrimaryKeyField, VarCharColumn

class UUIDModel(Model):
    # explicitly declare a primary key field, and specify the class to use
    id = CharField(primary_key=True)

Auto-increment IDs are, as their name says, automatically generated for you when you insert a new row into the database. The way peewee determines whether to do an INSERT versus an UPDATE comes down to checking whether the primary key value is None. If None, it will do an insert, otherwise it does an update on the existing 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:

inst = UUIDModel(id=str(uuid.uuid4()))
inst.save() # <-- WRONG!!  this will try to do an update

inst.save(force_insert=True) # <-- CORRECT

# to update the instance after it has been saved once
inst.save()

Field class API¶

class Field¶

The base class from which all other field types extend.

db_field = '<some field type>'

Attribute used to map this field to a column type, e.g. “string” or “datetime”

template = '%(column_type)s'

A template for generating the SQL for this field

__init__(null=False, index=False, unique=False, verbose_name=None, help_text=None, db_column=None, default=None, choices=None, *args, **kwargs)¶

Parameters:

null – this column can accept None or NULL values index – create an index for this column when creating the table unique – create a unique index for this column when creating the table verbose_name – specify a “verbose name” for this field, useful for metadata purposes help_text – specify some instruction text for the usage/meaning of this field db_column – column class to use for underlying storage default – a value to use as an uninitialized default choices – an iterable of 2-tuples mapping value to display primary_key (boolean) – whether to use this as the primary key for the table sequence – name of sequence (if backend supports it)

db_value(value)¶

Parameters:	value – python data type to prep for storage in the database
Return type:	converted python datatype

python_value(value)¶

Parameters:	value – data coming from the backend storage
Return type:	python data type

coerce(value)¶

This method is a shorthand that is used, by default, by both db_value and python_value. You can usually get away with just implementing this.

Parameters:	value – arbitrary data from app or backend
Return type:	python data type

field_attributes()¶

This method is responsible for return a dictionary containing the default field attributes for the column, e.g. {'max_length': 255}

Return type:	a python dictionary

class_prepared()¶

Simple hook for Field classes to indicate when the Model class the field exists on has been created.

class CharField¶

Stores: small strings (0-255 bytes)

class TextField¶

Stores: arbitrarily large strings

class DateTimeField¶

Stores: python datetime.datetime instances

Accepts a special parameter formats, which contains a list of formats the datetime can be encoded with. The default behavior is:

'%Y-%m-%d %H:%M:%S.%f' # year-month-day hour-minute-second.microsecond
'%Y-%m-%d %H:%M:%S' # year-month-day hour-minute-second
'%Y-%m-%d' # year-month-day

Note

If the incoming value does not match a format, it will be returned as-is

class DateField¶

Stores: python datetime.date instances

Accepts a special parameter formats, which contains a list of formats the date can be encoded with. The default behavior is:

'%Y-%m-%d' # year-month-day
'%Y-%m-%d %H:%M:%S' # year-month-day hour-minute-second
'%Y-%m-%d %H:%M:%S.%f' # year-month-day hour-minute-second.microsecond

Note

If the incoming value does not match a format, it will be returned as-is

class TimeField¶

Stores: python datetime.time instances

Accepts a special parameter formats, which contains a list of formats the time can be encoded with. The default behavior is:

'%H:%M:%S.%f' # hour:minute:second.microsecond
'%H:%M:%S' # hour:minute:second
'%H:%M' # hour:minute
'%Y-%m-%d %H:%M:%S.%f' # year-month-day hour-minute-second.microsecond
'%Y-%m-%d %H:%M:%S' # year-month-day hour-minute-second

Note

If the incoming value does not match a format, it will be returned as-is

class IntegerField¶

Stores: integers

class BooleanField¶

Stores: True / False

class FloatField¶

Stores: floating-point numbers

class DecimalField¶

Stores: decimal numbers

class PrimaryKeyField¶

Stores: auto-incrementing integer fields suitable for use as primary key.

class ForeignKeyField¶

Stores: relationship to another model

__init__(to[, related_name=None[, ...]])¶

Parameters:	rel_model – related Model class or the string ‘self’ if declaring a self-referential foreign key related_name – attribute to expose on related model

class User(Model):
    name = CharField()

class Tweet(Model):
    user = ForeignKeyField(User, related_name='tweets')
    content = TextField()

# "user" attribute
>>> some_tweet.user
<User: charlie>

# "tweets" related name attribute
>>> for tweet in charlie.tweets:
...     print tweet.content
Some tweet
Another tweet
Yet another tweet

Constructing queries¶

Queries in peewee are constructed one piece at a time.

The “pieces” of a peewee query are generally representative of clauses you might find in a SQL query. Most methods are chainable, so you build your query up one clause at a time. This way, rather complex queries are possible.

Here is a barebones select query:

>>> user_q = User.select() # <-- query is not executed
>>> user_q
<peewee.SelectQuery object at 0x7f6b0810c610>

>>> [u.username for u in user_q] # <-- query is evaluated here
[u'admin', u'staff', u'editor']

We can build up the query by adding some clauses to it:

>>> user_q = user_q.where(User.username << ['admin', 'editor'])
>>> user_q = user_q.order_by(User.username.desc())
>>> [u.username for u in user_q] # <-- query is re-evaluated here
[u'editor', u'admin']

Where clause¶

All queries except InsertQuery support the where() method. If you are familiar with Django’s ORM, it is analagous to the filter() method.

>>> User.select().where(User.is_staff == True)

>>> User.select().join(Blog).where(User.is_staff == True, Blog.status == LIVE)

>>> Blog.select().join(User).where(User.is_staff == True, Blog.status == LIVE)

>>> staff = User.select().where(User.is_staff == true)
>>> Blog.select().where(Blog.status == LIVE, Blog.user << staff)

-- translates roughly to --
SELECT t1.* FROM blog AS t1
WHERE (
    t1.status = ? AND
    t1.user_id IN (
        SELECT t2.id FROM user AS t2 WHERE t2.is_staff = ?
    )
)

-- and here it would be if using joins --
SELECT t1.* FROM blog AS t1
INNER JOIN user AS t2
    ON t1.user_id = t2.id
WHERE
    t1.status = ? AND
    t2.is_staff = ?

Performing advanced queries¶

As you may have noticed, all the examples up to now have shown queries that combine multiple clauses with “AND”. To create arbitrarily complex queries, simply use python’s bitwise “and” and “or” operators:

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

The WHERE clause will look something like:

WHERE (is_staff = ? OR is_superuser = ?)

In order to negate an expression, use the bitwise “invert” operator:

>>> staff_users = User.select().where(is_staff=True)
>>> Tweet.select().where(
...     ~(Tweet.user << staff_users)
... )

This query generates roughly the following SQL:

SELECT t1.* FROM blog AS t1
WHERE
    NOT t1.user_id IN (
        SELECT t2.id FROM user AS t2 WHERE t2.is_staff = ?
    )

Rather complex lookups are possible:

>>> sq = User.select().where(
...     ((User.is_staff == True) | (User.is_superuser == True)) &
...     (User.join_date >= datetime(2009, 1, 1)
... )

This generates roughly the following SQL:

SELECT * FROM user
WHERE (
    (is_staff = ? OR is_superuser = ?) AND
    (join_date >= ?)
)

class WorkerProfiles(Model):
    salary = IntegerField()
    desired = IntegerField()
    tenure = IntegerField()

WorkerProfile.select().where(
    WorkerProfile.salary < WorkerProfile.desired
)

WorkerProfile.select().where(
    WorkerProfile.salary < (WorkerProfile.tenure * 1000) + 40000
)

PageView.update(count=PageView.count + 1).where(
    PageView.url == request.url
)

query = User.select().annotate(Tweet)

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

query = User.select().join(Tweet, JOIN_LEFT_OUTER).annotate(Tweet)

query = User.select().annotate(Tweet, fn.Max(Tweet.created_date).alias('latest'))

most_recent_tweet = Tweet.select().aggregate(fn.Max(Tweet.created_date))

>>> users = User.select(User, fn.Count(Tweet.id).alias('count')).join(Tweet).group_by(User)
>>> for user in users:
...     print user.username, 'posted', user.count, 'tweets'

>>> a_users = User.select().where(fn.Lower(fn.Substr(User.username, 1, 1)) == 'a')
>>> for user in a_users:
...    print user.username

alpha
Alton

for tweet in Tweet.select().order_by(Tweet.created_date.desc()).limit(10):
    print '%s, posted on %s' % (tweet.message, tweet.user.username)

tweets = Tweet.select(Tweet, User).join(User)
for tweet in tweets.order_by(Tweet.created_date.desc()).limit(10):
    print '%s, posted on %s' % (tweet.message, tweet.user.username)

SELECT t1.id, t1.message, t1.user_id, t1.created_date, t2.id, t2.username
FROM tweet AS t1
INNER JOIN user AS t2
    ON t1.user_id = t2.id
ORDER BY t1.created_date desc
LIMIT 10

# above example
tweets = Tweet.select(Tweet, User).join(User)
for tweet in tweets.order_by(Tweet.created_date.desc()).limit(10):
    print '%s, posted on %s' % (tweet.message, tweet.user.username)

# very similar query to the above -- main difference is we're
# aliasing the blog title to "blog_title"
tweets = Tweet.select(Tweet, User.username).join(User).naive()
for tweet in tweets.order_by(Tweet.created_date.desc()).limit(10):
    print '%s, posted on %s' % (tweet.message, tweet.username)

Query evaluation¶

In order to execute a query, it is always necessary to call the execute() method.

To get a better idea of how querying works let’s look at some example queries and their return values:

>>> dq = User.delete().where(User.active == False) # <-- returns a DeleteQuery
>>> dq
<peewee.DeleteQuery object at 0x7fc866ada4d0>
>>> dq.execute() # <-- executes the query and returns number of rows deleted
3

>>> uq = User.update(active=True).where(User.id > 3) # <-- returns an UpdateQuery
>>> uq
<peewee.UpdateQuery object at 0x7fc865beff50>
>>> uq.execute() # <-- executes the query and returns number of rows updated
2

>>> iq = User.insert(username='new user') # <-- returns an InsertQuery
>>> iq
<peewee.InsertQuery object at 0x7fc865beff10>
>>> iq.execute() # <-- executes query and returns the new row's PK
8

>>> sq = User.select().where(User.active == True) # <-- returns a SelectQuery
>>> sq
<peewee.SelectQuery object at 0x7fc865b7a510>
>>> qr = sq.execute() # <-- executes query and returns a QueryResultWrapper
>>> qr
<peewee.QueryResultWrapper object at 0x7fc865b7a6d0>
>>> [u.id for u in qr]
[1, 2, 3, 4, 7, 8]
>>> [u.id for u in qr] # <-- re-iterating over qr does not re-execute query
[1, 2, 3, 4, 7, 8]

>>> [u.id for u in sq] # <-- as a shortcut, you can iterate directly over
>>>                    #     a SelectQuery (which uses a QueryResultWrapper
>>>                    #     behind-the-scenes)
[1, 2, 3, 4, 7, 8]

QueryResultWrapper¶

As I hope the previous bit showed, Delete, Insert and Update queries are all pretty straightforward. Select queries are a little bit tricky in that they return a special object called a QueryResultWrapper. The sole purpose of this class is to allow the results of a query to be iterated over efficiently. In general it should not need to be dealt with explicitly.

The preferred method of iterating over a result set is to iterate directly over the SelectQuery, allowing it to manage the QueryResultWrapper internally.

SelectQuery¶

class SelectQuery¶

By far the most complex of the 4 query classes available in peewee. It supports JOIN operations on other tables, aggregation via GROUP BY and HAVING clauses, ordering via ORDER BY, and can be iterated and sliced to return only a subset of results.

__init__(model, *selection)¶

Parameters:	model – a Model class to perform query on selection – a list of models, fields, functions or expressions

If no query is provided, it will default to all the fields of the given model.

>>> sq = SelectQuery(User, User.id, User.username)
>>> sq = SelectQuery(User,
...     User, fn.Count(Tweet.id).alias('count')
... ).join(Tweet).group_by(User)

where(*q_or_node)¶

Parameters:	q_or_node – a list of expressions (Q or Node objects
Return type:	a SelectQuery instance

>>> sq = SelectQuery(User).where(User.username == 'somebody')
>>> sq = SelectQuery(Blog).where(
...     (User.username == 'somebody') |
...     (User.username == 'nobody')
... )

Note

where() calls are chainable

join(model, join_type=None, on=None)¶

Parameters:	model – the model to join on. there must be a ForeignKeyField between the current query context and the model passed in. join_type – allows the type of JOIN used to be specified explicitly, one of JOIN_INNER, JOIN_LEFT_OUTER, JOIN_FULL on – if multiple foreign keys exist between two models, this parameter is the ForeignKeyField to join on.
Return type:	a SelectQuery instance

Generate a JOIN clause from the current query context to the model passed in, and establishes model as the new query context.

>>> sq = SelectQuery(Tweet).join(User)
>>> sq = SelectQuery(User).join(Relationship, on=Relationship.to_user)

group_by(*clauses)¶

Parameters:	clauses – either a list of model classes or field names
Return type:	SelectQuery

>>> # get a list of blogs with the count of entries each has
>>> sq = User.select(
...     User, fn.Count(Tweet.id).alias('count')
... ).join(Tweet).group_by(User)

having(*q_or_node)¶

Parameters:	q_or_node – a list of expressions (Q or Node objects
Return type:	SelectQuery

>>> sq = User.select(
...     User, fn.Count(Tweet.id).alias('count')
... ).join(Tweet).group_by(User).having(fn.Count(Tweet.id) > 10)

order_by(*clauses)¶

Parameters:	clauses – a list of fields or calls to field.[asc|desc]()
Return type:	SelectQuery

example:

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

paginate(page_num, paginate_by=20)¶

Parameters:	page_num – a 1-based page number to use for paginating results paginate_by – number of results to return per-page
Return type:	SelectQuery

applies a LIMIT and OFFSET to the query.

>>> User.select().order_by(User.username).paginate(3, 20) # <-- get users 41-60

limit(num)¶

Parameters:	num (int) – limit results to num rows

offset(num)¶

Parameters:	num (int) – offset results by num rows

count()¶

Return type:	an integer representing the number of rows in the current query

>>> sq = SelectQuery(Tweet)
>>> sq.count()
45 # <-- number of tweets
>>> sq.where(Tweet.status == DELETED)
>>> sq.count()
3 # <-- number of tweets that are marked as deleted

get()¶

Return type:	Model instance or raises DoesNotExist exception

Get a single row from the database that matches the given query. Raises a <model-class>.DoesNotExist if no rows are returned:

>>> active = User.select().where(User.active == True)
>>> try:
...     user = active.where(User.username == username).get()
... except User.DoesNotExist:
...     user = None

This method is also exposed via the Model api, in which case it accepts arguments that are translated to the where clause:

>>> user = User.get(User.active == True, User.username == username)

exists()¶

Return type:	boolean whether the current query will return any rows. uses an optimized lookup, so use this rather than get().

>>> sq = User.select().where(User.active == True)
>>> if sq.where(User.username==username, User.password==password).exists():
...     authenticated = True

annotate(related_model, aggregation=None)¶

Parameters:	related_model – related Model on which to perform aggregation, must be linked by ForeignKeyField. aggregation – the type of aggregation to use, e.g. fn.Count(Tweet.id).alias('count')
Return type:	SelectQuery

Annotate a query with an aggregation performed on a related model, for example, “get a list of users with the number of tweets for each”:

>>> User.select().annotate(Tweet)

if aggregation is None, it will default to fn.Count(related_model.id).alias('count') but can be anything:

>>> user_latest = User.select().annotate(Tweet, fn.Max(Tweet.created_date).alias('latest'))

Note

If the ForeignKeyField is nullable, then a LEFT OUTER join may need to be used:

>>> User.select().join(Tweet, JOIN_LEFT_OUTER).annotate(Tweet)

aggregate(aggregation)¶

Parameters:	aggregation – a function specifying what aggregation to perform, for example fn.Max(Tweet.created_date).

Method to look at an aggregate of rows using a given function and return a scalar value, such as the count of all rows or the average value of a particular column.

for_update([for_update=True])¶

Return type:	SelectQuery

indicates that this query should lock rows for update

distinct()¶

Return type:	SelectQuery

indicates that this query should only return distinct rows. results in a SELECT DISTINCT query.

naive()¶

Return type:	SelectQuery

indicates that this query should only attempt to reconstruct a single model instance for every row returned by the cursor. if multiple tables were queried, the columns returned are patched directly onto the single model instance.

Note

this can provide a significant speed improvement when doing simple iteration over a large result set.

switch(model)¶

Parameters:	model – model to switch the query context to.
Return type:	a SelectQuery instance

Switches the query context to the given model. Raises an exception if the model has not been selected or joined on previously. The following example selects from blog and joins on both entry and user:

>>> sq = SelectQuery(Blog).join(Entry).switch(Blog).join(User)

filter(*args, **kwargs)¶

Parameters:	args – a list of DQ or Node objects kwargs – a mapping of column + lookup to value, e.g. “age__gt=55”
Return type:	SelectQuery with appropriate WHERE clauses

Provides a django-like syntax for building a query. The key difference between filter() and SelectQuery.where() is that filter() supports traversing joins using django’s “double-underscore” syntax:

>>> sq = Entry.filter(blog__title='Some Blog')

This method is chainable:

>>> base_q = User.filter(active=True)
>>> some_user = base_q.filter(username='charlie')

Note

this method is provided for compatibility with peewee 1.

execute()¶

Return type:	QueryResultWrapper

Executes the query and returns a QueryResultWrapper for iterating over the result set. The results are managed internally by the query and whenever a clause is added that would possibly alter the result set, the query is marked for re-execution.

__iter__()¶

Executes the query:

>>> for user in User.select().where(User.active == True):
...     print user.username