Statements & Stack¶

from sqlspec.core import (
    SQL,
    ExplainFormat,
    ExplainOptions,
    ProcessedState,
    StackExecutionMetrics,
    StackOperation,
    Statement,
    StatementConfig,
    StatementStack,
    get_default_config,
    get_default_parameter_config,
)

SQL statement encapsulation, dialect configuration, batched StatementStack pipelines, and EXPLAIN options. See Querying and Transactions for usage examples.

Statement & Configuration¶

`SQL` ¶

SQL statement with parameter and filter support.

Represents a SQL statement that can be compiled with parameters and filters. Supports both positional and named parameters, statement filtering, and various execution modes including batch operations.

`sql` ¶

Get the raw SQL string (alias of :attr:raw_sql).

`raw_sql` ¶

Get raw SQL string (public API).

Returns:

Type	Description
`str`	The raw SQL string

`parameters` ¶

Get the values that will be bound to the statement.

`named_parameters` ¶

Get named parameters (public API).

`declared_parameters` ¶

Get declared parameter metadata carried with this statement (public API).

`filters` ¶

Applied filters.

`dialect` ¶

SQL dialect.

`compile()` ¶

Compile SQL statement with parameters.

Returns:

Type	Description
`tuple[str, Any]`	Tuple of compiled SQL string and execution parameters

`as_script()` ¶

Create copy marked for script execution.

Script execution binds no parameters, so any supplied parameters are embedded statically into the SQL via sqlglot. The script copy forces needs_static_script_compilation so every adapter compiles bind-free.

Returns:

Type	Description
`SQL`	New SQL instance configured for script execution

`copy(statement=None, parameters=None, **kwargs)` ¶

Create copy with modifications.

Parameters:

Name	Type	Description	Default
`statement`	`str \| Expr \| None`	New SQL statement to use	`None`
`parameters`	`Any \| None`	New parameters to use	`None`
`**kwargs`	`Any`	Additional modifications	`{}`

Returns:

Type	Description
`SQL`	New SQL instance with modifications applied

`add_named_parameter(name, value)` ¶

Add a named parameter and return a new SQL instance.

Parameters:

Name	Type	Description	Default
`name`	`str`	Parameter name	required
`value`	`Any`	Parameter value	required

Returns:

Type	Description
`SQL`	New SQL instance with the added parameter

`returns_rows()` ¶

Check if statement returns rows.

Returns:

Type	Description
`bool`	True if the SQL statement returns result rows

`is_modifying_operation()` ¶

Check if the SQL statement is a modifying operation.

Returns:

Type	Description
`bool`	True if the operation modifies data (INSERT/UPDATE/DELETE)

`where(condition)` ¶

Add WHERE condition to the SQL statement.

Parameters:

Name	Type	Description	Default
`condition`	`str \| Expr`	WHERE condition as string or SQLGlot expression	required

Returns:

Type	Description
`SQL`	New SQL instance with the WHERE condition applied

`where_eq(column, value)` ¶

Add WHERE column = value condition.

None renders column IS NULL, because column = NULL is never true.

Parameters:

Name	Type	Description	Default
`column`	`str \| Column`	Column name or expression	required
`value`	`Any`	Value to compare against	required

Returns:

Type	Description
`SQL`	New SQL instance with WHERE condition applied

`where_neq(column, value)` ¶

Add WHERE column != value condition.

None renders column IS NOT NULL, because column <> NULL is never true.

Parameters:

Name	Type	Description	Default
`column`	`str \| Column`	Column name or expression	required
`value`	`Any`	Value to compare against	required

Returns:

Type	Description
`SQL`	New SQL instance with WHERE condition applied

`where_lt(column, value)` ¶

Add WHERE column < value condition.

Parameters:

Name	Type	Description	Default
`column`	`str \| Column`	Column name or expression	required
`value`	`Any`	Value to compare against	required

Returns:

Type	Description
`SQL`	New SQL instance with WHERE condition applied

`where_lte(column, value)` ¶

Add WHERE column <= value condition.

Parameters:

Name	Type	Description	Default
`column`	`str \| Column`	Column name or expression	required
`value`	`Any`	Value to compare against	required

Returns:

Type	Description
`SQL`	New SQL instance with WHERE condition applied

`where_gt(column, value)` ¶

Add WHERE column > value condition.

Parameters:

Name	Type	Description	Default
`column`	`str \| Column`	Column name or expression	required
`value`	`Any`	Value to compare against	required

Returns:

Type	Description
`SQL`	New SQL instance with WHERE condition applied

`where_gte(column, value)` ¶

Add WHERE column >= value condition.

Parameters:

Name	Type	Description	Default
`column`	`str \| Column`	Column name or expression	required
`value`	`Any`	Value to compare against	required

Returns:

Type	Description
`SQL`	New SQL instance with WHERE condition applied

`where_like(column, pattern)` ¶

Add WHERE column LIKE pattern condition.

Parameters:

Name	Type	Description	Default
`column`	`str \| Column`	Column name or expression	required
`pattern`	`str`	LIKE pattern	required

Returns:

Type	Description
`SQL`	New SQL instance with WHERE condition applied

`where_ilike(column, pattern)` ¶

Add WHERE column ILIKE pattern condition (case-insensitive).

Parameters:

Name	Type	Description	Default
`column`	`str \| Column`	Column name or expression	required
`pattern`	`str`	ILIKE pattern	required

Returns:

Type	Description
`SQL`	New SQL instance with WHERE condition applied

`where_is_null(column)` ¶

Add WHERE column IS NULL condition.

Parameters:

Name	Type	Description	Default
`column`	`str \| Column`	Column name or expression	required

Returns:

Type	Description
`SQL`	New SQL instance with WHERE condition applied

`where_is_not_null(column)` ¶

Add WHERE column IS NOT NULL condition.

Parameters:

Name	Type	Description	Default
`column`	`str \| Column`	Column name or expression	required

Returns:

Type	Description
`SQL`	New SQL instance with WHERE condition applied

`where_in(column, values)` ¶

Add WHERE column IN (values) condition.

Parameters:

Name	Type	Description	Default
`column`	`str \| Column`	Column name or expression	required
`values`	`Sequence[Any]`	Sequence of values for IN clause	required

Returns:

Type	Description
`SQL`	New SQL instance with WHERE condition applied

`where_not_in(column, values)` ¶

Add WHERE column NOT IN (values) condition.

Parameters:

Name	Type	Description	Default
`column`	`str \| Column`	Column name or expression	required
`values`	`Sequence[Any]`	Sequence of values for NOT IN clause	required

Returns:

Type	Description
`SQL`	New SQL instance with WHERE condition applied

`where_between(column, low, high)` ¶

Add WHERE column BETWEEN low AND high condition.

Parameters:

Name	Type	Description	Default
`column`	`str \| Column`	Column name or expression	required
`low`	`Any`	Lower bound value	required
`high`	`Any`	Upper bound value	required

Returns:

Type	Description
`SQL`	New SQL instance with WHERE condition applied

`order_by(*items, desc=False)` ¶

Add ORDER BY clause to the SQL statement.

Parameters:

Name	Type	Description	Default
`*items`	`str \| Expr`	ORDER BY expressions as strings or SQLGlot expressions	`()`
`desc`	`bool`	Apply descending order to each item	`False`

Returns:

Type	Description
`SQL`	New SQL instance with ORDER BY applied

`limit(value)` ¶

Add LIMIT clause to the SQL statement.

Parameters:

Name	Type	Description	Default
`value`	`int`	Maximum number of rows to return	required

Returns:

Type	Description
`SQL`	New SQL instance with LIMIT applied

`offset(value)` ¶

Add OFFSET clause to the SQL statement.

Parameters:

Name	Type	Description	Default
`value`	`int`	Number of rows to skip	required

Returns:

Type	Description
`SQL`	New SQL instance with OFFSET applied

`paginate(page, page_size)` ¶

Add LIMIT and OFFSET for pagination.

Parameters:

Name	Type	Description	Default
`page`	`int`	Page number (1-indexed)	required
`page_size`	`int`	Number of items per page	required

Returns:

Type	Description
`SQL`	New SQL instance with LIMIT and OFFSET applied

`select_only(*columns, prune_columns=None)` ¶

Replace SELECT columns with only the specified columns.

This is useful for narrowing down the columns returned by a query without modifying the FROM clause or WHERE conditions.

Parameters:

Name	Type	Description	Default
`*columns`	`str \| Expr`	Column names or expressions to select	`()`
`prune_columns`	`bool \| None`	Remove unused columns from subqueries. When True, applies SQLGlot's qualify and pushdown_projections optimizations. Defaults to the config's enable_column_pruning setting.	`None`

Returns:

Type	Description
`SQL`	New SQL instance with only the specified columns

`builder(dialect=None)` ¶

Create a query builder seeded from this SQL statement.

Parameters:

Name	Type	Description	Default
`dialect`	`DialectType \| None`	Optional SQL dialect override for parsing and rendering.	`None`

Returns:

Type	Description
`QueryBuilder`	QueryBuilder instance initialized with the parsed statement.

Raises:

Type	Description
`SQLBuilderError`	If the statement cannot be parsed.

`explain(analyze=False, verbose=False, format=None)` ¶

Create an EXPLAIN statement for this SQL.

Wraps the current SQL statement in an EXPLAIN clause with dialect-aware syntax generation.

Parameters:

Name	Type	Description	Default
`analyze`	`bool`	Execute the statement and show actual runtime statistics	`False`
`verbose`	`bool`	Show additional information	`False`
`format`	`str \| None`	Output format (TEXT, JSON, XML, YAML, TREE, TRADITIONAL)	`None`

Returns:

Type	Description
`SQL`	New SQL instance containing the EXPLAIN statement

Statement ¶

Statement = str | exp.Expr | SQL

StatementConfig ¶

Configuration for SQL statement processing.

Controls SQL parsing, validation, transformations, parameter handling, and other processing options for SQL statements.

ProcessedState ¶

Processing results for SQL statements.

Contains the compiled SQL, execution parameters, parsed expression, operation type, and validation errors for a processed SQL statement.

get_default_config ¶

get_default_config()

Get default statement configuration.

Returns:

Type	Description
`StatementConfig`	Cached StatementConfig singleton with default settings.

get_default_parameter_config ¶

get_default_parameter_config()

Get default parameter configuration.

Returns:

Type	Description
`ParameterStyleConfig`	ParameterStyleConfig with QMARK style as default

Statement Stacks¶

StatementStack ¶

Immutable builder that preserves ordered SQL operations.

push_execute ¶

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

push_execute_many ¶

push_execute_many(
    statement, parameter_sets, /, *filters, statement_config=None, **kwargs
)

push_execute_script ¶

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

extend ¶

extend(*stacks)

StackOperation ¶

Single SQL operation captured inside a statement stack.

StackExecutionMetrics ¶

Capture telemetry facts about a stack execution.

Explain Options¶

ExplainOptions ¶

Configuration options for EXPLAIN statements.

Encapsulates all possible EXPLAIN options across different database dialects. Not all options are supported by all databases - the builder will select appropriate options based on the target dialect.

PostgreSQL Options

analyze: Execute the statement and show actual runtime statistics verbose: Show additional information (schema-qualified names, etc.) costs: Include estimated costs (default True in PostgreSQL) buffers: Include buffer usage information (requires ANALYZE) timing: Include actual timing information (requires ANALYZE) summary: Include summary information after the query plan memory: Include memory usage information (PostgreSQL 17+) settings: Include configuration parameter information (PostgreSQL 12+) wal: Include WAL usage information (requires ANALYZE, PostgreSQL 13+) generic_plan: Generate a generic plan ignoring parameter values (PostgreSQL 16+) format: Output format (TEXT, JSON, XML, YAML)

MySQL Options

analyze: Execute and show actual statistics (always uses TREE format) format: Output format (TRADITIONAL, JSON, TREE)

SQLite

Only supports EXPLAIN QUERY PLAN (no additional options)

DuckDB

analyze: Execute and show actual statistics format: Output format (TEXT, JSON)

Oracle

Uses EXPLAIN PLAN FOR + DBMS_XPLAN.DISPLAY (special handling)

BigQuery

analyze: Execute and show actual statistics (incurs costs!)

ExplainFormat ¶

Bases: StrEnum

Output formats for EXPLAIN statements.

Different databases support different output formats

TEXT: All databases (default)
JSON: PostgreSQL, MySQL (8.0+), DuckDB
XML: PostgreSQL
YAML: PostgreSQL
TREE: MySQL (8.0+), DuckDB
TRADITIONAL: MySQL (tabular output)

GeezerVison

Statements & Stack¶

Statement & Configuration¶

SQL ¶

sql ¶

raw_sql ¶

parameters ¶

named_parameters ¶

declared_parameters ¶

filters ¶

dialect ¶

compile() ¶

as_script() ¶

copy(statement=None, parameters=None, **kwargs) ¶

add_named_parameter(name, value) ¶

returns_rows() ¶

is_modifying_operation() ¶

where(condition) ¶

where_eq(column, value) ¶

where_neq(column, value) ¶

where_lt(column, value) ¶

where_lte(column, value) ¶

where_gt(column, value) ¶

where_gte(column, value) ¶

where_like(column, pattern) ¶

where_ilike(column, pattern) ¶

where_is_null(column) ¶

where_is_not_null(column) ¶

where_in(column, values) ¶

where_not_in(column, values) ¶

where_between(column, low, high) ¶

order_by(*items, desc=False) ¶

limit(value) ¶

offset(value) ¶

paginate(page, page_size) ¶

select_only(*columns, prune_columns=None) ¶

builder(dialect=None) ¶

explain(analyze=False, verbose=False, format=None) ¶

Statement ¶

StatementConfig ¶

ProcessedState ¶

get_default_config ¶

get_default_parameter_config ¶

Statement Stacks¶

StatementStack ¶

push_execute ¶

push_execute_many ¶

push_execute_script ¶

extend ¶

StackOperation ¶

StackExecutionMetrics ¶

Explain Options¶

ExplainOptions ¶

ExplainFormat ¶

`SQL` ¶

`sql` ¶

`raw_sql` ¶

`parameters` ¶

`named_parameters` ¶

`declared_parameters` ¶

`filters` ¶

`dialect` ¶

`compile()` ¶

`as_script()` ¶

`copy(statement=None, parameters=None, **kwargs)` ¶

`add_named_parameter(name, value)` ¶

`returns_rows()` ¶

`is_modifying_operation()` ¶

`where(condition)` ¶

`where_eq(column, value)` ¶

`where_neq(column, value)` ¶

`where_lt(column, value)` ¶

`where_lte(column, value)` ¶

`where_gt(column, value)` ¶

`where_gte(column, value)` ¶

`where_like(column, pattern)` ¶

`where_ilike(column, pattern)` ¶

`where_is_null(column)` ¶

`where_is_not_null(column)` ¶

`where_in(column, values)` ¶

`where_not_in(column, values)` ¶

`where_between(column, low, high)` ¶

`order_by(*items, desc=False)` ¶

`limit(value)` ¶

`offset(value)` ¶

`paginate(page, page_size)` ¶

`select_only(*columns, prune_columns=None)` ¶

`builder(dialect=None)` ¶

`explain(analyze=False, verbose=False, format=None)` ¶