Skip to content

SQL Factory and Base Builder

from sqlspec.builder import (
    BuiltQuery,
    Explain,
    ExplainMixin,
    ExpressionBuilder,
    QueryBuilder,
    SQLBuilderError,
    SQLFactory,
    build_copy_from_statement,
    build_copy_statement,
    build_copy_to_statement,
    build_explain_sql,
    build_oracle_display_cursor,
    normalize_dialect_name,
)

Entry-point sql factory (SQLFactory), base QueryBuilder class, BuiltQuery result container, Explain builder, and COPY statement helpers. See Query Builder: SELECT, Filtering and Joins for usage examples.

SQL Factory

SQLFactory

Attributes:

Name Type Description
dialect
c ColumnNamespace

Return the column namespace supporting call, attribute, and subscript access.

merge_ Merge

Create a new MERGE builder (property shorthand).

case_ Case

Create a CASE expression builder.

row_number_ WindowFunctionBuilder

Create a ROW_NUMBER() window function builder.

rank_ WindowFunctionBuilder

Create a RANK() window function builder.

dense_rank_ WindowFunctionBuilder

Create a DENSE_RANK() window function builder.

lag_ WindowFunctionBuilder

Create a LAG() window function builder.

lead_ WindowFunctionBuilder

Create a LEAD() window function builder.

count_over_ WindowFunctionBuilder

Create a COUNT(*) OVER() window function builder.

sum_over_ WindowFunctionBuilder

Create a SUM() OVER() window function builder.

avg_over_ WindowFunctionBuilder

Create an AVG() OVER() window function builder.

max_over_ WindowFunctionBuilder

Create a MAX() OVER() window function builder.

min_over_ WindowFunctionBuilder

Create a MIN() OVER() window function builder.

exists_ SubqueryBuilder

Create an EXISTS subquery builder.

in_ SubqueryBuilder

Create an IN subquery builder.

any_ SubqueryBuilder

Create an ANY subquery builder.

all_ SubqueryBuilder

Create an ALL subquery builder.

inner_join_ JoinBuilder

Create an INNER JOIN builder.

left_join_ JoinBuilder

Create a LEFT JOIN builder.

right_join_ JoinBuilder

Create a RIGHT JOIN builder.

full_join_ JoinBuilder

Create a FULL OUTER JOIN builder.

cross_join_ JoinBuilder

Create a CROSS JOIN builder.

lateral_join_ JoinBuilder

Create a LATERAL JOIN builder.

left_lateral_join_ JoinBuilder

Create a LEFT LATERAL JOIN builder.

cross_lateral_join_ JoinBuilder

Create a CROSS LATERAL JOIN builder.

select

insert

update

delete

merge

values

explain

upsert

create_table

create_table_as_select

create_view

create_materialized_view

create_index

create_schema

create_sequence

drop_table

drop_view

drop_materialized_view

drop_index

drop_schema

drop_sequence

alter_table

rename_table

comment_on

copy_from

copy_to

copy

column

col

func

var

raw

count

count_distinct

count_over

sum_over

avg_over

max_over

min_over

sum

avg

max

min

rollup

cube

grouping_sets

any

not_any_

concat

upper

lower

length

round

to_literal

decode

cast

coalesce

nvl

nvl2

bulk_insert

truncate

case

row_number

rank

dense_rank

lag

lead

Base Query Builder

QueryBuilder

Attributes:

Name Type Description
dialect
schema
enable_optimization
optimize_joins
optimize_predicates
simplify_expressions
dialect_name str | None

Returns the normalized name of the dialect, if set.

parameters dict[str, Any]

Public access to query parameters.

with_ctes dict[str, CTE]

Get WITH clause CTEs (public API).

get_expression

set_expression

has_expression

add_parameter_for_expression

add_parameter

load_parameters

load_ctes

create_placeholder

with_cte

with_

build

to_sql

to_statement

apply_filters

set_parameters

generate_unique_parameter_name

build_static_expression

BuiltQuery

SQL query with bound parameters.

sql = sql

parameters = parameters if parameters is not None else {}

dialect = dialect

ExpressionBuilder

Bases: QueryBuilder

Builder wrapper for a pre-parsed SQLGlot expression.

SQLBuilderError

Bases: SQLSpecError

Issues Building or Generating SQL statements.

normalize_dialect_name(dialect)

Normalize dialect to lowercase string.

Parameters:

Name Type Description Default
dialect DialectType | None

Dialect type, string, or None

required

Returns:

Type Description
str | None

Lowercase string representation of dialect or None

Explain and COPY Helpers

Explain

Attributes:

Name Type Description
options ExplainOptions

Get current ExplainOptions.

dialect DialectType | None

Get current dialect.

parameters dict[str, Any]

Get parameters from the underlying statement.

analyze
verbose
format
costs
buffers
timing
summary
memory
settings
wal
generic_plan
display_cursor
xplan_format
with_options
build
to_sql

ExplainMixin

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

build_explain_sql(statement_sql, options, dialect=None)

Build dialect-specific EXPLAIN SQL.

build_oracle_display_cursor(sql_id=None, cursor_child_no=None, format='TYPICAL')

Build an Oracle DBMS_XPLAN.DISPLAY_CURSOR query.

build_copy_statement(*, direction, table, location, columns=None, options=None, dialect=None)

build_copy_from_statement(table, source, *, columns=None, options=None, dialect=None)

build_copy_to_statement(table, target, *, columns=None, options=None, dialect=None)