API reference
sqlite_utils.db.Database
- class sqlite_utils.db.Database(filename_or_conn: Optional[Union[str, pathlib.Path, sqlite3.Connection]] = None, memory: bool = False, memory_name: Optional[str] = None, recreate: bool = False, recursive_triggers: bool = True, tracer: Optional[Callable] = None, use_counts_table: bool = False)
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)
filename_or_conn
- String path to a file, or apathlib.Path
object, or asqlite3
connectionmemory
- set toTrue
to create an in-memory databasememory_name
- creates a named in-memory database that can be shared across multiple connections.recreate
- set toTrue
to delete and recreate a file database (dangerous)recursive_triggers
- defaults toTrue
, which setsPRAGMA recursive_triggers=on;
- set toFalse
to avoid setting this pragmatracer
- set a tracer function (print
works for this) which will be called withsql, parameters
every time a SQL query is executeduse_counts_table
- set toTrue
to use a cached counts table, if available. See Cached table counts using triggers.
- tracer(tracer: Callable = None)
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.
- __getitem__(table_name: str) Union[sqlite_utils.db.Table, sqlite_utils.db.View]
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: Optional[Callable] = None, deterministic: bool = False, replace: bool = False)
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 def upper(value): return str(value).upper()
The decorator can take arguments:
@db.register(deterministic=True, replace=True) def upper(value): return str(value).upper()
deterministic
- setTrue
for functions that always returns the same output for a given inputreplace
- setTrue
to replace an existing function with the same name - otherwise throw an error
- register_fts4_bm25()
Register the
rank_bm25(match_info)
function used for calculating relevance with SQLite FTS4.
- attach(alias: str, filepath: Union[str, pathlib.Path])
Attach another SQLite database file to this connection with the specified alias, equivalent to:
ATTACH DATABASE 'filepath.db' AS alias
- query(sql: str, params: Optional[Union[Iterable, dict]] = None) Generator[dict, None, None]
Execute
sql
and return an iterable of dictionaries representing each row.
- execute(sql: str, parameters: Optional[Union[Iterable, dict]] = None) sqlite3.Cursor
Execute SQL query and return a
sqlite3.Cursor
.
- executescript(sql: str) sqlite3.Cursor
Execute multiple SQL statements separated by ; and return the
sqlite3.Cursor
.
- table(table_name: str, **kwargs) Union[sqlite_utils.db.Table, sqlite_utils.db.View]
Return a table object, optionally configured with default options.
- quote(value: str) str
Apply SQLite string quoting to a value, including wrappping it in single quotes.
- quote_fts(query: str) str
Escape special characters in a SQLite full-text search query
- table_names(fts4: bool = False, fts5: bool = False) List[str]
A list of string table names in this database.
- view_names() List[str]
A list of string view names in this database.
- property tables: List[sqlite_utils.db.Table]
A list of Table objects in this database.
- property views: List[sqlite_utils.db.View]
A list of View objects in this database.
- property triggers: List[sqlite_utils.db.Trigger]
A list of
(name, table_name, sql)
tuples representing triggers in this database.
- property triggers_dict: Dict[str, str]
A
{trigger_name: sql}
dictionary of triggers in this database.
- property schema: str
SQL schema for this database
- property supports_strict: bool
Does this database support STRICT mode?
- property journal_mode: str
Current
journal_mode
of this database.
- enable_wal()
Set
journal_mode
to'wal'
to enable Write-Ahead Log mode.
- disable_wal()
Set
journal_mode
back to'delete'
to disable Write-Ahead Log mode.
- enable_counts()
Enable trigger-based count caching for every table in the database, see Cached table counts using triggers.
- cached_counts(tables: Optional[Iterable[str]] = None) Dict[str, int]
Return
{table_name: count}
dictionary of cached counts for specified tables, or all tables iftables
not provided.
- reset_counts()
Re-calculate cached counts for tables.
- create_table_sql(name: str, columns: Dict[str, Any], pk: Optional[Any] = None, foreign_keys: Optional[Union[Iterable[str], Iterable[sqlite_utils.db.ForeignKey], Iterable[Tuple[str, str]], Iterable[Tuple[str, str, str]], Iterable[Tuple[str, str, str, str]]]] = None, column_order: Optional[List[str]] = None, not_null: Optional[Iterable[str]] = None, defaults: Optional[Dict[str, Any]] = None, hash_id: Optional[str] = None, extracts: Optional[Union[Dict[str, str], List[str]]] = None, if_not_exists: bool = False) str
Returns the SQL
CREATE TABLE
statement for creating the specified table.
- create_table(name: str, columns: Dict[str, Any], pk: Optional[Any] = None, foreign_keys: Optional[Union[Iterable[str], Iterable[sqlite_utils.db.ForeignKey], Iterable[Tuple[str, str]], Iterable[Tuple[str, str, str]], Iterable[Tuple[str, str, str, str]]]] = None, column_order: Optional[List[str]] = None, not_null: Optional[Iterable[str]] = None, defaults: Optional[Dict[str, Any]] = None, hash_id: Optional[str] = None, extracts: Optional[Union[Dict[str, str], List[str]]] = None, if_not_exists: bool = False) sqlite_utils.db.Table
Create a table with the specified name and the specified
{column_name: type}
columns.
- create_view(name: str, sql: str, ignore: bool = False, replace: bool = False)
Create a new SQL view with the specified name -
sql
should start withSELECT ...
.ignore
- set toTrue
to do nothing if a view with this name already existsreplace
- set toTrue
to replace the view if one with this name already exists
- m2m_table_candidates(table: str, other_table: str) List[str]
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.
- add_foreign_keys(foreign_keys: Iterable[Tuple[str, str, str, str]])
See Adding multiple foreign key constraints at once.
foreign_keys
should be a list of(table, column, other_table, other_column)
tuples, see Adding multiple foreign key constraints at once.
- index_foreign_keys()
Create indexes for every foreign key column on every table in the database.
- vacuum()
Run a SQLite
VACUUM
against the database.
- analyze(name=None)
Run
ANALYZE
against the entire database or a named table or index.
- init_spatialite(path: Optional[str] = None) bool
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")
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)
- exists() bool
Does this table or view exist yet?
- count_where(where: Optional[str] = None, where_args: Optional[Union[Iterable, dict]] = None) int
Executes
SELECT count(*) FROM table WHERE ...
and returns a count.
- property count: int
A count of the rows in this table or view.
- property rows: Generator[dict, None, None]
Iterate over every dictionaries for each row in this table or view.
- rows_where(where: Optional[str] = None, where_args: Optional[Union[Iterable, dict]] = None, order_by: Optional[str] = None, select: str = '*', limit: Optional[int] = None, offset: Optional[int] = None) Generator[dict, None, None]
Iterate over every row in this table or view that matches the specified where clause.
where
- a SQL fragment to use as aWHERE
clause, for exampleage > ?
orage > :age
.where_args
- a list of arguments (if using?
) or a dictionary (if using:age
).order_by
- optional column or fragment of SQL to order by.select
- optional comma-separated list of columns to select.limit
- optional integer number of rows to limit to.offset
- optional integer for SQL offset.
Returns each row as a dictionary. See Listing rows for more details.
- pks_and_rows_where(where: Optional[str] = None, where_args: Optional[Union[Iterable, dict]] = None, order_by: Optional[str] = None, limit: Optional[int] = None, offset: Optional[int] = None) Generator[Tuple[Any, Dict], None, None]
Like
.rows_where()
but returns(pk, row)
pairs -pk
can be a single value or tuple.
- property columns: List[sqlite_utils.db.Column]
List of Columns representing the columns in this table or view.
- property columns_dict: Dict[str, Any]
{column_name: python-type}
dictionary representing columns in this table or view.
- property schema: str
SQL schema for this table or view.
sqlite_utils.db.Table
- class sqlite_utils.db.Table(db: sqlite_utils.db.Database, name: str, pk: Optional[Any] = None, foreign_keys: Optional[Union[Iterable[str], Iterable[sqlite_utils.db.ForeignKey], Iterable[Tuple[str, str]], Iterable[Tuple[str, str, str]], Iterable[Tuple[str, str, str, str]]]] = None, column_order: Optional[List[str]] = None, not_null: Optional[Iterable[str]] = None, defaults: Optional[Dict[str, Any]] = None, batch_size: int = 100, hash_id: Optional[str] = None, alter: bool = False, ignore: bool = False, replace: bool = False, extracts: Optional[Union[Dict[str, str], List[str]]] = None, conversions: Optional[dict] = None, columns: Optional[Dict[str, Any]] = None)
Bases:
sqlite_utils.db.Queryable
Tables should usually be initialized using the
db.table(table_name)
ordb[table_name]
methods.- last_rowid: Optional[int] = None
The
rowid
of the last inserted, updated or selected row.
- last_pk: Optional[Any] = None
The primary key of the last inserted, updated or selected row.
- property count: int
Count of the rows in this table - optionally from the table count cache, if configured.
- exists() bool
Does this table or view exist yet?
- property pks: List[str]
Primary key columns for this table.
- property use_rowid: bool
Does this table use
rowid
for its primary key (no other primary keys are specified)?
- get(pk_values: Union[list, tuple, str, int]) dict
Return row (as dictionary) for the specified primary key.
Primary key can be a single value, or a tuple for tables with a compound primary key.
Raises
NotFoundError
if a matching row cannot be found.
- property foreign_keys: List[sqlite_utils.db.ForeignKey]
List of foreign keys defined on this table.
- property virtual_table_using: Optional[str]
Type of virtual table, or
None
if this is not a virtual table.
- property indexes: List[sqlite_utils.db.Index]
List of indexes defined on this table.
- property xindexes: List[sqlite_utils.db.XIndex]
List of indexes defined on this table using the more detailed
XIndex
format.
- property triggers: List[sqlite_utils.db.Trigger]
List of triggers defined on this table.
- property triggers_dict: Dict[str, str]
{trigger_name: sql}
dictionary of triggers defined on this table.
- property strict: bool
Is this a STRICT table?
- create(columns: Dict[str, Any], pk: Optional[Any] = None, foreign_keys: Optional[Union[Iterable[str], Iterable[sqlite_utils.db.ForeignKey], Iterable[Tuple[str, str]], Iterable[Tuple[str, str, str]], Iterable[Tuple[str, str, str, str]]]] = None, column_order: Optional[List[str]] = None, not_null: Optional[Iterable[str]] = None, defaults: Optional[Dict[str, Any]] = None, hash_id: Optional[str] = None, extracts: Optional[Union[Dict[str, str], List[str]]] = None, if_not_exists: bool = False) sqlite_utils.db.Table
Create a table with the specified columns.
See Explicitly creating a table for full details.
- transform(*, types=None, rename=None, drop=None, pk=<sqlite_utils.db.Default object>, not_null=None, defaults=None, drop_foreign_keys=None, column_order=None) sqlite_utils.db.Table
Apply an advanced alter table, including operations that are not supported by
ALTER TABLE
in SQLite itself.See Transforming a table for full details.
- transform_sql(*, types=None, rename=None, drop=None, pk=<sqlite_utils.db.Default object>, not_null=None, defaults=None, drop_foreign_keys=None, column_order=None, tmp_suffix=None) List[str]
Returns a list of SQL statements that would be executed in order to apply this transformation.
- extract(columns: Union[str, Iterable[str]], table: Optional[str] = None, fk_column: Optional[str] = None, rename: Optional[Dict[str, str]] = None) sqlite_utils.db.Table
Extract specified columns into a separate table.
See Extracting columns into a separate table for details.
- create_index(columns: Iterable[Union[str, sqlite_utils.db.DescIndex]], index_name: Optional[str] = None, unique: bool = False, if_not_exists: bool = False, find_unique_name: bool = False, analyze: bool = False)
Create an index on this table.
columns
- 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
- the name to use for the new index. Defaults to the column names joined on_
.unique
- should the index be marked as unique, forcing unique values?if_not_exists
- only create the index if one with that name does not already exist.find_unique_name
- ifindex_name
is not provided and the automatically derived name already exists, keep incrementing a suffix number to find an available name.analyze
- runANALYZE
against this index after creating it.
See Creating indexes.
- add_column(col_name: str, col_type=None, fk=None, fk_col=None, not_null_default=None)
Add a column to this table. See Adding columns.
- drop(ignore: bool = False)
Drop this table.
ignore=True
means errors will be ignored.
- guess_foreign_table(column: str) str
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.
- add_foreign_key(column: str, other_table: Optional[str] = None, other_column: Optional[str] = None, ignore: bool = False)
Alter the schema to mark the specified column as a foreign key to another table.
column
- the column to mark as a foreign key.other_table
- the table it refers to - if omitted, will be guessed based on the column name.other_column
- the column on the other table it - if omitted, will be guessed.ignore
- set this toTrue
to ignore an existing foreign key - otherwise aAlterError
will be raised.
- enable_counts()
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
Does this table have triggers setup to update cached counts?
- enable_fts(columns: Iterable[str], fts_version: str = 'FTS5', create_triggers: bool = False, tokenize: Optional[str] = None, replace: bool = False)
Enable SQLite full-text search against the specified columns.
columns
- list of column names to include in the search index.fts_version
- FTS version to use - defaults toFTS5
but you may wantFTS4
for older SQLite versions.create_triggers
- should triggers be created to keep the search index up-to-date? Defaults toFalse
.tokenize
- custom SQLite tokenizer to use, for example"porter"
to enable Porter stemming.replace
- should any existing FTS index for this table be replaced by the new one?
See Full-text search for more details.
- populate_fts(columns: Iterable[str]) sqlite_utils.db.Table
Update the associated SQLite full-text search index with the latest data from the table for the specified columns.
- disable_fts() sqlite_utils.db.Table
Remove any full-text search index and related triggers configured for this table.
- rebuild_fts()
Run the
rebuild
operation against the associated full-text search index table.
- detect_fts() Optional[str]
Detect if table has a corresponding FTS virtual table and return it
- optimize() sqlite_utils.db.Table
Run the
optimize
operation against the associated full-text search index table.
- search_sql(columns: Optional[Iterable[str]] = None, order_by: Optional[str] = None, limit: Optional[int] = None, offset: Optional[int] = None) str
Return SQL string that can be used to execute searches against this table.
- search(q: str, order_by: Optional[str] = None, columns: Optional[Iterable[str]] = None, limit: Optional[int] = None, offset: Optional[int] = None, quote: bool = False) Generator[dict, None, None]
Execute a search against this table using SQLite full-text search, returning a sequence of dictionaries for each row.
q
- terms to search fororder_by
- defaults to order by rank, or specify a column here.columns
- list of columns to return, defaults to all columns.limit
- optional integer limit for returned rows.offset
- optional integer SQL offset.quote
- apply quoting to disable any special characters in the search query
- delete(pk_values: Union[list, tuple, str, int, float]) sqlite_utils.db.Table
Delete row matching the specified primary key.
- delete_where(where: Optional[str] = None, where_args: Optional[Union[Iterable, dict]] = None, analyze: bool = False) sqlite_utils.db.Table
Delete rows matching the specified where clause, or delete all rows in the table.
where
- a SQL fragment to use as aWHERE
clause, for exampleage > ?
orage > :age
.where_args
- a list of arguments (if using?
) or a dictionary (if using:age
).analyze
- set toTrue
to runANALYZE
after the rows have been deleted.
- update(pk_values: Union[list, tuple, str, int, float], updates: Optional[dict] = None, alter: bool = False, conversions: Optional[dict] = None) sqlite_utils.db.Table
Execute a SQL
UPDATE
against the specified row.pk_values
- the primary key of an individual record - can be a tuple if the table has a compound primary key.updates
- a dictionary mapping columns to their updated values.alter
- set toTrue
to add any missing columns.conversions
- optional dictionary of SQL functions to apply during the update, for example{"mycolumn": "upper(?)"}
.
- convert(columns: Union[str, List[str]], fn: Callable, output: Optional[str] = None, output_type: Optional[Any] = None, drop: bool = False, multi: bool = False, where: Optional[str] = None, where_args: Optional[Union[Iterable, dict]] = None, show_progress: bool = False)
Apply conversion function
fn
to every value in the specified columns.columns
- a single column or list of string column names to convert.fn
- a callable that takes a single argument,value
, and returns it converted.output
- optional string column name to write the results to (defaults to the input column).output_type
- if the output column needs to be created, this is the type that will be used for the new column.drop
- boolean, should the original column be dropped once the conversion is complete?multi
- boolean, ifTrue
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
- a SQL fragment to use as aWHERE
clause to limit the rows to which the conversion is applied, for exampleage > ?
orage > :age
.where_args
- a list of arguments (if using?
) or a dictionary (if using:age
).show_progress
- boolean, should a progress bar be displayed?
- insert(record: typing.Dict[str, typing.Any], pk=<sqlite_utils.db.Default object>, foreign_keys=<sqlite_utils.db.Default object>, column_order: typing.Optional[typing.Union[typing.List[str], sqlite_utils.db.Default]] = <sqlite_utils.db.Default object>, not_null: typing.Optional[typing.Union[typing.Set[str], sqlite_utils.db.Default]] = <sqlite_utils.db.Default object>, defaults: typing.Optional[typing.Union[typing.Dict[str, typing.Any], sqlite_utils.db.Default]] = <sqlite_utils.db.Default object>, hash_id: typing.Optional[typing.Union[str, sqlite_utils.db.Default]] = <sqlite_utils.db.Default object>, alter: typing.Optional[typing.Union[bool, sqlite_utils.db.Default]] = <sqlite_utils.db.Default object>, ignore: typing.Optional[typing.Union[bool, sqlite_utils.db.Default]] = <sqlite_utils.db.Default object>, replace: typing.Optional[typing.Union[bool, sqlite_utils.db.Default]] = <sqlite_utils.db.Default object>, extracts: typing.Optional[typing.Union[typing.Dict[str, str], typing.List[str], sqlite_utils.db.Default]] = <sqlite_utils.db.Default object>, conversions: typing.Optional[typing.Union[typing.Dict[str, str], sqlite_utils.db.Default]] = <sqlite_utils.db.Default object>, columns: typing.Optional[typing.Union[typing.Dict[str, typing.Any], sqlite_utils.db.Default]] = <sqlite_utils.db.Default object>) sqlite_utils.db.Table
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.pk
- if creating the table, which column should be the primary key.foreign_keys
- see Specifying foreign keys.column_order
- optional list of strings specifying a full or partial column order to use when creating the table.not_null
- optional set of strings specifying columns that should beNOT NULL
.defaults
- optional dictionary specifying default values for specific columns.hash_id
- optional 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
- boolean, should any missing columns be added automatically?ignore
- boolean, if a record already exists with this primary key, ignore this insert.replace
- boolean, if a record already exists with this primary key, replace it with this new record.extracts
- 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
- 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
- dictionary over-riding the detected types used for the columns, for example{"age": int, "weight": float}
.
- 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>, 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) sqlite_utils.db.Table
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.
- 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>, alter=<sqlite_utils.db.Default object>, extracts=<sqlite_utils.db.Default object>, conversions=<sqlite_utils.db.Default object>, columns=<sqlite_utils.db.Default object>) sqlite_utils.db.Table
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.
- 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>, 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) sqlite_utils.db.Table
Like
.upsert()
but can be applied to a list of records.
- lookup(lookup_values: Dict[str, Any], extra_values: Optional[Dict[str, Any]] = None, pk: Optional[str] = 'id', foreign_keys: Optional[Union[Iterable[str], Iterable[sqlite_utils.db.ForeignKey], Iterable[Tuple[str, str]], Iterable[Tuple[str, str, str]], Iterable[Tuple[str, str, str, str]]]] = None, column_order: Optional[List[str]] = None, not_null: Optional[Set[str]] = None, defaults: Optional[Dict[str, Any]] = None, extracts: Optional[Union[Dict[str, str], List[str]]] = None, conversions: Optional[Dict[str, str]] = None, columns: Optional[Dict[str, Any]] = None)
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.See Working with lookup tables for more details.
All other keyword arguments are passed through to
.insert()
.
- m2m(other_table: typing.Union[str, sqlite_utils.db.Table], record_or_iterable: typing.Optional[typing.Union[typing.Iterable[typing.Dict[str, typing.Any]], typing.Dict[str, typing.Any]]] = None, pk: typing.Optional[typing.Union[typing.Any, sqlite_utils.db.Default]] = <sqlite_utils.db.Default object>, lookup: typing.Optional[typing.Dict[str, typing.Any]] = None, m2m_table: typing.Optional[str] = None, alter: bool = False)
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.
other_table
- the name of the table to insert the new records into.record_or_iterable
- a single dictionary record to insert, or a list of records.pk
- the primary key to use if creatingother_table
.lookup
- same dictionary as for.lookup()
, to create a many-to-many lookup table.m2m_table
- 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
- set toTrue
to add any missing columns onother_table
if that table already exists.
- analyze()
Run ANALYZE against this table
- analyze_column(column: str, common_limit: int = 10, value_truncate=None, total_rows=None) sqlite_utils.db.ColumnDetails
Return statistics about the specified column.
See Analyzing a column.
- add_geometry_column(column_name: str, geometry_type: str, srid: int = 4326, coord_dimension: str = 'XY', not_null: bool = False) bool
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")
- create_spatial_index(column_name) bool
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)
sqlite_utils.db.View
- class sqlite_utils.db.View(db, name)
Bases:
sqlite_utils.db.Queryable
- exists()
Does this table or view exist yet?
- drop(ignore=False)
- enable_fts(*args, **kwargs)
Other
sqlite_utils.db.Column
- class sqlite_utils.db.Column(cid, name, type, notnull, default_value, is_pk)
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)
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
)