Databases

Below the Model level, peewee uses an abstraction for representing the database. The Database is responsible for establishing and closing connections, making queries, and gathering information from the database. The Database encapsulates functionality specific to a given db driver. For example difference in column types across database engines, or support for certain features like sequences. The database is responsible for smoothing out the quirks of each backend driver to provide a consistent interface.

The Database also uses a subclass of QueryCompiler to generate valid SQL. The QueryCompiler maps the internal data structures used by peewee to SQL statements.

For a high-level overview of working with transactions, check out the transactions cookbook.

For notes on deferring instantiation of database, for example if loading configuration at run-time, see the notes on deferring initialization.

Note

The internals of the Database and QueryCompiler will be of interest to anyone interested in adding support for another database driver.

Writing a database driver

Peewee currently supports Sqlite, MySQL and Postgresql. 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.

Starting out

The first thing is to provide a subclass of Database that will open a connection.

from peewee import Database
import foodb # our fictional driver


class FooDatabase(Database):
    def _connect(self, database, **kwargs):
        return foodb.connect(database, **kwargs)

Essential methods to override

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

    def get_indexes_for_table(self, table):
        res = self.execute('SHOW INDEXES IN %s;' % self.quote_name(table))
        rows = sorted([(r[2], r[1] == 0) for r in res.fetchall()])
        return rows

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

  • last insert id and number of rows modified
  • specifying characters used for string interpolation and quoting identifiers, for instance, sqlite uses ”?” for interpolation and MySQL uses a backtick for quoting
  • mapping operations such as “LIKE/ILIKE” to their database equivalent

Refer to the documentation below 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.

Using our new database

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

Database and its subclasses

class Database

A high-level api for working with the supported database engines. Database provides a wrapper around some of the functions performed by the Adapter, in addition providing support for:

  • execution of SQL queries
  • creating and dropping tables and indexes
compiler_class = QueryCompiler

A class suitable for compiling queries

expr_overrides = {}

A mapping of expression codes to string operators

field_overrides = {}

A mapping of field types to database column types, e.g. {'primary_key': 'SERIAL'}

for_update = False

Whether the given backend supports selecting rows for update

interpolation = '%s'

The string used by the driver to interpolate query parameters

op_overrides = {}

A mapping of operation codes to string operations, e.g. {OP_LIKE: 'LIKE BINARY'}

quote_char = '"'

The string used by the driver to quote names

reserved_tables = []

Table names that are reserved by the backend – if encountered in the application a warning will be issued.

sequences = False

Whether the given backend supports sequences

subquery_delete_same_table = True

Whether the given backend supports deleting rows using a subquery that selects from the same table

__init__(database[, threadlocals=False[, autocommit=True[, **connect_kwargs]]])
Parameters:
  • database – the name of the database (or filename if using sqlite)
  • threadlocals – whether to store connections in a threadlocal
  • autocommit – automatically commit every query executed by calling execute()
  • connect_kwargs – any arbitrary parameters to pass to the database driver when connecting

Note

if your database name is not known when the class is declared, you can pass None in as the database name which will mark the database as “deferred” and any attempt to connect while in this state will raise an exception. To initialize your database, call the Database.init() method with the database name

init(database[, **connect_kwargs])

If the database was instantiated with database=None, the database is said to be in a ‘deferred’ state (see notes) – if this is the case, you can initialize it at any time by calling the init method.

Parameters:
  • database – the name of the database (or filename if using sqlite)
  • connect_kwargs – any arbitrary parameters to pass to the database driver when connecting
connect()

Establishes a connection to the database

Note

If you initialized with threadlocals=True, then this will store the connection inside a threadlocal, ensuring that connections are not shared across threads.

close()

Closes the connection to the database (if one is open)

Note

If you initialized with threadlocals=True, only a connection local to the calling thread will be closed.

get_conn()
Return type:a connection to the database, creates one if does not exist
get_cursor()
Return type:a cursor for executing queries
get_compiler()
Return type:an instance of QueryCompiler
set_autocommit(autocommit)
Parameters:autocommit – a boolean value indicating whether to turn on/off autocommit for the current connection
get_autocommit()
Return type:a boolean value indicating whether autocommit is on for the current connection
execute(query)
Param :a query instance, such as a SelectQuery
Return type:the resulting cursor
execute_sql(sql[, params=None[, require_commit=True]])
Parameters:
  • sql – a string sql query
  • params – a list or tuple of parameters to interpolate

Note

You can configure whether queries will automatically commit by using the set_autocommit() and Database.get_autocommit() methods.

commit()

Call commit() on the active connection, committing the current transaction

rollback()

Call rollback() on the active connection, rolling back the current transaction

commit_on_success(func)

Decorator that wraps the given function in a single transaction, which, upon success will be committed. If an error is raised inside the function, the transaction will be rolled back and the error will be re-raised.

Parameters:func – function to decorate
@database.commit_on_success
def transfer_money(from_acct, to_acct, amt):
    from_acct.charge(amt)
    to_acct.pay(amt)
    return amt
transaction()

Return a context manager that executes statements in a transaction. If an error is raised inside the context manager, the transaction will be rolled back, otherwise statements are committed when exiting.

# delete a blog instance and all its associated entries, but
# do so within a transaction
with database.transaction():
    blog.delete_instance(recursive=True)
last_insert_id(cursor, model)
Parameters:
  • cursor – the database cursor used to perform the insert query
  • model – the model class that was just created
Return type:

the primary key of the most recently inserted instance

rows_affected(cursor)
Return type:number of rows affected by the last query
create_table(model_class)
Parameters:model_classModel class to create table for
create_index(model_class, fields[, unique=False])
Parameters:
  • model_classModel table on which to create index
  • fields – field(s) to create index on (either field instances or field names)
  • unique – whether the index should enforce uniqueness
create_foreign_key(model_class, field)
Parameters:
  • model_classModel table on which to create foreign key index / constraint
  • fieldField object
drop_table(model_class[, fail_silently=False])
Parameters:
  • model_classModel table to drop
  • fail_silently – if True, query will add a IF EXISTS clause

Note

Cascading drop tables are not supported at this time, so if a constraint exists that prevents a table being dropped, you will need to handle that in application logic.

create_sequence(sequence_name)
Parameters:sequence_name – name of sequence to create

Note

only works with database engines that support sequences

drop_sequence(sequence_name)
Parameters:sequence_name – name of sequence to drop

Note

only works with database engines that support sequences

get_indexes_for_table(table)
Parameters:table – the name of table to introspect
Return type:a list of (index_name, is_unique) tuples

Warning

Not implemented – implementations exist in subclasses

get_tables()
Return type:a list of table names in the database

Warning

Not implemented – implementations exist in subclasses

sequence_exists(sequence_name)
Rtype boolean:
class SqliteDatabase(Database)

Database subclass that communicates to the “sqlite3” driver

class MySQLDatabase(Database)

Database subclass that communicates to the “MySQLdb” driver

class PostgresqlDatabase(Database)

Database subclass that communicates to the “psycopg2” driver

Project Versions

Table Of Contents

Previous topic

Querying API

Next topic

Playhouse, a collection of addons

This Page