APSW Module¶

The module is the main interface to SQLite. Methods and data on the module have process wide effects.

Type Annotations¶

Comprehensive type annotations are included, and your code using apsw can be checked using tools like mypy. You can refer to the types below for your annotations (eg as apsw.SQLiteValue)

class SQLiteValue¶: Union[None, int, float, bytes, str]

SQLite supports 5 types - None (NULL), 64 bit signed int, 64 bit float, bytes, and str (unicode text)

class SQLiteValues¶: Union[Tuple[()], Tuple[SQLiteValue, ...]]

A sequence of zero or more SQLiteValue

class Bindings¶: Union[Sequence[Union[SQLiteValue, zeroblob]], Mapping[str, Union[SQLiteValue, zeroblob]]]

Query bindings are either a sequence of SQLiteValue, or a dict mapping names to SQLiteValues. You can also provide zeroblob in Bindings. You can use dict subclasses or any type registered with collections.abc.Mapping for named bindings

class AggregateT¶: Any

An object provided as first parameter of step and final aggregate functions

class AggregateStep¶: Union [

Callable[[AggregateT], None],

Callable[[AggregateT, SQLiteValue], None],

Callable[[AggregateT, SQLiteValue, SQLiteValue], None],

Callable[[AggregateT, SQLiteValue, SQLiteValue, SQLiteValue], None],

Callable[[AggregateT, SQLiteValue, SQLiteValue, SQLiteValue, SQLiteValue], None],

Callable[[AggregateT, SQLiteValue, SQLiteValue, SQLiteValue, SQLiteValue, SQLiteValue], None],

Callable[[AggregateT, SQLiteValue, SQLiteValue, SQLiteValue, SQLiteValue, SQLiteValue, SQLiteValue], None],

]

AggregateStep is called on each matching row with the relevant number of SQLiteValue

class AggregateFinal¶: Callable[[AggregateT], SQLiteValue]

Final is called after all matching rows have been processed by step, and returns a SQLiteValue

class AggregateFactory¶: Callable[[], Tuple[AggregateT, AggregateStep, AggregateFinal]]

Called each time for the start of a new calculation using an aggregate function, returning an object, a step function and a final function

class ScalarProtocol¶: Union [

Callable[[], SQLiteValue],

Callable[[SQLiteValue], SQLiteValue],

Callable[[SQLiteValue, SQLiteValue], SQLiteValue],

Callable[[SQLiteValue, SQLiteValue, SQLiteValue], SQLiteValue],

Callable[[SQLiteValue, SQLiteValue, SQLiteValue, SQLiteValue], SQLiteValue],

Callable[[SQLiteValue, SQLiteValue, SQLiteValue, SQLiteValue, SQLiteValue], SQLiteValue],

Callable[[SQLiteValue, SQLiteValue, SQLiteValue, SQLiteValue, SQLiteValue, SQLiteValue], SQLiteValue],

Callable[[SQLiteValue, SQLiteValue, SQLiteValue, SQLiteValue, SQLiteValue, SQLiteValue, SQLiteValue], SQLiteValue]

]

Scalar callbacks take zero or more SQLiteValues, and return a SQLiteValue

class WindowClass¶: WindowClass(Protocol):

"""Represents a running window function"""

def step(self, param: SQLiteValue) -> None:

"""Adds the param(s) to the window"""

...

def final(self) -> SQLiteValue:

"""Finishes the function and returns final value"""

...

def value(self) -> SQLiteValue:

"""Returns the current value"""

...

def inverse(self, param: SQLiteValue) -> None:

"""Removes the param(s) from the window"""

...

WindowClass Protocol

class WindowT¶: Any

An object provided as first parameter of the 4 window functions, if not using class based callbacks

class WindowStep¶: Union[

Callable[[WindowT], None],

Callable[[WindowT, SQLiteValue], None],

Callable[[WindowT, SQLiteValue, SQLiteValue], None],

Callable[[WindowT, SQLiteValue, SQLiteValue, SQLiteValue], None],

Callable[[WindowT, SQLiteValue, SQLiteValue, SQLiteValue, SQLiteValue], None]

]

Window function step takes zero or more SQLiteValues

class WindowFinal¶: Union[

Callable[[WindowT], SQLiteValue],

Callable[[WindowT, SQLiteValue], SQLiteValue],

Callable[[WindowT, SQLiteValue, SQLiteValue], SQLiteValue],

Callable[[WindowT, SQLiteValue, SQLiteValue, SQLiteValue], SQLiteValue],

Callable[[WindowT, SQLiteValue, SQLiteValue, SQLiteValue, SQLiteValue], SQLiteValue]

]

Window function final takes zero or more SQLiteValues, and returns a SQLiteValue

class WindowValue¶: Callable[[WindowT], SQLiteValue]

Window function value returns the current SQLiteValue

class WindowInverse¶: Union[

Callable[[WindowT], None],

Callable[[WindowT, SQLiteValue], None],

Callable[[WindowT, SQLiteValue, SQLiteValue], None],

Callable[[WindowT, SQLiteValue, SQLiteValue, SQLiteValue], None],

Callable[[WindowT, SQLiteValue, SQLiteValue, SQLiteValue, SQLiteValue], None]

]

Window function inverse takes zero or more SQLiteValues

class WindowFactory¶: Callable[[], Union[WindowClass, Tuple[WindowT, WindowStep, WindowFinal, WindowValue, WindowInverse]]]

Called each time at the start of a new window function execution. It should return either an object with relevant methods or an object used as the first parameter and the 4 methods

class RowTracer¶: Callable[[Cursor, SQLiteValues], Any]

Row tracers are called with the Cursor, and the row that would be returned. If you return None, then no row is returned, otherwise whatever is returned is returned as a result row for the query

class ExecTracer¶: Callable[[Cursor, str, Optional[Bindings]], bool]

Execution tracers are called with the cursor, sql query text, and the bindings used. Return False/None to abort execution, or True to continue

class Authorizer¶: Callable[[int, Optional[str], Optional[str], Optional[str], Optional[str]], int]

Authorizers are called with an operation code and 4 strings (which could be None) depending on the operatation. Return SQLITE_OK, SQLITE_DENY, or SQLITE_IGNORE

class CommitHook¶: Callable[[], bool]

Commit hook is called with no arguments and should return True to abort the commit and False to let it continue

API Reference¶

SQLITE_VERSION_NUMBER: int¶: The integer version number of SQLite that APSW was compiled against. For example SQLite 3.6.4 will have the value 3006004. This number may be different than the actual library in use if the library is shared and has been updated. Call sqlitelibversion() to get the actual library version.

allow_missing_dict_bindings(value: bool) → bool¶

Changes how missing bindings are handled when using a dict. Historically missing bindings were treated as None. It was anticipated that dict bindings would be used when there were lots of columns, so having missing ones defaulting to None was convenient.

Unfortunately this also has the side effect of not catching typos and similar issues.

APSW 3.41.0.0 changed the default so that missing dict entries will result in an exception. Call this with True to restore the earlier behaviour, and False to have an exception.

The previous value is returned.

apswversion() → str¶: Returns the APSW version.

compile_options: Tuple[str, ...]¶

A tuple of the options used to compile SQLite. For example it will be something like this:

('ENABLE_LOCKING_STYLE=0', 'TEMP_STORE=1', 'THREADSAFE=1')

Calls: sqlite3_compileoption_get

complete(statement: str) → bool¶

Returns True if the input string comprises one or more complete SQL statements by looking for an unquoted trailing semi-colon.

An example use would be if you were prompting the user for SQL statements and needed to know if you had a whole statement, or needed to ask for another line:

statement = input("SQL> ")
while not apsw.complete(statement):
   more = input("  .. ")
   statement = statement + "\\n" + more

Calls: sqlite3_complete

config(op: int, *args: Any) → None¶

Parameters:

op -- A configuration operation
args -- Zero or more arguments as appropriate for op

Many operations don't make sense from a Python program. The following configuration operations are supported: SQLITE_CONFIG_LOG, SQLITE_CONFIG_SINGLETHREAD, SQLITE_CONFIG_MULTITHREAD, SQLITE_CONFIG_SERIALIZED, SQLITE_CONFIG_URI, SQLITE_CONFIG_MEMSTATUS, SQLITE_CONFIG_COVERING_INDEX_SCAN, SQLITE_CONFIG_PCACHE_HDRSZ, SQLITE_CONFIG_PMASZ, and SQLITE_CONFIG_STMTJRNL_SPILL.

See tips for an example of how to receive log messages (SQLITE_CONFIG_LOG)

Calls: sqlite3_config

connection_hooks: List[Callable[[Connection], None]]¶

The purpose of the hooks is to allow the easy registration of functions, virtual tables or similar items with each Connection as it is created. The default value is an empty list. Whenever a Connection is created, each item in apsw.connection_hooks is invoked with a single parameter being the new Connection object. If the hook raises an exception then the creation of the Connection fails.

If you wanted to store your own defined functions in the database then you could define a hook that looked in the relevant tables, got the Python text and turned it into the functions.

enablesharedcache(enable: bool) → None¶

If you use the same Connection across threads or use multiple connections accessing the same file, then SQLite can share the cache between them. It is not recommended that you use this.

Calls: sqlite3_enable_shared_cache

exceptionfor(code: int) → Exception¶

If you would like to raise an exception that corresponds to a particular SQLite error code then call this function. It also understands extended error codes.

For example to raise SQLITE_IOERR_ACCESS:

raise apsw.exceptionfor(apsw.SQLITE_IOERR_ACCESS)

fork_checker() → None¶

Note This method is not available on Windows as it does not support the fork system call.

SQLite does not allow the use of database connections across forked processes (see the SQLite FAQ Q6). (Forking creates a child process that is a duplicate of the parent including the state of all data structures in the program. If you do this to SQLite then parent and child would both consider themselves owners of open databases and silently corrupt each other's work and interfere with each other's locks.)

One example of how you may end up using fork is if you use the multiprocessing module which uses fork to make child processes.

If you do use fork or multiprocessing on a platform that supports fork then you must ensure database connections and their objects (cursors, backup, blobs etc) are not used in the parent process, or are all closed before calling fork or starting a Process. (Note you must call close to ensure the underlying SQLite objects are closed. It is also a good idea to call gc.collect(2) to ensure anything you may have missed is also deallocated.)

Once you run this method, extra checking code is inserted into SQLite's mutex operations (at a very small performance penalty) that verifies objects are not used across processes. You will get a ForkingViolationError if you do so. Note that due to the way Python's internals work, the exception will be delivered to sys.excepthook in addition to the normal exception mechanisms and may be reported by Python after the line where the issue actually arose. (Destructors of objects you didn't close also run between lines.)

You should only call this method as the first line after importing APSW, as it has to shutdown and re-initialize SQLite. If you have any SQLite objects already allocated when calling the method then the program will later crash. The recommended use is to use the fork checking as part of your test suite.

format_sql_value(value: SQLiteValue) → str¶

Returns a Python string representing the supplied value in SQLite syntax.

Note that SQLite represents floating point Nan as NULL, infinity as 1e999 and loses the sign on negative zero.

hard_heap_limit(limit: int) → int¶

Enforces SQLite keeping memory usage below limit bytes and returns the previous limit.

See also

softheaplimit()

Calls: sqlite3_hard_heap_limit64

initialize() → None¶

It is unlikely you will want to call this method as SQLite automatically initializes.

Calls: sqlite3_initialize

keywords: Set[str]¶

A set containing every SQLite keyword

Calls:

log(errorcode: int, message: str) → None¶

Calls the SQLite logging interface. Note that you must format the message before passing it to this method:

apsw.log(apsw.SQLITE_NOMEM, f"Need { needed } bytes of memory")

See tips for an example of how to receive log messages.

Calls: sqlite3_log

memoryhighwater(reset: bool = False) → int¶

Returns the maximum amount of memory SQLite has used. If reset is True then the high water mark is reset to the current value.

See also

status()

Calls: sqlite3_memory_highwater

memoryused() → int¶

Returns the amount of memory SQLite is currently using.

See also

status()

Calls: sqlite3_memory_used

no_change¶: A sentinel value used to indicate no change in a value when used with VTCursor.ColumnNoChange() and VTTable.UpdateChangeRow()

randomness(amount: int) → bytes¶

Gets random data from SQLite's random number generator.

Parameters:: amount -- How many bytes to return

Calls: sqlite3_randomness

releasememory(amount: int) → int¶

Requests SQLite try to free amount bytes of memory. Returns how many bytes were freed.

Calls: sqlite3_release_memory

set_default_vfs(name: str) → None¶

Sets the default vfs to name which must be an existing vfs. See vfsnames().

Calls:

shutdown() → None¶

It is unlikely you will want to call this method and there is no need to do so. It is a really bad idea to call it unless you are absolutely sure all connections, blobs, cursors, vfs etc have been closed, deleted and garbage collected.

Calls: sqlite3_shutdown

softheaplimit(limit: int) → int¶

Requests SQLite try to keep memory usage below limit bytes and returns the previous limit.

See also

hard_heap_limit()

Calls: sqlite3_soft_heap_limit64

sqlite3_sourceid() → str¶

Returns the exact checkin information for the SQLite 3 source being used.

Calls: sqlite3_sourceid

sqlitelibversion() → str¶

Returns the version of the SQLite library. This value is queried at run time from the library so if you use shared libraries it will be the version in the shared library.

Calls: sqlite3_libversion

status(op: int, reset: bool = False) → Tuple[int, int]¶

Returns current and highwater measurements.

Parameters:

op -- A status parameter
reset -- If True then the highwater is set to the current value

Returns:

A tuple of current value and highwater value

See also

Status example

Calls: sqlite3_status64

strglob(glob: str, string: str) → int¶

Does string GLOB matching. Note that zero is returned on on a match.

Calls: sqlite3_strglob

stricmp(string1: str, string2: str) → int¶

Does string case-insensitive comparison. Note that zero is returned on on a match.

Calls: sqlite3_stricmp

strlike(glob: str, string: str, escape: int = 0) → int¶

Does string LIKE matching. Note that zero is returned on on a match.

Calls: sqlite3_strlike

strnicmp(string1: str, string2: str, count: int) → int¶

Does string case-insensitive comparison. Note that zero is returned on on a match.

Calls: sqlite3_strnicmp

unregister_vfs(name: str) → None¶

Unregisters the named vfs. See vfsnames().

Calls:

using_amalgamation: bool¶: If True then SQLite amalgamation is in use (statically compiled into APSW). Using the amalgamation means that SQLite shared libraries are not used and will not affect your code.

vfsnames() → List[str]¶: Returns a list of the currently installed vfs. The first item in the list is the default vfs.

SQLite constants¶

SQLite has many constants used in various interfaces. To use a constant such as SQLITE_OK, just use apsw.SQLITE_OK.

The same values can be used in different contexts. For example SQLITE_OK and SQLITE_CREATE_INDEX both have a value of zero. For each group of constants there is also a mapping (dict) available that you can supply a string to and get the corresponding numeric value, or supply a numeric value and get the corresponding string. These can help improve diagnostics/logging, calling other modules etc. For example:

apsw.mapping_authorizer_function["SQLITE_READ"] == 20
apsw.mapping_authorizer_function[20] == "SQLITE_READ"

mapping_access: Dict[Union[str, int], Union[int, str]]¶

Flags for the xAccess VFS method constants

SQLITE_ACCESS_EXISTS, SQLITE_ACCESS_READ, SQLITE_ACCESS_READWRITE

mapping_authorizer_return: Dict[Union[str, int], Union[int, str]]¶

Authorizer Return Codes constants

SQLITE_DENY, SQLITE_IGNORE, SQLITE_OK

mapping_conflict_resolution_modes: Dict[Union[str, int], Union[int, str]]¶

Conflict resolution modes constants

SQLITE_ABORT, SQLITE_FAIL, SQLITE_IGNORE, SQLITE_REPLACE, SQLITE_ROLLBACK

mapping_extended_result_codes: Dict[Union[str, int], Union[int, str]]¶

Extended Result Codes constants

mapping_file_control: Dict[Union[str, int], Union[int, str]]¶

Standard File Control Opcodes constants

SQLITE_FCNTL_BEGIN_ATOMIC_WRITE, SQLITE_FCNTL_BUSYHANDLER, SQLITE_FCNTL_CHUNK_SIZE, SQLITE_FCNTL_CKPT_DONE, SQLITE_FCNTL_CKPT_START, SQLITE_FCNTL_CKSM_FILE, SQLITE_FCNTL_COMMIT_ATOMIC_WRITE, SQLITE_FCNTL_COMMIT_PHASETWO, SQLITE_FCNTL_DATA_VERSION, SQLITE_FCNTL_EXTERNAL_READER, SQLITE_FCNTL_FILE_POINTER, SQLITE_FCNTL_GET_LOCKPROXYFILE, SQLITE_FCNTL_HAS_MOVED, SQLITE_FCNTL_JOURNAL_POINTER, SQLITE_FCNTL_LAST_ERRNO, SQLITE_FCNTL_LOCKSTATE, SQLITE_FCNTL_LOCK_TIMEOUT, SQLITE_FCNTL_MMAP_SIZE, SQLITE_FCNTL_OVERWRITE, SQLITE_FCNTL_PDB, SQLITE_FCNTL_PERSIST_WAL, SQLITE_FCNTL_POWERSAFE_OVERWRITE, SQLITE_FCNTL_PRAGMA, SQLITE_FCNTL_RBU, SQLITE_FCNTL_RESERVE_BYTES, SQLITE_FCNTL_RESET_CACHE, SQLITE_FCNTL_ROLLBACK_ATOMIC_WRITE, SQLITE_FCNTL_SET_LOCKPROXYFILE, SQLITE_FCNTL_SIZE_HINT, SQLITE_FCNTL_SIZE_LIMIT, SQLITE_FCNTL_SYNC, SQLITE_FCNTL_SYNC_OMITTED, SQLITE_FCNTL_TEMPFILENAME, SQLITE_FCNTL_TRACE, SQLITE_FCNTL_VFSNAME, SQLITE_FCNTL_VFS_POINTER, SQLITE_FCNTL_WAL_BLOCK, SQLITE_FCNTL_WIN32_AV_RETRY, SQLITE_FCNTL_WIN32_GET_HANDLE, SQLITE_FCNTL_WIN32_SET_HANDLE, SQLITE_FCNTL_ZIPVFS

mapping_function_flags: Dict[Union[str, int], Union[int, str]]¶

Function Flags constants

SQLITE_DETERMINISTIC, SQLITE_DIRECTONLY, SQLITE_INNOCUOUS, SQLITE_SUBTYPE

mapping_locking_level: Dict[Union[str, int], Union[int, str]]¶

File Locking Levels constants

SQLITE_LOCK_EXCLUSIVE, SQLITE_LOCK_NONE, SQLITE_LOCK_PENDING, SQLITE_LOCK_RESERVED, SQLITE_LOCK_SHARED

mapping_prepare_flags: Dict[Union[str, int], Union[int, str]]¶

Prepare Flags constants

SQLITE_PREPARE_NORMALIZE, SQLITE_PREPARE_NO_VTAB, SQLITE_PREPARE_PERSISTENT

mapping_statement_status: Dict[Union[str, int], Union[int, str]]¶

Status Parameters for prepared statements constants

SQLITE_STMTSTATUS_AUTOINDEX, SQLITE_STMTSTATUS_FILTER_HIT, SQLITE_STMTSTATUS_FILTER_MISS, SQLITE_STMTSTATUS_FULLSCAN_STEP, SQLITE_STMTSTATUS_MEMUSED, SQLITE_STMTSTATUS_REPREPARE, SQLITE_STMTSTATUS_RUN, SQLITE_STMTSTATUS_SORT, SQLITE_STMTSTATUS_VM_STEP

mapping_status: Dict[Union[str, int], Union[int, str]]¶

Status Parameters constants

SQLITE_STATUS_MALLOC_COUNT, SQLITE_STATUS_MALLOC_SIZE, SQLITE_STATUS_MEMORY_USED, SQLITE_STATUS_PAGECACHE_OVERFLOW, SQLITE_STATUS_PAGECACHE_SIZE, SQLITE_STATUS_PAGECACHE_USED, SQLITE_STATUS_PARSER_STACK, SQLITE_STATUS_SCRATCH_OVERFLOW, SQLITE_STATUS_SCRATCH_SIZE, SQLITE_STATUS_SCRATCH_USED

mapping_sync: Dict[Union[str, int], Union[int, str]]¶

Synchronization Type Flags constants

SQLITE_SYNC_DATAONLY, SQLITE_SYNC_FULL, SQLITE_SYNC_NORMAL

mapping_trace_codes: Dict[Union[str, int], Union[int, str]]¶

SQL Trace Event Codes constants

SQLITE_TRACE_CLOSE, SQLITE_TRACE_PROFILE, SQLITE_TRACE_ROW, SQLITE_TRACE_STMT

mapping_txn_state: Dict[Union[str, int], Union[int, str]]¶

Allowed return values from [sqlite3_txn_state()] constants

SQLITE_TXN_NONE, SQLITE_TXN_READ, SQLITE_TXN_WRITE

mapping_virtual_table_configuration_options: Dict[Union[str, int], Union[int, str]]¶

Virtual Table Configuration Options constants

SQLITE_VTAB_CONSTRAINT_SUPPORT, SQLITE_VTAB_DIRECTONLY, SQLITE_VTAB_INNOCUOUS

mapping_virtual_table_scan_flags: Dict[Union[str, int], Union[int, str]]¶

Virtual Table Scan Flags constants

SQLITE_INDEX_SCAN_UNIQUE

mapping_wal_checkpoint: Dict[Union[str, int], Union[int, str]]¶

Checkpoint Mode Values constants

SQLITE_CHECKPOINT_FULL, SQLITE_CHECKPOINT_PASSIVE, SQLITE_CHECKPOINT_RESTART, SQLITE_CHECKPOINT_TRUNCATE

mapping_xshmlock_flags: Dict[Union[str, int], Union[int, str]]¶

Flags for the xShmLock VFS method constants

SQLITE_SHM_EXCLUSIVE, SQLITE_SHM_LOCK, SQLITE_SHM_SHARED, SQLITE_SHM_UNLOCK

APSW Module¶

Type Annotations¶

API Reference¶

SQLite constants¶

Previous topic

Next topic

On this page

Table of Contents

This Page