SQLiteStore

tiatoolbox . annotation . storage . SQLiteStore

class SQLiteStore(connection=':memory:', compression='zlib', compression_level=9)

SQLite backed annotation store.

Uses and rtree index for fast spatial queries.

Methods

append_many
bquery
clear	Remove all annotations from the store.
close
commit
compile_options	Get the list of options that sqlite3 was compiled with.
create_index	Create an SQLite expression index based on the provided predicate.
deserialise_geometry	Deserialise a geometry from a string or bytes.
drop_index	Drop an index from the store.
dump
dumps
features
indexes	Returns a list of the names of all indexes in the store.
iquery
items
keys
open
optimize	Optimize the database with VACUUM and ANALYZE.
patch_many
query
remove_many
serialise_geometry	Serialise a geometry to WKB with optional compression.
to_dataframe
values

Parameters

connection (Union[pathlib.Path, str, IO]) –

Return type

None

clear()

Remove all annotations from the store.

Return type

None

static compile_options()

Get the list of options that sqlite3 was compiled with.

Example

>>> for opt in SQLiteRTreeStore.compile_options():
>>>     print(opt)
COMPILER=gcc-7.5.0
ENABLE_COLUMN_METADATA
ENABLE_DBSTAT_VTAB
ENABLE_FTS3
ENABLE_FTS3_PARENTHESIS
ENABLE_FTS3_TOKENIZER
ENABLE_FTS4
ENABLE_FTS5
ENABLE_JSON1
ENABLE_LOAD_EXTENSION
ENABLE_PREUPDATE_HOOK
ENABLE_RTREE
ENABLE_SESSION
ENABLE_STMTVTAB
ENABLE_UNLOCK_NOTIFY
ENABLE_UPDATE_DELETE_LIMIT
HAVE_ISNAN
LIKE_DOESNT_MATCH_BLOBS
MAX_SCHEMA_RETRY=25
MAX_VARIABLE_NUMBER=250000
OMIT_LOOKASIDE
SECURE_DELETE
SOUNDEX
TEMP_STORE=1
THREADSAFE=1

Return type

List[str]

create_index(name, where, analyze=True)

Create an SQLite expression index based on the provided predicate.

Note that an expression index will only be used if the query expression (in the WHERE clause) exactly matches the expression used when creating the index (excluding minor inconsequential changes such as whitespace).

SQLite expression indexes require SQLite version 3.9.0 or higher.

Parameters

• name (str) – Name of the index to create.

• where (Union[str, bytes]) – The predicate used to create the index.

• analyze (bool) – Whether to run the ANALYZE command after creating the index.

Return type

None

deserialise_geometry(data)

Deserialise a geometry from a string or bytes.

Parameters

data (bytes or str) – The serialised representation of a Shapely geometry.

Returns

The deserialised Shapely geometry.

Return type

Geometry

drop_index(name)

Drop an index from the store.

Parameters

name (str) – The name of the index to drop.

Return type

None

indexes()

Returns a list of the names of all indexes in the store.

Returns

The list of index names.

Return type

List[str]

optimize(vacuum=True, limit=1000)

Optimize the database with VACUUM and ANALYZE.

Parameters

• vacuum (bool) – Whether to run VACUUM.

• limit (int) – The approximate maximum number of rows to examine when running ANALYZE. If zero or negative, not limit will be used. For more information see https://www.sqlite.org/pragma.html#pragma_analysis_limit.

Return type

None

serialise_geometry(geometry)

Serialise a geometry to WKB with optional compression.

Converts shapely geometry objects to well-known binary (WKB) and applies optional compression.

Parameters

geometry (Geometry) – The Shapely geometry to be serialised.

Returns

The serialised geometry.

Return type

bytes or str