API reference

Warning

This project is no longer maintained as of February 2023. It is recommended that you use a proper ORM like SQLAlchemy or Django’s ORM instead.

Database class

class isqlite.Database(path: str, *, transaction: bool = True, debug: bool = False, readonly: Optional[bool] = None, uri: bool = False, cached_statements: int = 100, enforce_foreign_keys: bool = True, insert_auto_timestamp_columns: List[str] = [], update_auto_timestamp_columns: List[str] = [], use_epoch_timestamps: bool = False)

A class to represent a connection to a SQLite database. Typically used as a context manager:

with Database("db.sqlite3") as db:
    ...

On creation, the Database connection will open a SQL transaction which will be either committed or rolled back at the end of the with statement, depending on whether an exception occurs.

You can also have multiple transactions over the life of the connection:

with Database("db.sqlite3", transaction=False) as db:
    with db.transaction():
        ...

    with db.transaction():
        ...
__init__(path: str, *, transaction: bool = True, debug: bool = False, readonly: Optional[bool] = None, uri: bool = False, cached_statements: int = 100, enforce_foreign_keys: bool = True, insert_auto_timestamp_columns: List[str] = [], update_auto_timestamp_columns: List[str] = [], use_epoch_timestamps: bool = False) → None

Initialize a Database object.

Parameters

  • path – The path to the database file. You may pass ":memory" for an in-memory database.

  • transaction –

    If true, a transaction is automatically opened with BEGIN. When the Database class is used in a with statement, the transaction will be committed at the end (or rolled back if an exception occurs), so either all of the changes in the with block will be enacted, or none of them.

    If false, the database will operate in autocommit mode by default, meaning that every statement will be committed immediately. Users can then manage their transactions explicitly with Database.transaction, e.g.:

    with Database(transaction=False) as db:
    with db.transaction():
        ...

    with db.transaction():
        ...

  • debug – If true, each SQL statement executed will be printed to standard output.

  • readonly – If true, the database will be opened in read-only mode. This option is incompatibility with uri=True; if you need to pass a URI, then append ?mode=ro to make it read-only. Defaults to false.

  • uri – If true, the path argument is interpreted as a URI rather than a file path.

  • cached_statements – Passed on to sqlite3.connect.

  • enforce_foreign_keys – If true, foreign-key constraint enforcement will be turned out with PRAGMA foreign_keys = 1.

  • insert_auto_timestamp_columns – A default value for auto_timestamp_columns in insert and insert_many. Usually set to ["created_at", "last_updated_at"] in conjunction with a schema defined using AutoTable. It is recommended to set use_epoch_timestamps to True if using this parameter.

  • update_auto_timestamp_columns – A default value for auto_timestamp_columns in update. Usually set to ["last_updated_at"] in conjunction with a schema defined using AutoTable. It is recommended to set use_epoch_timestamps to True if using this parameter.

  • use_epoch_timestamps – Store auto_timestamp_columns as seconds since the Unix epoch instead of as ISO 8601 datetime strings. Recommended setting is True, but default is False for backwards compatibility.

select(table: str, *, columns: List[str] = [], where: str = '', values: Dict[str, Any] = {}, limit: Optional[int] = None, offset: Optional[int] = None, order_by: Optional[Union[Tuple[str], str]] = None, descending: Optional[bool] = None, get_related: Union[List[str], bool] = []) → List[Dict]

Return a list of database rows as OrderedDict objects.

Parameters

  • table – The database table to query. WARNING: This value is directly interpolated into the SQL statement. Do not pass untrusted input, to avoid SQL injection attacks.

  • columns – The columns of the table to return. By default, all columns are returned.

  • where – A ‘where’ clause to restrict the query, as a string. The initial WHERE keyword should be omitted. To interpolate Python values, put, e.g., :placeholder in the SQL and then pass {"placeholder": x} as the values parameter.

  • values – A dictionary of values to interpolate into the where argument.

  • limit – An integer limit to the number of rows returned.

  • offset – If not None, return results starting from this offset. Can be used in conjunction with limit for pagination.

  • order_by – If not None, order the results by this column. Order is ascending (smallest values first) by default; for descending order, pass descending=True. To order by multiple columns, pass in a tuple.

  • descending – If true, return results in descending order instead of ascending.

  • get_related – A list of foreign-key columns of the table to be retrieved and embedded into the returned dictionaries. If true, all foreign-key columns will be retrieved. This parameter requires that Database was initialized with a schema parameter.

get(table: str, *, columns: List[str] = [], where: str = '', values: Dict[str, Any] = {}, order_by: Optional[Union[Tuple[str], str]] = None, descending: Optional[bool] = None, get_related: Union[List[str], bool] = []) → Optional[Dict[str, Any]]

Retrieve a single row from the database table and return it as an OrderedDict object.

Equivalent to Database.select(*args, **kwargs)[0] except that None is returned if no matching row is found, and the SQLite engine only fetches a single row from the database.

Parameters

  • table – The database table to query. WARNING: This value is directly interpolated into the SQL statement. Do not pass untrusted input, to avoid SQL injection attacks.

  • columns – Same as for Database.select.

  • where – Same as for Database.select.

  • values – Same as for Database.select.

  • order_by – Same as for Database.select.

  • descending – Same as for Database.select.

  • get_related – Same as for Database.select.

get_by_pk(table: str, pk: int, *, columns: List[str] = [], get_related: Union[List[str], bool] = []) → Optional[Dict[str, Any]]

Retrieve a single row from the database table by its primary key.

Parameters

  • table – The database table to query. WARNING: This value is directly interpolated into the SQL statement. Do not pass untrusted input, to avoid SQL injection attacks.

  • pk – The primary key of the row to return.

  • columns – Passed on to Database.get.

  • get_related – Passed on to Database.get.

get_or_insert(table: str, data: Dict[str, Any], **kwargs) → Dict[str, Any]

Retrieve a single row from the database table matching the parameters in data. If no such row exists, insert it and return it.

Not to be confused with insert_and_get, which unconditionally inserts a row and returns it.

The returned collections.OrderedDict object will have an additional inserted attribute that indicates whether or not a new row was inserted into the database.

Parameters

  • table – The database table to query. WARNING: This value is directly interpolated into the SQL statement. Do not pass untrusted input, to avoid SQL injection attacks.

  • data – The parameters to match the database row. All required columns of the table must be included, or else the internal call to Database.insert will fail.

  • kwargs – Additional arguments to pass on to Database.insert. If the database row already exists, these arguments are ignored.

count(table: str, *, where: str = '', values: Dict[str, Any] = {}, distinct: str = '') → int

Return the count of rows matching the parameters.

Parameters

  • table – The database table to query. WARNING: This value is directly interpolated into the SQL statement. Do not pass untrusted input, to avoid SQL injection attacks.

  • where – Same as for Database.select.

  • values – Same as for Database.select.

  • distinct – Only count rows with distinct values of this column.

insert(table: str, data: Dict[str, Any], *, auto_timestamp_columns: Union[List[str], bool] = True) → int

Insert a new row and return its primary key.

To get the contents of the row after it is inserted, use insert_and_get.

Parameters

  • table – The database table. WARNING: This value is directly interpolated into the SQL statement. Do not pass untrusted input, to avoid SQL injection attacks.

  • data – The row to insert, as a dictionary from column names to column values.

  • auto_timestamp_columns – A list of columns into which to insert the current date and time, as an ISO 8601 timestamp. If true, it defaults to the value of insert_auto_timestamp_columns passed to __init__.

insert_and_get(table: str, data: Dict[str, Any], *, columns: List[str] = [], auto_timestamp_columns: Union[List[str], bool] = True, get_related: Union[List[str], bool] = []) → Dict[str, Any]

Same as insert, except it fetches the row after it is inserted and returns it. Note that this requires an extra SQL query.

Not to be confused with get_or_insert, which first tries to fetch a matching row and only inserts a new row if no matching one exists.

The returned row may differ from data for two reasons:

  • The auto_timestamp_columns parameter causes isqlite to insert values into additional columns besides those in data.

  • SQLite will supply default values if possible for any columns of the table omitted from data.

insert_many(table: str, data: List[Dict], *, auto_timestamp_columns: Union[List[str], bool] = True) → None

Insert multiple rows at once.

Equivalent to:

for row in data:
    db.insert(table, row)

but more efficient.

update(table: str, data: Dict[str, Any], *, where: str = '', values: Dict[str, Any] = {}, auto_timestamp_columns: Union[List[str], bool] = True) → int

Update existing rows and return the number of rows updated.

Parameters

  • table – The database table. WARNING: This value is directly interpolated into the SQL statement. Do not pass untrusted input, to avoid SQL injection attacks.

  • data – The columns to update, as a dictionary from column names to column values.

  • where – Restrict the set of rows to update. Same as for Database.select.

  • values – Same as for Database.select.

  • auto_timestamp_columns – Same as for Database.insert, except that if the same column appears in both values and auto_timestamp_columns, the timestamp will be inserted instead of the value.

update_by_pk(table: str, pk: int, data: Dict[str, Any], **kwargs) → bool

Update a single row and return whether it was updated or not.

Parameters

  • table – The database table. WARNING: This value is directly interpolated into the SQL statement. Do not pass untrusted input, to avoid SQL injection attacks.

  • pk – The primary key of the row to update.

  • data – Same as for Database.update.

  • kwargs – Additional arguments to pass on to Database.update.

delete(table: str, *, where: str, values: Dict[str, Any] = {}) → None

Delete a set of rows.

Parameters

  • table – The database table. WARNING: This value is directly interpolated into the SQL statement. Do not pass untrusted input, to avoid SQL injection attacks.

  • where – Same as for Database.select, except that it is required, to avoid accidentally deleting every row in a table. If you indeed wish to delete every row, then pass where="1".

  • values – Same as for Database.select.

delete_by_pk(table: str, pk: int) → None

Delete a single row.

Parameters

  • table – The database table. WARNING: This value is directly interpolated into the SQL statement. Do not pass untrusted input, to avoid SQL injection attacks.

  • pk – The primary key of the row to delete.

delete_many_by_pks(table: str, pks: Sequence[int]) → None

Delete multiple rows.

Parameters

  • table – The database table. WARNING: This value is directly interpolated into the SQL statement. Do not pass untrusted input, to avoid SQL injection attacks.

  • pks – The list of primary keys of the rows to delete. If the list is empty, this method is a no-op.

sql(query: str, values: Dict[str, Any] = {}, *, as_tuple: bool = False, multiple: bool = True) → Any

Execute a raw SQL query.

Parameters

  • query – The SQL query, as a string.

  • values – A dictionary of values to interpolate into the query.

  • as_tuple – If true, the rows are returned as tuples of values instead of OrderedDict objects. This is useful for aggregation queries, e.g. COUNT(*).

  • multiple – If true, the return type will be a list (though the list may be empty or only contain a single row). If false, the return type will either be a tuple (if as_tuple=True) or an OrderedDict object.

create_table(table_name: str, columns: List[str]) → None

Create a new table.

Parameters

  • table_name – The name of the table to create.

  • columns – A list of columns, as raw SQL strings.

drop_table(table_name: str) → None

Drop a table.

Parameters

table_name – The name of the table to drop.

rename_table(old_table_name: str, new_table_name: str) → None

Rename a table.

add_column(table_name: str, column_def: str) → None

Add a column to the table’s schema.

Parameters

  • table_name – The name of the table.

  • column_def – The definition of the column to add, as raw SQL.

drop_column(table_name: str, column_name: str) → None

Drop a column from the database.

reorder_columns(table_name: str, column_names: List[str]) → None

Reorder the columns of a database table.

Parameters

  • table_name – The table to reorder.

  • column_names – The new order of the columns, as a list of strings. The column names must be the same as in the database; otherwise, an exception will be raised.

alter_column(table_name: str, column_name: str, new_column: str) → None

Alter the definition of a column.

Parameters

  • table_name – The table to alter.

  • column_name – The column to alter.

  • new_column – The new definition of the column, without the name, as a SQL string.

rename_column(table_name: str, old_column_name: str, new_column_name: str) → None

Rename a column.

diff(schema: Schema, *, table='', detect_renaming=True) → List[MigrateOperation]

Return a list of differences between the Python schema and the actual database schema.

Parameters

  • schema – The Python schema to compare against the database.

  • table – The table to diff. If empty, the entire database will be diffed.

  • detect_renaming – If true, the differ will attempt to detect renamed columns. Sometimes the differ does not detect renames correctly, so this option is available to disable renaming detection.

apply_diff(diff: List[MigrateOperation]) → None

Apply the diff returned by Database.diff to the database.

WARNING: This may cause columns or entire tables to be dropped from the database. Make sure to examine the diff before applying it, e.g. by using the isqlite migrate command.

The entire operation will occur in a transaction.

Parameters

diff – A list of differences, as returned by Database.diff.

migrate(schema: Schema, *, detect_renaming=True) → None

Migrate the database to match the Python schema.

WARNING: This may cause columns or entire tables to be dropped from the database.

The entire operation will occur in a transaction.

Parameters

  • schema – The Python schema to compare against the database.

  • detect_renaming – Passed on to Database.diff.

refresh_schema() → None

Refresh the database’s internal representation of the SQL schema.

Users do not normally need to call this function, as all the schema-altering methods on this class already call it automatically. But if you alter the schema using Database.sql or in an external database connection, you may need to call this method for correct behavior.

The internal schema is used by the get_related functionality of select and get.

transaction(*, disable_foreign_keys: bool = False) → TransactionContextManager

Begin a new transaction in a context manager.

Intended for use as:

with Database(transaction=False) as db:
   with db.transaction():
       ...

   with db.transaction():
       ...

The return value of this method should be ignored.

Parameters

disable_foreign_keys – If true, foreign key enforcement will be disabled during the transaction. This is useful during database migrations.

begin_transaction() → None

Begin a new transaction.

Most users do not need this method. Instead, they should either use the default transaction opened by Database as a context manager, or they should explicitly manage their transactions with nested with db.transaction() statements.

commit() → None

Commit the current transaction.

Most users do not need this method. See the note to Database.begin_transaction.

rollback() → None

Roll back the current transaction.

Most users do not need this method. See the note to Database.begin_transaction.

property in_transaction: bool

Whether or not the database is currently in a transaction.

close() → None

Close the database connection. If a transaction is pending, commit it.

Most users do not need this method. Instead, they should use Database in a with statement so that the database will be closed automatically.

Schema definitions

class isqlite.Table(name: str, columns: List[Union[str, Column]])

A class to represent a SQL table as part of a schema defined in Python.

class isqlite.AutoTable(name: str, columns: List[Union[str, Column]], use_epoch_timestamps: bool = False)

An extension of the Table class which automatically creates a primary-key column called id and timestamp columns called created_at and last_updated_at.

class isqlite.Schema(tables: List[Table])

A class to represent an entire database schema.

property table_names: List[str]

Returns the names of the tables in the schema as a list.

property tables: List[Table]

Returns the tables in the schema as a list.

Column functions

isqlite.columns.boolean(name: str, *, required: bool = True, default: Optional[bool] = None) → Column

A BOOLEAN column.

Note that SQLite lacks a built-in boolean type, and instead represents boolean values as 0 or 1.

isqlite.columns.date(name: str, *, required: bool = True, default: Optional[str] = None, unique: bool = False) → Column

A DATE column for values in ISO 8601 format, e.g. 2021-01-01.

isqlite.columns.decimal(name: str, *, required: bool = True, default: Optional[int] = None, unique: bool = False) → Column

A DECIMAL column.

isqlite.columns.foreign_key(name: str, foreign_table: str, *, required: bool = True, on_delete=OnDelete.SET_NULL, unique: bool = False) → Column

A foreign key column.

isqlite.columns.integer(name: str, *, required: bool = True, choices: List[int] = [], default: Optional[int] = None, max: Optional[int] = None, min: Optional[int] = None, unique: bool = False) → Column

An INTEGER column.

isqlite.columns.primary_key(name: str, *, autoincrement: bool = True) → Column

A primary key column.

isqlite.columns.text(name: str, *, required: bool = True, choices: List[str] = [], default: Optional[str] = None, unique: bool = False) → Column

A TEXT column.

There are two possible “empty” values for a TEXT column: the empty string and NULL. To avoid confusion, this function always returns a NOT NULL column so that the only possible empty value is the empty string.

isqlite.columns.time(name: str, *, required: bool = True, default: Optional[str] = None, unique: bool = False) → Column

A TIME column for values in HH:MM:SS format

isqlite.columns.timestamp(name: str, *, required: bool = True, default: Optional[str] = None, unique: bool = False) → Column

A TIMESTAMP column for values in ISO 8601 format, e.g. 2021-01-01 01:00:00.00.