Provided by: peewee_3.7.0+dfsg-1build1_all bug


       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

       · python 2.7+ and 3.4+ (developed with 3.6)

       · supports sqlite, mysql and postgresql

       · tons of extensions

       Peewee's source code hosted on GitHub.

       New to peewee? These may help:

       · Quickstart

       · Example twitter app

       · Models and fields

       · Querying

       · Relationships and joins


   Installing and Testing
       Most users will want to simply install the latest version, hosted on PyPI:

          pip install peewee

       Peewee comes with a couple C extensions that will be built if Cython is available.

       · Speedups, which includes miscellaneous functions re-implemented with Cython.

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

   Installing with git
       The  project  is  hosted  at and can be installed using

          git clone
          cd peewee
          python install

          On some systems you may need to use sudo python  install  to  install  peewee

       If you would like to build the SQLite extension in a git checkout, you can run:

          # Build the C extension and place shared libraries alongside other modules.
          python build_ext -i

   Running tests
       You can test your installation by running the test suite.


       You  can test specific features or specific database drivers using the script.
       To view the available test runner options, use:

          python --help

          To  run  tests  against  Postgres  or  MySQL  you  need  to  create  a  database  named
          "peewee_test". To test the Postgres extension module, you will also want to install the
          HStore extension in the postgres test database:

              -- install the hstore extension on the peewee_test postgres db.
              CREATE EXTENSION hstore;

   Optional dependencies
          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.  Since  the  generated C files are included with the
         package distribution, Cython is no longer required  to  use  the  C  extensions  (as  of

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

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

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

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

   Note on the SQLite extensions
       Peewee includes two SQLite-specific C extensions which  provide  additional  functionality
       and  improved  performance  for  SQLite  database  users. Peewee will attempt to determine
       ahead-of-time if SQLite3 is installed, and only build the SQLite extensions if the  SQLite
       shared-library is available on your system.

       If,  however, you receive errors like the following when attempting to install Peewee, you
       can explicitly disable the  compilation  of  the  SQLite  C  extensions  by  settings  the
       NO_SQLITE environment variable.

       Here is how to install Peewee with the SQLite extensions explicitly disabled:

          $ NO_SQLITE=1 python install

       This  document  presents  a  brief, high-level overview of Peewee's primary features. This
       guide will cover:

       · Model Definition

       · Storing data

       · Retrieving Data

          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. In the projects examples/
          folder you can find more self-contained Peewee examples, like a blog app.

       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:

                               │Object         │ 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()

              class Meta:
                  database = db # This model uses the "people.db" database.

          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  key
       relationships. This is simple with peewee:

          class Pet(Model):
              owner = ForeignKeyField(Person, backref='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.


       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

          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))
 # bob is now stored in the database
          # Returns: 1

          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))
          herb = Person.create(name='Herb', birthday=date(1950, 5, 5))

       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 L.'
  # Update grandma's name in the database.
          # Returns: 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
          # Returns: 1

          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

          herb_fido.owner = uncle_bob

   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 Select.get():

          grandma = == 'Grandma L.').get()

       We can also use the equivalent shorthand Model.get():

          grandma = Person.get( == 'Grandma L.')

   Lists of records
       Let's list all the people in the database:

          for person in

          # prints:
          # Bob
          # Grandma L.
          # Herb

       Let's list all the cats and their owner's name:

          query = == 'cat')
          for pet in query:

          # prints:
          # Kitty Bob
          # Mittens Jr Herb

          There is a big problem with the previous query: because we are accessing
          and  we did not select this relation 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.

          For  an  in-depth  guide  to  working  with  relationships  and  joins,  refer  to  the
          relationships documentation.

       We can avoid the extra queries by selecting both Pet and Person, and adding a join.

          query = (Pet
                   .select(Pet, Person)
                   .where(Pet.animal_type == 'cat'))

          for pet in query:

          # prints:
          # Kitty Bob
          # Mittens Jr Herb

       Let's get all the pets owned by Bob:

          for pet in == 'Bob'):

          # prints:
          # 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 == uncle_bob):

       Let's make sure these are sorted alphabetically by adding an order_by() clause:

          for pet in == uncle_bob).order_by(

          # prints:
          # Fido
          # Kitty

       Let's list all the people now, youngest to oldest:

          for person in
              print(, person.birthday)

          # prints:
          # Bob 1960-01-15
          # Herb 1950-05-05
          # Grandma L. 1935-03-01

   Combining filter expressions
       Peewee  supports  arbitrarily-nested  expressions. 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
                   .where((Person.birthday < d1940) | (Person.birthday > d1960)))

          for person in query:
              print(, person.birthday)

          # prints:
          # 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
                   .where(Person.birthday.between(d1940, d1960)))

          for person in query:
              print(, person.birthday)

          # prints:
          # Herb 1950-05-05

   Aggregates and Prefetch
       Now let's list all the people and how many pets they have:

          for person in
              print(, person.pets.count(), 'pets')

          # prints:
          # Bob 2 pets
          # Grandma L. 0 pets
          # Herb 1 pets

       Once again we've run into a classic example of N+1 query behavior.  In  this  case,  we're
       executing  an  additional  query  for every Person returned by the original SELECT! We can
       avoid this by performing a JOIN and using a SQL function to aggregate the results.

          query = (Person
                   .select(Person, fn.COUNT('pet_count'))
                   .join(Pet, JOIN.LEFT_OUTER)  # include people without pets.

          for person in query:
              # "pet_count" becomes an attribute on the returned model instances.
              print(, person.pet_count, 'pets')

          # prints:
          # Bob 2 pets
          # Grandma L. 0 pets
          # Herb 1 pets

       Now let's list all the people and the names of all their pets. As you  may  have  guessed,
       this could easily turn into another N+1 situation if we're not careful.

       Before  diving  into  the  code,  consider  how this example is different from the earlier
       example where we listed all the pets and their owner's name.  A  pet  can  only  have  one
       owner,  so  when  we performed the join from Pet to Person, there was always going to be a
       single match. The situation is different when we are joining from Person to Pet because  a
       person  may have zero pets or they may have several pets. Because we're using a relational
       databases, if we were to do a join from Person to Pet then every person with multiple pets
       would be repeated, once for each pet.

       It would look like this:

          query = (Person
                   .select(Person, Pet)
                   .join(Pet, JOIN.LEFT_OUTER)
          for person in query:
              # We need to check if they have a pet instance attached, since not all
              # people have pets.
              if hasattr(person, 'pet'):
                  print(, 'no pets')

          # prints:
          # Bob Fido
          # Bob Kitty
          # Grandma L. no pets
          # Herb Mittens Jr

       Usually  this  type  of  duplication  is  undesirable. To accommodate the more common (and
       intuitive) workflow of listing a person and attaching a list of that person's pets, we can
       use a special method called prefetch():

          query =
          for person in query:
              for pet in person.pets:
                  print('  *',

          # prints:
          # Bob
          #   * Kitty
          #   * Fido
          # Grandma L.
          # Herb
          #   * Mittens Jr

   SQL Functions
       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(, 1, 1)) == 'g'
          for person in

          # prints:
          # Grandma L.

   Closing the database
       We're done with our database, let's close the connection:


       This is just the basics! You can make your queries as complex  as  you  like.   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 >

   What next?
       That's it for the quickstart. If you want to  look  at  a  full  web-app,  check  out  the

   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, however it is not covered in this

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


       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/  For  a guide on structuring larger Flask apps with peewee, check
       out Structuring Flask Apps.

       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.

              This is a utility model that contains two foreign-keys to the User model and stores
              which users follow one another.

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

          # 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, backref='relationships')
              to_user = ForeignKeyField(User, backref='related_to')

              class Meta:
                  # `indexes` is a tuple of 2-tuples, where the 2-tuples are
                  # a tuple of column names to index and a boolean indicating
                  # whether the index is unique or not.
                  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.messages
          class Message(BaseModel):
              user = ForeignKeyField(User, backref='messages')
              content = TextField()
              pub_date = DateTimeField()

          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

       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():
              with database:
                  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()

          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, ensuring the  tables  are  created  in

          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.

   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)

          # Create a base-class all our models will inherit, which defines
          # the database we'll be using.
          class BaseModel(Model):
              class Meta:
                  database = database

       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:

          def before_request():

          def after_request(response):
              return response

          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

       · 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
                      .join(Relationship, on=Relationship.to_user)
                      .where(Relationship.from_user == self)

          def followers(self):
              return (User
                      .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

              with database.atomic():
                  # Attempt to create the user. If the username is taken, due to the
                  # unique constraint, the database will raise an IntegrityError.
                  user = User.create(

              # mark the user as being 'authenticated' by setting the session vars
              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)
              with database.atomic():
          except IntegrityError:

   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:

          The  subquery,  user.following(), by default would ordinarily select all the columns on
          the User model. Because we're using it as a  subquery,  peewee  will  only  select  the
          primary key.

          # python code
          user = get_current_user()
          messages = (Message

       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

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

            def get_object_or_404(model, *expressions):
                    return model.get(*expressions)
                except model.DoesNotExist:

          To avoid having to frequently copy/paste object_list()  or  get_object_or_404(),  these
          functions are included as part of the playhouse flask extension module.

              from playhouse.flask_utils import get_object_or_404, object_list

   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.

          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.

       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.

       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.

       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!

       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 on the mailing list,

       The  Peewee  Database object represents a connection to a database.  The Database class is
       instantiated with all the information needed to open a connection to a database, and  then
       can be used to:

       · Open and close connections.

       · Execute queries.

       · Manage transactions (and savepoints).

       · Introspect tables, columns, indexes, and constraints.

       Peewee  comes  with  support  for SQLite, MySQL and Postgres. Each database class provides
       some basic, database-specific configuration options.

          from peewee import *

          # SQLite database using WAL journal mode and 64MB cache.
          sqlite_db = SqliteDatabase('/path/to/app.db', pragmas={
              'journal_mode': 'wal',
              'cache_size': -1024 * 64})

          # Connect to a MySQL database on network.
          mysql_db = MySQLDatabase('my_app', user='app', password='db_password',
                                   host='', port=3316)

          # Connect to a Postgres database.
          pg_db = PostgresqlDatabase('my_app', user='postgres', password='secret',
                                     host='', port=5432)

       Peewee provides advanced support for SQLite and Postgres via  database-specific  extension
       modules.  To  use  the  extended-functionality,  import  the appropriate database-specific
       module and use the database class provided:

          from playhouse.sqlite_ext import SqliteExtDatabase

          # Use SQLite (will register a REGEXP function and set busy timeout to 3s).
          db = SqliteExtDatabase('/path/to/app.db', regexp_function=True, timeout=3,
                                 pragmas={'journal_mode': 'wal'})

          from playhouse.postgres_ext import PostgresqlExtDatabase

          # Use Postgres (and register hstore extension).
          db = PostgresqlExtDatabase('my_app', user='postgres', register_hstore=True)

       For more information on database extensions, see:

       · postgres_ext

       · sqlite_ext

       · sqlcipher_ext

       · apsw

       · sqliteq

   Initializing a Database
       The Database initialization  method  expects  the  name  of  the  database  as  the  first
       parameter.  Subsequent keyword arguments are passed to the underlying database driver when
       establishing the connection, allowing you to pass vendor-specific parameters easily.

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

          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

          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 a list  or  pragmas  or  any  other
       arbitrary sqlite3 parameters.

          sqlite_db = SqliteDatabase('my_app.db', pragmas={'journal_mode': 'wal'})

          class BaseModel(Model):
              """A base model that will use our Sqlite database."""
              class Meta:
                  database = sqlite_db

          class User(BaseModel):
              username = TextField()
              # etc, etc

       Peewee  includes  a  SQLite  extension module which provides many SQLite-specific features
       such as full-text search, json extension support, and much, much more. If you  would  like
       to  use  these  awesome  features, use the SqliteExtDatabase from the playhouse.sqlite_ext

          from playhouse.sqlite_ext import SqliteExtDatabase

          sqlite_db = SqliteExtDatabase('my_app.db', pragmas={
              'journal_mode': 'wal',  # WAL-mode.
              'cache_size': -64 * 1000,  # 64MB cache.
              'synchronous': 0})  # Let the OS manage syncing.

   PRAGMA statements
       SQLite allows run-time configuration of a number of parameters through  PRAGMA  statements
       (SQLite documentation).  These statements are typically run when a new database connection
       is created.  To run one or more PRAGMA statements against new connections, you can specify
       them as a dictionary or a list of 2-tuples containing the pragma name and value:

          db = SqliteDatabase('my_app.db', pragmas={
              'journal_mode': 'wal',
              'cache_size': 10000,  # 10000 pages, or ~40MB
              'foreign_keys': 1,  # Enforce foreign-key constraints

       PRAGMAs may also be configured dynamically using either the pragma() method or the special
       properties exposed on the SqliteDatabase object:

          # Set cache size to 64MB for *current connection*.
          db.pragma('cache_size', -1024 * 64)

          # Same as above.
          db.cache_size = -1024 * 64

          # Read the value of several pragmas:
          print('cache_size:', db.cache_size)
          print('foreign_keys:', db.foreign_keys)
          print('journal_mode:', db.journal_mode)
          print('page_size:', db.page_size)

          # Set foreign_keys pragma on current connection *AND* on all
          # connections opened subsequently.
          db.pragma('foreign_keys', 1, permanent=True)

          Pragmas set using the pragma() method, by default, do not persist after the  connection
          is  closed.  To  configure  a pragma to be run whenever a connection is opened, specify

          A full list of PRAGMA settings, their meaning and accepted values can be found  in  the
          SQLite documentation:

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

              │pragma                   │ recommended setting │ explanation              │
              │journal_mode             │ wal                 │ allow     readers    and │
              │                         │                     │ writers to co-exist      │
              │cache_size               │ -1 * data_size_kb   │ set page-cache  size  in │
              │                         │                     │ KiB, e.g. -32000 = 32MB  │
              │foreign_keys             │ 1                   │ enforce      foreign-key │
              │                         │                     │ constraints              │
              │ignore_check_constraints │ 0                   │ enforce            CHECK │
              │                         │                     │ constraints              │

              │synchronous              │ 0                   │ let OS handle fsync (use │
              │                         │                     │ with caution)            │

       Example database using the above options:

          db = SqliteDatabase('my_app.db', pragmas={
              'journal_mode': 'wal',
              'cache_size': -1 * 64000,  # 64MB
              'foreign_keys': 1,
              'ignore_check_constraints': 0,
              'synchronous': 0})

   User-defined functions
       SQLite can be extended with user-defined Python code. The  SqliteDatabase  class  supports
       three types of user-defined extensions:

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

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

       · Collations - which describe how to sort some value.

          For   even   more   extension   support,   see   SqliteExtDatabase,  which  is  in  the
          playhouse.sqlite_ext module.

       Example user-defined function:

          db = SqliteDatabase('analytics.db')

          from urllib.parse import urlparse

          def hostname(url):
              if url is not None:
                  return urlparse(url).netloc

          # Call this function in our code:
          # The following finds the most common hostnames of referrers by count:
          query = (PageView
                   .select(fn.hostname(PageView.referrer), fn.COUNT(

       Example user-defined aggregate:

          from hashlib import md5

          class MD5Checksum(object):
              def __init__(self):
                  self.checksum = md5()

              def step(self, value):

              def finalize(self):
                  return self.checksum.hexdigest()

          # Usage:
          # The following computes an aggregate MD5 checksum for files broken
          # up into chunks and stored in the database.
          query = (FileChunk
                   .select(FileChunk.filename, fn.MD5(
                   .order_by(FileChunk.filename, FileChunk.sequence))

       Example collation:

          def collate_reverse(s1, s2):
              # Case-insensitive reverse.
              s1, s2 = s1.lower(), s2.lower()
              return (s1 < s2) - (s1 > s2)  # Equivalent to -cmp(s1, s2)

          # To use this collation to sort books in reverse order...

          # Or...

       Example user-defined table-value  function  (see  TableFunction  and  table_function)  for
       additional details:

          from playhouse.sqlite_ext import TableFunction

          db = SqliteDatabase('my_app.db')

          class Series(TableFunction):
              columns = ['value']
              params = ['start', 'stop', 'step']

              def initialize(self, start=0, stop=None, step=1):
                  Table-functions declare an initialize() method, which is
                  called with whatever arguments the user has called the
                  function with.
                  self.start = self.current = start
                  self.stop = stop or float('Inf')
                  self.step = step

              def iterate(self, idx):
                  Iterate is called repeatedly by the SQLite database engine
                  until the required number of rows has been read **or** the
                  function raises a `StopIteration` signalling no more rows
                  are available.
                  if self.current > self.stop:
                      raise StopIteration

                  ret, self.current = self.current, self.current + self.step
                  return (ret,)

          # Usage:
          cursor = db.execute_sql('SELECT * FROM series(?, ?, ?)', (0, 5, 2))
          for value, in cursor:

          # Prints:
          # 0
          # 2
          # 4

       For more information, see:

       · SqliteDatabase.func()

       · SqliteDatabase.aggregate()

       · SqliteDatabase.collation()

       · SqliteDatabase.table_function()

       · For even more SQLite extensions, see sqlite_ext

   Set locking mode for transaction
       SQLite transactions can be opened in three different modes:

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

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

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

       Example specifying the locking mode:

          db = SqliteDatabase('app.db')

          with db.atomic('EXCLUSIVE'):

          def some_other_function():
              # This function is wrapped in an "IMMEDIATE" transaction.

       For more  information,  see  the  SQLite  locking  documentation.   To  learn  more  about
       transactions in Peewee, see the Managing Transactions documentation.

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

       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 Framework Integration section for examples of configuring common web frameworks to
       manage database connections.

   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.

   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 = PostgresqlDatabase(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:')
              database = PostgresqlDatabase('mega_production_db')

          # Configure our proxy to use the db we specified in config.

          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 Management
       To open a connection to a database, use the Database.connect() method:

          >>> db = SqliteDatabase(':memory:')  # In-memory SQLite database.
          >>> db.connect()

       If we try to call connect() on an already-open database, we get a OperationalError:

          >>> db.connect()
          Traceback (most recent call last):
            File "<stdin>", line 1, in <module>
            File "/home/charles/pypath/", line 2390, in connect
              raise OperationalError('Connection already opened.')
          peewee.OperationalError: Connection already opened.

       To prevent this exception from being raised, we can  call  connect()  with  an  additional
       argument, reuse_if_open:

          >>> db.close()  # Close connection.
          >>> db.connect()
          >>> db.connect(reuse_if_open=True)

       Note that the call to connect() returns False if the database connection was already open.

       To close a connection, use the Database.close() method:

          >>> db.close()

       Calling  close() on an already-closed connection will not result in an exception, but will
       return False:

          >>> db.connect()  # Open connection.
          >>> db.close()  # Close connection.
          >>> db.close()  # Connection already closed, returns False.

       You can test whether the database is closed using the Database.is_closed() method:

          >>> db.is_closed()

   A note of caution
       Although it is not necessary to explicitly  connect  to  the  database  before  using  it,
       managing  connections  explicitly  is  considered  a  best  practice.  For example, if the
       connection fails, the exception will be caught when the connection is being opened, rather
       than  some  arbitrary time later when a query is executed. Furthermore, if you are using a
       connection pool, it is necessary to call connect() and close() to ensure  connections  are
       recycled properly.

   Thread Safety
       Peewee  keeps  track of the connection state using thread-local storage, making the Peewee
       Database object safe to use  with  multiple  threads.  Each  thread  will  have  it's  own
       connection,  and as a result any given thread will only have a single connection open at a
       given time.

   Context managers
       The database object itself can be used as a context-manager, which opens a connection  for
       the  duration  of  the wrapped block of code. Additionally, a transaction is opened at the
       start of the wrapped block and committed before the connection is closed (unless an  error
       occurs, in which case the transaction is rolled back).

          >>> db.is_closed()
          >>> with db:
          ...     print(db.is_closed())  # db is open inside context manager.
          >>> db.is_closed()  # db is closed.

       If    you    want    to    manage    transactions    separately,    you    can   use   the
       Database.connection_context() context manager.

          >>> with db.connection_context():
          ...     # db connection is open.
          ...     pass
          >>> db.is_closed()  # db connection is closed.

       The connection_context() method can also be used as a decorator:

          def prepare_database():
              # DB connection will be managed by the decorator, which opens
              # a connection, calls function, and closes upon returning.
              db.create_tables(MODELS)  # Create schema.

   DB-API Connection Object
       To  obtain  a   reference   to   the   underlying   DB-API   2.0   connection,   use   the
       Database.connection()  method.  This  method  will  return  the  currently-open connection
       object, if one exists, otherwise it will open a new connection.

          >>> db.connection()
          <sqlite3.Connection object at 0x7f94e9362f10>

   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.

          from playhouse.pool import PooledPostgresqlExtDatabase

          db = PooledPostgresqlExtDatabase(

          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.

   Testing Peewee Applications
       When writing tests for an application that uses Peewee, it  may  be  desirable  to  use  a
       special  database  for  tests.  Another  common  practice  is to run tests against a clean
       database, which means ensuring tables are empty at the start of each test.

       To bind your models to a database at run-time, you can use the following methods:

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

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

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

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

       Depending  on  your  use-case,  one of these options may make more sense. For the examples
       below, I will use Model.bind().

       Example test-case setup:

          import unittest
          from my_app.models import EventLog, Relationship, Tweet, User

          MODELS = [User, Tweet, EventLog, Relationship]

          # use an in-memory SQLite for tests.
          test_db = SqliteDatabase(':memory:')

          class BaseTestCase(unittest.TestCase):
              def setUp(self):
                  # Bind model classes to test db. Since we have a complete list of
                  # all models, we do not need to recursively bind dependencies.
                  test_db.bind(MODELS, bind_refs=False, bind_backrefs=False)


              def tearDown(self):
                  # Not strictly necessary since SQLite in-memory databases only live
                  # for the duration of the connection, and in the next step we close
                  # the connection...but a good practice all the same.

                  # Close connection to db.

                  # If we wanted, we could re-bind the models to their original
                  # database here. But for tests this is probably not necessary.

       As an aside, and speaking from experience, I recommend testing your application using  the
       same  database  backend  you use in production, so as to avoid any potential compatibility

       If you'd like to see some more examples of how  to  run  tests  using  Peewee,  check  out
       Peewee's own test-suite.

   Async with Gevent
       gevent  is  recommended  for  doing  asynchronous  I/O with Postgresql or MySQL. Reasons I
       prefer gevent:

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

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

       · Gevent works with both Python 2 and Python 3.

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

       Besides monkey-patching socket, no special steps are required if you are using MySQL  with
       a  pure Python driver like pymysql or are using mysql-connector in pure-python mode. MySQL
       drivers written in C will require special configuration which is beyond the scope of  this

       For  Postgres and psycopg2, which is a C extension, you can use the following code snippet
       to register event hooks that will make your connection async:

          from gevent.socket import wait_read, wait_write
          from psycopg2 import extensions

          # Call this function after monkey-patching socket (etc).
          def patch_psycopg2():

          def _psycopg2_gevent_callback(conn, timeout=None):
              while True:
                  state = conn.poll()
                  if state == extensions.POLL_OK:
                  elif state == extensions.POLL_READ:
                      wait_read(conn.fileno(), timeout=timeout)
                  elif state == extensions.POLL_WRITE:
                      wait_write(conn.fileno(), timeout=timeout)
                      raise ValueError('poll() returned unexpected result')

       SQLite, because it is embedded in the Python application itself, does not  do  any  socket
       operations  that would be a candidate for non-blocking. Async has no effect one way or the
       other on SQLite databases.

   Framework Integration
       For web applications, it is common to open a connection when a request is received, and to
       close  the  connection when the response is delivered. 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.

          Applications  that  receive lots of traffic may benefit from using a connection pool to
          mitigate the cost of setting up and tearing down connections on every request.

       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.
          def _db_connect():

          # This hook ensures that the connection is closed when we've finished
          # processing the request.
          def _db_close(exc):
              if not database.is_closed():

       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:

          from my_blog.db import database  # Import the peewee database instance.

          class PeeweeConnectionMiddleware(object):
              def process_request(self, request):

              def process_response(self, request, response):
                  if not database.is_closed():
                  return response

       To ensure this middleware gets executed, add it to your settings module:

              # Our custom middleware appears first in the list.

              # These are the default Django 1.7 middlewares. Yours may differ,
              # but the important this is that our Peewee middleware comes first.

          # ... other Django settings ...

       I  haven't  used  bottle  myself, but looking at the documentation I believe the following
       code should ensure the database connections are properly managed:

          from bottle import hook  #, route, etc, etc.
          from peewee import *

          db = SqliteDatabase('my-bottle-app.db')

          def _connect_db():

          def _close_db():
              if not db.is_closed():

          # Rest of your bottle app goes here.
       See the documentation for application processors.

          db = SqliteDatabase('my_webpy_app.db')

          def connection_processor(handler):
                  return handler()
                  if not db.is_closed():


       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):
                  return super(PeeweeRequestHandler, self).prepare()

              def on_finish(self):
                  if not db.is_closed():
                  return super(PeeweeRequestHandler, self).on_finish()

       In  your  app,  instead  of  extending  the  default  RequestHandler,  now  you can extend

       Note that this does not address how to use peewee asynchronously with Tornado  or  another
       event loop.

       The connection handling code can be placed in a middleware.

          def peewee_middleware(request, following):
                  response = following(request)
                  if not db.is_closed():
              return response

          app = WSGIApplication(middleware=[
              lambda x: peewee_middleware,
              # ... other middlewares ...

       Thanks to GitHub user @tuukkamustonen for submitting this code.

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

              def process_response(self, req, resp, resource):
                  if not database.is_closed():

          application = falcon.API(middleware=[
              # ... other middlewares ...

       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)

              def finish(self, request):
                  if not db.is_closed():

       In your application main() make sure MyRequest is used as request_factory:

          def main(global_settings, **settings):
              config = Configurator(settings=settings, ...)

       See Publish/Subscribe pattern.

          def _db_connect():

          def _db_close():
              if not db.is_closed():

          cherrypy.engine.subscribe('before_request', _db_connect)
          cherrypy.engine.subscribe('after_request', _db_close)

       In  Sanic,  the  connection  handling  code  can  be  placed  in  the request and response
       middleware sanic middleware.

          async def handle_request(request):

          async def handle_response(request, response):
              if not db.is_closed():

   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.

   Executing Queries
       SQL  queries  will typically be executed by calling execute() on a query constructed using
       the query-builder APIs (or by simply iterating over a query object in the case of a Select
       query).   For   cases   where   you  wish  to  execute  SQL  directly,  you  can  use  the
       Database.execute_sql() method.

          db = SqliteDatabase('my_app.db')

          # Example of executing a simple query and ignoring the results.
          db.execute_sql("ATTACH DATABASE ':memory:' AS cache;")

          # Example of iterating over the results of a query using the cursor.
          cursor = db.execute_sql('SELECT * FROM users WHERE status = ?', (ACTIVE,))
          for row in cursor.fetchall():
              # Do something with row, which is a tuple containing column data.

   Managing Transactions
       Peewee provides several interfaces for working with transactions. The most general is  the
       Database.atomic() method, which also supports nested transactions. atomic() blocks will be
       run in a transaction or savepoint, depending on the level of nesting.

       If an exception occurs in a wrapped  block,  the  current  transaction/savepoint  will  be
       rolled back. Otherwise the statements will be committed at the end of the wrapped block.

          While  inside  a  block  wrapped  by  the  atomic() context manager, you can explicitly
          rollback   or   commit   at   any   point   by   calling   Transaction.rollback()    or
          Transaction.commit().  When  you  do  this  inside  a  wrapped  block  of  code,  a new
          transaction will be started automatically.

              with db.atomic() as transaction:  # Opens new transaction.
                  except ErrorSavingData:
                      # Because this block of code is wrapped with "atomic", a
                      # new transaction will begin automatically after the call
                      # to rollback().
                      error_saving = True

                  # Note: no need to call commit. Since this marks the end of the
                  # wrapped block of code, the `atomic` context manager will
                  # automatically call commit for us.

          atomic() can be used as either a context manager or a decorator.

   Context manager
       Using atomic as context manager:

          db = SqliteDatabase(':memory:')

          with db.atomic() as txn:
              # This is the outer-most level, so this block corresponds to
              # a transaction.

              with db.atomic() as nested_txn:
                  # This block corresponds to a savepoint.

                  # This will roll back the above create() query.


          # When the block ends, the transaction is committed (assuming no error
          # occurs). At that point there will be two users, "charlie" and "mickey".

       You can use the atomic method to perform get or create operations as well:

              with db.atomic():
                  user = User.create(username=username)
              return 'Success'
          except peewee.IntegrityError:
              return 'Failure: %s is already in use.' % username

       Using atomic as a decorator:

          def create_user(username):
              # This statement will run in a transaction. If the caller is already
              # running in an `atomic` block, then a savepoint will be used instead.
              return User.create(username=username)


   Nesting Transactions
       atomic() provides transparent nesting of transactions. When using atomic(), the outer-most
       call will be wrapped in a transaction, and any nested calls will use savepoints.

          with db.atomic() as txn:

              with db.atomic() as nested_txn:

       Peewee  supports  nested transactions through the use of savepoints (for more information,
       see savepoint()).

   Explicit transaction
       If you wish to explicitly run code in a  transaction,  you  can  use  transaction().  Like
       atomic(), transaction() can be used as a context manager or as a decorator.

       If an exception occurs in a wrapped block, the transaction will be rolled back.  Otherwise
       the statements will be committed at the end of the wrapped block.

          db = SqliteDatabase(':memory:')

          with db.transaction() as txn:
              # Delete the user and their associated tweets.

       Transactions can be explicitly committed or rolled-back within  the  wrapped  block.  When
       this happens, a new transaction will be started.

          with db.transaction() as txn:
              txn.commit()  # Changes are saved and a new transaction begins.

              # Roll back. "huey" will not be saved, but since "mickey" was already
              # committed, that row will remain in the database.

          with db.transaction() as txn:
              # Roll back changes, which removes "whiskers".

              # Create a new row for "mr. whiskers" which will be implicitly committed
              # at the end of the `with` block.
              User.create(username='mr. whiskers')

          If  you  attempt  to  nest  transactions  with  peewee  using the transaction() context
          manager, only the outer-most transaction will be used. However if an  exception  occurs
          in  a  nested  block,  this  can  lead  to  unpredictable  behavior,  so it is strongly
          recommended that you use atomic().

   Explicit Savepoints
       Just as you can explicitly create transactions, you can also explicitly create  savepoints
       using  the  savepoint()  method.  Savepoints  must  occur within a transaction, but can be
       nested arbitrarily deep.

          with db.transaction() as txn:
              with db.savepoint() as sp:

              with db.savepoint() as sp2:
                  sp2.rollback()  # "zaizee" will not be saved, but "mickey" will be.

          If you manually commit or roll back a savepoint, a new savepoint will not automatically
          be  created.  This  differs  from the behavior of transaction, which will automatically
          open a new transaction after manual commit/rollback.

   Autocommit Mode
       By default, Peewee operates in autocommit mode, such that any statements executed  outside
       of  a  transaction  are  run in their own transaction. To group multiple statements into a
       transaction, Peewee provides the atomic() context-manager/decorator. This should cover all
       use-cases,  but in the unlikely event you want to temporarily disable Peewee's transaction
       management completely, you can use the Database.manual_commit() context-manager/decorator.

       Here is how you might emulate the behavior of the transaction() context manager:

          with db.manual_commit():
              db.begin()  # Have to begin transaction explicitly.
                  db.rollback()  # Rollback! An error occurred.
                      db.commit()  # Commit changes.

       Again -- I don't anticipate anyone needing this, but it's here just in case.

   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

          All of these error classes extend PeeweeException.

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

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

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

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

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

       Refer to the Database API reference or the source code. for details.

          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, backref='tweets')
              message = TextField()
              created_date = DateTimeField(
              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

       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.

          If you would like to start using peewee with an existing database, you can use pwiz  to
          automatically generate model definitions.

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

       In the above example, because none of the fields are initialized with primary_key=True, an
       auto-incrementing primary key will automatically be created and named "id".

       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, backref='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

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

       For full documentation on fields, see the Fields API notes

   Field types table
               │Field Type        │ Sqlite        │ Postgresql       │ MySQL            │
               │IntegerField      │ integer       │ integer          │ integer          │
               │BigIntegerField   │ integer       │ bigint           │ bigint           │
               │SmallIntegerField │ integer       │ smallint         │ smallint         │
               │AutoField         │ integer       │ serial           │ integer          │
               │BigAutoField      │ integer       │ bigserial        │ bigint           │
               │IdentityField     │ not supported │ int identity     │ not supported    │
               │FloatField        │ real          │ real             │ real             │
               │DoubleField       │ real          │ double precision │ double precision │

               │DecimalField      │ decimal       │ numeric          │ numeric          │
               │CharField         │ varchar       │ varchar          │ varchar          │
               │FixedCharField    │ char          │ char             │ char             │
               │TextField         │ text          │ text             │ longtext         │
               │BlobField         │ blob          │ bytea            │ blob             │
               │BitField          │ integer       │ bigint           │ bigint           │
               │BigBitField       │ blob          │ bytea            │ blob             │
               │UUIDField         │ text          │ uuid             │ varchar(40)      │
               │BinaryUUIDField   │ blob          │ bytea            │ varbinary(16)    │
               │DateTimeField     │ datetime      │ timestamp        │ datetime         │
               │DateField         │ date          │ date             │ date             │
               │TimeField         │ time          │ time             │ time             │
               │TimestampField    │ integer       │ integer          │ integer          │
               │IPField           │ integer       │ bigint           │ bigint           │
               │BooleanField      │ integer       │ boolean          │ bool             │
               │BareField         │ untyped       │ not supported    │ not supported    │
               │ForeignKeyField   │ integer       │ integer          │ integer          │

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

          · Creating a custom field

          · Database, particularly the fields parameter.

   Field initialization arguments
       Parameters accepted by all field types and their default values:

       · null = False -- allow null values

       · index = False -- create an index on this column

       · unique = False -- create a unique index  on  this  column.  See  also  adding  composite

       · column_name = None -- explicitly specify the column name in the database.

       · default = None -- any value or callable to use as a default for uninitialized models

       · primary_key = False -- primary key for the table

       · constraints = None - one or more constraints, e.g. [Check('price > 0')]

       · sequence = None -- sequence name (if backend supports it)

       · collation = None -- collation to use for ordering the field / index

       · unindexed = False -- indicate field on virtual table should be unindexed (SQLite-only)

       · choices = None -- optional iterable containing 2-tuples of value, display

       · help_text = None -- string representing any helpful text for this field

       · verbose_name = None -- string representing the "user-friendly" name of this field

   Some fields take special parameters...
                           Field type        Special Parameters
                           CharField         max_length
                           FixedCharField    max_length
                           DateTimeField     formats
                           DateField         formats
                           TimeField         formats
                           TimestampField    resolution, utc
                           DecimalField      max_digits,      decimal_places,
                                             auto_round, rounding
                           ForeignKeyField   model,      field,      backref,
                                             on_delete, on_update, deferrable
                           BareField         adapt
                          │                │                                  │
       NOTE:              │                │                                  │
Binary file (standard input) matches