Best Practice

Explanation

Because SQLite keeps very strong backwards compatibility, there are several quirks and settings improvements that are not automatically done. This module does them for you, and keeps up to date with SQLite best practises. Several are described in the SQLite documentation.

Functions whose name begin with library apply to the SQLite library as a whole, while those with connection apply to individual connections.

You can call the individual functions, or apsw.bestpractice.apply() to apply several. apply() will setup apsw.connection_hooks to affect future connections that are made. recommended is the recommended group:

import apsw.bestpractice

apsw.bestpractice.apply(apsw.bestpractice.recommended)

API

Ensure SQLite usage prevents common mistakes, and get best performance.

apsw.bestpractice.apply(which: tuple[Callable, ...]) → None

Applies library immediately and connection to new connections

apsw.bestpractice.connection_busy_timeout(connection: Connection, duration_ms: int = 100) → None

Sets a short busy timeout

Parameters:

duration_ms – How many thousandths of a second to wait

When another thread or process has locked the database, SQLite immediately raises apsw.BusyError. Changing the busy timeout gives a grace period during which SQLite retries.

apsw.bestpractice.connection_dqs(connection: Connection) → None

Double quotes are for identifiers only, not strings

Turns off allowing double quoted strings if they don’t match any identifier (column/table names etc), making it an error to use double quotes around strings. SQL strings use single quotes.

apsw.bestpractice.connection_enable_foreign_keys(connection: Connection) → None

Enables foreign key constraints

Foreign keys need to be enabled to have an effect.

apsw.bestpractice.connection_optimize(connection: Connection) → None

Enables query planner optimization

It enables the query planner to record cases when it would benefit from having accurate statistics about tables and indexes for the queries you make.

You can later run connection.pragma("optimize") to have those statistics updated, such as when closing a database or periodically when the database is open for long periods of time. The statistics are recorded in the database and help with future queries during this connection, and all future connections.

There is more detail in the documentation.

apsw.bestpractice.connection_recursive_triggers(connection: Connection) → None

Recursive triggers are off for historical backwards compatibility

This enables them.

apsw.bestpractice.connection_wal(connection: Connection) → None

Turns on write ahead logging

Reduces contention and improves write performance. WAL is described here.

apsw.bestpractice.library_logging() → None

Forwards SQLite logging to Python logging module

See apsw.ext.log_sqlite()

apsw.bestpractice.recommended: tuple[Callable, ...] = (<function connection_wal>, <function connection_busy_timeout>, <function connection_enable_foreign_keys>, <function connection_dqs>, <function connection_optimize>, <function connection_recursive_triggers>, <function library_logging>)

All of them