Various interesting and useful bits of functionality

You need to import apsw.ext to use this module. dataclasses are used, and only Python 3.7+ is supported.

Accessing result rows by column name

See the example.

Use apsw.ext.DataClassRowFactory as a apsw.Connection.rowtrace for an entire connection, or apsw.Cursor.rowtrace for a specific cursor.

Converting types into and out of SQLite

SQLite only stores and returns 5 types:

• None

• int

• float

• str

• bytes

Use TypesConverterCursorFactory as apsw.Connection.cursor_factory to adapt values going into SQLite, and convert them coming out. See the example.

To convert values going into SQLite, do either of:

• Inherit from apsw.ext.SQLiteTypeAdapter and define a to_sqlite_value method on the class

• Call TypesConverterCursorFactory.register_adapter() with the type and a adapter function

To adapt values coming out of SQLite:

• Call TypesConverterCursorFactory.register_converter() with the exact type string in the table and a converter function

Detailed Query Information

SQLite can provide lots of information about queries. The query_info function can gather them up for you. This includes:

• readonly if the query makes no direct changes

• first_query if multiple queries are provided

• actions which databases, tables, columns, functions, views etc are referenced - see actions

• query_plan which indices, tables scans etc are used to find the query results - see query plans

• explain for the low level steps taken inside SQLite - see SQLite bytecode

See the example.

API Reference

class DataClassRowFactory(*, rename: bool = True, dataclass_kwargs: Optional[Dict[str, Any]] = None)

Returns each row as a dataclass, accessible by column name.

To use set an instance as Connection.rowtrace to affect all cursors, or on a specific cursor:

connection.rowtrace = apsw.ext.DataClassRowFactory()
for row in connection.execute("SELECT title, sum(orders) AS total, ..."):
    # You can now access by name
    print (row.title, row.total)
    # you can get the underlying description
    print (row.__description__)

You can use as many instances of this class as you want, each across as many connections as you want.

Parameters

• rename – Column names could be duplicated, or not valid in Python (eg a column named continue). If rename is True, then invalid/duplicate names are replaced with _ and their position starting at zero. For example title, total, title, continue would become title, total, _2, _3. If rename is False then problem column names will result in TypeError raised by dataclasses.make_dataclass()

• dataclass_kwargs – Additional parameters when creating the dataclass as described in dataclasses.dataclass(). For example you may want frozen = True to make the dataclass read-only, or slots = True to reduce memory consumption.

get_dataclass(description: Tuple[Tuple[str, str], ...]) → Tuple[Any, Tuple[str, ...]]

Returns dataclass and tuple of (potentially renamed) column names

The dataclass is what is returned for each row with that description

This method caches it results.

get_type(t: Optional[str]) → Any

Returns the type hint to use in the dataclass based on the type in the description

SQLite’s affinity rules are followed.

The values have no effect on how your program runs, but can be used by tools like mypy. Column information like whether null is allowed is not present, so this is just a hint.

__call__(cursor: apsw.Cursor, row: apsw.SQLiteValues) → Any

What the row tracer calls

This looks up the dataclass and column names, and then returns an instance of the dataclass.

class SQLiteTypeAdapter

A metaclass to indicate conversion to SQLite types is supported

This is one way to indicate your type supports conversion to a value supported by SQLite. You can either inherit from this class, or call the register method:

apsw.ext.SQLiteTypeAdapter.register(YourClassHere)

Doing either is entirely sufficient and there is no need to register with TypesConverterCursorFactory

abstract to_sqlite_value() → apsw.SQLiteValue

Return a SQLite compatible value for this object

class TypesConverterCursorFactory(abstract_base_class: ~abc.ABCMeta = <class 'apsw.ext.SQLiteTypeAdapter'>)

Provides cursors that can convert objects into one of the types supported by SQLite. or back from SQLite

Parameters

metaclass – Which metaclass to consider as conversion capable

register_adapter(klass: type, callable: Callable[[Any], apsw.SQLiteValue]) → None

Registers a callable that converts from klass to one of the supported SQLite types

register_converter(name: str, callable: Callable[[apsw.SQLiteValue], Any]) → None

Registers a callable that converts from a SQLite value

__call__(connection: apsw.Connection) → TypeConverterCursor

Returns a new cursor for the connection

adapt_value(value: Any) → apsw.SQLiteValue

Returns SQLite representation of value

convert_value(schematype: str, value: apsw.SQLiteValue) → Any

Returns Python object from schema type and SQLite value

wrap_bindings(bindings: Optional[apsw.Bindings]) → Optional[apsw.Bindings]

Wraps bindings that are supplied to underlying execute

wrap_sequence_bindings(sequenceofbindings: Sequence[apsw.Bindings])

class DictAdapter(factory: TypesConverterCursorFactory, data: collections.abc.Mapping[str, apsw.SQLiteValue])

Used to wrap dictionaries supplied as bindings

class TypeConverterCursor(connection: Connection, factory: TypesConverterCursorFactory)

Cursor used to do conversions

execute(statements: str, bindings: Optional[apsw.Bindings] = None, *, can_cache: bool = True, prepare_flags: int = 0) → apsw.Cursor

Executes the statements doing conversions on supplied and returned values

See apsw.Cursor.execute() for parameter details

executemany(statements: str, sequenceofbindings: Sequence[apsw.Bindings], *, can_cache: bool = True, prepare_flags: int = 0) → apsw.Cursor

Executes the statements against each item in sequenceofbindings, doing conversions on supplied and returned values

See apsw.Cursor.executemany() for parameter details

query_info(db: apsw.Connection, query: str, bindings: Optional[apsw.Bindings] = None, *, prepare_flags: int = 0, actions: bool = False, expanded_sql: bool = False, explain: bool = False, explain_query_plan: bool = False) → QueryDetails

Returns information about the query, but does not run it.

Set the various parameters to True if you also want the actions, expanded_sql, explain, query_plan etc filled in.

class QueryDetails(query: str, bindings: Optional[apsw.Bindings], first_query: str, query_remaining: Optional[str], is_explain: int, is_readonly: bool, description: Tuple[Tuple[str, str], ...], description_full: Optional[Tuple[Tuple[str, str, str, str, str], ...]], expanded_sql: Optional[str], actions: Optional[List[QueryAction]], explain: Optional[List[VDBEInstruction]], query_plan: Optional[QueryPlan])

A dataclass that provides detailed information about a query, returned by query_info()

query: str

Original query provided

bindings: Optional[apsw.Bindings]

Bindings provided

first_query: str

The first statement present in query

query_remaining: Optional[str]

Query text after the first one if multiple were in query, else None

is_explain: int

Cursor.is_explain

is_readonly: bool

Cursor.is_readonly

description: Tuple[Tuple[str, str], ...]

Cursor.getdescription

description_full: Optional[Tuple[Tuple[str, str, str, str, str], ...]]

Cursor.description_full

expanded_sql: Optional[str]

Cursor.expanded_sql

actions: Optional[List[QueryAction]]

A list of the actions taken by the query, as discovered via Connection.authorizer

explain: Optional[List[VDBEInstruction]]

A list of instructions of the internal code used by SQLite to execute the query

query_plan: Optional[QueryPlan]

The steps taken against tables and indices described here

class QueryAction(action: int, action_name: str, column_name: Optional[str] = None, database_name: Optional[str] = None, file_name: Optional[str] = None, function_name: Optional[str] = None, module_name: Optional[str] = None, operation: Optional[str] = None, pragma_name: Optional[str] = None, pragma_value: Optional[str] = None, table_name: Optional[str] = None, trigger_name: Optional[str] = None, trigger_or_view: Optional[str] = None, view_name: Optional[str] = None)

A dataclass that provides information about one action taken by a query

Depending on the action, only a subset of the fields will have non-None values

action: int

Authorizer code (also present in apsw.mapping_authorizer_function)

action_name: str

The string corresponding to the action. For example action could be 21 in which case action_name will be SQLITE_SELECT

column_name: Optional[str] = None

database_name: Optional[str] = None

eg main, temp, the name in ATTACH

file_name: Optional[str] = None

function_name: Optional[str] = None

module_name: Optional[str] = None

operation: Optional[str] = None

pragma_name: Optional[str] = None

pragma_value: Optional[str] = None

table_name: Optional[str] = None

trigger_name: Optional[str] = None

trigger_or_view: Optional[str] = None

This action is happening due to a trigger or view, and not directly expressed in the query itself

view_name: Optional[str] = None

class QueryPlan(detail: str, sub: Optional[List[QueryPlan]] = None)

A dataclass for one step of a query plan

detail: str

Description of this step

sub: Optional[List[QueryPlan]] = None

Steps that run within this one

class VDBEInstruction(addr: int, opcode: str, comment: Optional[str] = None, p1: Optional[int] = None, p2: Optional[int] = None, p3: Optional[int] = None, p4: Optional[int] = None, p5: Optional[int] = None)

A dataclass representing one instruction and its parameters

addr: int

Address of this opcode. It will be the target of goto, loops etc

opcode: str

The instruction

comment: Optional[str] = None

Additional human readable information

p1: Optional[int] = None

First opcode parameter

p2: Optional[int] = None

Second opcode parameter

p3: Optional[int] = None

Third opcode parameter

p4: Optional[int] = None

Fourth opcode parameter

p5: Optional[int] = None

Fifth opcode parameter