Query Stack

The Query Stack APIs let you compose multiple SQL operations into an immutable StatementStack and execute them in a single driver call. Each operation preserves the underlying SQLResult/ArrowResult so downstream helpers continue to work without copying data.

Overview

The stack system is composed of:

  • StatementStack – immutable builder with push helpers for execute/execute_many/execute_script/execute_arrow

  • StackOperation – the tuple-like value object stored inside the stack (method, statement, arguments, keyword arguments)

  • StackResult – wraps the driver’s raw result while surfacing stack metadata (rows_affected, warning, error)

  • AsyncDriverAdapterBase.execute_stack / SyncDriverAdapterBase.execute_stack – adapter hooks that select native pipelines or the sequential fallback

Execute a Stack

statement stack
from sqlspec import SQLSpec
from sqlspec.adapters.sqlite import SqliteConfig
from sqlspec.core.stack import StatementStack

spec = SQLSpec()
config = spec.add_config(SqliteConfig(connection_config={"database": ":memory:"}))

stack = (
    StatementStack()
    .push_execute("create table teams (id integer primary key, name text)")
    .push_execute_many("insert into teams (name) values (:name)", [{"name": "Litestar"}, {"name": "SQLSpec"}])
    .push_execute("select id, name from teams order by id")
)

with spec.provide_session(config) as session:
    results = session.execute_stack(stack)
    rows = results[-1].result.all()

Native Pipelines and Round Trips

StatementStack always runs in order. When a driver supports native pipelines, the stack can be sent as a single pipeline or batch, reducing round trips. Drivers without native support fall back to sequential execution while preserving results per operation.

Native stack execution is available in:

  • AsyncPG

  • Psycopg (pipeline mode)

  • OracleDB

StatementStack

class sqlspec.core.stack.StatementStack[source]

Bases: object

Immutable builder that preserves ordered SQL operations.

__init__(operations=None)[source]
property operations: tuple[StackOperation, ...]
push_execute(statement, /, *parameters, statement_config=None, **kwargs)[source]
push_execute_many(statement, parameter_sets, /, *filters, statement_config=None, **kwargs)[source]
push_execute_script(statement, /, *parameters, statement_config=None, **kwargs)[source]
push_execute_arrow(statement, /, *parameters, statement_config=None, **kwargs)[source]
extend(*stacks)[source]
Return type:

StatementStack

classmethod from_operations(operations=None)[source]
Return type:

StatementStack

class sqlspec.core.stack.StackOperation[source]

Bases: object

Single SQL operation captured inside a statement stack.

__init__(method, statement, arguments=None, keyword_arguments=None)[source]
method
statement
arguments
keyword_arguments

StackResult

class sqlspec.core.result.StackResult[source]

Bases: object

Wrapper for per-operation stack results that surfaces driver results directly.

__init__(result=None, *, rows_affected=None, error=None, warning=None, metadata=None)[source]
result: StatementResult | ArrowResult
rows_affected
error
warning
metadata
get_result()[source]

Return the underlying driver result.

Return type:

StatementResult | ArrowResult

property result_type: str

Describe the underlying result type (SQL operation, Arrow, or custom).

is_sql_result()[source]

Return True when the underlying result is an SQLResult.

Return type:

bool

is_arrow_result()[source]

Return True when the underlying result is an ArrowResult.

Return type:

bool

is_error()[source]

Return True when the stack operation captured an error.

Return type:

bool

with_error(error)[source]

Return a copy of the result that records the provided error.

Return type:

StackResult

classmethod from_sql_result(result)[source]

Convert a standard SQLResult into a stack-friendly representation.

Return type:

StackResult

classmethod from_arrow_result(result)[source]

Create a stack result from an ArrowResult instance.

Return type:

StackResult

classmethod from_error(error)[source]

Create an error-only stack result.

Return type:

StackResult

Driver APIs

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

Execute a StatementStack sequentially using the adapter’s primitives.

Return type:

tuple[StackResult, ...]

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

Execute a StatementStack sequentially using the adapter’s primitives.

Return type:

tuple[StackResult, ...]

Exceptions

class sqlspec.exceptions.StackExecutionError[source]

Bases: SQLSpecError

Raised when a statement stack operation fails.

__init__(operation_index, sql, original_error, *, adapter=None, mode='fail-fast', native_pipeline=None, downgrade_reason=None)[source]

Initialize SQLSpecError.

Parameters:

  • *args – args are converted to str before passing to Exception

  • detail – detail of the exception.

Usage Highlights

  • Build stacks once and reuse them across requests/tasks.

  • Call session.execute_stack(stack, continue_on_error=False) to run fail-fast or set continue_on_error=True to record per-operation errors.

  • Inspect StackResult.result to call helpers like all(), one(), to_pandas(), or to_arrow().

  • Adapters lists per-adapter capabilities, including whether native pipelines or sequential fallback are used for stacks.