Skip to content

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

Raises:

Type Description
SQLParsingError

If the condition is empty, contains a NUL character, contains a statement separator outside quoted text, or is a statement rather than a condition.

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)