Database

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.
  • Model integration

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

  1. from peewee import *
  3. # SQLite database using WAL journal mode and 64MB cache.
  4. sqlite_db = SqliteDatabase('/path/to/app.db', pragmas=(
  5.     ('journal_mode', 'wal'),
  6.     ('cache_size', -1024 * 64)))
  8. # Connect to a MySQL database on network.
  9. mysql_db = MySQLDatabase('my_app', user='app', password='db_password',
  10.                          host='10.1.0.8', port=3316)
  12. # Connect to a Postgres database.
  13. pg_db = PostgresqlDatabase('my_app', user='postgres', password='secret',
  14.                            host='10.1.0.9', 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:

  1. from playhouse.sqlite_ext import SqliteExtDatabase
  3. # Use SQLite (will register a REGEXP function and set busy timeout to 3s).
  4. db = SqliteExtDatabase('/path/to/app.db', regexp_function=True, timeout=3,
  5.                        pragmas=(('journal_mode', 'wal'),))
  8. from playhouse.postgres_ext import PostgresqlExtDatabase
  10. # Use Postgres (and register hstore extension).
  11. db = PostgresqlExtDatabase('my_app', user='postgres', register_hstore=True)

For more information on database extensions, see:

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:

  1. db = PostgresqlDatabase(
  2.     'database_name',  # Required by Peewee.
  3.     user='postgres',  # Will be passed directly to psycopg2.
  4.     password='secret',  # Ditto.
  5.     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:

  1. db = MySQLDatabase('database_name', user='www-data', charset='utf8mb4')

Consult your database driver’s documentation for the available parameters:

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.

  1. psql_db = PostgresqlDatabase('my_database', user='postgres')
  3. class BaseModel(Model):
  4.     """A base model that will use our Postgresql database"""
  5.     class Meta:
  6.         database = psql_db
  8. class User(BaseModel):
  9.     username = CharField()

The Playhouse, extensions to Peewee contains a Postgresql extension module which provides many postgres-specific features such as:

If you would like to use these awesome features, use the PostgresqlExtDatabase from the playhouse.postgres_ext module:

  1. from playhouse.postgres_ext import PostgresqlExtDatabase
  3. 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.

  1. sqlite_db = SqliteDatabase('my_app.db')
  3. class BaseModel(Model):
  4.     """A base model that will use our Sqlite database."""
  5.     class Meta:
  6.         database = sqlite_db
  8. class User(BaseModel):
  9.     username = CharField()
  10.     # etc, etc

The Playhouse, extensions to Peewee contains 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 module:

  1. from playhouse.sqlite_ext import SqliteExtDatabase
  3. sqlite_db = SqliteExtDatabase('my_app.db', journal_mode='WAL')

PRAGMA statements

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:

  1. db = SqliteDatabase('my_app.db', pragmas=(
  2.     ('journal_mode', 'WAL'),
  3.     ('cache_size', 10000),
  4.     ('mmap_size', 1024 * 1024 * 32),
  5. ))

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

  1. # Set cache size to 64MB for current connection.
  2. db.pragma('cache_size', -1024 * 64)
  4. # Same as above.
  5. db.cache_size = -1024 * 64
  7. # Read the value of several pragmas:
  8. print('cache_size:', db.cache_size)
  9. print('foreign_keys:', db.foreign_keys)
  10. print('journal_mode:', db.journal_mode)
  11. print('page_size:', db.page_size)
  13. # Set foreign_keys pragma on current connection *AND* on all
  14. # connections opened subsequently.
  15. db.pragma('foreign_keys', 1, permanent=True)

Attention

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 permanent=True.

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.

Note

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

Example user-defined function:

  1. db = SqliteDatabase('analytics.db')
  3. from urllib.parse import urlparse
  5. @db.func('hostname')
  6. def hostname(url):
  7.     if url is not None:
  8.         return urlparse(url).netloc
  10. # Call this function in our code:
  11. # The following finds the most common hostnames of referrers by count:
  12. query = (PageView
  13.          .select(fn.hostname(PageView.referrer), fn.COUNT(PageView.id))
  14.          .group_by(fn.hostname(PageView.referrer))
  15.          .order_by(fn.COUNT(PageView.id).desc()))

Example user-defined aggregate:

  1. from hashlib import md5
  3. @db.aggregate('md5')
  4. class MD5Checksum(object):
  5.     def __init__(self):
  6.         self.checksum = md5()
  8.     def step(self, value):
  9.         self.checksum.update(value.encode('utf-8'))
  11.     def finalize(self):
  12.         return self.checksum.hexdigest()
  14. # Usage:
  15. # The following computes an aggregate MD5 checksum for files broken
  16. # up into chunks and stored in the database.
  17. query = (FileChunk
  18.          .select(FileChunk.filename, fn.MD5(FileChunk.data))
  19.          .group_by(FileChunk.filename)
  20.          .order_by(FileChunk.filename, FileChunk.sequence))

Example collation:

  1. @db.collation('ireverse')
  2. def collate_reverse(s1, s2):
  3.     # Case-insensitive reverse.
  4.     s1, s2 = s1.lower(), s2.lower()
  5.     return (s1 < s2) - (s1 > s2)  # Equivalent to -cmp(s1, s2)
  7. # To use this collation to sort books in reverse order...
  8. Book.select().order_by(collate_reverse.collation(Book.title))
  10. # Or...
  11. Book.select().order_by(Book.title.asc(collation='reverse'))

For more information, see:

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:

  1. db = SqliteDatabase('app.db')
  3. with db.atomic('EXCLUSIVE'):
  4.     do_something()
  7. @db.atomic('IMMEDIATE')
  8. def some_other_function():
  9.     # This function is wrapped in an "IMMEDIATE" transaction.
  10.     do_something_else()

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

APSW, an Advanced SQLite Driver

Peewee also comes with an alternate SQLite database that uses apsw, an advanced sqlite driver, 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:

  1. from playhouse.apsw_ext import APSWDatabase
  3. 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).

  1. mysql_db = MySQLDatabase('my_database')
  3. class BaseModel(Model):
  4.     """A base model that will use our MySQL database"""
  5.     class Meta:
  6.         database = mysql_db
  8. class User(BaseModel):
  9.     username = CharField()
  10.     # 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.

Connecting using a Database URL

The playhouse module Database URL provides a helper connect() function that accepts a database URL and returns a Database instance.

Example code:

  1. import os
  3. from peewee import *
  4. from playhouse.db_url import connect
  6. # Connect to the database URL defined in the environment, falling
  7. # back to a local Sqlite database if no database URL is specified.
  8. db = connect(os.environ.get('DATABASE') or 'sqlite:///default.db')
  10. class BaseModel(Model):
  11.     class Meta:
  12.         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.

  1. database = SqliteDatabase(None)  # Un-initialized database.
  3. class SomeModel(Model):
  4.     class Meta:
  5.         database = database

If you try to connect or issue any queries while your database is uninitialized you will get an exception:

  1. >>> database.connect()
  2. 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:

  1. database_name = raw_input('What is the name of the db? ')
  2. 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:

  1. database_proxy = Proxy()  # Create a proxy for our db.
  3. class BaseModel(Model):
  4.     class Meta:
  5.         database = database_proxy  # Use proxy for our DB.
  7. class User(BaseModel):
  8.     username = CharField()
  10. # Based on configuration, use a different database.
  11. if app.config['DEBUG']:
  12.     database = SqliteDatabase('local.db')
  13. elif app.config['TESTING']:
  14.     database = SqliteDatabase(':memory:')
  15. else:
  16.     database = PostgresqlDatabase('mega_production_db')
  18. # Configure our proxy to use the db we specified in config.
  19. 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 Management

To open a connection to a database, use the Database.connect() method:

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

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

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

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

  1. >>> db.close()  # Close connection.
  2. True
  3. >>> db.connect()
  4. True
  5. >>> db.connect(reuse_if_open=True)
  6. False

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

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

  1. >>> db.close()
  2. True

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

  1. >>> db.connect()  # Open connection.
  2. True
  3. >>> db.close()  # Close connection.
  4. True
  5. >>> db.close()  # Connection already closed, returns False.
  6. False

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

  1. >>> db.is_closed()
  2. True

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

  1. >>> db.is_closed()
  2. True
  3. >>> with db:
  4. ...     print(db.is_closed())  # db is open inside context manager.
  5. ...
  6. False
  7. >>> db.is_closed()  # db is closed.
  8. True

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

  1. >>> with db.connection_context():
  2. ...     # db connection is open.
  3. ...     pass
  4. ...
  5. >>> db.is_closed()  # db connection is closed.
  6. True

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

  1. @db.connection_context()
  2. def prepare_database():
  3.     # DB connection will be managed by the decorator, which opens
  4.     # a connection, calls function, and closes upon returning.
  5.     db.create_tables(MODELS)  # Create schema.
  6.     load_fixture_data(db)

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.

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

Connection Pooling

Connection pooling is provided by the pool module, included in the Playhouse, extensions to Peewee extensions library. The pool supports:

  • Timeout after which connections will be recycled.
  • Upper bound on the number of open connections.
  1. from playhouse.pool import PooledPostgresqlExtDatabase
  3. db = PooledPostgresqlExtDatabase(
  4.     'my_database',
  5.     max_connections=8,
  6.     stale_timeout=300,
  7.     user='postgres')
  9. class BaseModel(Model):
  10.     class Meta:
  11.         database = db

The following pooled database classes are available:

For an in-depth discussion of peewee’s connection pool, see the Connection pool section of the Playhouse, extensions to Peewee documentation.

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.

Note

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

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.

  1. from flask import Flask
  2. from peewee import *
  4. database = SqliteDatabase('my_app.db')
  5. app = Flask(__name__)
  7. # This hook ensures that a connection is opened to handle any queries
  8. # generated by the request.
  9. @app.before_request
  10. def _db_connect():
  11.     database.connect()
  13. # This hook ensures that the connection is closed when we've finished
  14. # processing the request.
  15. @app.teardown_request
  16. def _db_close(exc):
  17.     if not database.is_closed():
  18.         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:

  1. # middleware.py
  2. from my_blog.db import database  # Import the peewee database instance.
  5. class PeeweeConnectionMiddleware(object):
  6.     def process_request(self, request):
  7.         database.connect()
  9.     def process_response(self, request, response):
  10.         if not database.is_closed():
  11.             database.close()
  12.         return response

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

  1. # settings.py
  2. MIDDLEWARE_CLASSES = (
  3.     # Our custom middleware appears first in the list.
  4.     'my_blog.middleware.PeeweeConnectionMiddleware',
  6.     # These are the default Django 1.7 middlewares. Yours may differ,
  7.     # but the important this is that our Peewee middleware comes first.
  8.     'django.middleware.common.CommonMiddleware',
  9.     'django.contrib.sessions.middleware.SessionMiddleware',
  10.     'django.middleware.csrf.CsrfViewMiddleware',
  11.     'django.contrib.auth.middleware.AuthenticationMiddleware',
  12.     'django.contrib.messages.middleware.MessageMiddleware',
  13. )
  15. # ... 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:

  1. # app.py
  2. from bottle import hook  #, route, etc, etc.
  3. from peewee import *
  5. db = SqliteDatabase('my-bottle-app.db')
  7. @hook('before_request')
  8. def _connect_db():
  9.     db.connect()
  11. @hook('after_request')
  12. def _close_db():
  13.     if not db.is_closed():
  14.         db.close()
  16. # Rest of your bottle app goes here.

Web.py

See the documentation for application processors.

  1. db = SqliteDatabase('my_webpy_app.db')
  3. def connection_processor(handler):
  4.     db.connect()
  5.     try:
  6.         return handler()
  7.     finally:
  8.         if not db.is_closed():
  9.             db.close()
  11. 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.

  1. from tornado.web import RequestHandler
  3. db = SqliteDatabase('my_db.db')
  5. class PeeweeRequestHandler(RequestHandler):
  6.     def prepare(self):
  7.         db.connect()
  8.         return super(PeeweeRequestHandler, self).prepare()
  10.     def on_finish(self):
  11.         if not db.is_closed():
  12.             db.close()
  13.         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.

  1. def peewee_middleware(request, following):
  2.     db.connect()
  3.     try:
  4.         response = following(request)
  5.     finally:
  6.         if not db.is_closed():
  7.             db.close()
  8.     return response
  10. app = WSGIApplication(middleware=[
  11.     lambda x: peewee_middleware,
  12.     # ... other middlewares ...
  13. ])

Thanks to GitHub user @tuukkamustonen for submitting this code.

Falcon

The connection handling code can be placed in a middleware component.

  1. import falcon
  2. from peewee import *
  4. database = SqliteDatabase('my_app.db')
  6. class PeeweeConnectionMiddleware(object):
  7.     def process_request(self, req, resp):
  8.         database.connect()
  10.     def process_response(self, req, resp, resource):
  11.         if not database.is_closed():
  12.             database.close()
  14. application = falcon.API(middleware=[
  15.     PeeweeConnectionMiddleware(),
  16.     # ... other middlewares ...
  17. ])

Pyramid

Set up a Request factory that handles database connection lifetime as follows:

  1. from pyramid.request import Request
  3. db = SqliteDatabase('pyramidapp.db')
  5. class MyRequest(Request):
  6.     def __init__(self, *args, **kwargs):
  7.         super().__init__(*args, **kwargs)
  8.         db.connect()
  9.         self.add_finished_callback(self.finish)
  11.     def finish(self, request):
  12.         if not db.is_closed():
  13.             db.close()

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

  1. def main(global_settings, **settings):
  2.     config = Configurator(settings=settings, ...)
  3.     config.set_request_factory(MyRequest)

CherryPy

See Publish/Subscribe pattern.

  1. def _db_connect():
  2.     db.connect()
  4. def _db_close():
  5.     if not db.is_closed():
  6.         db.close()
  8. cherrypy.engine.subscribe('before_request', _db_connect)
  9. 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.

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.

  1. db = SqliteDatabase('my_app.db')
  2. db.connect()
  4. # Example of executing a simple query and ignoring the results.
  5. db.execute_sql("ATTACH DATABASE ':memory:' AS cache;")
  7. # Example of iterating over the results of a query using the cursor.
  8. cursor = db.execute_sql('SELECT * FROM users WHERE status = ?', (ACTIVE,))
  9. for row in cursor.fetchall():
  10.     # Do something with row, which is a tuple containing column data.
  11.     pass

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.

Note

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.

  1. with db.atomic() as transaction:  # Opens new transaction.
  2.     try:
  3.         save_some_objects()
  4.     except ErrorSavingData:
  5.         # Because this block of code is wrapped with "atomic", a
  6.         # new transaction will begin automatically after the call
  7.         # to rollback().
  8.         transaction.rollback()
  9.         error_saving = True
  11.     create_report(error_saving=error_saving)
  12.     # Note: no need to call commit. Since this marks the end of the
  13.     # wrapped block of code, the `atomic` context manager will
  14.     # automatically call commit for us.

Note

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

Context manager

Using atomic as context manager:

  1. db = SqliteDatabase(':memory:')
  3. with db.atomic() as txn:
  4.     # This is the outer-most level, so this block corresponds to
  5.     # a transaction.
  6.     User.create(username='charlie')
  8.     with db.atomic() as nested_txn:
  9.         # This block corresponds to a savepoint.
  10.         User.create(username='huey')
  12.         # This will roll back the above create() query.
  13.         nested_txn.rollback()
  15.     User.create(username='mickey')
  17. # When the block ends, the transaction is committed (assuming no error
  18. # 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:

  1. try:
  2.     with db.atomic():
  3.         user = User.create(username=username)
  4.     return 'Success'
  5. except peewee.IntegrityError:
  6.     return 'Failure: %s is already in use.' % username

Decorator

Using atomic as a decorator:

  1. @db.atomic()
  2. def create_user(username):
  3.     # This statement will run in a transaction. If the caller is already
  4.     # running in an `atomic` block, then a savepoint will be used instead.
  5.     return User.create(username=username)
  7. create_user('charlie')

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.

  1. with db.atomic() as txn:
  2.     perform_operation()
  4.     with db.atomic() as nested_txn:
  5.         perform_another_operation()

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.

  1. db = SqliteDatabase(':memory:')
  3. with db.transaction() as txn:
  4.     # Delete the user and their associated tweets.
  5.     user.delete_instance(recursive=True)

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

  1. with db.transaction() as txn:
  2.     User.create(username='mickey')
  3.     txn.commit()  # Changes are saved and a new transaction begins.
  4.     User.create(username='huey')
  6.     # Roll back. "huey" will not be saved, but since "mickey" was already
  7.     # committed, that row will remain in the database.
  8.     txn.rollback()
  10. with db.transaction() as txn:
  11.     User.create(username='whiskers')
  12.     # Roll back changes, which removes "whiskers".
  13.     txn.rollback()
  15.     # Create a new row for "mr. whiskers" which will be implicitly committed
  16.     # at the end of the `with` block.
  17.     User.create(username='mr. whiskers')

Note

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.

  1. with db.transaction() as txn:
  2.     with db.savepoint() as sp:
  3.         User.create(username='mickey')
  5.     with db.savepoint() as sp2:
  6.         User.create(username='zaizee')
  7.         sp2.rollback()  # "zaizee" will not be saved, but "mickey" will be.

Warning

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:

  1. with db.manual_commit():
  2.     db.begin()  # Have to begin transaction explicitly.
  3.     try:
  4.         user.delete_instance(recursive=True)
  5.     except:
  6.         db.rollback()  # Rollback! An error occurred.
  7.         raise
  8.     else:
  9.         try:
  10.             db.commit()  # Commit changes.
  11.         except:
  12.             db.rollback()
  13.             raise

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

Note

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.

  1. # Print all queries to stderr.
  2. import logging
  3. logger = logging.getLogger('peewee')
  4. logger.setLevel(logging.DEBUG)
  5. logger.addHandler(logging.StreamHandler())

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.

  1. from peewee import Database
  2. import foodb  # Our fictional DB-API 2.0 driver.
  5. class FooDatabase(Database):
  6.     def _connect(self, database, **kwargs):
  7.         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:

  1. class FooDatabase(Database):
  2.     def _connect(self, database, **kwargs):
  3.         return foodb.connect(database, **kwargs)
  5.     def get_tables(self):
  6.         res = self.execute('SHOW TABLES;')
  7.         return [r[0] for r in res.fetchall()]

Other things the database handles that are not covered here include:

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:

  1. from peewee import *
  2. from foodb_ext import FooDatabase
  4. db = FooDatabase('my_database', user='foo', password='secret')
  6. class BaseModel(Model):
  7.     class Meta:
  8.         database = db
  10. class Blog(BaseModel):
  11.     title = CharField()
  12.     contents = TextField()
  13.     pub_date = DateTimeField()