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
in turn uses another abstraction called an Adapter
, which
is backend-specific and encapsulates functionality specific to a given db driver. Since there
is some difference in column types across database engines, this information also resides
in the adapter. The adapter is responsible for smoothing out the quirks of each database
driver to provide a consistent interface, for example sqlite uses the question-mark ”?” character
for parameter interpolation, while all the other backends use “%s”.
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 BaseAdapter
will be
of interest to anyone interested in adding support for another database driver.
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 theAdapter
, in addition providing support for:- execution of SQL queries
- creating and dropping tables and indexes
-
__init__
(adapter, database[, threadlocals=False[, autocommit=True[, **connect_kwargs]]])¶ Parameters: - adapter – an instance of a
BaseAdapter
subclass - 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 theDatabase.init()
method with the database name- adapter – an instance of a
-
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
-
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
(sql[, params=None])¶ 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()
andDatabase.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[, safe=False])¶ Parameters: - model_class –
Model
class to create table for - safe – if
True
, query will add aIF NOT EXISTS
clause
- model_class –
-
create_index
(model_class, field_name[, unique=False])¶ Parameters: - model_class –
Model
table on which to create index - field_name – name of field to create index on
- unique – whether the index should enforce uniqueness
- model_class –
-
create_foreign_key
(model_class, field)¶ Parameters:
-
drop_table
(model_class[, fail_silently=False])¶ Parameters: - model_class –
Model
table to drop - fail_silently – if
True
, query will add aIF 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.
- model_class –
-
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)
tuplesWarning
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:
BaseAdapter and its subclasses¶
-
class
BaseAdapter
¶ The various subclasses of BaseAdapter provide a bridge between the high- level
Database
abstraction and the underlying python libraries like psycopg2. It also provides a way to unify the pythonic field types with the underlying column types used by the database engine.The BaseAdapter provides two types of mappings: - mapping between filter operations and their database equivalents - mapping between basic field types and their database column types
The BaseAdapter also is the mechanism used by the
Database
class to: - handle connections with the database - extract information from the database cursor-
operations = {'eq': '= %s'}
A mapping of query operation to SQL
-
interpolation = '%s'
The string used by the driver to interpolate query parameters
-
sequence_support = False
Whether the given backend supports sequences
-
reserved_tables = []
Table names that are reserved by the backend – if encountered in the application a warning will be issued.
-
get_field_types
()¶ Return type: a dictionary mapping “user-friendly field type” to specific column type, e.g. {'string': 'VARCHAR', 'float': 'REAL', ... }
-
get_field_type_overrides
()¶ Return type: a dictionary similar to that returned by get_field_types()
.Provides a mechanism to override any number of field types without having to override all of them.
-
connect
(database, **kwargs)¶ Parameters: - database – string representing database name (or filename if using sqlite)
- kwargs – any keyword arguments to pass along to the database driver when connecting
Return type: a database connection
-
close
(conn)¶ Parameters: conn – a database connection Close the given database connection
-
lookup_cast
(lookup, value)¶ Parameters: - lookup – a string representing the lookup type
- value – a python value that will be passed in to the lookup
Return type: a converted value appropriate for the given lookup
Used as a hook when a specific lookup requires altering the given value, like for example when performing a LIKE query you may need to insert wildcards.
-
last_insert_id
(cursor, model)¶ Return type: most recently inserted primary key
-
rows_affected
(cursor)¶ Return type: number of rows affected by most recent query
-
-
class
SqliteAdapter
(BaseAdapter)¶ Subclass of
BaseAdapter
that works with the “sqlite3” driver
-
class
MySQLAdapter
(BaseAdapter)¶ Subclass of
BaseAdapter
that works with the “MySQLdb” driver
-
class
PostgresqlAdapter
(BaseAdapter)¶ Subclass of
BaseAdapter
that works with the “psycopg2” driver