Statement & Compiler¶

SQL statement representation, configuration, and compilation.

SQL¶

class sqlspec.core.statement.SQL[source]¶

Bases: object

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.

__init__(statement, *parameters, statement_config=None, is_many=None, **kwargs)[source]¶

Initialize SQL statement.

Parameters:

statement¶ – SQL string, expression, or existing SQL object
*parameters¶ – Parameters and filters
statement_config¶ – Configuration
is_many¶ – Mark as execute_many operation
**kwargs¶ – Additional parameters

reset()[source]¶

Reset SQL object for reuse in pooling scenarios.

Return type:: None

property sql: str¶: Get the raw SQL string.

property raw_sql: str¶

Get raw SQL string (public API).

Returns:: The raw SQL string

property parameters: Any¶: Get the original parameters.

property positional_parameters: list[TypeAliasForwardRef('typing.Any')]¶: Get positional parameters (public API).

property named_parameters: dict[str, TypeAliasForwardRef('typing.Any')]¶: Get named parameters (public API).

property original_parameters: Any¶: Get original parameters (public API).

property operation_type: Literal['SELECT', 'INSERT', 'UPDATE', 'DELETE', 'COPY', 'COPY_FROM', 'COPY_TO', 'EXECUTE', 'SCRIPT', 'DDL', 'PRAGMA', 'MERGE', 'COMMAND', 'UNKNOWN']¶: SQL operation type.

property statement_config: StatementConfig¶: Statement configuration.

property expression: Expression | None¶: SQLGlot expression.

property raw_expression: Expression | None¶: Original expression supplied at construction, if available.

property filters: list[StatementFilter]¶: Applied filters.

get_filters_view()[source]¶

Get zero-copy filters view (public API).

Return type:: FiltersView
Returns:: Read-only view of filters without copying

property is_processed: bool¶: Check if SQL has been processed (public API).

get_processed_state()[source]¶

Get processed state (public API).

Return type:: Any

property dialect: str | None¶: SQL dialect.

property statement_expression: Expression | None¶

Get parsed statement expression (public API).

Returns:: Parsed SQLGlot expression or None if not parsed

property is_many: bool¶: Check if this is execute_many.

property is_script: bool¶: Check if this is script execution.

property validation_errors: list[str]¶: Validation errors.

property has_errors: bool¶: Check if there are validation errors.

returns_rows()[source]¶

Check if statement returns rows.

Return type:: bool
Returns:: True if the SQL statement returns result rows

is_modifying_operation()[source]¶

Check if the SQL statement is a modifying operation.

Return type:: bool
Returns:: True if the operation modifies data (INSERT/UPDATE/DELETE)

compile()[source]¶

Compile SQL statement with parameters.

Return type:: tuple[str, typing.Any]
Returns:: Tuple of compiled SQL string and execution parameters

as_script()[source]¶

Create copy marked for script execution.

Return type:: SQL
Returns:: New SQL instance configured for script execution

copy(statement=None, parameters=None, **kwargs)[source]¶

Create copy with modifications.

Parameters:

statement¶ (str | Expression | None) – New SQL statement to use
parameters¶ (Any | None) – New parameters to use
**kwargs¶ (Any) – Additional modifications

Return type:

SQL

Returns:

New SQL instance with modifications applied

add_named_parameter(name, value)[source]¶

Add a named parameter and return a new SQL instance.

Parameters:

name¶ (str) – Parameter name
value¶ (Any) – Parameter value

Return type:

SQL

Returns:

New SQL instance with the added parameter

where(condition)[source]¶

Add WHERE condition to the SQL statement.

Parameters:: condition¶ (str | Expression) – WHERE condition as string or SQLGlot expression
Return type:: SQL
Returns:: New SQL instance with the WHERE condition applied

where_eq(column, value)[source]¶

Add WHERE column = value condition.

Parameters:

column¶ (str | Column) – Column name or expression
value¶ (Any) – Value to compare against

Return type:

SQL

Returns:

New SQL instance with WHERE condition applied

where_neq(column, value)[source]¶

Add WHERE column != value condition.

Parameters:

column¶ (str | Column) – Column name or expression
value¶ (Any) – Value to compare against

Return type:

SQL

Returns:

New SQL instance with WHERE condition applied

where_lt(column, value)[source]¶

Add WHERE column < value condition.

Parameters:

column¶ (str | Column) – Column name or expression
value¶ (Any) – Value to compare against

Return type:

SQL

Returns:

New SQL instance with WHERE condition applied

where_lte(column, value)[source]¶

Add WHERE column <= value condition.

Parameters:

column¶ (str | Column) – Column name or expression
value¶ (Any) – Value to compare against

Return type:

SQL

Returns:

New SQL instance with WHERE condition applied

where_gt(column, value)[source]¶

Add WHERE column > value condition.

Parameters:

column¶ (str | Column) – Column name or expression
value¶ (Any) – Value to compare against

Return type:

SQL

Returns:

New SQL instance with WHERE condition applied

where_gte(column, value)[source]¶

Add WHERE column >= value condition.

Parameters:

column¶ (str | Column) – Column name or expression
value¶ (Any) – Value to compare against

Return type:

SQL

Returns:

New SQL instance with WHERE condition applied

where_like(column, pattern)[source]¶

Add WHERE column LIKE pattern condition.

Parameters:

column¶ (str | Column) – Column name or expression
pattern¶ (str) – LIKE pattern (e.g., ‘%search%’)

Return type:

SQL

Returns:

New SQL instance with WHERE condition applied

where_ilike(column, pattern)[source]¶

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

Parameters:

column¶ (str | Column) – Column name or expression
pattern¶ (str) – ILIKE pattern (e.g., ‘%search%’)

Return type:

SQL

Returns:

New SQL instance with WHERE condition applied

where_is_null(column)[source]¶

Add WHERE column IS NULL condition.

Parameters:: column¶ (str | Column) – Column name or expression
Return type:: SQL
Returns:: New SQL instance with WHERE condition applied

where_is_not_null(column)[source]¶

Add WHERE column IS NOT NULL condition.

Parameters:: column¶ (str | Column) – Column name or expression
Return type:: SQL
Returns:: New SQL instance with WHERE condition applied

where_in(column, values)[source]¶

Add WHERE column IN (values) condition.

Parameters:

column¶ (str | Column) – Column name or expression
values¶ (Sequence[typing.Any]) – Sequence of values for IN clause

Return type:

SQL

Returns:

New SQL instance with WHERE condition applied

where_not_in(column, values)[source]¶

Add WHERE column NOT IN (values) condition.

Parameters:

column¶ (str | Column) – Column name or expression
values¶ (Sequence[typing.Any]) – Sequence of values for NOT IN clause

Return type:

SQL

Returns:

New SQL instance with WHERE condition applied

where_between(column, low, high)[source]¶

Add WHERE column BETWEEN low AND high condition.

Parameters:

column¶ (str | Column) – Column name or expression
low¶ (Any) – Lower bound value
high¶ (Any) – Upper bound value

Return type:

SQL

Returns:

New SQL instance with WHERE condition applied

order_by(*items, desc=False)[source]¶

Add ORDER BY clause to the SQL statement.

Parameters:

*items¶ (str | Expression) – ORDER BY expressions as strings or SQLGlot expressions
desc¶ (bool) – Apply descending order to each item

Return type:

SQL

Returns:

New SQL instance with ORDER BY applied

limit(value)[source]¶

Add LIMIT clause to the SQL statement.

Parameters:: value¶ (int) – Maximum number of rows to return
Return type:: SQL
Returns:: New SQL instance with LIMIT applied
Raises:: SQLSpecError – If statement is not a SELECT

offset(value)[source]¶

Add OFFSET clause to the SQL statement.

Parameters:: value¶ (int) – Number of rows to skip
Return type:: SQL
Returns:: New SQL instance with OFFSET applied
Raises:: SQLSpecError – If statement is not a SELECT

paginate(page, page_size)[source]¶

Add LIMIT and OFFSET for pagination.

Parameters:

page¶ (int) – Page number (1-indexed)
page_size¶ (int) – Number of items per page

Return type:

SQL

Returns:

New SQL instance with LIMIT and OFFSET applied

Example

# Get page 3 with 20 items per page stmt = SQL(“SELECT * FROM users”).paginate(3, 20) # Results in: SELECT * FROM users LIMIT 20 OFFSET 40

select_only(*columns, prune_columns=None)[source]¶

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:

*columns¶ (str | Expression) – 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.

Return type:

SQL

Returns:

New SQL instance with only the specified columns

Example

stmt = SQL(“SELECT * FROM users WHERE active = 1”) narrow = stmt.select_only(“id”, “name”, “email”) # Results in: SELECT id, name, email FROM users WHERE active = 1

# With column pruning on a subquery: stmt = SQL(“SELECT * FROM (SELECT id, name, email, created_at FROM users) AS u”) narrow = stmt.select_only(“id”, “name”, prune_columns=True) # Results in: SELECT id, name FROM (SELECT id, name FROM users) AS u

explain(analyze=False, verbose=False, format=None)[source]¶

Create an EXPLAIN statement for this SQL.

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

Parameters:

analyze¶ (bool) – Execute the statement and show actual runtime statistics
verbose¶ (bool) – Show additional information
format¶ (str | None) – Output format (TEXT, JSON, XML, YAML, TREE, TRADITIONAL)

Return type:

SQL

Returns:

New SQL instance containing the EXPLAIN statement

Examples

Basic EXPLAIN:: stmt = SQL(“SELECT * FROM users”) explain_stmt = stmt.explain()
With options:: explain_stmt = stmt.explain(analyze=True, format=”json”)

builder(dialect=None)[source]¶

Create a query builder seeded from this SQL statement.

Parameters:: dialect¶ (Union[str, Dialect, Type[Dialect], None]) – Optional SQL dialect override for parsing and rendering.
Return type:: QueryBuilder
Returns:: QueryBuilder instance initialized with the parsed statement.
Raises:: SQLBuilderError – If the statement cannot be parsed.

Notes

Statements outside the DML set return an ExpressionBuilder without DML-specific helper methods.

__hash__()[source]¶

Hash value computation.

Return type:: int

__eq__(other)[source]¶

Equality comparison.

Return type:: bool

__repr__()[source]¶

String representation.

Return type:: str

StatementConfig¶

class sqlspec.core.statement.StatementConfig[source]¶

Bases: object

Configuration for SQL statement processing.

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

__init__(parameter_config=None, enable_parsing=True, enable_validation=True, enable_transformations=True, enable_analysis=False, enable_expression_simplification=False, enable_column_pruning=False, enable_parameter_type_wrapping=True, enable_caching=True, parameter_converter=None, parameter_validator=None, dialect=None, execution_mode=None, execution_args=None, output_transformer=None, statement_transformers=None)[source]¶

Initialize StatementConfig.

Parameters:

parameter_config¶ – Parameter style configuration
enable_parsing¶ – Enable SQL parsing
enable_validation¶ – Run SQL validators
enable_transformations¶ – Apply SQL transformers
enable_analysis¶ – Run SQL analyzers
enable_expression_simplification¶ – Apply expression simplification
enable_column_pruning¶ – Remove unused columns from subqueries during select_only
enable_parameter_type_wrapping¶ – Wrap parameters with type information
enable_caching¶ – Cache processed SQL statements
parameter_converter¶ – Handles parameter style conversions
parameter_validator¶ – Validates parameter usage and styles
dialect¶ – SQL dialect
execution_mode¶ – Special execution mode
execution_args¶ – Arguments for special execution modes
output_transformer¶ – Optional output transformation function
statement_transformers¶ – Optional AST transformers executed during compilation

freeze()[source]¶

Mark the configuration as immutable to enable caching.

Return type:: None

replace(**kwargs)[source]¶

Immutable update pattern.

Parameters:: **kwargs¶ (Any) – Attributes to update
Return type:: StatementConfig
Returns:: New StatementConfig instance with updated attributes

__hash__()[source]¶

Hash based on configuration settings.

Return type:: int

__repr__()[source]¶

String representation of the StatementConfig instance.

Return type:: str

__eq__(other)[source]¶

Equality comparison.

Return type:: bool

ProcessedState¶

class sqlspec.core.statement.ProcessedState[source]¶

Bases: object

Processing results for SQL statements.

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

__init__(compiled_sql, execution_parameters, parsed_expression=None, operation_type='COMMAND', input_named_parameters=None, applied_wrap_types=False, filter_hash=0, parameter_fingerprint=None, parameter_casts=None, validation_errors=None, parameter_profile=None, operation_profile=None, is_many=False)[source]¶

reset()[source]¶

Reset processing state for reuse.

Return type:: None

Helper Functions¶

sqlspec.core.statement.get_default_config()[source]¶

Get default statement configuration.

Return type:: StatementConfig
Returns:: Cached StatementConfig singleton with default settings.

sqlspec.core.statement.get_default_parameter_config()[source]¶

Get default parameter configuration.

Return type:: ParameterStyleConfig
Returns:: ParameterStyleConfig with QMARK style as default

Compiler¶

class sqlspec.core.compiler.SQLProcessor[source]¶

Bases: object

SQL processor with compilation and caching.

Processes SQL statements by compiling them into executable format with parameter substitution. Includes LRU-style caching for compilation results to avoid re-processing identical statements.

__init__(config, max_cache_size=1000, parse_cache_size=None, parameter_cache_size=None, validator_cache_size=None, cache_enabled=True)[source]¶

Initialize processor.

Parameters:

config¶ (StatementConfig) – Statement configuration
max_cache_size¶ (int) – Maximum number of compilation results to cache
parse_cache_size¶ (int | None) – Maximum number of parsed expressions to cache
parameter_cache_size¶ (int | None) – Maximum parameter conversion cache entries
validator_cache_size¶ (int | None) – Maximum cached parameter metadata entries
cache_enabled¶ (bool) – Toggle compiled SQL caching (parse/parameter caches remain size-driven)

compile(sql, parameters=None, is_many=False, expression=None)[source]¶

Compile SQL statement.

Parameters:

sql¶ (str) – SQL string for compilation
parameters¶ (Any) – Parameter values for substitution
is_many¶ (bool) – Whether this is for execute_many operation
expression¶ (Expression | None) – Pre-parsed SQLGlot expression to reuse

Return type:

CompiledSQL

Returns:

CompiledSQL with execution information

clear_cache()[source]¶

Clear compilation cache and reset statistics.

Return type:: None

property cache_stats: dict[str, int]¶

Get cache statistics.

Returns:: Dictionary with cache statistics

class sqlspec.core.compiler.CompiledSQL[source]¶

Bases: object

Compiled SQL result.

Contains the result of SQL compilation with information needed for execution. Immutable container holding compiled SQL text, processed parameters, operation type, and execution metadata.

__init__(compiled_sql, execution_parameters, operation_type, expression=None, parameter_style=None, supports_many=False, parameter_casts=None, parameter_profile=None, operation_profile=None, input_named_parameters=(), applied_wrap_types=False)[source]¶

Initialize compiled result.

Parameters:

compiled_sql¶ (str) – SQL string ready for execution
execution_parameters¶ (Any) – Parameters in driver-specific format
operation_type¶ (Literal['SELECT', 'INSERT', 'UPDATE', 'DELETE', 'COPY', 'COPY_FROM', 'COPY_TO', 'EXECUTE', 'SCRIPT', 'DDL', 'PRAGMA', 'MERGE', 'COMMAND', 'UNKNOWN']) – SQL operation type (SELECT, INSERT, etc.)
expression¶ (Expression | None) – SQLGlot AST expression
parameter_style¶ (str | None) – Parameter style used in compilation
supports_many¶ (bool) – Whether this supports execute_many operations
parameter_casts¶ (dict[int, str] | None) – Mapping of parameter positions to cast types
parameter_profile¶ (ParameterProfile | None) – Profile describing detected placeholders
operation_profile¶ (OperationProfile | None) – Profile describing semantic characteristics
input_named_parameters¶ (tuple[str, ...]) – Original input named parameter order
applied_wrap_types¶ (bool) – Whether type wrapping was applied

__hash__()[source]¶

Cached hash value.

Return type:: int

__eq__(other)[source]¶

Equality comparison.

Return type:: bool

__repr__()[source]¶

String representation.

Return type:: str

class sqlspec.core.compiler.OperationProfile[source]¶

Bases: object

Semantic characteristics derived from the parsed SQL expression.

__init__(returns_rows=False, modifies_rows=False)[source]¶

sqlspec.core.compiler.is_copy_operation(operation_type)[source]¶

Determine if the operation corresponds to any PostgreSQL COPY variant.

Parameters:: operation_type¶ (Literal['SELECT', 'INSERT', 'UPDATE', 'DELETE', 'COPY', 'COPY_FROM', 'COPY_TO', 'EXECUTE', 'SCRIPT', 'DDL', 'PRAGMA', 'MERGE', 'COMMAND', 'UNKNOWN']) – Operation type detected by the compiler.
Return type:: bool
Returns:: True when the operation type represents COPY, COPY FROM, or COPY TO.

sqlspec.core.compiler.is_copy_from_operation(operation_type)[source]¶

Check if the operation streams data into the database using COPY.

Parameters:: operation_type¶ (Literal['SELECT', 'INSERT', 'UPDATE', 'DELETE', 'COPY', 'COPY_FROM', 'COPY_TO', 'EXECUTE', 'SCRIPT', 'DDL', 'PRAGMA', 'MERGE', 'COMMAND', 'UNKNOWN']) – Operation type detected by the compiler.
Return type:: bool
Returns:: True for COPY operations that read from client input (COPY FROM).

sqlspec.core.compiler.is_copy_to_operation(operation_type)[source]¶

Check if the operation streams data out from the database using COPY.

Parameters:: operation_type¶ (Literal['SELECT', 'INSERT', 'UPDATE', 'DELETE', 'COPY', 'COPY_FROM', 'COPY_TO', 'EXECUTE', 'SCRIPT', 'DDL', 'PRAGMA', 'MERGE', 'COMMAND', 'UNKNOWN']) – Operation type detected by the compiler.
Return type:: bool
Returns:: True for COPY operations that write to client output (COPY TO).