Example app

Example app

We’ll be building a simple twitter-like site. The source code for the example can be found in the examples/twitter directory. You can also browse the source-code on github. There is also an example blog app if that’s more to your liking.

The example app uses the flask web framework which is very easy to get started with. If you don’t have flask already, you will need to install it to run the example:

pip install flask

Running the example

After ensuring that flask is installed, cd into the twitter example directory and execute the run_example.py script:

python run_example.py

The example app will be accessible at http://localhost:5000/

Diving into the code

For simplicity all example code is contained within a single module, examples/twitter/app.py. For a guide on structuring larger Flask apps with peewee, check out Structuring Flask Apps.

Models

In the spirit of the popular web framework Django, peewee uses declarative model definitions. If you’re not familiar with Django, the idea is that you declare a model class for each table. The model class then defines one or more field attributes which correspond to the table’s columns. 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 stores which users follow one another.

Message:

Analagous to a tweet. The Message model stores the text content of the tweet, when it was created, and who posted it (foreign key to User).

If you like UML, these are the tables and relationships:

In order to create these models we need to instantiate a SqliteDatabase object. Then we define our model classes, specifying the columns as Field instances on the class.

# 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.
class BaseModel(Model):
    class Meta:
        database = database
# the user model specifies its fields (or columns) declaratively, like django
class User(BaseModel):
    username = CharField(unique=True)
    password = CharField()
    email = CharField()
    join_date = DateTimeField()
    class Meta:
        order_by = ('username',)
# 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')
    class Meta:
        indexes = (
            # Specify a unique multi-column index on from/to-user.
            (('from_user', 'to_user'), True),
        )
# 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',)

Note

Note that we create a BaseModel class that simply defines what database we would like to use. All other models then extend this class and will also use the correct database connection.

Peewee supports many different field types which map to different column types commonly supported by database engines. Conversion between python types and those used in the database is handled transparently, allowing you to use the following in your application:

Strings (unicode or otherwise)
Integers, floats, and Decimal numbers.
Boolean values
Dates, times and datetimes
None (NULL)
Binary data

Creating tables

In order to start using the models, its necessary to create the tables. This is a one-time operation and can be done quickly using the interactive interpreter. We can create a small helper function to accomplish this:

def create_tables():
    database.connect()
    database.create_tables([User, Relationship, Message])

Open a python shell in the directory alongside the example app and execute the following:

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

Note

If you encounter an ImportError it means that either flask or peewee was not found and may not be installed correctly. Check the Installing and Testing document for instructions on installing peewee.

Every model has a create_table() classmethod which runs a SQL CREATE TABLE statement in the database. This method will create the table, including all columns, foreign-key constraints, indexes, and sequences. Usually this is something you’ll only do once, whenever a new model is added.

Peewee provides a helper method Database.create_tables() which will resolve inter-model dependencies and call create_table() on each model.

Note

Adding fields after the table has been created will required you to either drop the table and re-create it or manually add the columns using an ALTER TABLE query.

Alternatively, you can use the schema migrations extension to alter your database schema using Python.

Note

You can also write database.create_tables([User, ...], True) and peewee will first check to see if the table exists before creating it.

Establishing a database connection

You may have noticed in the above model code that there is a class defined on the base model named Meta that sets the database attribute. Peewee allows every model to specify which database it uses. There are many Meta options you can specify which control the behavior of your model.

This is a peewee idiom:

DATABASE = 'tweepee.db'
# Create a database instance that will manage the connection and
# execute queries
database = SqliteDatabase(DATABASE, threadlocals=True)

When developing a web application, it’s common to open a connection when a request starts, and close it when the response is returned. You should always manage your connections explicitly. For instance, if you are using a connection pool, connections will only be recycled correctly if you call connect() and close().

We will tell flask that during the request/response cycle we need to create a connection to the database. Flask provides some handy decorators to make this a snap:

@app.before_request
def before_request():
    database.connect()
@app.after_request
def after_request(response):
    database.close()
    return response

Note

Peewee uses thread local storage to manage connection state, so this pattern can be used with multi-threaded WSGI servers.

Making queries

In the User model there are a few instance methods that encapsulate some user-specific functionality:

following(): who is this user following?
followers(): who is following this user?

These methods are similar in their implementation but with an important difference in the SQL JOIN and WHERE clauses:

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

Creating new objects

When a new user wants to join the site we need to make sure the username is available, and if so, create a new User record. Looking at the join() view, we can see that our application attempts to create the User using Model.create(). We defined the User.username field with a unique constraint, so if the username is taken the database will raise an IntegrityError.

try:
    with database.transaction():
        # Attempt to create the user. If the username is taken, due to the
        # unique constraint, the database will raise an IntegrityError.
        user = User.create(
            username=request.form['username'],
            password=md5(request.form['password']).hexdigest(),
            email=request.form['email'],
            join_date=datetime.datetime.now()
        )
    # mark the user as being 'authenticated' by setting the session vars
    auth_user(user)
    return redirect(url_for('homepage'))
except IntegrityError:
    flash('That username is already taken')

We will use a similar approach when a user wishes to follow someone. To indicate a following relationship, we create a row in the Relationship table pointing from one user to another. Due to the unique index on from_user and to_user, we will be sure not to end up with duplicate rows:

user = get_object_or_404(User, username=username)
try:
    with database.transaction():
        Relationship.create(
            from_user=get_current_user(),
            to_user=user)
except IntegrityError:
    pass

Performing subqueries

If you are logged-in and visit the twitter homepage, you will see tweets from the users that you follow. In order to implement this cleanly, we can use a subquery:

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

This code corresponds to the following SQL query:

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" = ?
)

More examples

There are more examples included in the peewee examples directory, including:

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.

Note

Like these snippets and interested in more? Check out flask-peewee - a flask plugin that provides a django-like Admin interface, RESTful API, Authentication and more for your peewee models.

Example app

Example app

Running the example

Diving into the code

Models

Creating tables

Establishing a database connection

Making queries

Creating new objects

Performing subqueries

Other topics of interest

More examples