Provided by: peewee_2.10.2+dfsg-2_all 
NAME
peewee - peewee Documentation [image]
Peewee is a simple and small ORM. It has few (but expressive) concepts, making it easy to learn and
intuitive to use.
• A small, expressive ORM
• Written in python with support for versions 2.6+ and 3.2+.
• built-in support for sqlite, mysql and postgresql
• numerous extensions available (postgres hstore/json/arrays, sqlite full-text-search, schema migrations,
and much more).
postgresqlmysqlsqlite
Peewee’s source code hosted on GitHub.
New to peewee? Here is a list of documents you might find most helpful when getting started:
• Quickstart guide – this guide covers all the bare essentials. It will take you between 5 and 10 minutes
to go through it.
• Guide to the various query operators describes how to construct queries and combine expressions.
• Field types table lists the various field types peewee supports and the parameters they accept. There
is also an extension module that contains special/custom field types.
CONTENTS:
Installing and Testing
Most users will want to simply install the latest version, hosted on PyPI:
pip install peewee
Peewee comes with two C extensions that can optionally be compiled:
• Speedups, which includes miscellaneous functions re-implemented with Cython. This module will be built
automatically if Cython is installed.
• Sqlite extensions, which includes Cython implementations of the SQLite date manipulation functions, the
REGEXP operator, and full-text search result ranking algorithms. This module should be built using the
build_sqlite_ext command.
NOTE:
If you have Cython installed, then the speedups module will automatically be built. If you wish to
also build the SQLite Cython extension, you must manually run:
python setup.py build_sqlite_ext
python setup.py install
Installing with git
The project is hosted at https://github.com/coleifer/peewee and can be installed using git:
git clone https://github.com/coleifer/peewee.git
cd peewee
python setup.py install
If you would like to build the SQLite extension in a git checkout, you can run:
# Build the sqlite extension and place the shared library alongside the other modules.
python setup.py build_sqlite_ext -i
NOTE:
On some systems you may need to use sudo python setup.py install to install peewee system-wide.
Running tests
You can test your installation by running the test suite.
python setup.py test
# Or use the test runner:
python runtests.py
You can test specific features or specific database drivers using the runtests.py script. By default the
test suite is run using SQLite and the playhouse extension tests are not run. To view the available test
runner options, use:
python runtests.py --help
Optional dependencies
NOTE:
To use Peewee, you typically won’t need anything outside the standard library, since most Python
distributions are compiled with SQLite support. You can test by running import sqlite3 in the Python
console. If you wish to use another database, there are many DB-API 2.0-compatible drivers out there,
such as pymysql or psycopg2 for MySQL and Postgres respectively.
• Cython: used for various speedups. Can give a big boost to certain operations, particularly if you use
SQLite.
• apsw: an optional 3rd-party SQLite binding offering greater performance and much, much saner semantics
than the standard library pysqlite. Use with APSWDatabase.
• pycrypto is used for the AESEncryptedField.
• bcrypt module is used for the PasswordField.
• vtfunc <https://github.com/coleifer/sqlite-vtfunc> is used to provide some table-valued functions for
Sqlite as part of the sqlite_udf extensions module.
• gevent is an optional dependency for SqliteQueueDatabase (though it works with threading just fine).
• BerkeleyDB can be compiled with a SQLite frontend, which works with Peewee. Compiling can be tricky so
here are instructions.
• Lastly, if you use the Flask or Django frameworks, there are helper extension modules available.
Quickstart
This document presents a brief, high-level overview of Peewee’s primary features. This guide will cover:
• Model Definition
• Storing data
• Retrieving Data
NOTE:
If you’d like something a bit more meaty, there is a thorough tutorial on creating a “twitter”-style
web app using peewee and the Flask framework.
I strongly recommend opening an interactive shell session and running the code. That way you can get a
feel for typing in queries.
Model Definition
Model classes, fields and model instances all map to database concepts:
┌────────────────┬─────────────────────────┐
│ Thing │ Corresponds to… │
├────────────────┼─────────────────────────┤
│ Model class │ Database table │
├────────────────┼─────────────────────────┤
│ Field instance │ Column on a table │
├────────────────┼─────────────────────────┤
│ Model instance │ Row in a database table │
└────────────────┴─────────────────────────┘
When starting a project with peewee, it’s typically best to begin with your data model, by defining one
or more Model classes:
from peewee import *
db = SqliteDatabase('people.db')
class Person(Model):
name = CharField()
birthday = DateField()
is_relative = BooleanField()
class Meta:
database = db # This model uses the "people.db" database.
NOTE:
Note that we named our model Person instead of People. This is a convention you should follow – even
though the table will contain multiple people, we always name the class using the singular form.
There are lots of field types suitable for storing various types of data. Peewee handles converting
between pythonic values those used by the database, so you can use Python types in your code without
having to worry.
Things get interesting when we set up relationships between models using foreign keys (wikipedia). This
is easy to do with peewee:
class Pet(Model):
owner = ForeignKeyField(Person, related_name='pets')
name = CharField()
animal_type = CharField()
class Meta:
database = db # this model uses the "people.db" database
Now that we have our models, let’s connect to the database. Although it’s not necessary to open the
connection explicitly, it is good practice since it will reveal any errors with your database connection
immediately, as opposed to some arbitrary time later when the first query is executed. It is also good to
close the connection when you are done – for instance, a web app might open a connection when it receives
a request, and close the connection when it sends the response.
>>> db.connect()
We’ll begin by creating the tables in the database that will store our data. This will create the tables
with the appropriate columns, indexes, sequences, and foreign key constraints:
>>> db.create_tables([Person, Pet])
Storing data
Let’s begin by populating the database with some people. We will use the save() and create() methods to
add and update people’s records.
>>> from datetime import date
>>> uncle_bob = Person(name='Bob', birthday=date(1960, 1, 15), is_relative=True)
>>> uncle_bob.save() # bob is now stored in the database
1
NOTE:
When you call save(), the number of rows modified is returned.
You can also add a person by calling the create() method, which returns a model instance:
>>> grandma = Person.create(name='Grandma', birthday=date(1935, 3, 1), is_relative=True)
>>> herb = Person.create(name='Herb', birthday=date(1950, 5, 5), is_relative=False)
To update a row, modify the model instance and call save() to persist the changes. Here we will change
Grandma’s name and then save the changes in the database:
>>> grandma.name = 'Grandma L.'
>>> grandma.save() # Update grandma's name in the database.
1
Now we have stored 3 people in the database. Let’s give them some pets. Grandma doesn’t like animals in
the house, so she won’t have any, but Herb is an animal lover:
>>> bob_kitty = Pet.create(owner=uncle_bob, name='Kitty', animal_type='cat')
>>> herb_fido = Pet.create(owner=herb, name='Fido', animal_type='dog')
>>> herb_mittens = Pet.create(owner=herb, name='Mittens', animal_type='cat')
>>> herb_mittens_jr = Pet.create(owner=herb, name='Mittens Jr', animal_type='cat')
After a long full life, Mittens sickens and dies. We need to remove him from the database:
>>> herb_mittens.delete_instance() # he had a great life
1
NOTE:
The return value of delete_instance() is the number of rows removed from the database.
Uncle Bob decides that too many animals have been dying at Herb’s house, so he adopts Fido:
>>> herb_fido.owner = uncle_bob
>>> herb_fido.save()
>>> bob_fido = herb_fido # rename our variable for clarity
Retrieving Data
The real strength of our database is in how it allows us to retrieve data through queries. Relational
databases are excellent for making ad-hoc queries.
Getting single records
Let’s retrieve Grandma’s record from the database. To get a single record from the database, use
SelectQuery.get():
>>> grandma = Person.select().where(Person.name == 'Grandma L.').get()
We can also use the equivalent shorthand Model.get():
>>> grandma = Person.get(Person.name == 'Grandma L.')
Lists of records
Let’s list all the people in the database:
>>> for person in Person.select():
... print person.name, person.is_relative
...
Bob True
Grandma L. True
Herb False
Let’s list all the cats and their owner’s name:
>>> query = Pet.select().where(Pet.animal_type == 'cat')
>>> for pet in query:
... print pet.name, pet.owner.name
...
Kitty Bob
Mittens Jr Herb
There is a big problem with the previous query: because we are accessing pet.owner.name and we did not
select this value in our original query, peewee will have to perform an additional query to retrieve the
pet’s owner. This behavior is referred to as N+1 and it should generally be avoided.
We can avoid the extra queries by selecting both Pet and Person, and adding a join.
>>> query = (Pet
... .select(Pet, Person)
... .join(Person)
... .where(Pet.animal_type == 'cat'))
>>> for pet in query:
... print pet.name, pet.owner.name
...
Kitty Bob
Mittens Jr Herb
Let’s get all the pets owned by Bob:
>>> for pet in Pet.select().join(Person).where(Person.name == 'Bob'):
... print pet.name
...
Kitty
Fido
We can do another cool thing here to get bob’s pets. Since we already have an object to represent Bob, we
can do this instead:
>>> for pet in Pet.select().where(Pet.owner == uncle_bob):
... print pet.name
Let’s make sure these are sorted alphabetically by adding an order_by() clause:
>>> for pet in Pet.select().where(Pet.owner == uncle_bob).order_by(Pet.name):
... print pet.name
...
Fido
Kitty
Let’s list all the people now, youngest to oldest:
>>> for person in Person.select().order_by(Person.birthday.desc()):
... print person.name, person.birthday
...
Bob 1960-01-15
Herb 1950-05-05
Grandma L. 1935-03-01
Now let’s list all the people and some info about their pets:
>>> for person in Person.select():
... print person.name, person.pets.count(), 'pets'
... for pet in person.pets:
... print ' ', pet.name, pet.animal_type
...
Bob 2 pets
Kitty cat
Fido dog
Grandma L. 0 pets
Herb 1 pets
Mittens Jr cat
Once again we’ve run into a classic example of N+1 query behavior. We can avoid this by performing a JOIN
and aggregating the records:
>>> subquery = Pet.select(fn.COUNT(Pet.id)).where(Pet.owner == Person.id)
>>> query = (Person
... .select(Person, Pet, subquery.alias('pet_count'))
... .join(Pet, JOIN.LEFT_OUTER)
... .order_by(Person.name))
>>> for person in query.aggregate_rows(): # Note the `aggregate_rows()` call.
... print person.name, person.pet_count, 'pets'
... for pet in person.pets:
... print ' ', pet.name, pet.animal_type
...
Bob 2 pets
Kitty cat
Fido dog
Grandma L. 0 pets
Herb 1 pets
Mittens Jr cat
Even though we created the subquery separately, only one query is actually executed.
Finally, let’s do a complicated one. Let’s get all the people whose birthday was either:
• before 1940 (grandma)
• after 1959 (bob)
>>> d1940 = date(1940, 1, 1)
>>> d1960 = date(1960, 1, 1)
>>> query = (Person
... .select()
... .where((Person.birthday < d1940) | (Person.birthday > d1960)))
...
>>> for person in query:
... print person.name, person.birthday
...
Bob 1960-01-15
Grandma L. 1935-03-01
Now let’s do the opposite. People whose birthday is between 1940 and 1960:
>>> query = (Person
... .select()
... .where((Person.birthday > d1940) & (Person.birthday < d1960)))
...
>>> for person in query:
... print person.name, person.birthday
...
Herb 1950-05-05
One last query. This will use a SQL function to find all people whose names start with either an upper or
lower-case G:
>>> expression = (fn.Lower(fn.Substr(Person.name, 1, 1)) == 'g')
>>> for person in Person.select().where(expression):
... print person.name
...
Grandma L.
We’re done with our database, let’s close the connection:
>>> db.close()
This is just the basics! You can make your queries as complex as you like.
All the other SQL clauses are available as well, such as:
• group_by()
• having()
• limit() and offset()
Check the documentation on querying for more info.
Working with existing databases
If you already have a database, you can autogenerate peewee models using pwiz. For instance, if I have a
postgresql database named charles_blog, I might run:
python -m pwiz -e postgresql charles_blog > blog_models.py
What next?
That’s it for the quickstart. If you want to look at a full web-app, check out the 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
[image]
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: [image]
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 installation 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" = ?
)
Other topics of interest
There are a couple other neat things going on in the example app that are worth mentioning briefly.
• Support for paginating lists of results is implemented in a simple function called object_list (after
it’s corollary in Django). This function is used by all the views that return lists of objects.
def object_list(template_name, qr, var_name='object_list', **kwargs):
kwargs.update(
page=int(request.args.get('page', 1)),
pages=qr.count() / 20 + 1
)
kwargs[var_name] = qr.paginate(kwargs['page'])
return render_template(template_name, **kwargs)
• Simple authentication system with a login_required decorator. The first function simply adds user data
into the current session when a user successfully logs in. The decorator login_required can be used to
wrap view functions, checking for whether the session is authenticated and if not redirecting to the
login page.
def auth_user(user):
session['logged_in'] = True
session['user'] = user
session['username'] = user.username
flash('You are logged in as %s' % (user.username))
def login_required(f):
@wraps(f)
def inner(*args, **kwargs):
if not session.get('logged_in'):
return redirect(url_for('login'))
return f(*args, **kwargs)
return inner
• Return a 404 response instead of throwing exceptions when an object is not found in the database.
def get_object_or_404(model, *expressions):
try:
return model.get(*expressions)
except model.DoesNotExist:
abort(404)
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.
Additional Resources
I’ve written a number of blog posts about building applications and web-services with peewee (and usually
Flask). If you’d like to see some “real-life” applications that use peewee, the following resources may
be useful:
• How to make a Flask blog in one hour or less.
• Building a note-taking app with Flask and Peewee as well as Part 2 and Part 3.
• Analytics web service built with Flask and Peewee.
• Personalized news digest (with a boolean query parser!).
• Using peewee to explore CSV files.
• Structuring Flask apps with Peewee.
• Creating a lastpass clone with Flask and Peewee.
• Building a web-based encrypted file manager with Flask, peewee and S3.
• Creating a bookmarking web-service that takes screenshots of your bookmarks.
• Building a pastebin, wiki and a bookmarking service using Flask and Peewee.
• Encrypted databases with Python and SQLCipher.
• Dear Diary, an Encrypted Command-Line Diary.
Contributing
In order to continually improve, Peewee needs the help of developers like you. Whether it’s contributing
patches, submitting bug reports, or just asking and answering questions, you are helping to make Peewee a
better library.
In this document I’ll describe some of the ways you can help.
Patches
Do you have an idea for a new feature, or is there a clunky API you’d like to improve? Before coding it
up and submitting a pull-request, open a new issue on GitHub describing your proposed changes. This
doesn’t have to be anything formal, just a description of what you’d like to do and why.
When you’re ready, you can submit a pull-request with your changes. Successful patches will have the
following:
• Unit tests.
• Documentation, both prose form and general API documentation.
• Code that conforms stylistically with the rest of the Peewee codebase.
Bugs
If you’ve found a bug, please check to see if it has already been reported, and if not create an issue on
GitHub. The more information you include, the more quickly the bug will get fixed, so please try to
include the following:
• Traceback and the error message (please format your code!)
• Relevant portions of your code or code to reproduce the error
• Peewee version: python -c "from peewee import __version__; print(__version__)"
• Which database you’re using
If you have found a bug in the code and submit a failing test-case, then hats-off to you, you are a hero!
Questions
If you have questions about how to do something with peewee, then I recommend either:
• Ask on StackOverflow. I check SO just about every day for new peewee questions and try to answer them.
This has the benefit also of preserving the question and answer for other people to find.
• Ask in IRC, #peewee on freenode. I always answer questions, but it may take a bit to get to them.
• Ask on the mailing list, https://groups.google.com/group/peewee-orm
Managing your Database
This document describes how to perform typical database-related tasks with peewee. Throughout this
document we will use the following example models:
from peewee import *
class User(Model):
username = CharField(unique=True)
class Tweet(Model):
user = ForeignKeyField(User, related_name='tweets')
message = TextField()
created_date = DateTimeField(default=datetime.datetime.now)
is_published = BooleanField(default=True)
Creating a database connection and tables
While it is not necessary to explicitly connect to the database before using it, managing connections
explicitly is a good practice. This way if the connection fails, the exception can be caught during the
connect step, rather than some arbitrary time later when a query is executed. Furthermore, if you’re
using a connection pool, it is actually necessary to call connect() and close() to ensure connections are
recycled correctly.
For web-apps you will typically open a connection when a request is started and close it when the
response is delivered:
database = SqliteDatabase('my_app.db')
def before_request_handler():
database.connect()
def after_request_handler():
database.close()
NOTE:
For examples of configuring connection hooks for several popular web frameworks, see the Adding
Request Hooks section.
NOTE:
For advanced connection management techniques, see the advanced connection management section.
To use this database with your models, set the database attribute on an inner Meta class:
class MyModel(Model):
some_field = CharField()
class Meta:
database = database
Best practice: define a base model class that points at the database object you wish to use, and then all
your models will extend it:
database = SqliteDatabase('my_app.db')
class BaseModel(Model):
class Meta:
database = database
class User(BaseModel):
username = CharField()
class Tweet(BaseModel):
user = ForeignKeyField(User, related_name='tweets')
message = TextField()
# etc, etc
NOTE:
Remember to specify a database on your model classes, otherwise peewee will fall back to a default
sqlite database named “peewee.db”.
Vendor-specific Parameters
Some database drivers accept special parameters when being initialized. Rather than try to accommodate
all these parameters, Peewee will pass back unrecognized parameters directly to the database driver.
For instance, with Postgresql it is common to need to specify the host, user and password when creating
your connection. These are not standard Peewee Database parameters, so they will be passed directly back
to psycopg2 when creating connections:
db = PostgresqlDatabase(
'database_name', # Required by Peewee.
user='postgres', # Will be passed directly to psycopg2.
password='secret', # Ditto.
host='db.mysite.com', # Ditto.
)
As another example, the pymysql driver accepts a charset parameter which is not a standard Peewee
Database parameter. To set this value, simply pass in charset alongside your other values:
db = MySQLDatabase('database_name', user='www-data', charset='utf8mb4')
Consult your database driver’s documentation for the available parameters:
• Postgres: psycopg2
• MySQL: MySQLdb
• MySQL: pymysql
• SQLite: sqlite3
Using Postgresql
To connect to a Postgresql database, we will use PostgresqlDatabase. The first parameter is always the
name of the database, and after that you can specify arbitrary psycopg2 parameters.
psql_db = PostgresqlDatabase('my_database', user='postgres')
class BaseModel(Model):
"""A base model that will use our Postgresql database"""
class Meta:
database = psql_db
class User(BaseModel):
username = CharField()
The playhouse contains a Postgresql extension module which provides many postgres-specific features such
as:
• Arrays
• HStore
• JSON
• Server-side cursors
• And more!
If you would like to use these awesome features, use the PostgresqlExtDatabase from the
playhouse.postgres_ext module:
from playhouse.postgres_ext import PostgresqlExtDatabase
psql_db = PostgresqlExtDatabase('my_database', user='postgres')
Using SQLite
To connect to a SQLite database, we will use SqliteDatabase. The first parameter is the filename
containing the database, or the string :memory: to create an in-memory database. After the database
filename, you can specify arbitrary sqlite3 parameters.
sqlite_db = SqliteDatabase('my_app.db')
class BaseModel(Model):
"""A base model that will use our Sqlite database."""
class Meta:
database = sqlite_db
class User(BaseModel):
username = CharField()
# etc, etc
The playhouse contains a SQLite extension module which provides many SQLite-specific features such as:
• Full-text search with BM25 ranking.
• Support for custom functions, aggregates and collations
• Advanced transaction support
• And more!
If you would like to use these awesome features, use the SqliteExtDatabase from the playhouse.sqlite_ext
module:
from playhouse.sqlite_ext import SqliteExtDatabase
sqlite_db = SqliteExtDatabase('my_app.db', journal_mode='WAL')
PRAGMA statements
New in version 2.6.4.
SQLite allows run-time configuration of a number of parameters through PRAGMA statements (documentation).
These statements are typically run against a new database connection. To run one or more PRAGMA
statements against new connections, you can specify them as a list or tuple of 2-tuples containing the
pragma name and value:
db = SqliteDatabase('my_app.db', pragmas=(
('journal_mode', 'WAL'),
('cache_size', 10000),
('mmap_size', 1024 * 1024 * 32),
))
SQLite and Autocommit
Changed in version 2.4.5.
In version 2.4.5, the default isolation level for SQLite databases is None, which equates to autocommit.
The reason for this change has to do with some idiosyncracies of pysqlite (or the standard library
sqlite3).
If you are using your database in autocommit mode (the default) then you should not need to make any
changes to your code.
If you are using autocommit=False, you will need to explicitly call begin() before executing queries.
NOTE:
This does not apply to code executed within transaction() or atomic().
WARNING:
If you are using peewee with autocommit disabled, you must explicitly call begin(), otherwise
statements will be executed in autocommit mode.
Example code:
# Define a database with autocommit turned off.
db = SqliteDatabase('my_app.db', autocommit=False)
# You must call begin()
db.begin()
User.create(username='charlie')
db.commit()
# If using a transaction, then no changes are necessary.
with db.transaction():
User.create(username='huey')
# If using a function decorated by transaction, no changes are necessary.
@db.transaction()
def create_user(username):
User.create(username=username)
APSW, an Advanced SQLite Driver
Peewee also comes with an alternate SQLite database that uses apsw, an advanced Python SQLite driver.
More information on APSW can be obtained on the APSW project website. APSW provides special features
like:
• Virtual tables, virtual file-systems, Blob I/O, backups and file control.
• Connections can be shared across threads without any additional locking.
• Transactions are managed explicitly by your code.
• Unicode is handled correctly.
• APSW is faster that the standard library sqlite3 module.
• Exposes pretty much the entire SQLite C API to your Python app.
If you would like to use APSW, use the APSWDatabase from the apsw_ext module:
from playhouse.apsw_ext import APSWDatabase
apsw_db = APSWDatabase('my_app.db')
Using BerkeleyDB
The playhouse contains a special extension module for using a BerkeleyDB database. BerkeleyDB can be
compiled with a SQLite-compatible API, then the python SQLite driver can be compiled to use the Berkeley
version of SQLite.
You can find up-to-date step by step instructions on my blog for compling the BerkeleyDB + SQLite
library, then building a statically-linked pysqlite that uses the custom sqlite library.
To connect to a BerkeleyDB database, we will use BerkeleyDatabase. Like SqliteDatabase, the first
parameter is the filename containing the database or the string :memory: to create an in-memory database.
from playhouse.berkeleydb import BerkeleyDatabase
berkeley_db = BerkeleyDatabase('my_app.db')
class BaseModel(Model):
"""A base model that will use our BDB database."""
class Meta:
database = berkeley_db
class User(BaseModel):
username = CharField()
# etc, etc
Using MySQL
To connect to a MySQL database, we will use MySQLDatabase. After the database name, you can specify
arbitrary connection parameters that will be passed back to the driver (either MySQLdb or pymysql).
mysql_db = MySQLDatabase('my_database')
class BaseModel(Model):
"""A base model that will use our MySQL database"""
class Meta:
database = mysql_db
class User(BaseModel):
username = CharField()
# etc, etc
Error 2006: MySQL server has gone away
This particular error can occur when MySQL kills an idle database connection. This typically happens with
web apps that do not explicitly manage database connections. What happens is your application starts, a
connection is opened to handle the first query that executes, and, since that connection is never closed,
it remains open, waiting for more queries.
To fix this, make sure you are explicitly connecting to the database when you need to execute queries,
and close your connection when you are done. In a web-application, this typically means you will open a
connection when a request comes in, and close the connection when you return a response.
See the Adding Request Hooks for more information.
If you would like to automatically reconnect and retry queries that fail due to an OperationalError,
peewee provides a Database mixin RetryOperationalError that will handle reconnecting and retrying the
query automatically. For more information see Automatic Reconnect.
Connecting using a Database URL
The playhouse module db_url provides a helper connect() function that accepts a database URL and returns
a Database instance.
Example code:
import os
from peewee import *
from playhouse.db_url import connect
# Connect to the database URL defined in the environment, falling
# back to a local Sqlite database if no database URL is specified.
db = connect(os.environ.get('DATABASE') or 'sqlite:///default.db')
class BaseModel(Model):
class Meta:
database = db
Example database URLs:
• sqlite:///my_database.db will create a SqliteDatabase instance for the file my_database.db in the
current directory.
• sqlite:///:memory: will create an in-memory SqliteDatabase instance.
• postgresql://postgres:my_password@localhost:5432/my_database will create a PostgresqlDatabase instance.
A username and password are provided, as well as the host and port to connect to.
• mysql://user:passwd@ip:port/my_db will create a MySQLDatabase instance for the local MySQL database
my_db.
• More examples in the db_url documentation.
Multi-threaded applications
peewee stores the connection state in a thread local, so each thread gets its own separate connection. If
you prefer to manage the connections yourself, you can disable this behavior by initializing your
database with threadlocals=False.
Run-time database configuration
Sometimes the database connection settings are not known until run-time, when these values may be loaded
from a configuration file or the environment. In these cases, you can defer the initialization of the
database by specifying None as the database_name.
database = SqliteDatabase(None) # Un-initialized database.
class SomeModel(Model):
class Meta:
database = database
If you try to connect or issue any queries while your database is uninitialized you will get an
exception:
>>> database.connect()
Exception: Error, database not properly initialized before opening connection
To initialize your database, call the init() method with the database name and any additional keyword
arguments:
database_name = raw_input('What is the name of the db? ')
database.init(database_name, host='localhost', user='postgres')
For even more control over initializing your database, see the next section, Dynamically defining a
database.
Dynamically defining a database
For even more control over how your database is defined/initialized, you can use the Proxy helper. Proxy
objects act as a placeholder, and then at run-time you can swap it out for a different object. In the
example below, we will swap out the database depending on how the app is configured:
database_proxy = Proxy() # Create a proxy for our db.
class BaseModel(Model):
class Meta:
database = database_proxy # Use proxy for our DB.
class User(BaseModel):
username = CharField()
# Based on configuration, use a different database.
if app.config['DEBUG']:
database = SqliteDatabase('local.db')
elif app.config['TESTING']:
database = SqliteDatabase(':memory:')
else:
database = PostgresqlDatabase('mega_production_db')
# Configure our proxy to use the db we specified in config.
database_proxy.initialize(database)
WARNING:
Only use this method if your actual database driver varies at run-time. For instance, if your tests
and local dev environment run on SQLite, but your deployed app uses PostgreSQL, you can use the Proxy
to swap out engines at run-time.
However, if it is only connection values that vary at run-time, such as the path to the database file,
or the database host, you should instead use Database.init(). See Run-time database configuration for
more details.
Connection Pooling
Connection pooling is provided by the pool module, included in the playhouse extensions library. The pool
supports:
• Timeout after which connections will be recycled.
• Upper bound on the number of open connections.
The connection pool module comes with support for Postgres and MySQL (though adding support for other
databases is trivial).
from playhouse.pool import PooledPostgresqlExtDatabase
db = PooledPostgresqlExtDatabase(
'my_database',
max_connections=8,
stale_timeout=300,
user='postgres')
class BaseModel(Model):
class Meta:
database = db
The following pooled database classes are available:
• PooledPostgresqlDatabase
• PooledPostgresqlExtDatabase
• PooledMySQLDatabase
• PooledSqliteDatabase
• PooledSqliteExtDatabase
For an in-depth discussion of peewee’s connection pool, see the pool section of the playhouse
documentation.
Read Slaves
Peewee can automatically run SELECT queries against one or more read replicas. The read_slave module,
included in the playhouse extensions library, contains a Model subclass which provides this behavior.
Here is how you might use the ReadSlaveModel:
from peewee import *
from playhouse.read_slave import ReadSlaveModel
# Declare a master and two read-replicas.
master = PostgresqlDatabase('master')
replica_1 = PostgresqlDatabase('replica', host='192.168.1.2')
replica_2 = PostgresqlDatabase('replica', host='192.168.1.3')
class BaseModel(ReadSlaveModel):
class Meta:
database = master
read_slaves = (replica_1, replica_2)
class User(BaseModel):
username = CharField()
Now when you execute writes (or deletes), they will be run on the master, while all read-only queries
will be executed against one of the replicas. Queries are dispatched among the read slaves in round-robin
fashion.
Schema migrations
Currently peewee does not have support for automatic schema migrations, but you can use the migrate
module to create simple migration scripts. The schema migrations module works with SQLite, MySQL and
Postgres, and will even allow you to do things like drop or rename columns in SQLite!
Here is an example of how you might write a migration script:
from playhouse.migrate import *
my_db = SqliteDatabase('my_database.db')
migrator = SqliteMigrator(my_db)
title_field = CharField(default='')
status_field = IntegerField(null=True)
with my_db.transaction():
migrate(
migrator.add_column('some_table', 'title', title_field),
migrator.add_column('some_table', 'status', status_field),
migrator.drop_column('some_table', 'old_column'),
)
Check the migrate documentation for more details.
Generating Models from Existing Databases
If you’d like to generate peewee model definitions for an existing database, you can try out the database
introspection tool pwiz that comes with peewee. pwiz is capable of introspecting Postgresql, MySQL and
SQLite databases.
Introspecting a Postgresql database:
python -m pwiz --engine=postgresql my_postgresql_database
Introspecting a SQLite database:
python -m pwiz --engine=sqlite test.db
pwiz will generate:
• Database connection object
• A BaseModel class to use with the database
• Model classes for each table in the database.
The generated code is written to stdout, and can easily be redirected to a file:
python -m pwiz -e postgresql my_postgresql_db > models.py
NOTE:
pwiz generally works quite well with even large and complex database schemas, but in some cases it
will not be able to introspect a column. You may need to go through the generated code to add
indexes, fix unrecognized column types, and resolve any circular references that were found.
Adding Request Hooks
When building web-applications, it is very important that you manage your database connections correctly.
In this section I will describe how to add hooks to your web app to ensure the database connection is
handled properly.
These steps will ensure that regardless of whether you’re using a simple SQLite database, or a pool of
multiple Postgres connections, peewee will handle the connections correctly.
Flask
Flask and peewee are a great combo and my go-to for projects of any size. Flask provides two hooks which
we will use to open and close our db connection. We’ll open the connection when a request is received,
then close it when the response is returned.
from flask import Flask
from peewee import *
database = SqliteDatabase('my_app.db')
app = Flask(__name__)
# This hook ensures that a connection is opened to handle any queries
# generated by the request.
@app.before_request
def _db_connect():
database.connect()
# This hook ensures that the connection is closed when we've finished
# processing the request.
@app.teardown_request
def _db_close(exc):
if not database.is_closed():
database.close()
Django
While it’s less common to see peewee used with Django, it is actually very easy to use the two. To manage
your peewee database connections with Django, the easiest way in my opinion is to add a middleware to
your app. The middleware should be the very first in the list of middlewares, to ensure it runs first
when a request is handled, and last when the response is returned.
If you have a django project named my_blog and your peewee database is defined in the module my_blog.db,
you might add the following middleware class:
# middleware.py
from my_blog.db import database # Import the peewee database instance.
class PeeweeConnectionMiddleware(object):
def process_request(self, request):
database.connect()
def process_response(self, request, response):
if not database.is_closed():
database.close()
return response
To ensure this middleware gets executed, add it to your settings module:
# settings.py
MIDDLEWARE_CLASSES = (
# Our custom middleware appears first in the list.
'my_blog.middleware.PeeweeConnectionMiddleware',
# These are the default Django 1.7 middlewares. Yours may differ,
# but the important this is that our Peewee middleware comes first.
'django.middleware.common.CommonMiddleware',
'django.contrib.sessions.middleware.SessionMiddleware',
'django.middleware.csrf.CsrfViewMiddleware',
'django.contrib.auth.middleware.AuthenticationMiddleware',
'django.contrib.messages.middleware.MessageMiddleware',
)
# ... other Django settings ...
Bottle
I haven’t used bottle myself, but looking at the documentation I believe the following code should ensure
the database connections are properly managed:
# app.py
from bottle import hook #, route, etc, etc.
from peewee import *
db = SqliteDatabase('my-bottle-app.db')
@hook('before_request')
def _connect_db():
db.connect()
@hook('after_request')
def _close_db():
if not db.is_closed():
db.close()
# Rest of your bottle app goes here.
Web.py
See application processors.
db = SqliteDatabase('my_webpy_app.db')
def connection_processor(handler):
db.connect()
try:
return handler()
finally:
if not db.is_closed():
db.close()
app.add_processor(connection_processor)
Tornado
It looks like Tornado’s RequestHandler class implements two hooks which can be used to open and close
connections when a request is handled.
from tornado.web import RequestHandler
db = SqliteDatabase('my_db.db')
class PeeweeRequestHandler(RequestHandler):
def prepare(self):
db.connect()
return super(PeeweeRequestHandler, self).prepare()
def on_finish(self):
if not db.is_closed():
db.close()
return super(PeeweeRequestHandler, self).on_finish()
In your app, instead of extending the default RequestHandler, now you can extend PeeweeRequestHandler.
Note that this does not address how to use peewee asynchronously with Tornado or another event loop.
Wheezy.web
The connection handling code can be placed in a middleware.
def peewee_middleware(request, following):
db.connect()
try:
response = following(request)
finally:
if not db.is_closed():
db.close()
return response
app = WSGIApplication(middleware=[
lambda x: peewee_middleware,
# ... other middlewares ...
])
Thanks to GitHub user @tuukkamustonen for submitting this code.
Falcon
The connection handling code can be placed in a middleware component.
import falcon
from peewee import *
database = SqliteDatabase('my_app.db')
class PeeweeConnectionMiddleware(object):
def process_request(self, req, resp):
database.connect()
def process_response(self, req, resp, resource):
if not database.is_closed():
database.close()
application = falcon.API(middleware=[
PeeweeConnectionMiddleware(),
# ... other middlewares ...
])
Pyramid
Set up a Request factory that handles database connection lifetime as follows:
from pyramid.request import Request
db = SqliteDatabase('pyramidapp.db')
class MyRequest(Request):
def __init__(self, *args, **kwargs):
super().__init__(*args, **kwargs)
db.connect()
self.add_finished_callback(self.finish)
def finish(self, request):
if not db.is_closed():
db.close()
In your application main() make sure MyRequest is used as request_factory:
def main(global_settings, **settings):
config = Configurator(settings=settings, ...)
config.set_request_factory(MyRequest)
CherryPy
See Publish/Subscribe pattern.
def _db_connect():
db.connect()
def _db_close():
if not db.is_closed():
db.close()
cherrypy.engine.subscribe('before_request', _db_connect)
cherrypy.engine.subscribe('after_request', _db_close)
Other frameworks
Don’t see your framework here? Please open a GitHub ticket and I’ll see about adding a section, or better
yet, submit a documentation pull-request.
Additional connection initialization
Peewee does a few basic things depending on your database to initialize a connection. For SQLite this
means registering custom user-defined functions, for Postgresql this means registering unicode support.
You may find it necessary to add additional initialization when a new connection is opened, however. For
example you may want to tell SQLite to enforce all foreign key constraints (off by default). To do this,
you can subclass the database and override the initialize_connection() method.
This method contains no implementation on the base database classes, so you do not need to call super()
with it.
Example turning on SQLite foreign keys:
class SqliteFKDatabase(SqliteDatabase):
def initialize_connection(self, conn):
self.execute_sql('PRAGMA foreign_keys=ON;')
Advanced Connection Management
Managing your database connections is as simple as calling connect() when you need to open a connection,
and close() when you are finished. In a web-app, you would typically connect when you receive a request,
and close the connection when you return a response. Because connection state is stored in a
thread-local, you do not need to worry about juggling connection objects – peewee will handle it for you.
In some situations, however, you may want to manage your connections more explicitly. Since peewee stores
the active connection in a threadlocal, this typically would mean that there could only ever be one
connection open per thread. For most applications this is desirable, but if you would like to manually
manage multiple connections you can create an ExecutionContext.
Execution contexts allow finer-grained control over managing multiple connections to the database. When
an execution context is initialized (either as a context manager or as a decorated function), a separate
connection will be used for the duration of the wrapped block. You can also choose whether to wrap the
block in a transaction.
Execution context examples:
with db.execution_context() as ctx:
# A new connection will be opened or, if using a connection pool,
# pulled from the pool of available connections. Additionally, a
# transaction will be started.
user = User.create(username='charlie')
# When the block ends, the transaction will be committed and the connection
# will be closed (or returned to the pool).
@db.execution_context(with_transaction=False)
def do_something(foo, bar):
# When this function is called, a separate connection is made and will
# be closed when the function returns.
If you are using the peewee connection pool, then the new connections used by the ExecutionContext will
be pulled from the pool of available connections and recycled appropriately.
Using multiple databases
With peewee you can use as many databases as you want. Each model can define it’s database by specifying
a Meta.database. What if you want to use the same model with multiple databases, though? Depending on
your use-case, peewee provides several options.
If you have a Master/Slave setup and want all writes to go to the master, but reads can go to any number
of replicated copies, check out the Read Slave extension.
For finer-grained control, check out the Using context manager / decorator. This allows you to specify
the database to use with a given list of models for the duration of the wrapped block.
Here is an example of how you might use the Using context manager:
master = PostgresqlDatabase('master')
read_replica = PostgresqlDatabase('replica')
class Data(Model):
value = IntegerField()
class Meta:
database = master
# By default all queries go to the master, since that is what
# is defined on our model.
for i in range(10):
Data.create(value=i)
# But what if we want to explicitly use the read replica?
with Using(read_replica, [Data]):
# Query is executed against the read replica.
Data.get(Data.value == 5)
# Since we did not specify this model in the list of overrides
# it will use whatever database it was defined with.
SomeOtherModel.get(SomeOtherModel.field == 3)
NOTE:
For simple master/slave configurations, check out the read_slaves extension. This extension ensures
writes are sent to the master database and reads occur from any of the listed read replicas.
Database Errors
The Python DB-API 2.0 spec describes several types of exceptions. Because most database drivers have
their own implementations of these exceptions, Peewee simplifies things by providing its own wrappers
around any implementation-specific exception classes. That way, you don’t need to worry about importing
any special exception classes, you can just use the ones from peewee:
• DatabaseError
• DataError
• IntegrityError
• InterfaceError
• InternalError
• NotSupportedError
• OperationalError
• ProgrammingError
NOTE:
All of these error classes extend PeeweeException.
Automatic Reconnect
Peewee provides very basic support for automatic reconnecting in the shortcuts module, through the use of
the RetryOperationalError mixin. This mixin will automatically reconnect to the database and retry any
queries that fail with an OperationalError. The query that failed will be retried only once, and if it
fails twice an exception will be raised.
Usage:
from peewee import *
from playhouse.shortcuts import RetryOperationalError
class MyRetryDB(RetryOperationalError, MySQLDatabase):
pass
db = MyRetryDB('my_app')
Logging queries
All queries are logged to the peewee namespace using the standard library logging module. Queries are
logged using the DEBUG level. If you’re interested in doing something with the queries, you can simply
register a handler.
# Print all queries to stderr.
import logging
logger = logging.getLogger('peewee')
logger.setLevel(logging.DEBUG)
logger.addHandler(logging.StreamHandler())
Generating skeleton code
For writing quick scripts, peewee comes with a helper script pskel which generates database connection
and model boilerplate code. If you find yourself frequently writing small programs, pskel can really save
you time.
To generate a script, you can simply run:
pskel User Tweet SomeModel AnotherModel > my_script.py
pskel will generate code to connect to an in-memory SQLite database, as well as blank model definitions
for the model names specified on the command line.
Here is a more complete example, which will use the PostgresqlExtDatabase with query logging enabled:
pskel -l -e postgres_ext -d my_database User Tweet > my_script.py
You can now fill in the model definitions and get to hacking!
Adding a new Database Driver
Peewee comes with built-in support for Postgres, MySQL and SQLite. These databases are very popular and
run the gamut from fast, embeddable databases to heavyweight servers suitable for large-scale
deployments. That being said, there are a ton of cool databases out there and adding support for your
database-of-choice should be really easy, provided the driver supports the DB-API 2.0 spec.
The db-api 2.0 spec should be familiar to you if you’ve used the standard library sqlite3 driver,
psycopg2 or the like. Peewee currently relies on a handful of parts:
• Connection.commit
• Connection.execute
• Connection.rollback
• Cursor.description
• Cursor.fetchone
These methods are generally wrapped up in higher-level abstractions and exposed by the Database, so even
if your driver doesn’t do these exactly you can still get a lot of mileage out of peewee. An example is
the apsw sqlite driver in the “playhouse” module.
The first thing is to provide a subclass of Database that will open a connection.
from peewee import Database
import foodb # Our fictional DB-API 2.0 driver.
class FooDatabase(Database):
def _connect(self, database, **kwargs):
return foodb.connect(database, **kwargs)
The Database provides a higher-level API and is responsible for executing queries, creating tables and
indexes, and introspecting the database to get lists of tables. The above implementation is the absolute
minimum needed, though some features will not work – for best results you will want to additionally add a
method for extracting a list of tables and indexes for a table from the database. We’ll pretend that
FooDB is a lot like MySQL and has special “SHOW” statements:
class FooDatabase(Database):
def _connect(self, database, **kwargs):
return foodb.connect(database, **kwargs)
def get_tables(self):
res = self.execute('SHOW TABLES;')
return [r[0] for r in res.fetchall()]
Other things the database handles that are not covered here include:
• last_insert_id() and rows_affected()
• interpolation and quote_char
• op_overrides for mapping operations such as “LIKE/ILIKE” to their database equivalent
Refer to the Database API reference or the source code. for details.
NOTE:
If your driver conforms to the DB-API 2.0 spec, there shouldn’t be much work needed to get up and
running.
Our new database can be used just like any of the other database subclasses:
from peewee import *
from foodb_ext import FooDatabase
db = FooDatabase('my_database', user='foo', password='secret')
class BaseModel(Model):
class Meta:
database = db
class Blog(BaseModel):
title = CharField()
contents = TextField()
pub_date = DateTimeField()
Models and Fields
Model classes, Field instances and model instances all map to database concepts:
┌────────────────┬─────────────────────────┐
│ Thing │ Corresponds to… │
├────────────────┼─────────────────────────┤
│ Model class │ Database table │
├────────────────┼─────────────────────────┤
│ Field instance │ Column on a table │
├────────────────┼─────────────────────────┤
│ Model instance │ Row in a database table │
└────────────────┴─────────────────────────┘
The following code shows the typical way you will define your database connection and model classes.
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, related_name='tweets')
message = TextField()
created_date = DateTimeField(default=datetime.datetime.now)
is_published = BooleanField(default=True)
1. 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.
2. Create a base model class which specifies our database.
class BaseModel(Model):
class Meta:
database = db
It is good practice to define a base model class which establishes the database connection. This
makes your code DRY as you will not have to specify the database for subsequent models.
Model configuration is kept namespaced in a special class called Meta. This convention is borrowed
from Django. 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.
3. Define a model class.
class User(BaseModel):
username = CharField(unique=True)
Model definition uses the declarative style seen in other popular ORMs like SQLAlchemy or Django.
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.
NOTE:
If you would like to start using peewee with an existing database, you can use pwiz to automatically
generate model definitions.
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 (i.e. varchar, int), and conversion between python data types and
underlying storage is handled transparently.
When creating a Model class, fields are defined as class attributes. This should look familiar to users
of the django framework. Here’s an example:
class User(Model):
username = CharField()
join_date = DateTimeField()
about_me = TextField()
There is one special type of field, ForeignKeyField, which allows you to represent foreign-key
relationships between models in an intuitive way:
class Message(Model):
user = ForeignKeyField(User, related_name='messages')
body = TextField()
send_date = DateTimeField()
This allows you to write code like the following:
>>> print some_message.user.username
Some User
>>> for message in some_user.messages:
... print message.body
some message
another message
yet another message
For full documentation on fields, see the Fields API notes
Field types table
──────────────────────────────────────────────────────────────────────
Field Type Sqlite Postgresql MySQL
──────────────────────────────────────────────────────────────────────
CharField varchar varchar varchar
──────────────────────────────────────────────────────────────────────
FixedCharField char char char
──────────────────────────────────────────────────────────────────────
TextField text text longtext
──────────────────────────────────────────────────────────────────────
DateTimeField datetime timestamp datetime
──────────────────────────────────────────────────────────────────────
IntegerField integer integer integer
──────────────────────────────────────────────────────────────────────
BooleanField integer boolean bool
──────────────────────────────────────────────────────────────────────
FloatField real real real
──────────────────────────────────────────────────────────────────────
DoubleField real double precision double precision
──────────────────────────────────────────────────────────────────────
BigIntegerField integer bigint bigint
──────────────────────────────────────────────────────────────────────
SmallIntegerField integer smallint smallint
──────────────────────────────────────────────────────────────────────
DecimalField decimal numeric numeric
──────────────────────────────────────────────────────────────────────
PrimaryKeyField integer serial integer
──────────────────────────────────────────────────────────────────────
ForeignKeyField integer integer integer
──────────────────────────────────────────────────────────────────────
DateField date date date
──────────────────────────────────────────────────────────────────────
TimeField time time time
──────────────────────────────────────────────────────────────────────
TimestampField integer integer integer
──────────────────────────────────────────────────────────────────────
BlobField blob bytea blob
──────────────────────────────────────────────────────────────────────
UUIDField text uuid varchar(40)
──────────────────────────────────────────────────────────────────────
BareField untyped not supported not supported
┌───────────────────┬──────────┬──────────────────┬──────────────────┐
│ │ │ │ │
NOTE: │ │ │ │ │
--
NOTE
If you find any bugs, odd behavior, or have an idea for a new feature please don’t hesitate to open an
issue on GitHub or contact me.
• genindex
• modindex
• search
AUTHOR
charles leifer
COPYRIGHT
charles leifer
2.10.2 Dec 21, 2017 PEEWEE(1)