SQLite Manager
Public module members
Section titled “Public module members”plexosdb.db_manager
Section titled “plexosdb.db_manager”SQLiteConfig
Section titled “SQLiteConfig”SQLite database configuration.
Methods and properties
Section titled “Methods and properties”for_in_memory
Section titled “for_in_memory”for_in_memory(cls) -> 'SQLiteConfig'Create optimized config for in-memory databases.
for_file_database
Section titled “for_file_database”for_file_database(cls) -> 'SQLiteConfig'Create optimized config for file-based databases.
SQLiteManager
Section titled “SQLiteManager”SQLite database manager with optimized transaction support.
Methods and properties
Section titled “Methods and properties”connection
Section titled “connection”connection: sqlite3.ConnectionSQLite connection.
config
Section titled “config”config: SQLiteConfigSQLite configuration.
sqlite_version
Section titled “sqlite_version”sqlite_version: strSQLite version.
tables
Section titled “tables”tables: list[str]List of table names.
add_collation
Section titled “add_collation”add_collation(self, name: str, callable_func: Callable[[str, str], int]) -> boolRegister a collation function.
Parameters
name : strName of the collationcallable_func : callableFunction implementing the collation
Returns
boolTrue if creation succeeded, False if it failed
backup
Section titled “backup”backup(self, target_path: str | Path) -> boolBackup the database to a file.
Parameters
target_path : str or PathPath to save the database backup
Returns
boolTrue if backup succeeded, False if it failed
close(self) -> NoneClose the database connection and release resources.
execute
Section titled “execute”execute(self, query: str, params: tuple[Any, ...] | dict[str, Any] | None=None) -> boolExecute a SQL statement that doesn’t return results.
Each execution is its own transaction unless used within a transaction context.
Parameters
query : strSQL statement to executeparams : tuple or dict, optionalParameters to bind to the statement
Returns
boolTrue if execution succeeded, False if it failed
Raises
sqlite3.ErrorIf a database error occurs within a transaction
executemany
Section titled “executemany”executemany(self, query: str, params_seq: list[tuple[Any, ...]] | list[dict[str, Any]]) -> boolExecute a SQL statement with multiple parameter sets.
Parameters
query : strSQL statement to executeparams_seq : list of tuples or dictsSequence of parameter sets to bind
Returns
boolTrue if execution succeeded, False if it failed
Raises
sqlite3.ErrorIf a database error occurs within a transaction
executescript
Section titled “executescript”executescript(self, script: str) -> boolExecute a SQL script containing multiple statements.
Parameters
script : strSQL script to execute
Returns
boolTrue if execution succeeded, False if it failed
iter_query
Section titled “iter_query”iter_query(self, query: str, params: tuple[Any, ...] | dict[str, Any] | None=None, batch_size: int=1000) -> Iterator[tuple[Any, ...]]Execute a read-only query and return an iterator of results.
This is memory-efficient for large result sets. Use only for SELECT statements.
Parameters
query : strSQL query to execute (SELECT statements only)params : tuple or dict, optionalParameters to bind to the querybatch_size : int, default=1000Number of records to fetch in each batch
Yields
tupleOne database row at a time
Raises
sqlite3.ErrorIf a database error occurs
last_insert_rowid
Section titled “last_insert_rowid”last_insert_rowid(self) -> intGet the ID of the last inserted row.
Returns
intID of the last inserted row
Raises
sqlite3.ErrorIf a database error occurs
list_table_names
Section titled “list_table_names”list_table_names(self) -> list[str]Return a list of current table names on the database.
optimize
Section titled “optimize”optimize(self) -> boolRun optimization routines on the database.
VACUUM can’t run inside a transaction, so this method may commit any pending changes before optimizing the database.
Returns
boolTrue if optimization succeeded, False if it failed
query(self, query: str, params: None=None) -> list[tuple[Any, ...]]Read-only queries without parameters use the default binding.
query(self, query: str, params: tuple[Any, ...] | dict[str, Any]) -> list[tuple[Any, ...]]Read-only queries that bind positional or named parameters.
query(self, query: str, params: tuple[Any, ...] | dict[str, Any] | None=None) -> list[tuple[Any, ...]]Execute a read-only query and return all results.
Note: This method should ONLY be used for SELECT statements. For INSERT/UPDATE/DELETE, use execute() instead.
Parameters
query : strSQL query to execute (SELECT statements only)params : tuple or dict, optionalParameters to bind to the query
Returns
listQuery results (tuples or named tuples based on initialization)
Raises
sqlite3.ErrorIf a database error occurs
fetchall
Section titled “fetchall”fetchall(self, query: str, params: tuple[Any, ...] | dict[str, Any] | None=None) -> list[tuple[Any, ...]]Execute a query and return all results as a list of rows.
This method is a standard DB-API style alias for query().
Parameters
query : strSQL query to execute (SELECT statements only)params : tuple or dict, optionalParameters to bind to the query
Returns
listAll rows (as tuples or named tuples based on row_factory setting)
Raises
sqlite3.ErrorIf a database error occurs
See Also
query: Equivalent method with PlexosDB-specific namingfetchone: Get only the first row of resultsfetchall_dict: Return results as dictionaries
Examples
>>> db = SQLiteManager()>>> db.execute("CREATE TABLE test (id INTEGER, name TEXT)")>>> db.execute("INSERT INTO test VALUES (1, 'Alice')")>>> db.fetchall("SELECT * FROM test")[(1, 'Alice')]fetchall_dict
Section titled “fetchall_dict”fetchall_dict(self, query: str, params: tuple[Any, ...] | dict[str, Any] | None=None) -> list[dict[str, Any]]Execute a query and return all results as a list of dictionaries.
Parameters
query : strSQL query to execute (SELECT statements only)params : tuple or dict, optionalParameters to bind to the query
Returns
list[dict[str, Any]]All rows as dictionaries with column names as keys
Raises
sqlite3.ErrorIf a database error occurs
See Also
query: Return results as tuplesiter_dicts: Memory-efficient iterator over dictionaries
Examples
>>> db = SQLiteManager()>>> db.execute("CREATE TABLE users (id INTEGER, name TEXT)")>>> db.execute("INSERT INTO users VALUES (1, 'Alice')")>>> db.fetchall_dict("SELECT * FROM users")[{'id': 1, 'name': 'Alice'}]>>> user = db.fetchall_dict("SELECT * FROM users")[0]>>> print(f"User {user['id']}: {user['name']}")User 1: Alicefetchmany
Section titled “fetchmany”fetchmany(self, query: str, size: int=1000, params: tuple[Any, ...] | dict[str, Any] | None=None) -> list[tuple[Any, ...]]Execute a query and return a specified number of rows.
Parameters
query : strSQL query to execute (SELECT statements only)size : int, default=1000Maximum number of rows to returnparams : tuple or dict, optionalParameters to bind to the query
Returns
listUp to ‘size’ rows from the query result
Raises
sqlite3.ErrorIf a database error occurs
See Also
fetchall: Get all rowsfetchone: Get only one rowiter_query: Iterator over results
Examples
>>> db = SQLiteManager()>>> db.execute("CREATE TABLE items (id INTEGER, name TEXT)")>>> db.executemany("INSERT INTO items VALUES (?, ?)", [(i, f"Item {i}") for i in range(1, 101)])>>> # Get first 10 items>>> first_batch = db.fetchmany("SELECT * FROM items", size=10)>>> len(first_batch)10fetchone
Section titled “fetchone”fetchone(self, query: str, params: tuple[Any, ...] | dict[str, Any] | None=None) -> Any | NoneExecute a query and return only the first result row.
Parameters
query : strSQL query to execute (SELECT statements only)params : tuple or dict, optionalParameters to bind to the query
Returns
tuple or namedtuple or NoneFirst row of results or None if no results
Raises
sqlite3.ErrorIf a database error occurs
See Also
fetchall: Get all rowsfetchone_dict: Get first row as dictionary
Examples
>>> db = SQLiteManager()>>> db.execute("CREATE TABLE users (id INTEGER, name TEXT)")>>> db.execute("INSERT INTO users VALUES (1, 'Alice')")>>> user = db.fetchone("SELECT * FROM users WHERE id = ?", (1,))>>> user(1, 'Alice')>>> user = db.fetchone("SELECT * FROM users WHERE id = ?", (999,))>>> user is NoneTruefetchone_dict
Section titled “fetchone_dict”fetchone_dict(self, query: str, params: tuple[Any, ...] | dict[str, Any] | None=None) -> dict[str, Any] | NoneExecute a query and return only the first result row as a dictionary.
Parameters
query : strSQL query to execute (SELECT statements only)params : tuple or dict, optionalParameters to bind to the query
Returns
dict[str, Any] or NoneFirst row as dictionary with column names as keys, or None if no results
Raises
sqlite3.ErrorIf a database error occurs
See Also
fetchone: Get first row as tuplefetchall_dict: Get all rows as dictionaries
Examples
>>> db = SQLiteManager()>>> db.execute("CREATE TABLE users (id INTEGER, name TEXT, email TEXT)")>>> db.execute("INSERT INTO users VALUES (1, 'Alice', 'alice@example.com')")>>> user = db.fetchone_dict("SELECT * FROM users WHERE id = ?", (1,))>>> print(f"{user['name']}'s email is {user['email']}")Alice's email is alice@example.comiter_dicts
Section titled “iter_dicts”iter_dicts(self, query: str, params: tuple[Any, ...] | dict[str, Any] | None=None, batch_size: int=1000) -> Iterator[dict[str, Any]]Execute a read-only query and yield results as dictionaries.
This is memory-efficient for large result sets. Each row is returned as a dictionary with column names as keys.
Parameters
query : strSQL query to execute (SELECT statements only)params : tuple or dict, optionalParameters to bind to the querybatch_size : int, default=1000Number of records to fetch in each batch
Yields
dict[str, Any]One database row at a time as a dictionary
Raises
sqlite3.ErrorIf a database error occurs
See Also
iter_query: Iterator over tuples or named tuplesfetchall_dict: Get all results as dictionaries at once
Examples
>>> db = SQLiteManager()>>> db.execute("CREATE TABLE users (id INTEGER, name TEXT)")>>> db.executemany("INSERT INTO users VALUES (?, ?)", [(i, f"User {i}") for i in range(1, 1001)])>>> # Process users efficiently one at a time>>> for user in db.iter_dicts("SELECT * FROM users"):... print(f"Processing user {user['name']}")transaction
Section titled “transaction”transaction(self) -> Generator['SQLiteManager', None, None]Begin a transaction that can span multiple operations.
This provides explicit transaction control for grouping multiple operations into a single atomic unit that either all succeed or all fail.
Returns
context managerA context manager for the transaction
Examples
>>> with db.transaction():>>> db.execute("INSERT INTO table1 VALUES (1, 'a')")>>> db.execute("INSERT INTO table2 VALUES (1, 'b')")Raises
ExceptionRe-raises any exception from the transaction after rolling back
insert_records
Section titled “insert_records”insert_records(self, table_name: str, records: dict[str, Any] | list[dict[str, Any]]) -> boolInsert records into a table using dictionaries with column names as keys.
Fails if duplicate records exist (uses INSERT without OR REPLACE).
Parameters
table_name : strName of the table to insert records intorecords : dict or list of dictsDictionary or list of dictionaries with column names as keys
Returns
boolTrue if insertion succeeded, False if it failed
Raises
ValueErrorIf table doesn’t exist, records are empty, or have inconsistent keyssqlite3.ErrorIf a database error occurs within a transaction or duplicate records exist