Driver

The driver module provides base classes and mixins for database operations. It defines the foundation for both synchronous and asynchronous database drivers.

Overview

The driver system consists of:

  • Base Drivers - Abstract base classes for sync/async drivers

  • Mixins - Reusable functionality for query operations

  • Transaction Management - Context managers for transactions

  • Result Processing - Standard result handling

  • Connection Lifecycle - Connection pooling and cleanup

Base Driver Classes

Synchronous Driver

class sqlspec.driver._sync.SyncDriverAdapterBase[source]

Bases: CommonDriverAttributesMixin, SQLTranslatorMixin, StorageDriverMixin

Base class for synchronous database drivers.

Base class for synchronous database drivers.

Abstract methods to implement:

  • execute() - Execute SQL and return results

  • execute_many() - Execute SQL for multiple parameter sets

  • begin_transaction() - Start a transaction

  • commit_transaction() - Commit a transaction

  • rollback_transaction() - Rollback a transaction

  • close() - Close the connection

is_async: bool = False
abstract property data_dictionary: SyncDataDictionaryBase

Get the data dictionary for this driver.

Returns:

Data dictionary instance for metadata queries

dispatch_statement_execution(statement, connection)[source]

Central execution dispatcher using the Template Method Pattern.

Parameters:

  • statement (SQL) – The SQL statement to execute

  • connection (typing.Any) – The database connection to use

Return type:

SQLResult

Returns:

The result of the SQL execution

abstractmethod with_cursor(connection)[source]

Create and return a context manager for cursor acquisition and cleanup.

Returns a context manager that yields a cursor for database operations. Concrete implementations handle database-specific cursor creation and cleanup.

Return type:

Any

abstractmethod handle_database_exceptions()[source]

Handle database-specific exceptions and wrap them appropriately.

Return type:

AbstractContextManager[None]

Returns:

ContextManager that can be used in with statements

abstractmethod begin()[source]

Begin a database transaction on the current connection.

Return type:

None

abstractmethod rollback()[source]

Rollback the current transaction on the current connection.

Return type:

None

abstractmethod commit()[source]

Commit the current transaction on the current connection.

Return type:

None

execute_stack(stack, *, continue_on_error=False)[source]

Execute a StatementStack sequentially using the adapter’s primitives.

Return type:

tuple[StackResult, ...]

execute(statement, /, *parameters, statement_config=None, **kwargs)[source]

Execute a statement with parameter handling.

execute_many(statement, /, parameters, *filters, statement_config=None, **kwargs)[source]

Execute statement multiple times with different parameters.

Parameters passed will be used as the batch execution sequence.

execute_script(statement, /, *parameters, statement_config=None, **kwargs)[source]

Execute a multi-statement script.

By default, validates each statement and logs warnings for dangerous operations. Use suppress_warnings=True for migrations and admin scripts.

select_one(statement, /, *parameters, schema_type=None, statement_config=None, **kwargs)[source]

Execute a select statement and return exactly one row.

Raises an exception if no rows or more than one row is returned.

select_one_or_none(statement, /, *parameters, schema_type=None, statement_config=None, **kwargs)[source]

Execute a select statement and return at most one row.

Returns None if no rows are found. Raises an exception if more than one row is returned.

select(statement, /, *parameters, schema_type=None, statement_config=None, **kwargs)[source]

Execute a select statement and return all rows.

select_to_arrow(statement, /, *parameters, statement_config=None, return_format='table', native_only=False, batch_size=None, arrow_schema=None, **kwargs)[source]

Execute query and return results as Apache Arrow format.

This base implementation uses the conversion path: execute() → dict → Arrow. Adapters with native Arrow support (ADBC, DuckDB, BigQuery) override this method to use zero-copy native paths for 5-10x performance improvement.

Parameters:

  • statement – SQL query string, Statement, or QueryBuilder

  • *parameters – Query parameters (same format as execute()/select())

  • statement_config – Optional statement configuration override

  • return_format – “table” for pyarrow.Table (default), “batch” for single RecordBatch, “batches” for iterator of RecordBatches, “reader” for RecordBatchReader

  • native_only – If True, raise error if native Arrow unavailable (default: False)

  • batch_size – Rows per batch for “batch”/”batches” format (default: None = all rows)

  • arrow_schema – Optional pyarrow.Schema for type casting

  • **kwargs – Additional keyword arguments

Returns:

ArrowResult containing pyarrow.Table, RecordBatchReader, or RecordBatches

Raises:

ImproperConfigurationError – If native_only=True and adapter doesn’t support native Arrow

Examples

>>> result = driver.select_to_arrow(
...     "SELECT * FROM users WHERE age > ?", 18
... )
>>> df = result.to_pandas()
>>> print(df.head())

>>> # Force native Arrow path (raises error if unavailable)
>>> result = driver.select_to_arrow(
...     "SELECT * FROM users", native_only=True
... )
select_value(statement, /, *parameters, statement_config=None, **kwargs)[source]

Execute a select statement and return a single scalar value.

Expects exactly one row with one column. Raises an exception if no rows or more than one row/column is returned.

select_value_or_none(statement, /, *parameters, statement_config=None, **kwargs)[source]

Execute a select statement and return a single scalar value or None.

Returns None if no rows are found. Expects at most one row with one column. Raises an exception if more than one row is returned.

select_with_total(statement, /, *parameters, schema_type=None, statement_config=None, **kwargs)[source]

Execute a select statement and return both the data and total count.

This method is designed for pagination scenarios where you need both the current page of data and the total number of rows that match the query.

Parameters:

  • statement – The SQL statement, QueryBuilder, or raw SQL string

  • *parameters – Parameters for the SQL statement

  • schema_type – Optional schema type for data transformation

  • statement_config – Optional SQL configuration

  • **kwargs – Additional keyword arguments

Returns:

  • List of data rows (transformed by schema_type if provided)

  • Total count of rows matching the query (ignoring LIMIT/OFFSET)

Return type:

A tuple containing

Asynchronous Driver

class sqlspec.driver._async.AsyncDriverAdapterBase[source]

Bases: CommonDriverAttributesMixin, SQLTranslatorMixin, StorageDriverMixin

Base class for asynchronous database drivers.

Base class for asynchronous database drivers.

Abstract methods to implement:

  • execute() - Execute SQL and return results (async)

  • execute_many() - Execute SQL for multiple parameter sets (async)

  • begin_transaction() - Start a transaction (async)

  • commit_transaction() - Commit a transaction (async)

  • rollback_transaction() - Rollback a transaction (async)

  • close() - Close the connection (async)

is_async: bool = True
abstract property data_dictionary: AsyncDataDictionaryBase

Get the data dictionary for this driver.

Returns:

Data dictionary instance for metadata queries

async dispatch_statement_execution(statement, connection)[source]

Central execution dispatcher using the Template Method Pattern.

Parameters:

  • statement (SQL) – The SQL statement to execute

  • connection (typing.Any) – The database connection to use

Return type:

SQLResult

Returns:

The result of the SQL execution

abstractmethod with_cursor(connection)[source]

Create and return an async context manager for cursor acquisition and cleanup.

Returns an async context manager that yields a cursor for database operations. Concrete implementations handle database-specific cursor creation and cleanup.

Return type:

Any

abstractmethod handle_database_exceptions()[source]

Handle database-specific exceptions and wrap them appropriately.

Return type:

AbstractAsyncContextManager[None]

Returns:

AsyncContextManager that can be used in async with statements

abstractmethod async begin()[source]

Begin a database transaction on the current connection.

Return type:

None

abstractmethod async rollback()[source]

Rollback the current transaction on the current connection.

Return type:

None

abstractmethod async commit()[source]

Commit the current transaction on the current connection.

Return type:

None

async execute_stack(stack, *, continue_on_error=False)[source]

Execute a StatementStack sequentially using the adapter’s primitives.

Return type:

tuple[StackResult, ...]

async execute(statement, /, *parameters, statement_config=None, **kwargs)[source]

Execute a statement with parameter handling.

async execute_many(statement, /, parameters, *filters, statement_config=None, **kwargs)[source]

Execute statement multiple times with different parameters.

Parameters passed will be used as the batch execution sequence.

async execute_script(statement, /, *parameters, statement_config=None, **kwargs)[source]

Execute a multi-statement script.

By default, validates each statement and logs warnings for dangerous operations. Use suppress_warnings=True for migrations and admin scripts.

async select_one(statement, /, *parameters, schema_type=None, statement_config=None, **kwargs)[source]

Execute a select statement and return exactly one row.

Raises an exception if no rows or more than one row is returned.

async select_one_or_none(statement, /, *parameters, schema_type=None, statement_config=None, **kwargs)[source]

Execute a select statement and return at most one row.

Returns None if no rows are found. Raises an exception if more than one row is returned.

async select(statement, /, *parameters, schema_type=None, statement_config=None, **kwargs)[source]

Execute a select statement and return all rows.

async select_to_arrow(statement, /, *parameters, statement_config=None, return_format='table', native_only=False, batch_size=None, arrow_schema=None, **kwargs)[source]

Execute query and return results as Apache Arrow format (async).

This base implementation uses the conversion path: execute() → dict → Arrow. Adapters with native Arrow support (ADBC, DuckDB, BigQuery) override this method to use zero-copy native paths for 5-10x performance improvement.

Parameters:

  • statement – SQL query string, Statement, or QueryBuilder

  • *parameters – Query parameters (same format as execute()/select())

  • statement_config – Optional statement configuration override

  • return_format – “table” for pyarrow.Table (default), “batch” for single RecordBatch, “batches” for iterator of RecordBatches, “reader” for RecordBatchReader

  • native_only – If True, raise error if native Arrow unavailable (default: False)

  • batch_size – Rows per batch for “batch”/”batches” format (default: None = all rows)

  • arrow_schema – Optional pyarrow.Schema for type casting

  • **kwargs – Additional keyword arguments

Returns:

ArrowResult containing pyarrow.Table, RecordBatchReader, or RecordBatches

Raises:

ImproperConfigurationError – If native_only=True and adapter doesn’t support native Arrow

Examples

>>> result = await driver.select_to_arrow(
...     "SELECT * FROM users WHERE age > ?", 18
... )
>>> df = result.to_pandas()
>>> print(df.head())

>>> # Force native Arrow path (raises error if unavailable)
>>> result = await driver.select_to_arrow(
...     "SELECT * FROM users", native_only=True
... )
async select_value(statement, /, *parameters, statement_config=None, **kwargs)[source]

Execute a select statement and return a single scalar value.

Expects exactly one row with one column. Raises an exception if no rows or more than one row/column is returned.

async select_value_or_none(statement, /, *parameters, statement_config=None, **kwargs)[source]

Execute a select statement and return a single scalar value or None.

Returns None if no rows are found. Expects at most one row with one column. Raises an exception if more than one row is returned.

async select_with_total(statement, /, *parameters, schema_type=None, statement_config=None, **kwargs)[source]

Execute a select statement and return both the data and total count.

This method is designed for pagination scenarios where you need both the current page of data and the total number of rows that match the query.

Parameters:

  • statement – The SQL statement, QueryBuilder, or raw SQL string

  • *parameters – Parameters for the SQL statement

  • schema_type – Optional schema type for data transformation

  • statement_config – Optional SQL configuration

  • **kwargs – Additional keyword arguments

Returns:

  • List of data rows (transformed by schema_type if provided)

  • Total count of rows matching the query (ignoring LIMIT/OFFSET)

Return type:

A tuple containing

Driver Mixins

Transaction Management

Both sync and async drivers support transaction context managers:

# Async transactions
       await driver.execute("INSERT INTO users VALUES (?, ?)", "Bob", 25)
    await driver.execute("UPDATE accounts SET balance = balance - 50 WHERE user = ?", "Bob")

Connection Pooling

Common driver attributes and utilities.

class sqlspec.driver._common.CommonDriverAttributesMixin[source]

Bases: object

Common attributes and methods for driver adapters.

__init__(connection, statement_config, driver_features=None, observability=None)[source]

Initialize driver adapter with connection and configuration.

Parameters:

  • connection (typing.Any) – Database connection instance

  • statement_config (StatementConfig) – Statement configuration for the driver

  • driver_features (dict[str, typing.Any] | None) – Driver-specific features like extensions, secrets, and connection callbacks

  • observability (ObservabilityRuntime | None) – Optional runtime handling lifecycle hooks, observers, and spans

connection: Any
statement_config: StatementConfig
driver_features: dict[str, Any]
attach_observability(runtime)[source]

Attach or replace the observability runtime.

Return type:

None

property observability: ObservabilityRuntime

Return the observability runtime, creating a disabled instance when absent.

property stack_native_disabled: bool

Return True when native stack execution is disabled for this driver.

create_execution_result(cursor_result, *, rowcount_override=None, special_data=None, selected_data=None, column_names=None, data_row_count=None, statement_count=None, successful_statements=None, is_script_result=False, is_select_result=False, is_many_result=False, last_inserted_id=None)[source]

Create ExecutionResult with all necessary data for any operation type.

Parameters:

  • cursor_result (Any) – The raw result returned by the database cursor/driver

  • rowcount_override (int | None) – Optional override for the number of affected rows

  • special_data (Any) – Any special metadata or additional information

  • selected_data (Optional[list[dict[str, typing.Any]]]) – For SELECT operations, the extracted row data

  • column_names (Optional[list[str]]) – For SELECT operations, the column names

  • data_row_count (int | None) – For SELECT operations, the number of rows returned

  • statement_count (int | None) – For script operations, total number of statements

  • successful_statements (int | None) – For script operations, number of successful statements

  • is_script_result (bool) – Whether this result is from script execution

  • is_select_result (bool) – Whether this result is from a SELECT operation

  • is_many_result (bool) – Whether this result is from an execute_many operation

  • last_inserted_id (int | str | None) – The ID of the last inserted row (if applicable)

Return type:

ExecutionResult

Returns:

ExecutionResult configured for the specified operation type

build_statement_result(statement, execution_result)[source]

Build and return the SQLResult from ExecutionResult data.

Parameters:

  • statement (SQL) – SQL statement that was executed

  • execution_result (ExecutionResult) – ExecutionResult containing all necessary data

Return type:

SQLResult

Returns:

SQLResult with complete execution data

prepare_statement(statement, parameters=(), *, statement_config, kwargs=None)[source]

Build SQL statement from various input types.

Ensures dialect is set and preserves existing state when rebuilding SQL objects.

Parameters:

  • statement – SQL statement or QueryBuilder to prepare

  • parameters – Parameters for the SQL statement

  • statement_config – Statement configuration

  • kwargs – Additional keyword arguments

Returns:

Prepared SQL statement

split_script_statements(script, statement_config, strip_trailing_semicolon=False)[source]

Split a SQL script into individual statements.

Uses a lexer-driven state machine to handle multi-statement scripts, including complex constructs like PL/SQL blocks, T-SQL batches, and nested blocks.

Parameters:

  • script (str) – The SQL script to split

  • statement_config (StatementConfig) – Statement configuration containing dialect information

  • strip_trailing_semicolon (bool) – If True, remove trailing semicolons from statements

Return type:

list[str]

Returns:

A list of individual SQL statements

prepare_driver_parameters(parameters, statement_config, is_many=False, prepared_statement=None)[source]

Prepare parameters for database driver consumption.

Normalizes parameter structure and unwraps TypedParameter objects to their underlying values, which database drivers expect.

Parameters:

  • parameters (Any) – Parameters in any format (dict, list, tuple, scalar, TypedParameter)

  • statement_config (StatementConfig) – Statement configuration for parameter style detection

  • is_many (bool) – If True, handle as executemany parameter sequence

  • prepared_statement (Any | None) – Optional prepared statement containing metadata for parameter processing

Return type:

Any

Returns:

Parameters with TypedParameter objects unwrapped to primitive values

static find_filter(filter_type, filters)[source]

Get the filter specified by filter type from the filters.

Parameters:

  • filter_type – The type of filter to find.

  • filters – filter types to apply to the query

Returns:

The match filter instance or None

class sqlspec.driver._common.DataDictionaryMixin[source]

Bases: object

Mixin providing common data dictionary functionality.

parse_version_string(version_str)[source]

Parse version string into VersionInfo.

Parameters:

version_str (str) – Raw version string from database

Return type:

VersionInfo | None

Returns:

VersionInfo instance or None if parsing fails

detect_version_with_queries(driver, queries)[source]

Try multiple version queries to detect database version.

Parameters:

  • driver (Any) – Database driver instance

  • queries (list[str]) – List of SQL queries to try

Return type:

VersionInfo | None

Returns:

Version information or None if detection fails

get_default_type_mapping()[source]

Get default type mappings for common categories.

Return type:

dict[str, str]

Returns:

Dictionary mapping type categories to generic SQL types

get_default_features()[source]

Get default feature flags supported by most databases.

Return type:

list[str]

Returns:

List of commonly supported feature names

class sqlspec.driver._common.ExecutionResult[source]

Bases: NamedTuple

Execution result containing all data needed for SQLResult building.

cursor_result: Any

Alias for field number 0

rowcount_override: int | None

Alias for field number 1

special_data: Any

Alias for field number 2

selected_data: Optional[list[dict[str, Any]]]

Alias for field number 3

column_names: Optional[list[str]]

Alias for field number 4

data_row_count: int | None

Alias for field number 5

statement_count: int | None

Alias for field number 6

successful_statements: int | None

Alias for field number 7

is_script_result: bool

Alias for field number 8

is_select_result: bool

Alias for field number 9

is_many_result: bool

Alias for field number 10

last_inserted_id: int | str | None

Alias for field number 11

class sqlspec.driver._common.ScriptExecutionResult[source]

Bases: NamedTuple

Result from script execution with statement count information.

cursor_result: Any

Alias for field number 0

rowcount_override: int | None

Alias for field number 1

special_data: Any

Alias for field number 2

statement_count: int

Alias for field number 3

successful_statements: int

Alias for field number 4

class sqlspec.driver._common.StackExecutionObserver[source]

Bases: object

Context manager that aggregates telemetry for stack execution.

__init__(driver, stack, continue_on_error, native_pipeline)[source]
driver
stack
continue_on_error
native_pipeline
runtime
metrics
hashed_operations
span: Any | None
started
record_operation_error(error)[source]

Record an operation error when continue-on-error is enabled.

Return type:

None

class sqlspec.driver._common.VersionInfo[source]

Bases: object

Database version information.

__init__(major, minor=0, patch=0)[source]

Initialize version info.

Parameters:

  • major (int) – Major version number

  • minor (int) – Minor version number

  • patch (int) – Patch version number

property version_tuple: tuple[int, int, int]

Get version as tuple for comparison.

__str__()[source]

String representation of version info.

Return type:

str

__repr__()[source]

Detailed string representation.

Return type:

str

__eq__(other)[source]

Check version equality.

Return type:

bool

__lt__(other)[source]

Check if this version is less than another.

Return type:

bool

__le__(other)[source]

Check if this version is less than or equal to another.

Return type:

bool

__gt__(other)[source]

Check if this version is greater than another.

Return type:

bool

__ge__(other)[source]

Check if this version is greater than or equal to another.

Return type:

bool

__hash__()[source]

Make VersionInfo hashable based on version tuple.

Return type:

int

sqlspec.driver._common.describe_stack_statement(statement)[source]

Return a readable representation of a stack statement for diagnostics.

Return type:

str

sqlspec.driver._common.handle_single_row_error(error)[source]

Normalize single-row selection errors to SQLSpec exceptions.

Return type:

NoReturn

sqlspec.driver._common.hash_stack_operations(stack)[source]

Return SHA256 fingerprints for statements contained in the stack.

Return type:

tuple[str, ...]

sqlspec.driver._common.make_cache_key_hashable(obj)[source]

Recursively convert unhashable types to hashable ones for cache keys.

For array-like objects (NumPy arrays, Python arrays, etc.), we use structural info (dtype + shape or typecode + length) rather than content for cache keys. This ensures high cache hit rates for parameterized queries with different vector values while avoiding expensive content hashing.

Parameters:

obj (Any) – Object to make hashable.

Return type:

Any

Returns:

A hashable representation of the object. Collections become tuples, arrays become structural tuples like (“ndarray”, dtype, shape).

Examples

>>> make_cache_key_hashable([1, 2, 3])
(1, 2, 3)
>>> make_cache_key_hashable({"a": 1, "b": 2})
(('a', 1), ('b', 2))

Driver Protocols

See Also