Database

• class Database(database[, thread_safe=True[, autorollback=False[, field_types=None[, operations=None[, autoconnect=True[, **kwargs]]]]]])

Parameters:

• database (str) – Database name or filename for SQLite (or None todefer initialization, in which caseyou must call Database.init(), specifying the database name).
• thread_safe (bool) – Whether to store connection state in athread-local.
• autorollback (bool) – Automatically rollback queries that fail whennot in an explicit transaction.
• field_types (dict) – A mapping of additional field types to support.
• operations (dict) – A mapping of additional operations to support.
• autoconnect (bool) – Automatically connect to database if attempting toexecute a query on a closed database.
• kwargs – Arbitrary keyword arguments that will be passed to thedatabase driver when a connection is created, for example password,host, etc.

The Database is responsible for:

• Executing queries
• Managing connections
• Transactions
• Introspection

Note

The database can be instantiated with None as the database name ifthe database is not known until run-time. In this way you can create adatabase instance and then configure it elsewhere when the settings areknown. This is called deferred* initialization.

Examples:

# Sqlite database using WAL-mode and 32MB page-cache.
db = SqliteDatabase('app.db', pragmas={
    'journal_mode': 'wal',
    'cache_size': -32 * 1000})
 
# Postgresql database on remote host.
db = PostgresqlDatabase('my_app', user='postgres', host='10.1.0.3',
                        password='secret')

Deferred initialization example:

db = PostgresqlDatabase(None)
 
class BaseModel(Model):
    class Meta:
        database = db
 
# Read database connection info from env, for example:
db_name = os.environ['DATABASE']
db_host = os.environ['PGHOST']
 
# Initialize database.
db.init(db_name, host=db_host, user='postgres')

• param = '?'

• String used as parameter placeholder in SQL queries.

• quote = '"'

• Type of quotation-mark to use to denote entities such as tables orcolumns.

• init(database[, **kwargs])

Parameters:

- **database** (_str_) – Database name or filename for SQLite.
- **kwargs** – Arbitrary keyword arguments that will be passed to thedatabase driver when a connection is created, for example<code>password</code>, <code>host</code>, etc.

Initialize a deferred database. See Run-time database configurationfor more info.

• enter()
• The Database instance can be used as a context-manager, inwhich case a connection will be held open for the duration of thewrapped block.

Additionally, any SQL executed within the wrapped block will beexecuted in a transaction.

• connection_context()
• Create a context-manager that will hold open a connection for theduration of the wrapped block.

Example:

def on_app_startup():
    # When app starts up, create the database tables, being sure
    # the connection is closed upon completion.
    with database.connection_context():
        database.create_tables(APP_MODELS)

• connect([reuse_if_open=False])

Parameters:reuse_if_open (bool) – Do not raise an exception if a connection isalready opened.Returns:whether a new connection was opened.Return type:boolRaises:OperationalError if connection already open andreuse_if_open is not set to True.

Open a connection to the database.

• close()

Returns:Whether a connection was closed. If the database was alreadyclosed, this returns False.Return type:bool

Close the connection to the database.

• is_closed()

Returns:return True if database is closed, False if open.Return type:bool

• connection()

• Return the open connection. If a connection is not open, one will beopened. The connection will be whatever the underlying database-driveruses to encapsulate a database connection.

• cursor([commit=None])

Parameters:commit – For internal use.

Return a cursor object on the current connection. If a connectionis not open, one will be opened. The cursor will be whatever theunderlying database-driver uses to encapsulate a database cursor.

• executesql(_sql[, params=None[, commit=SENTINEL]])

Parameters:

- **sql** (_str_) – SQL string to execute.
- **params** (_tuple_) – Parameters for query.
- **commit** – Boolean flag to override the default commit logic.Returns:

cursor object.

Execute a SQL query and return a cursor over the results.

• execute(query[, commit=SENTINEL[, **context_options]])

Parameters:

- **query** – A [<code>Query</code>](#Query) instance.
- **commit** – Boolean flag to override the default commit logic.
- **context_options** – Arbitrary options to pass to the SQL generator.Returns:

cursor object.

Execute a SQL query by compiling a Query instance and executing theresulting SQL.

• lastinsert_id(_cursor[, query_type=None])

Parameters:cursor – cursor object.Returns:primary key of last-inserted row.

• rowsaffected(_cursor)

Parameters:cursor – cursor object.Returns:number of rows modified by query.

• in_transaction()

Returns:whether or not a transaction is currently open.Return type:bool

• atomic()
• Create a context-manager which runs any queries in the wrapped block ina transaction (or save-point if blocks are nested).

Calls to atomic() can be nested.

atomic() can also be used as a decorator.

Example code:

with db.atomic() as txn:
    perform_operation()
 
    with db.atomic() as nested_txn:
        perform_another_operation()

Transactions and save-points can be explicitly committed or rolled-backwithin the wrapped block. If this occurs, a new transaction orsavepoint is begun after the commit/rollback.

Example:

with db.atomic() as txn:
    User.create(username='mickey')
    txn.commit()  # Changes are saved and a new transaction begins.
 
    User.create(username='huey')
    txn.rollback()  # "huey" will not be saved.
 
    User.create(username='zaizee')
 
# Print the usernames of all users.
print [u.username for u in User.select()]
 
# Prints ["mickey", "zaizee"]

• manual_commit()
• Create a context-manager which disables all transaction management forthe duration of the wrapped block.

Example:

with db.manual_commit():
    db.begin()  # Begin transaction explicitly.
    try:
        user.delete_instance(recursive=True)
    except:
        db.rollback()  # Rollback -- an error occurred.
        raise
    else:
        try:
            db.commit()  # Attempt to commit changes.
        except:
            db.rollback()  # Error committing, rollback.
            raise

The above code is equivalent to the following:

with db.atomic():
    user.delete_instance(recursive=True)

• session_start()
• Begin a new transaction (without using a context-manager or decorator).This method is useful if you intend to execute a sequence of operationsinside a transaction, but using a decorator or context-manager wouldnot be appropriate.

Note

It is strongly advised that you use the Database.atomic()method whenever possible for managing transactions/savepoints. Theatomic method correctly manages nesting, uses the appropriateconstruction (e.g., transaction-vs-savepoint), and always cleans upafter itself.

The session_start() method should only be usedif the sequence of operations does not easily lend itself towrapping using either a context-manager or decorator.

Warning

You must always call either session_commit()or session_rollback() after calling thesession_start method.

• session_commit()

• Commit any changes made during a transaction begun withsession_start().

• session_rollback()

• Roll back any changes made during a transaction begun withsession_start().

• transaction()

• Create a context-manager that runs all queries in the wrapped block ina transaction.

Warning

Calls to transaction cannot be nested. Only the top-most callwill take effect. Rolling-back or committing a nested transactioncontext-manager has undefined behavior.

• savepoint()
• Create a context-manager that runs all queries in the wrapped block ina savepoint. Savepoints can be nested arbitrarily.

Warning

Calls to savepoint must occur inside of a transaction.

• begin()
• Begin a transaction when using manual-commit mode.

Note

This method should only be used in conjunction with themanual_commit() context manager.

• commit()
• Manually commit the currently-active transaction.

Note

This method should only be used in conjunction with themanual_commit() context manager.

• rollback()
• Manually roll-back the currently-active transaction.

Note

This method should only be used in conjunction with themanual_commit() context manager.

• batchcommit(_it, n)

Parameters:

- **it** (_iterable_) – an iterable whose items will be yielded.
- **n** (_int_) – commit every _n_ items.Returns:

an equivalent iterable to the one provided, with the additionthat groups of n items will be yielded in a transaction.

The purpose of this method is to simplify batching large operations,such as inserts, updates, etc. You pass in an iterable and the numberof items-per-batch, and the items will be returned by an equivalentiterator that wraps each batch in a transaction.

Example:

# Some list or iterable containing data to insert.
row_data = [{'username': 'u1'}, {'username': 'u2'}, ...]
 
# Insert all data, committing every 100 rows. If, for example,
# there are 789 items in the list, then there will be a total of
# 8 transactions (7x100 and 1x89).
for row in db.batch_commit(row_data, 100):
    User.create(**row)

An alternative that may be more efficient is to batch the data into amulti-value INSERT statement (for example, usingModel.insert_many()):

with db.atomic():
    for idx in range(0, len(row_data), 100):
        # Insert 100 rows at a time.
        rows = row_data[idx:idx + 100]
        User.insert_many(rows).execute()

• tableexists(_table[, schema=None])

Parameters:

- **table** (_str_) – Table name.
- **schema** (_str_) – Schema name (optional).Returns:

bool indicating whether table exists.

• gettables([_schema=None])

Parameters:schema (str) – Schema name (optional).Returns:a list of table names in the database.

• getindexes(_table[, schema=None])

Parameters:

- **table** (_str_) – Table name.
- **schema** (_str_) – Schema name (optional).

Return a list of IndexMetadata tuples.

Example:

print(db.get_indexes('entry'))
[IndexMetadata(
     name='entry_public_list',
     sql='CREATE INDEX "entry_public_list" ...',
     columns=['timestamp'],
     unique=False,
     table='entry'),
 IndexMetadata(
     name='entry_slug',
     sql='CREATE UNIQUE INDEX "entry_slug" ON "entry" ("slug")',
     columns=['slug'],
     unique=True,
     table='entry')]

• getcolumns(_table[, schema=None])

Parameters:

- **table** (_str_) – Table name.
- **schema** (_str_) – Schema name (optional).

Return a list of ColumnMetadata tuples.

Example:

print(db.get_columns('entry'))
[ColumnMetadata(
     name='id',
     data_type='INTEGER',
     null=False,
     primary_key=True,
     table='entry'),
 ColumnMetadata(
     name='title',
     data_type='TEXT',
     null=False,
     primary_key=False,
     table='entry'),
 ...]

• getprimary_keys(_table[, schema=None])

Parameters:

- **table** (_str_) – Table name.
- **schema** (_str_) – Schema name (optional).

Return a list of column names that comprise the primary key.

Example:

print(db.get_primary_keys('entry'))
['id']

• getforeign_keys(_table[, schema=None])

Parameters:

- **table** (_str_) – Table name.
- **schema** (_str_) – Schema name (optional).

Return a list of ForeignKeyMetadata tuples for keys presenton the table.

Example:

print(db.get_foreign_keys('entrytag'))
[ForeignKeyMetadata(
     column='entry_id',
     dest_table='entry',
     dest_column='id',
     table='entrytag'),
 ...]

• getviews([_schema=None])

Parameters:schema (str) – Schema name (optional).

Return a list of ViewMetadata tuples for VIEWs present inthe database.

Example:

print(db.get_views())
[ViewMetadata(
     name='entries_public',
     sql='CREATE VIEW entries_public AS SELECT ... '),
 ...]

• sequenceexists(_seq)

Parameters:seq (str) – Name of sequence.Returns:Whether sequence exists.Return type:bool

• createtables(_models[, **options])

Parameters:

- **models** (_list_) – A list of [<code>Model</code>](#Model) classes.
- **options** – Options to specify when calling[<code>Model.create_table()</code>](#Model.create_table).

Create tables, indexes and associated metadata for the given list ofmodels.

Dependencies are resolved so that tables are created in the appropriateorder.

• droptables(_models[, **options])

Parameters:

- **models** (_list_) – A list of [<code>Model</code>](#Model) classes.
- **kwargs** – Options to specify when calling[<code>Model.drop_table()</code>](#Model.drop_table).

Drop tables, indexes and associated metadata for the given list ofmodels.

Dependencies are resolved so that tables are dropped in the appropriateorder.

• bind(models[, bind_refs=True[, bind_backrefs=True]])

Parameters:

- **models** (_list_) – One or more [<code>Model</code>](#Model) classes to bind.
- **bind_refs** (_bool_) – Bind related models.
- **bind_backrefs** (_bool_) – Bind back-reference related models.

Bind the given list of models, and specified relations, to thedatabase.

• bindctx(_models[, bind_refs=True[, bind_backrefs=True]])

Parameters:

- **models** (_list_) – List of models to bind to the database.
- **bind_refs** (_bool_) – Bind models that are referenced usingforeign-keys.
- **bind_backrefs** (_bool_) – Bind models that reference the given modelwith a foreign-key.

Create a context-manager that binds (associates) the given models withthe current database for the duration of the wrapped block.

Example:

MODELS = (User, Account, Note)
 
# Bind the given models to the db for the duration of wrapped block.
def use_test_database(fn):
    @wraps(fn)
    def inner(self):
        with test_db.bind_ctx(MODELS):
            test_db.create_tables(MODELS)
            try:
                fn(self)
            finally:
                test_db.drop_tables(MODELS)
    return inner
 
 
class TestSomething(TestCase):
    @use_test_database
    def test_something(self):
        # ... models are bound to test database ...
        pass

• extractdate(_date_part, date_field)

Parameters:

- **date_part** (_str_) – date part to extract, e.g. ‘year’.
- **date_field** ([_Node_](#Node)) – a SQL node containing a date/time, for examplea [<code>DateTimeField</code>](#DateTimeField).Returns:

a SQL node representing a function call that will return theprovided date part.

Provides a compatible interface for extracting a portion of a datetime.

• truncatedate(_date_part, date_field)

Parameters:

- **date_part** (_str_) – date part to truncate to, e.g. ‘day’.
- **date_field** ([_Node_](#Node)) – a SQL node containing a date/time, for examplea [<code>DateTimeField</code>](#DateTimeField).Returns:

a SQL node representing a function call that will return thetruncated date part.

Provides a compatible interface for truncating a datetime to the givenresolution.

• random()

Returns:a SQL node representing a function call that returns a randomvalue.

A compatible interface for calling the appropriate random numbergeneration function provided by the database. For Postgres and Sqlite,this is equivalent to fn.random(), for MySQL fn.rand().

• class SqliteDatabase(database[, pragmas=None[, timeout=5[, **kwargs]]])

Parameters:

• pragmas – Either a dictionary or a list of 2-tuples containingpragma key and value to set every time a connection is opened.
• timeout – Set the busy-timeout on the SQLite driver (in seconds).

Sqlite database implementation. SqliteDatabase that providessome advanced features only offered by Sqlite.

• Register custom aggregates, collations and functions
• Load C extensions
• Advanced transactions (specify lock type)
• For even more features, see SqliteExtDatabase.Example of initializing a database and configuring some PRAGMAs:

db = SqliteDatabase('my_app.db', pragmas=(
    ('cache_size', -16000),  # 16MB
    ('journal_mode', 'wal'),  # Use write-ahead-log journal mode.
))
 
# Alternatively, pragmas can be specified using a dictionary.
db = SqliteDatabase('my_app.db', pragmas={'journal_mode': 'wal'})

• pragma(key[, value=SENTINEL[, permanent=False]])

Parameters:

- **key** – Setting name.
- **value** – New value for the setting (optional).
- **permanent** – Apply this pragma whenever a connection is opened.

Execute a PRAGMA query once on the active connection. If a value is notspecified, then the current value will be returned.

If permanent is specified, then the PRAGMA query will also beexecuted whenever a new connection is opened, ensuring it is alwaysin-effect.

Note

By default this only affects the current connection. If the PRAGMAbeing executed is not persistent, then you must specifypermanent=True to ensure the pragma is set on subsequentconnections.

• cache_size

• Get or set the cache_size pragma for the current connection.

• foreign_keys

• Get or set the foreign_keys pragma for the current connection.

• journal_mode

• Get or set the journal_mode pragma.

• journal_size_limit

• Get or set the journal_size_limit pragma.

• mmap_size

• Get or set the mmap_size pragma for the current connection.

• page_size

• Get or set the page_size pragma.

• read_uncommitted

• Get or set the read_uncommitted pragma for the current connection.

• synchronous

• Get or set the synchronous pragma for the current connection.

• wal_autocheckpoint

• Get or set the wal_autocheckpoint pragma for the current connection.

• timeout

• Get or set the busy timeout (seconds).

• registeraggregate(_klass[, name=None[, num_params=-1]])

Parameters:

- **klass** – Class implementing aggregate API.
- **name** (_str_) – Aggregate function name (defaults to name of class).
- **num_params** (_int_) – Number of parameters the aggregate accepts, or-1 for any number.

Register a user-defined aggregate function.

The function will be registered each time a new connection is opened.Additionally, if a connection is already open, the aggregate will beregistered with the open connection.

• aggregate([name=None[, num_params=-1]])

Parameters:

- **name** (_str_) – Name of the aggregate (defaults to class name).
- **num_params** (_int_) – Number of parameters the aggregate accepts,or -1 for any number.

Class decorator to register a user-defined aggregate function.

Example:

@db.aggregate('md5')class MD5(object):    def initialize(self):        self.md5 = hashlib.md5()
def step(self, value):
    self.md5.update(value)
def finalize(self):
    return self.md5.hexdigest()
@db.aggregate()class Product(object):    '''Like SUM() except calculates cumulative product.'''    def init(self):        self.product = 1
def step(self, value):
    self.product *= value
def finalize(self):
    return self.product

• registercollation(_fn[, name=None])

Parameters:

- **fn** – The collation function.
- **name** (_str_) – Name of collation (defaults to function name)

Register a user-defined collation. The collation will be registeredeach time a new connection is opened. Additionally, if a connection isalready open, the collation will be registered with the openconnection.

• collation([name=None])

Parameters:name (str) – Name of collation (defaults to function name)

Decorator to register a user-defined collation.

Example:

@db.collation('reverse')def collate_reverse(s1, s2):    return -cmp(s1, s2)
Usage:
Book.select().order_by(collate_reverse.collation(Book.title))
Equivalent:
Book.select().order_by(Book.title.asc(collation='reverse'))

As you might have noticed, the original collate_reverse functionhas a special attribute called collation attached to it. Thisextra attribute provides a shorthand way to generate the SQL necessaryto use our custom collation.

• registerfunction(_fn[, name=None[, num_params=-1]])

Parameters:

- **fn** – The user-defined scalar function.
- **name** (_str_) – Name of function (defaults to function name)
- **num_params** (_int_) – Number of arguments the function accepts, or-1 for any number.

Register a user-defined scalar function. The function will beregistered each time a new connection is opened. Additionally, if aconnection is already open, the function will be registered with theopen connection.

• func([name=None[, num_params=-1]])

Parameters:

- **name** (_str_) – Name of the function (defaults to function name).
- **num_params** (_int_) – Number of parameters the function accepts,or -1 for any number.

Decorator to register a user-defined scalar function.

Example:

@db.func('title_case')def title_case(s):    return s.title() if s else ''
Usage:
title_case_books = Book.select(fn.title_case(Book.title))

• registerwindow_function(_klass[, name=None[, num_params=-1]])

Parameters:

- **klass** – Class implementing window function API.
- **name** (_str_) – Window function name (defaults to name of class).
- **num_params** (_int_) – Number of parameters the function accepts, or-1 for any number.

Register a user-defined window function.

Attention

This feature requires SQLite >= 3.25.0 andpysqlite3 >= 0.2.0.

The window function will be registered each time a new connection isopened. Additionally, if a connection is already open, the windowfunction will be registered with the open connection.

• windowfunction([_name=None[, num_params=-1]])

Parameters:

- **name** (_str_) – Name of the window function (defaults to class name).
- **num_params** (_int_) – Number of parameters the function accepts, or -1for any number.

Class decorator to register a user-defined window function. Windowfunctions must define the following methods:

- <code>step(&lt;params&gt;)</code> - receive values from a row and update state.
- <code>inverse(&lt;params&gt;)</code> - inverse of <code>step()</code> for the given values.
- <code>value()</code> - return the current value of the window function.
- <code>finalize()</code> - return the final value of the window function.

Example:

@db.windowfunction('mysum')class MySum(object):    def __init(self):        self._value = 0
def step(self, value):
    self._value += value
def inverse(self, value):
    self._value -= value
def value(self):
    return self._value
def finalize(self):
    return self._value

• tablefunction([_name=None])
• Class-decorator for registering a TableFunction. Tablefunctions are user-defined functions that, rather than returning asingle, scalar value, can return any number of rows of tabular data.

Example:

from playhouse.sqlite_ext import TableFunction
 
@db.table_function('series')
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:
    print(value)
 
# Prints:
# 0
# 2
# 4

• unregisteraggregate(_name)

Parameters:name – Name of the user-defined aggregate function.

Unregister the user-defined aggregate function.

• unregistercollation(_name)

Parameters:name – Name of the user-defined collation.

Unregister the user-defined collation.

• unregisterfunction(_name)

Parameters:name – Name of the user-defined scalar function.

Unregister the user-defined scalar function.

• unregistertable_function(_name)

Parameters:name – Name of the user-defined table function.Returns:True or False, depending on whether the function was removed.

Unregister the user-defined scalar function.

• loadextension(_extension_module)
• Load the given C extension. If a connection is currently open in thecalling thread, then the extension will be loaded for that connectionas well as all subsequent connections.

For example, if you’ve compiled the closure table extension and wish touse it in your application, you might write:

db = SqliteExtDatabase('my_app.db')
db.load_extension('closure')

• attach(filename, name)

Parameters:

- **filename** (_str_) – Database to attach (or <code>:memory:</code> for in-memory)
- **name** (_str_) – Schema name for attached database.Returns:

boolean indicating success

Register another database file that will be attached to every databaseconnection. If the main database is currently connected, the newdatabase will be attached on the open connection.

Note

Databases that are attached using this method will be attachedevery time a database connection is opened.

• detach(name)

Parameters:name (str) – Schema name for attached database.Returns:boolean indicating success

Unregister another database file that was attached previously with acall to attach(). If the main database iscurrently connected, the attached database will be detached from theopen connection.

• transaction([lock_type=None])

Parameters:lock_type (str) – Locking strategy: DEFERRED, IMMEDIATE, EXCLUSIVE.

Create a transaction context-manager using the specified lockingstrategy (defaults to DEFERRED).

• class PostgresqlDatabase(database[, register_unicode=True[, encoding=None[, isolation_level=None]]])
• Postgresql database implementation.

Additional optional keyword-parameters:

Parameters:

• register_unicode (bool) – Register unicode types.
• encoding (str) – Database encoding.
• isolation_level (int) – Isolation level constant, defined in thepsycopg2.extensions module.

• settime_zone(_timezone)

Parameters:timezone (str) – timezone name, e.g. “US/Central”.Returns:no return value.

Set the timezone on the current connection. If no connection is open,then one will be opened.

• class MySQLDatabase(database[, **kwargs])
• MySQL database implementation.