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). |
BuiltQuery
¶
ExpressionBuilder
¶
SQLBuilderError
¶
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. |
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.