Synchronous Driver Base¶

from sqlspec.driver import (
    SyncDataDictionaryBase,
    SyncDriverAdapterBase,
    SyncPoolConnectionContext,
    SyncPoolSessionFactory,
    SyncRowStream,
)

Base synchronous driver adapter class inherited by all synchronous SQLSpec database adapters (SqliteDriver, PsycopgSyncDriver, PymysqlDriver, OracleSyncDriver, DuckDBDriver, BigQueryDriver, SpannerSyncDriver, Db2SyncDriver, MssqlSyncDriver, PymssqlDriver, AdbcDriver, ArrowOdbcDriver). See Querying for usage examples.

Synchronous Driver Adapter¶

SyncDriverAdapterBase ¶

Bases: CommonDriverAttributesMixin

Base execution, transaction, Arrow, and storage adapter for synchronous database drivers.

Provides uniform SQL compilation, parameter binding, statement caching, result mapping, Apache Arrow fetching, and storage pipeline execution over synchronous driver connections.

execute ¶

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

Execute a statement with parameter handling.

execute_many ¶

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

Execute statement multiple times with different parameters.

Parameters passed will be used as the batch execution sequence.

execute_script ¶

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

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.

execute_stack ¶

execute_stack(stack, *, continue_on_error=False)

Execute a StatementStack sequentially using the adapter's primitives.

select ¶

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

Execute a select statement and return all rows.

select_one ¶

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

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 ¶

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

Execute a select statement and return at most one row.

Returns None if no rows are found. Raises :class:~sqlspec.exceptions.MultipleResultsFoundError if more than one row is returned. Any database or SQL execution errors raised by the driver are propagated unchanged.

select_value ¶

select_value(
    statement, /, *parameters, value_type=None, statement_config=None, **kwargs
)

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.

Parameters:

Name	Type	Description	Default
`statement`	`Statement \| QueryBuilder`	SQL statement or query builder to execute.	required
`*parameters`	`StatementParameters \| StatementFilter`	Positional parameters for the statement.	`()`
`value_type`	`type[ValueT] \| None`	Optional type to convert the result to. When provided, the return value is converted to this type and the return type is narrowed for type checkers. Supports int, float, str, bool, datetime, date, time, Decimal, UUID, Path, dict, and list.	`None`
`statement_config`	`StatementConfig \| None`	Optional statement configuration.	`None`
`**kwargs`	`Any`	Additional keyword arguments.	`{}`

Returns:

Type	Description
`ValueT \| Any`	The scalar value, optionally converted to the specified type.

select_value_or_none ¶

select_value_or_none(
    statement, /, *parameters, value_type=None, statement_config=None, **kwargs
)

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.

Parameters:

Name	Type	Description	Default
`statement`	`Statement \| QueryBuilder`	SQL statement or query builder to execute.	required
`*parameters`	`StatementParameters \| StatementFilter`	Positional parameters for the statement.	`()`
`value_type`	`type[ValueT] \| None`	Optional type to convert the result to. When provided, the return value is converted to this type and the return type is narrowed to `T \| None` for type checkers. Supports int, float, str, bool, datetime, date, time, Decimal, UUID, Path, dict, and list.	`None`
`statement_config`	`StatementConfig \| None`	Optional statement configuration.	`None`
`**kwargs`	`Any`	Additional keyword arguments.	`{}`

Returns:

Type	Description
`ValueT \| Any \| None`	The scalar value (optionally converted), or None if no rows found.

select_with_total ¶

select_with_total(
    statement,
    /,
    *parameters,
    schema_type=None,
    statement_config=None,
    count_with_window=False,
    **kwargs,
)

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:

Name	Type	Description	Default
`statement`	`Statement \| QueryBuilder`	The SQL statement, QueryBuilder, or raw SQL string	required
`*parameters`	`StatementParameters \| StatementFilter`	Parameters for the SQL statement	`()`
`schema_type`	`type[SchemaT] \| None`	Optional schema type for data transformation	`None`
`statement_config`	`StatementConfig \| None`	Optional SQL configuration	`None`
`count_with_window`	`bool`	If True, use a single query with COUNT(*) OVER() window function instead of two separate queries. This can be more efficient for some databases but adds a column to each row. Default False.	`False`
`**kwargs`	`Any`	Additional keyword arguments	`{}`

A tuple containing: - List of data rows (transformed by schema_type if provided) - Total count of rows matching the query (ignoring LIMIT/OFFSET)

select_to_arrow ¶

select_to_arrow(
    statement,
    /,
    *parameters,
    statement_config=None,
    return_format="table",
    native_only=False,
    batch_size=None,
    arrow_schema=None,
    **kwargs,
)

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:

Name	Type	Description	Default
`statement`	`Statement \| QueryBuilder`	SQL query string, Statement, or QueryBuilder	required
`*parameters`	`StatementParameters \| StatementFilter`	Query parameters (same format as execute()/select())	`()`
`statement_config`	`StatementConfig \| None`	Optional statement configuration override	`None`
`return_format`	`ArrowReturnFormat`	"table" for pyarrow.Table (default), "batch" for single RecordBatch, "batches" for iterator of RecordBatches, "reader" for RecordBatchReader	`'table'`
`native_only`	`bool`	If True, raise error if native Arrow unavailable (default: False)	`False`
`batch_size`	`int \| None`	Rows per batch for "batch"/"batches" format (default: None = all rows)	`None`
`arrow_schema`	`Any`	Optional pyarrow.Schema for type casting	`None`
`**kwargs`	`Any`	Additional keyword arguments	`{}`

Returns:

Type	Description
`ArrowResult`	ArrowResult containing pyarrow.Table, RecordBatchReader, or RecordBatches

Raises:

Type	Description
`ImproperConfigurationError`	If native_only=True and adapter doesn't support native Arrow

load_from_arrow ¶

load_from_arrow(table, source, *, partitioner=None, overwrite=False)

Load Arrow data into the target table.

Parameters:

Name	Type	Description	Default
`table`	`str`	Target table name.	required
`source`	`ArrowResult \| Any`	Arrow data source.	required
`partitioner`	`dict[str, object] \| None`	Optional partitioner configuration.	`None`
`overwrite`	`bool`	Whether to overwrite existing data.	`False`

Returns:

Type	Description
`StorageBridgeJob`	StorageBridgeJob with execution telemetry.

begin ¶

begin()

Begin a database transaction on the current connection.

commit ¶

commit()

Commit the current transaction on the current connection.

rollback ¶

rollback()

Rollback the current transaction on the current connection.

transaction ¶

transaction()

Return a context manager that wraps a block in a transaction.

Entering the block calls begin() and yields this driver. A normal exit calls commit(); an exception calls rollback() and propagates. A failed commit is followed by a rollback attempt before the commit error propagates. When the connection already has an open transaction, the block joins it instead of calling begin() and commits it on exit. Inside another transaction() or service begin_transaction() block on this driver, the block runs in a savepoint instead and leaves the outer transaction open. Isolation settings are applied with execute_script inside the block.

Example

.. code-block:: python

with session.transaction():
    session.execute(
        "INSERT INTO items (id) VALUES (:id)", id=1
    )

Returns:

Type	Description
`_SyncDriverTransaction[Self]`	A context manager yielding this driver.

Synchronous Data Dictionary & Streaming¶

SyncDataDictionaryBase ¶

Bases: DataDictionaryDialectMixin, DataDictionaryMixin

Base class for synchronous data dictionary implementations.

Uses Python-compatible class layouts for cross-module inheritance. Child classes define dialect as a class attribute.

SyncRowStream ¶

Bases: Generic[RowT]

Bounded-memory iterator backed by a chunk source.

Pool Context Managers¶

SyncPoolConnectionContext ¶

Generic sync connection context using pool.get_connection() pattern.

Subclass per adapter for type-safe provide_connection() return annotations.

SyncPoolSessionFactory ¶

Generic sync session factory using pool.get_connection() pattern.

Subclass per adapter for type-safe acquire_connection() return annotations.