API reference¶
sqlite_utils.db.Database¶
- class sqlite_utils.db.Database(filename_or_conn=None, memory=False, memory_name=None, recreate=False, recursive_triggers=True, tracer=None, use_counts_table=False, execute_plugins=True, strict=False)[source]¶
Wrapper for a SQLite database connection that adds a variety of useful utility methods.
To create an instance:
# create data.db file, or open existing: db = Database("data.db") # Create an in-memory database: dB = Database(memory=True)
- Parameters:
filename_or_conn (str | Path | Connection | None) – String path to a file, or a
pathlib.Path
object, or asqlite3
connectionmemory (bool) – set to
True
to create an in-memory databasememory_name (str | None) – creates a named in-memory database that can be shared across multiple connections
recreate (bool) – set to
True
to delete and recreate a file database (dangerous)recursive_triggers (bool) – defaults to
True
, which setsPRAGMA recursive_triggers=on;
- set toFalse
to avoid setting this pragmatracer (Callable | None) – set a tracer function (
print
works for this) which will be called withsql, parameters
every time a SQL query is executeduse_counts_table (bool) – set to
True
to use a cached counts table, if available. See Cached table counts using triggersstrict (bool) – Apply STRICT mode to all created tables (unless overridden)
execute_plugins (bool)
- ensure_autocommit_off()[source]¶
Ensure autocommit is off for this database connection.
Example usage:
with db.ensure_autocommit_off(): # do stuff here
This will reset to the previous autocommit state at the end of the block.
- tracer(tracer=None)[source]¶
Context manager to temporarily set a tracer function - all executed SQL queries will be passed to this.
The tracer function should accept two arguments:
sql
andparameters
Example usage:
with db.tracer(print): db["creatures"].insert({"name": "Cleo"})
See Tracing queries.
- Parameters:
tracer (Callable | None) – Callable accepting
sql
andparameters
arguments
- __getitem__(table_name)[source]¶
db[table_name]
returns aTable
object for the table with the specified name. If the table does not exist yet it will be created the first time data is inserted into it.
- register_function(fn=None, deterministic=False, replace=False, name=None)[source]¶
fn
will be made available as a function within SQL, with the same name and number of arguments. Can be used as a decorator:@db.register_function def upper(value): return str(value).upper()
The decorator can take arguments:
@db.register_function(deterministic=True, replace=True) def upper(value): return str(value).upper()
See Registering custom SQL functions.
- Parameters:
fn (Callable | None) – Function to register
deterministic (bool) – set
True
for functions that always returns the same output for a given inputreplace (bool) – set
True
to replace an existing function with the same name - otherwise throw an errorname (str | None) – name of the SQLite function - if not specified, the Python function name will be used
- register_fts4_bm25()[source]¶
Register the
rank_bm25(match_info)
function used for calculating relevance with SQLite FTS4.
- attach(alias, filepath)[source]¶
Attach another SQLite database file to this connection with the specified alias, equivalent to:
ATTACH DATABASE 'filepath.db' AS alias
- Parameters:
alias (str) – Alias name to use
filepath (str | Path) – Path to SQLite database file on disk
- query(sql, params=None)[source]¶
Execute
sql
and return an iterable of dictionaries representing each row.- Parameters:
sql (str) – SQL query to execute
params (Iterable | dict | None) – Parameters to use in that query - an iterable for
where id = ?
parameters, or a dictionary forwhere id = :id
- Return type:
Generator[dict, None, None]
- execute(sql, parameters=None)[source]¶
Execute SQL query and return a
sqlite3.Cursor
.- Parameters:
sql (str) – SQL query to execute
parameters (Iterable | dict | None) – Parameters to use in that query - an iterable for
where id = ?
parameters, or a dictionary forwhere id = :id
- Return type:
Cursor
- executescript(sql)[source]¶
Execute multiple SQL statements separated by ; and return the
sqlite3.Cursor
.- Parameters:
sql (str) – SQL to execute
- Return type:
Cursor
- table(table_name, **kwargs)[source]¶
Return a table object, optionally configured with default options.
See sqlite_utils.db.Table for option details.
- quote(value)[source]¶
Apply SQLite string quoting to a value, including wrapping it in single quotes.
- Parameters:
value (str) – String to quote
- Return type:
str
- quote_fts(query)[source]¶
Escape special characters in a SQLite full-text search query.
This works by surrounding each token within the query with double quotes, in order to avoid words like
NOT
andOR
having special meaning as defined by the FTS query syntax here:https://www.sqlite.org/fts5.html#full_text_query_syntax
If the query has unbalanced
"
characters, adds one at end.- Parameters:
query (str) – String to escape
- Return type:
str
- table_names(fts4=False, fts5=False)[source]¶
List of string table names in this database.
- Parameters:
fts4 (bool) – Only return tables that are part of FTS4 indexes
fts5 (bool) – Only return tables that are part of FTS5 indexes
- Return type:
List[str]
- property triggers: List[Trigger][source]¶
List of
(name, table_name, sql)
tuples representing triggers in this database.
- property triggers_dict: Dict[str, str][source]¶
A
{trigger_name: sql}
dictionary of triggers in this database.
- property sqlite_version: Tuple[int, ...][source]¶
Version of SQLite, as a tuple of integers for example
(3, 36, 0)
.
- enable_counts()[source]¶
Enable trigger-based count caching for every table in the database, see Cached table counts using triggers.
- cached_counts(tables=None)[source]¶
Return
{table_name: count}
dictionary of cached counts for specified tables, or all tables iftables
not provided.- Parameters:
tables (Iterable[str] | None) – Subset list of tables to return counts for.
- Return type:
Dict[str, int]
- create_table_sql(name, columns, pk=None, foreign_keys=None, column_order=None, not_null=None, defaults=None, hash_id=None, hash_id_columns=None, extracts=None, if_not_exists=False, strict=False)[source]¶
Returns the SQL
CREATE TABLE
statement for creating the specified table.- Parameters:
name (str) – Name of table
columns (Dict[str, Any]) – Dictionary mapping column names to their types, for example
{"name": str, "age": int}
pk (Any | None) – String name of column to use as a primary key, or a tuple of strings for a compound primary key covering multiple columns
foreign_keys (Iterable[str | ForeignKey | Tuple[str, str] | Tuple[str, str, str] | Tuple[str, str, str, str]] | List[str | ForeignKey | Tuple[str, str] | Tuple[str, str, str] | Tuple[str, str, str, str]] | None) – List of foreign key definitions for this table
column_order (List[str] | None) – List specifying which columns should come first
not_null (Iterable[str] | None) – List of columns that should be created as
NOT NULL
defaults (Dict[str, Any] | None) – Dictionary specifying default values for columns
hash_id (str | None) – Name of column to be used as a primary key containing a hash of the other columns
hash_id_columns (Iterable[str] | None) – List of columns to be used when calculating the hash ID for a row
extracts (Dict[str, str] | List[str] | None) – List or dictionary of columns to be extracted during inserts, see Populating lookup tables automatically during insert/upsert
if_not_exists (bool) – Use
CREATE TABLE IF NOT EXISTS
strict (bool) – Apply STRICT mode to table
- Return type:
str
- create_table(name, columns, pk=None, foreign_keys=None, column_order=None, not_null=None, defaults=None, hash_id=None, hash_id_columns=None, extracts=None, if_not_exists=False, replace=False, ignore=False, transform=False, strict=False)[source]¶
Create a table with the specified name and the specified
{column_name: type}
columns.See Explicitly creating a table.
- Parameters:
name (str) – Name of table
columns (Dict[str, Any]) – Dictionary mapping column names to their types, for example
{"name": str, "age": int}
pk (Any | None) – String name of column to use as a primary key, or a tuple of strings for a compound primary key covering multiple columns
foreign_keys (Iterable[str | ForeignKey | Tuple[str, str] | Tuple[str, str, str] | Tuple[str, str, str, str]] | List[str | ForeignKey | Tuple[str, str] | Tuple[str, str, str] | Tuple[str, str, str, str]] | None) – List of foreign key definitions for this table
column_order (List[str] | None) – List specifying which columns should come first
not_null (Iterable[str] | None) – List of columns that should be created as
NOT NULL
defaults (Dict[str, Any] | None) – Dictionary specifying default values for columns
hash_id (str | None) – Name of column to be used as a primary key containing a hash of the other columns
hash_id_columns (Iterable[str] | None) – List of columns to be used when calculating the hash ID for a row
extracts (Dict[str, str] | List[str] | None) – List or dictionary of columns to be extracted during inserts, see Populating lookup tables automatically during insert/upsert
if_not_exists (bool) – Use
CREATE TABLE IF NOT EXISTS
replace (bool) – Drop and replace table if it already exists
ignore (bool) – Silently do nothing if table already exists
transform (bool) – If table already exists transform it to fit the specified schema
strict (bool) – Apply STRICT mode to table
- Return type:
- rename_table(name, new_name)[source]¶
Rename a table.
- Parameters:
name (str) – Current table name
new_name (str) – Name to rename it to
- create_view(name, sql, ignore=False, replace=False)[source]¶
Create a new SQL view with the specified name -
sql
should start withSELECT ...
.- Parameters:
name (str) – Name of the view
sql (str) – SQL
SELECT
query to use for this view.ignore (bool) – Set to
True
to do nothing if a view with this name already existsreplace (bool) – Set to
True
to replace the view if one with this name already exists
- m2m_table_candidates(table, other_table)[source]¶
Given two table names returns the name of tables that could define a many-to-many relationship between those two tables, based on having foreign keys to both of the provided tables.
- Parameters:
table (str) – Table name
other_table (str) – Other table name
- Return type:
List[str]
- add_foreign_keys(foreign_keys)[source]¶
See Adding multiple foreign key constraints at once.
- Parameters:
foreign_keys (Iterable[Tuple[str, str, str, str]]) – A list of
(table, column, other_table, other_column)
tuples
- index_foreign_keys()[source]¶
Create indexes for every foreign key column on every table in the database.
- analyze(name=None)[source]¶
Run
ANALYZE
against the entire database or a named table or index.- Parameters:
name – Run
ANALYZE
against this specific named table or index
- iterdump()[source]¶
A sequence of strings representing a SQL dump of the database
- Return type:
Generator[str, None, None]
- init_spatialite(path=None)[source]¶
The
init_spatialite
method will load and initialize the SpatiaLite extension. Thepath
argument should be an absolute path to the compiled extension, which can be found usingfind_spatialite
.Returns
True
if SpatiaLite was successfully initialized.from sqlite_utils.db import Database from sqlite_utils.utils import find_spatialite db = Database("mydb.db") db.init_spatialite(find_spatialite())
If you’ve installed SpatiaLite somewhere unexpected (for testing an alternate version, for example) you can pass in an absolute path:
from sqlite_utils.db import Database from sqlite_utils.utils import find_spatialite db = Database("mydb.db") db.init_spatialite("./local/mod_spatialite.dylib")
- Parameters:
path (str | None) – Path to SpatiaLite module on disk
- Return type:
bool
sqlite_utils.db.Queryable¶
Table and View are both subclasses of Queryable
, providing access to the following methods:
- class sqlite_utils.db.Queryable(db, name)[source]¶
-
- count_where(where=None, where_args=None)[source]¶
Executes
SELECT count(*) FROM table WHERE ...
and returns a count.- Parameters:
where (str | None) – SQL where fragment to use, for example
id > ?
where_args (Iterable | dict | None) – Parameters to use with that fragment - an iterable for
id > ?
parameters, or a dictionary forid > :id
- Return type:
int
- property rows: Generator[dict, None, None][source]¶
Iterate over every dictionaries for each row in this table or view.
- rows_where(where=None, where_args=None, order_by=None, select='*', limit=None, offset=None)[source]¶
Iterate over every row in this table or view that matches the specified where clause.
Returns each row as a dictionary. See Listing rows for more details.
- Parameters:
where (str | None) – SQL where fragment to use, for example
id > ?
where_args (Iterable | dict | None) – Parameters to use with that fragment - an iterable for
id > ?
parameters, or a dictionary forid > :id
order_by (str | None) – Column or fragment of SQL to order by
select (str) – Comma-separated list of columns to select - defaults to
*
limit (int | None) – Integer number of rows to limit to
offset (int | None) – Integer for SQL offset
- Return type:
Generator[dict, None, None]
- pks_and_rows_where(where=None, where_args=None, order_by=None, limit=None, offset=None)[source]¶
Like
.rows_where()
but returns(pk, row)
pairs -pk
can be a single value or tuple.- Parameters:
where (str | None) – SQL where fragment to use, for example
id > ?
where_args (Iterable | dict | None) – Parameters to use with that fragment - an iterable for
id > ?
parameters, or a dictionary forid > :id
order_by (str | None) – Column or fragment of SQL to order by
select – Comma-separated list of columns to select - defaults to
*
limit (int | None) – Integer number of rows to limit to
offset (int | None) – Integer for SQL offset
- Return type:
Generator[Tuple[Any, Dict], None, None]
- property columns: List[Column][source]¶
List of Columns representing the columns in this table or view.
sqlite_utils.db.Table¶
- class sqlite_utils.db.Table(db, name, pk=None, foreign_keys=None, column_order=None, not_null=None, defaults=None, batch_size=100, hash_id=None, hash_id_columns=None, alter=False, ignore=False, replace=False, extracts=None, conversions=None, columns=None, strict=False)[source]¶
Bases:
Queryable
Tables should usually be initialized using the
db.table(table_name)
ordb[table_name]
methods.The following optional parameters can be passed to
db.table(table_name, ...)
:- Parameters:
db (Database) – Provided by
db.table(table_name)
name (str) – Provided by
db.table(table_name)
pk (Any | None) – Name of the primary key column, or tuple of columns
foreign_keys (Iterable[str | ForeignKey | Tuple[str, str] | Tuple[str, str, str] | Tuple[str, str, str, str]] | List[str | ForeignKey | Tuple[str, str] | Tuple[str, str, str] | Tuple[str, str, str, str]] | None) – List of foreign key definitions
column_order (List[str] | None) – List of column names in the order they should be in the table
not_null (Iterable[str] | None) – List of columns that cannot be null
defaults (Dict[str, Any] | None) – Dictionary of column names and default values
batch_size (int) – Integer number of rows to insert at a time
hash_id (str | None) – If True, use a hash of the row values as the primary key
hash_id_columns (Iterable[str] | None) – List of columns to use for the hash_id
alter (bool) – If True, automatically alter the table if it doesn’t match the schema
ignore (bool) – If True, ignore rows that already exist when inserting
replace (bool) – If True, replace rows that already exist when inserting
extracts (Dict[str, str] | List[str] | None) – Dictionary or list of column names to extract into a separate table on inserts
conversions (dict | None) – Dictionary of column names and conversion functions
columns (Dict[str, Any] | None) – Dictionary of column names to column types
strict (bool) – If True, apply STRICT mode to table
- last_rowid: int | None = None¶
The
rowid
of the last inserted, updated or selected row.
- last_pk: Any | None = None¶
The primary key of the last inserted, updated or selected row.
- property count: int[source]¶
Count of the rows in this table - optionally from the table count cache, if configured.
- property use_rowid: bool[source]¶
Does this table use
rowid
for its primary key (no other primary keys are specified)?
- get(pk_values)[source]¶
Return row (as dictionary) for the specified primary key.
Raises
sqlite_utils.db.NotFoundError
if a matching row cannot be found.- Parameters:
pk_values (list | tuple | str | int) – A single value, or a tuple of values for tables that have a compound primary key
- Return type:
dict
- property virtual_table_using: str | None[source]¶
Type of virtual table, or
None
if this is not a virtual table.
- property xindexes: List[XIndex][source]¶
List of indexes defined on this table using the more detailed
XIndex
format.
- property triggers_dict: Dict[str, str][source]¶
{trigger_name: sql}
dictionary of triggers defined on this table.
- property default_values: Dict[str, Any][source]¶
{column_name: default_value}
dictionary of default values for columns in this table.
- create(columns, pk=None, foreign_keys=None, column_order=None, not_null=None, defaults=None, hash_id=None, hash_id_columns=None, extracts=None, if_not_exists=False, replace=False, ignore=False, transform=False, strict=False)[source]¶
Create a table with the specified columns.
See Explicitly creating a table for full details.
- Parameters:
columns (Dict[str, Any]) – Dictionary mapping column names to their types, for example
{"name": str, "age": int}
pk (Any | None) – String name of column to use as a primary key, or a tuple of strings for a compound primary key covering multiple columns
foreign_keys (Iterable[str | ForeignKey | Tuple[str, str] | Tuple[str, str, str] | Tuple[str, str, str, str]] | List[str | ForeignKey | Tuple[str, str] | Tuple[str, str, str] | Tuple[str, str, str, str]] | None) – List of foreign key definitions for this table
column_order (List[str] | None) – List specifying which columns should come first
not_null (Iterable[str] | None) – List of columns that should be created as
NOT NULL
defaults (Dict[str, Any] | None) – Dictionary specifying default values for columns
hash_id (str | None) – Name of column to be used as a primary key containing a hash of the other columns
hash_id_columns (Iterable[str] | None) – List of columns to be used when calculating the hash ID for a row
extracts (Dict[str, str] | List[str] | None) – List or dictionary of columns to be extracted during inserts, see Populating lookup tables automatically during insert/upsert
if_not_exists (bool) – Use
CREATE TABLE IF NOT EXISTS
replace (bool) – Drop and replace table if it already exists
ignore (bool) – Silently do nothing if table already exists
transform (bool) – If table already exists transform it to fit the specified schema
strict (bool) – Apply STRICT mode to table
- Return type:
- duplicate(new_name)[source]¶
Create a duplicate of this table, copying across the schema and all row data.
- Parameters:
new_name (str) – Name of the new table
- Return type:
- transform(*, types=None, rename=None, drop=None, pk=<sqlite_utils.db.Default object>, not_null=None, defaults=None, drop_foreign_keys=None, add_foreign_keys=None, foreign_keys=None, column_order=None, keep_table=None)[source]¶
Apply an advanced alter table, including operations that are not supported by
ALTER TABLE
in SQLite itself.See Transforming a table for full details.
- Parameters:
types (dict | None) – Columns that should have their type changed, for example
{"weight": float}
rename (dict | None) – Columns to rename, for example
{"headline": "title"}
drop (Iterable | None) – Columns to drop
pk (Any | None) – New primary key for the table
not_null (Iterable[str] | None) – Columns to set as
NOT NULL
defaults (Dict[str, Any] | None) – Default values for columns
drop_foreign_keys (Iterable[str] | None) – Names of columns that should have their foreign key constraints removed
add_foreign_keys (Iterable[str | ForeignKey | Tuple[str, str] | Tuple[str, str, str] | Tuple[str, str, str, str]] | List[str | ForeignKey | Tuple[str, str] | Tuple[str, str, str] | Tuple[str, str, str, str]] | None) – List of foreign keys to add to the table
foreign_keys (Iterable[str | ForeignKey | Tuple[str, str] | Tuple[str, str, str] | Tuple[str, str, str, str]] | List[str | ForeignKey | Tuple[str, str] | Tuple[str, str, str] | Tuple[str, str, str, str]] | None) – List of foreign keys to set for the table, replacing any existing foreign keys
column_order (List[str] | None) – List of strings specifying a full or partial column order to use when creating the table
keep_table (str | None) – If specified, the existing table will be renamed to this and will not be dropped
- Return type:
- transform_sql(*, types=None, rename=None, drop=None, pk=<sqlite_utils.db.Default object>, not_null=None, defaults=None, drop_foreign_keys=None, add_foreign_keys=None, foreign_keys=None, column_order=None, tmp_suffix=None, keep_table=None)[source]¶
Return a list of SQL statements that should be executed in order to apply this transformation.
- Parameters:
types (dict | None) – Columns that should have their type changed, for example
{"weight": float}
rename (dict | None) – Columns to rename, for example
{"headline": "title"}
drop (Iterable | None) – Columns to drop
pk (Any | None) – New primary key for the table
not_null (Iterable[str] | None) – Columns to set as
NOT NULL
defaults (Dict[str, Any] | None) – Default values for columns
drop_foreign_keys (Iterable | None) – Names of columns that should have their foreign key constraints removed
add_foreign_keys (Iterable[str | ForeignKey | Tuple[str, str] | Tuple[str, str, str] | Tuple[str, str, str, str]] | List[str | ForeignKey | Tuple[str, str] | Tuple[str, str, str] | Tuple[str, str, str, str]] | None) – List of foreign keys to add to the table
foreign_keys (Iterable[str | ForeignKey | Tuple[str, str] | Tuple[str, str, str] | Tuple[str, str, str, str]] | List[str | ForeignKey | Tuple[str, str] | Tuple[str, str, str] | Tuple[str, str, str, str]] | None) – List of foreign keys to set for the table, replacing any existing foreign keys
column_order (List[str] | None) – List of strings specifying a full or partial column order to use when creating the table
tmp_suffix (str | None) – Suffix to use for the temporary table name
keep_table (str | None) – If specified, the existing table will be renamed to this and will not be dropped
- Return type:
List[str]
- extract(columns, table=None, fk_column=None, rename=None)[source]¶
Extract specified columns into a separate table.
See Extracting columns into a separate table for details.
- Parameters:
columns (str | Iterable[str]) – Single column or list of columns that should be extracted
table (str | None) – Name of table in which the new records should be created
fk_column (str | None) – Name of the foreign key column to populate in the original table
rename (Dict[str, str] | None) – Dictionary of columns that should be renamed when populating the new table
- Return type:
- create_index(columns, index_name=None, unique=False, if_not_exists=False, find_unique_name=False, analyze=False)[source]¶
Create an index on this table.
- Parameters:
columns (Iterable[str | DescIndex]) – A single columns or list of columns to index. These can be strings or, to create an index using the column in descending order,
db.DescIndex(column_name)
objects.index_name (str | None) – The name to use for the new index. Defaults to the column names joined on
_
.unique (bool) – Should the index be marked as unique, forcing unique values?
if_not_exists (bool) – Only create the index if one with that name does not already exist.
find_unique_name (bool) – If
index_name
is not provided and the automatically derived name already exists, keep incrementing a suffix number to find an available name.analyze (bool) – Run
ANALYZE
against this index after creating it.
See Creating indexes.
- add_column(col_name, col_type=None, fk=None, fk_col=None, not_null_default=None)[source]¶
Add a column to this table. See Adding columns.
- Parameters:
col_name (str) – Name of the new column
col_type (Any | None) – Column type - a Python type such as
str
or a SQLite type string such as"BLOB"
fk (str | None) – Name of a table that this column should be a foreign key reference to
fk_col (str | None) – Column in the foreign key table that this should reference
not_null_default (Any | None) – Set this column to
not null
and give it this default value
- drop(ignore=False)[source]¶
Drop this table.
- Parameters:
ignore (bool) – Set to
True
to ignore the error if the table does not exist
- guess_foreign_table(column)[source]¶
For a given column, suggest another table that might be referenced by this column should it be used as a foreign key.
For example, a column called
tag_id
ortag
ortags
might suggest atag
table, if one exists.If no candidates can be found, raises a
NoObviousTable
exception.- Parameters:
column (str) – Name of column
- Return type:
str
- add_foreign_key(column, other_table=None, other_column=None, ignore=False)[source]¶
Alter the schema to mark the specified column as a foreign key to another table.
- Parameters:
column (str) – The column to mark as a foreign key.
other_table (str | None) – The table it refers to - if omitted, will be guessed based on the column name.
other_column (str | None) – The column on the other table it - if omitted, will be guessed.
ignore (bool) – Set this to
True
to ignore an existing foreign key - otherwise aAlterError
will be raised.
- enable_counts()[source]¶
Set up triggers to update a cache of the count of rows in this table.
See Cached table counts using triggers for details.
- property has_counts_triggers: bool[source]¶
Does this table have triggers setup to update cached counts?
- enable_fts(columns, fts_version='FTS5', create_triggers=False, tokenize=None, replace=False)[source]¶
Enable SQLite full-text search against the specified columns.
See Full-text search for more details.
- Parameters:
columns (Iterable[str]) – List of column names to include in the search index.
fts_version (str) – FTS version to use - defaults to
FTS5
but you may wantFTS4
for older SQLite versions.create_triggers (bool) – Should triggers be created to keep the search index up-to-date? Defaults to
False
.tokenize (str | None) – Custom SQLite tokenizer to use, for example
"porter"
to enable Porter stemming.replace (bool) – Should any existing FTS index for this table be replaced by the new one?
- populate_fts(columns)[source]¶
Update the associated SQLite full-text search index with the latest data from the table for the specified columns.
- Parameters:
columns (Iterable[str]) – Columns to populate the data for
- Return type:
- disable_fts()[source]¶
Remove any full-text search index and related triggers configured for this table.
- Return type:
- rebuild_fts()[source]¶
Run the
rebuild
operation against the associated full-text search index table.
- detect_fts()[source]¶
Detect if table has a corresponding FTS virtual table and return it
- Return type:
str | None
- optimize()[source]¶
Run the
optimize
operation against the associated full-text search index table.- Return type:
- search_sql(columns=None, order_by=None, limit=None, offset=None, where=None, include_rank=False)[source]¶
” Return SQL string that can be used to execute searches against this table.
- Parameters:
columns (Iterable[str] | None) – Columns to search against
order_by (str | None) – Column or SQL expression to sort by
limit (int | None) – SQL limit
offset (int | None) – SQL offset
where (str | None) – Extra SQL fragment for the WHERE clause
include_rank (bool) – Select the search rank column in the final query
- Return type:
str
- search(q, order_by=None, columns=None, limit=None, offset=None, where=None, where_args=None, quote=False)[source]¶
Execute a search against this table using SQLite full-text search, returning a sequence of dictionaries for each row.
- Parameters:
q (str) – Terms to search for
order_by (str | None) – Defaults to order by rank, or specify a column here.
columns (Iterable[str] | None) – List of columns to return, defaults to all columns.
limit (int | None) – Optional integer limit for returned rows.
offset (int | None) – Optional integer SQL offset.
where (str | None) – Extra SQL fragment for the WHERE clause
where_args (Iterable | dict | None) – Arguments to use for :param placeholders in the extra WHERE clause
quote (bool) – Apply quoting to disable any special characters in the search query
- Return type:
Generator[dict, None, None]
- delete(pk_values)[source]¶
Delete row matching the specified primary key.
- Parameters:
pk_values (list | tuple | str | int | float) – A single value, or a tuple of values for tables that have a compound primary key
- Return type:
- delete_where(where=None, where_args=None, analyze=False)[source]¶
Delete rows matching the specified where clause, or delete all rows in the table.
See Deleting multiple records.
- Parameters:
where (str | None) – SQL where fragment to use, for example
id > ?
where_args (Iterable | dict | None) – Parameters to use with that fragment - an iterable for
id > ?
parameters, or a dictionary forid > :id
analyze (bool) – Set to
True
to runANALYZE
after the rows have been deleted.
- Return type:
- update(pk_values, updates=None, alter=False, conversions=None)[source]¶
Execute a SQL
UPDATE
against the specified row.See Updating a specific record.
- Parameters:
pk_values (list | tuple | str | int | float) – The primary key of an individual record - can be a tuple if the table has a compound primary key.
updates (dict | None) – A dictionary mapping columns to their updated values.
alter (bool) – Set to
True
to add any missing columns.conversions (dict | None) – Optional dictionary of SQL functions to apply during the update, for example
{"mycolumn": "upper(?)"}
.
- Return type:
- convert(columns, fn, output=None, output_type=None, drop=False, multi=False, where=None, where_args=None, show_progress=False, skip_false=True)[source]¶
Apply conversion function
fn
to every value in the specified columns.- Parameters:
columns (str | List[str]) – A single column or list of string column names to convert.
fn (Callable) – A callable that takes a single argument,
value
, and returns it converted.output (str | None) – Optional string column name to write the results to (defaults to the input column).
output_type (Any | None) – If the output column needs to be created, this is the type that will be used for the new column.
drop (bool) – Should the original column be dropped once the conversion is complete?
multi (bool) – If
True
the return value offn(value)
will be expected to be a dictionary, and new columns will be created for each key of that dictionary.where (str | None) – SQL fragment to use as a
WHERE
clause to limit the rows to which the conversion is applied, for exampleage > ?
orage > :age
.where_args (Iterable | dict | None) – List of arguments (if using
?
) or a dictionary (if using:age
).show_progress (bool) – Should a progress bar be displayed?
skip_false (bool)
- insert(record, pk=<sqlite_utils.db.Default object>, foreign_keys=<sqlite_utils.db.Default object>, column_order=<sqlite_utils.db.Default object>, not_null=<sqlite_utils.db.Default object>, defaults=<sqlite_utils.db.Default object>, hash_id=<sqlite_utils.db.Default object>, hash_id_columns=<sqlite_utils.db.Default object>, alter=<sqlite_utils.db.Default object>, ignore=<sqlite_utils.db.Default object>, replace=<sqlite_utils.db.Default object>, extracts=<sqlite_utils.db.Default object>, conversions=<sqlite_utils.db.Default object>, columns=<sqlite_utils.db.Default object>, strict=<sqlite_utils.db.Default object>)[source]¶
Insert a single record into the table. The table will be created with a schema that matches the inserted record if it does not already exist, see Creating tables.
record
- required: a dictionary representing the record to be inserted.
The other parameters are optional, and mostly influence how the new table will be created if that table does not exist yet.
Each of them defaults to
DEFAULT
, which indicates that the default setting for the currentTable
object (specified in the table constructor) should be used.- Parameters:
record (Dict[str, Any]) – Dictionary record to be inserted
pk – If creating the table, which column should be the primary key.
foreign_keys – See Specifying foreign keys.
column_order (List[str] | Default | None) – List of strings specifying a full or partial column order to use when creating the table.
not_null (Iterable[str] | Default | None) – Set of strings specifying columns that should be
NOT NULL
.defaults (Dict[str, Any] | Default | None) – Dictionary specifying default values for specific columns.
hash_id (str | Default | None) – Name of a column to create and use as a primary key, where the value of thet primary key will be derived as a SHA1 hash of the other column values in the record.
hash_id="id"
is a common column name used for this.alter (bool | Default | None) – Boolean, should any missing columns be added automatically?
ignore (bool | Default | None) – Boolean, if a record already exists with this primary key, ignore this insert.
replace (bool | Default | None) – Boolean, if a record already exists with this primary key, replace it with this new record.
extracts (Dict[str, str] | List[str] | Default | None) – A list of columns to extract to other tables, or a dictionary that maps
{column_name: other_table_name}
. See Populating lookup tables automatically during insert/upsert.conversions (Dict[str, str] | Default | None) – Dictionary specifying SQL conversion functions to be applied to the data while it is being inserted, for example
{"name": "upper(?)"}
. See Converting column values using SQL functions.columns (Dict[str, Any] | Default | None) – Dictionary over-riding the detected types used for the columns, for example
{"age": int, "weight": float}
.strict (bool | Default | None) – Boolean, apply STRICT mode if creating the table.
hash_id_columns (Iterable[str] | Default | None)
- Return type:
- insert_all(records, pk=<sqlite_utils.db.Default object>, foreign_keys=<sqlite_utils.db.Default object>, column_order=<sqlite_utils.db.Default object>, not_null=<sqlite_utils.db.Default object>, defaults=<sqlite_utils.db.Default object>, batch_size=<sqlite_utils.db.Default object>, hash_id=<sqlite_utils.db.Default object>, hash_id_columns=<sqlite_utils.db.Default object>, alter=<sqlite_utils.db.Default object>, ignore=<sqlite_utils.db.Default object>, replace=<sqlite_utils.db.Default object>, truncate=False, extracts=<sqlite_utils.db.Default object>, conversions=<sqlite_utils.db.Default object>, columns=<sqlite_utils.db.Default object>, upsert=False, analyze=False, strict=<sqlite_utils.db.Default object>)[source]¶
Like
.insert()
but takes a list of records and ensures that the table that it creates (if table does not exist) has columns for ALL of that data.Use
analyze=True
to runANALYZE
after the insert has completed.- Return type:
- upsert(record, pk=<sqlite_utils.db.Default object>, foreign_keys=<sqlite_utils.db.Default object>, column_order=<sqlite_utils.db.Default object>, not_null=<sqlite_utils.db.Default object>, defaults=<sqlite_utils.db.Default object>, hash_id=<sqlite_utils.db.Default object>, hash_id_columns=<sqlite_utils.db.Default object>, alter=<sqlite_utils.db.Default object>, extracts=<sqlite_utils.db.Default object>, conversions=<sqlite_utils.db.Default object>, columns=<sqlite_utils.db.Default object>, strict=<sqlite_utils.db.Default object>)[source]¶
Like
.insert()
but performs anUPSERT
, where records are inserted if they do not exist and updated if they DO exist, based on matching against their primary key.See Upserting data.
- Return type:
- upsert_all(records, pk=<sqlite_utils.db.Default object>, foreign_keys=<sqlite_utils.db.Default object>, column_order=<sqlite_utils.db.Default object>, not_null=<sqlite_utils.db.Default object>, defaults=<sqlite_utils.db.Default object>, batch_size=<sqlite_utils.db.Default object>, hash_id=<sqlite_utils.db.Default object>, hash_id_columns=<sqlite_utils.db.Default object>, alter=<sqlite_utils.db.Default object>, extracts=<sqlite_utils.db.Default object>, conversions=<sqlite_utils.db.Default object>, columns=<sqlite_utils.db.Default object>, analyze=False, strict=<sqlite_utils.db.Default object>)[source]¶
Like
.upsert()
but can be applied to a list of records.- Return type:
- lookup(lookup_values, extra_values=None, pk='id', foreign_keys=None, column_order=None, not_null=None, defaults=None, extracts=None, conversions=None, columns=None, strict=False)[source]¶
Create or populate a lookup table with the specified values.
db["Species"].lookup({"name": "Palm"})
will create a table calledSpecies
(if one does not already exist) with two columns:id
andname
. It will set up a unique constraint on thename
column to guarantee it will not contain duplicate rows.It will then insert a new row with the
name
set toPalm
and return the new integer primary key value.An optional second argument can be provided with more
name: value
pairs to be included only if the record is being created for the first time. These will be ignored on subsequent lookup calls for records that already exist.All other keyword arguments are passed through to
.insert()
.See Working with lookup tables for more details.
- Parameters:
lookup_values (Dict[str, Any]) – Dictionary specifying column names and values to use for the lookup
extra_values (Dict[str, Any] | None) – Additional column values to be used only if creating a new record
strict (bool | None) – Boolean, apply STRICT mode if creating the table.
pk (str | None)
foreign_keys (Iterable[str | ForeignKey | Tuple[str, str] | Tuple[str, str, str] | Tuple[str, str, str, str]] | List[str | ForeignKey | Tuple[str, str] | Tuple[str, str, str] | Tuple[str, str, str, str]] | None)
column_order (List[str] | None)
not_null (Iterable[str] | None)
defaults (Dict[str, Any] | None)
extracts (Dict[str, str] | List[str] | None)
conversions (Dict[str, str] | None)
columns (Dict[str, Any] | None)
- m2m(other_table, record_or_iterable=None, pk=<sqlite_utils.db.Default object>, lookup=None, m2m_table=None, alter=False)[source]¶
After inserting a record in a table, create one or more records in some other table and then create many-to-many records linking the original record and the newly created records together.
For example:
db["dogs"].insert({"id": 1, "name": "Cleo"}, pk="id").m2m( "humans", {"id": 1, "name": "Natalie"}, pk="id" )
See Working with many-to-many relationships for details.
- Parameters:
other_table (str | Table) – The name of the table to insert the new records into.
record_or_iterable (Iterable[Dict[str, Any]] | Dict[str, Any] | None) – A single dictionary record to insert, or a list of records.
pk (Any | Default | None) – The primary key to use if creating
other_table
.lookup (Dict[str, Any] | None) – Same dictionary as for
.lookup()
, to create a many-to-many lookup table.m2m_table (str | None) – The string name to use for the many-to-many table, defaults to creating this automatically based on the names of the two tables.
alter (bool) – Set to
True
to add any missing columns onother_table
if that table already exists.
- analyze_column(column, common_limit=10, value_truncate=None, total_rows=None, most_common=True, least_common=True)[source]¶
Return statistics about the specified column.
See Analyzing a column.
- Parameters:
column (str) – Column to analyze
common_limit (int) – Show this many column values
value_truncate – Truncate display of common values to this many characters
total_rows – Optimization - pass the total number of rows in the table to save running a fresh
count(*)
querymost_common (bool) – If
True
, calculate the most common valuesleast_common (bool) – If
True
, calculate the least common values
- Return type:
- add_geometry_column(column_name, geometry_type, srid=4326, coord_dimension='XY', not_null=False)[source]¶
In SpatiaLite, a geometry column can only be added to an existing table. To do so, use
table.add_geometry_column
, passing in a geometry type.By default, this will add a nullable column using SRID 4326. This can be customized using the
column_name
,srid
andnot_null
arguments.Returns
True
if the column was successfully added,False
if not.from sqlite_utils.db import Database from sqlite_utils.utils import find_spatialite db = Database("mydb.db") db.init_spatialite(find_spatialite()) # the table must exist before adding a geometry column table = db["locations"].create({"name": str}) table.add_geometry_column("geometry", "POINT")
- Parameters:
column_name (str) – Name of column to add
geometry_type (str) – Type of geometry column, for example
"GEOMETRY"
or"POINT" or ``"POLYGON"
srid (int) – Integer SRID, defaults to 4326 for WGS84
coord_dimension (str) – Dimensions to use, defaults to
"XY"
- set to"XYZ"
to work in three dimensionsnot_null (bool) – Should the column be
NOT NULL
- Return type:
bool
- create_spatial_index(column_name)[source]¶
A spatial index allows for significantly faster bounding box queries. To create one, use
create_spatial_index
with the name of an existing geometry column.Returns
True
if the index was successfully created,False
if not. Calling this function if an index already exists is a no-op.# assuming SpatiaLite is loaded, create the table, add the column table = db["locations"].create({"name": str}) table.add_geometry_column("geometry", "POINT") # now we can index it table.create_spatial_index("geometry") # the spatial index is a virtual table, which we can inspect print(db["idx_locations_geometry"].schema) # outputs: # CREATE VIRTUAL TABLE "idx_locations_geometry" USING rtree(pkid, xmin, xmax, ymin, ymax)
- Parameters:
column_name – Geometry column to create the spatial index against
- Return type:
bool
sqlite_utils.db.View¶
Other¶
sqlite_utils.db.Column¶
- class sqlite_utils.db.Column(cid, name, type, notnull, default_value, is_pk)[source]¶
Describes a SQLite column returned by the
Table.columns
property.cid
Column index
name
Column name
type
Column type
notnull
Does the column have a
not null
constraintdefault_value
Default value for this column
is_pk
Is this column part of the primary key
sqlite_utils.db.ColumnDetails¶
- class sqlite_utils.db.ColumnDetails(table, column, total_rows, num_null, num_blank, num_distinct, most_common, least_common)[source]¶
Summary information about a column, see Analyzing a column.
table
The name of the table
column
The name of the column
total_rows
The total number of rows in the table
num_null
The number of rows for which this column is null
num_blank
The number of rows for which this column is blank (the empty string)
num_distinct
The number of distinct values in this column
most_common
The
N
most common values as a list of(value, count)
tuples, orNone
if the table consists entirely of distinct valuesleast_common
The
N
least common values as a list of(value, count)
tuples, orNone
if the table is entirely distinct or if the number of distinct values is less than N (since they will already have been returned inmost_common
)
sqlite_utils.utils¶
sqlite_utils.utils.hash_record¶
- sqlite_utils.utils.hash_record(record, keys=None)[source]¶
record
should be a Python dictionary. Returns a sha1 hash of the keys and values in that record.If
keys=
is provided, uses just those keys to generate the hash.Example usage:
from sqlite_utils.utils import hash_record hashed = hash_record({"name": "Cleo", "twitter": "CleoPaws"}) # Or with the keys= option: hashed = hash_record( {"name": "Cleo", "twitter": "CleoPaws", "age": 7}, keys=("name", "twitter") )
- Parameters:
record (Dict) – Record to generate a hash for
keys (Iterable[str] | None) – Subset of keys to use for that hash
sqlite_utils.utils.rows_from_file¶
- sqlite_utils.utils.rows_from_file(fp, format=None, dialect=None, encoding=None, ignore_extras=False, extras_key=None)[source]¶
Load a sequence of dictionaries from a file-like object containing one of four different formats.
from sqlite_utils.utils import rows_from_file import io rows, format = rows_from_file(io.StringIO("id,name\n1,Cleo"))) print(list(rows), format) # Outputs [{'id': '1', 'name': 'Cleo'}] Format.CSV
This defaults to attempting to automatically detect the format of the data, or you can pass in an explicit format using the format= option.
Returns a tuple of
(rows_generator, format_used)
whererows_generator
can be iterated over to return dictionaries, whileformat_used
is a value from thesqlite_utils.utils.Format
enum:class Format(enum.Enum): CSV = 1 TSV = 2 JSON = 3 NL = 4
If a CSV or TSV file includes rows with more fields than are declared in the header a
sqlite_utils.utils.RowError
exception will be raised when you loop over the generator.You can instead ignore the extra data by passing
ignore_extras=True
.Or pass
extras_key="rest"
to put those additional values in a list in a key calledrest
.- Parameters:
fp (BinaryIO) – a file-like object containing binary data
format (Format | None) – the format to use - omit this to detect the format
dialect (Type[Dialect] | None) – the CSV dialect to use - omit this to detect the dialect
encoding (str | None) – the character encoding to use when reading CSV/TSV data
ignore_extras (bool | None) – ignore any extra fields on rows
extras_key (str | None) – put any extra fields in a list with this key
- Return type:
Tuple[Iterable[dict], Format]
sqlite_utils.utils.TypeTracker¶
- class sqlite_utils.utils.TypeTracker[source]¶
Wrap an iterator of dictionaries and keep track of which SQLite column types are the most likely fit for each of their keys.
Example usage:
from sqlite_utils.utils import TypeTracker import sqlite_utils db = sqlite_utils.Database(memory=True) tracker = TypeTracker() rows = [{"id": "1", "name": "Cleo", "id": "2", "name": "Cardi"}] db["creatures"].insert_all(tracker.wrap(rows)) print(tracker.types) # Outputs {'id': 'integer', 'name': 'text'} db["creatures"].transform(types=tracker.types)