DML Builders¶
from sqlspec.builder import (
ConflictBuilder,
Delete,
DeleteFromClauseMixin,
Insert,
InsertFromSelectMixin,
InsertIntoClauseMixin,
InsertValuesMixin,
Merge,
ReturningClauseMixin,
Update,
UpdateFromClauseMixin,
UpdateSetClauseMixin,
UpdateTableClauseMixin,
Values,
)
Fluent builders for INSERT, ON CONFLICT / ON DUPLICATE KEY UPDATE upserts, UPDATE, DELETE, MERGE, VALUES, and RETURNING clauses. See DML, Upserts, MERGE and DDL Schema Builders for usage examples.
Insert, Update, Delete and Merge¶
Insert
¶
Bases: QueryBuilder, ReturningClauseMixin, InsertValuesMixin, InsertFromSelectMixin, InsertIntoClauseMixin, ExplainMixin
Builder for INSERT statements.
Constructs SQL INSERT queries with parameter binding and validation.
get_insert_expression()
¶
Get the insert expression (public API).
values_from(data, *, exclude_unset=True)
¶
Add a row of values from a dict, dataclass, msgspec.Struct, Pydantic model, or attrs class.
Schema instances are normalised via :func:sqlspec.utils.serializers.schema_dump
with wire_format=False then bound as a single row. The dict shape uses Python
attribute names regardless of msgspec rename= or Pydantic Field(alias=...);
wire-aligned names never make sense for SQL column binding.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
data
|
Any
|
A dict, dataclass instance, |
required |
exclude_unset
|
bool
|
If True, exclude fields that were never set. Honoured by
msgspec (UNSET fields), Pydantic ( |
True
|
Returns:
| Type | Description |
|---|---|
Self
|
The current builder instance for method chaining. |
values_from_many(items, *, exclude_unset=True)
¶
Add multiple rows from a sequence of dicts, dataclasses, msgspec Structs, Pydantic models, or attrs classes.
Each item is normalised via :func:sqlspec.utils.serializers.schema_dump with
wire_format=False then bound as a multi-row INSERT. Mixed schema kinds in the
same sequence are supported (each item normalises independently); however, the
resulting dict shapes must agree on key sets.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
items
|
Sequence[Any]
|
A sequence of schema instances or dicts. |
required |
exclude_unset
|
bool
|
See :meth: |
True
|
Returns:
| Type | Description |
|---|---|
Self
|
The current builder instance for method chaining. Empty input returns the |
Self
|
builder unchanged. |
on_conflict(*columns)
¶
Adds an ON CONFLICT clause with specified columns.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
*columns
|
str
|
Column names that define the conflict. If no columns provided, creates an ON CONFLICT without specific columns (catches all conflicts). |
()
|
Returns:
| Type | Description |
|---|---|
ConflictBuilder
|
A ConflictBuilder instance for chaining conflict resolution methods. |
on_conflict_do_nothing(*columns)
¶
Adds an ON CONFLICT DO NOTHING clause (convenience method).
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
*columns
|
str
|
Column names that define the conflict. If no columns provided, creates an ON CONFLICT without specific columns. |
()
|
Returns:
| Type | Description |
|---|---|
Insert
|
The current builder instance for method chaining. |
ConflictBuilder
¶
Builder for ON CONFLICT clauses in INSERT statements.
Constructs conflict resolution clauses using PostgreSQL-style syntax, which SQLGlot can transpile to other dialects.
Update
¶
Bases: QueryBuilder, WhereClauseMixin, ReturningClauseMixin, UpdateSetClauseMixin, UpdateFromClauseMixin, UpdateTableClauseMixin, ExplainMixin
Builder for UPDATE statements.
Constructs SQL UPDATE statements with parameter binding and validation.
join(table, on, alias=None, join_type='INNER')
¶
Add JOIN clause to the UPDATE statement.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
table
|
str | Expr | Select
|
The table name, expression, or subquery to join. |
required |
on
|
str | Expr
|
The JOIN condition. |
required |
alias
|
str | None
|
Optional alias for the joined table. |
None
|
join_type
|
str
|
Type of join (INNER, LEFT, RIGHT, FULL). |
'INNER'
|
Returns:
| Type | Description |
|---|---|
Self
|
The current builder instance for method chaining. |
Raises:
| Type | Description |
|---|---|
SQLBuilderError
|
If the current expression is not an UPDATE statement. |
build(dialect=None)
¶
Build the UPDATE query with validation.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
dialect
|
DialectType
|
Optional dialect override for SQL generation. |
None
|
Returns:
| Name | Type | Description |
|---|---|---|
BuiltQuery |
BuiltQuery
|
The built query with SQL and parameters. |
Raises:
| Type | Description |
|---|---|
SQLBuilderError
|
If no table is set or expression is not an UPDATE. |
Delete
¶
Bases: QueryBuilder, WhereClauseMixin, ReturningClauseMixin, DeleteFromClauseMixin, ExplainMixin
Builder for DELETE statements.
Constructs SQL DELETE statements with parameter binding and validation. Does not support JOIN operations to maintain cross-dialect compatibility.
build(dialect=None)
¶
Build the DELETE query with validation.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
dialect
|
DialectType
|
Optional dialect override for SQL generation. |
None
|
Returns:
| Name | Type | Description |
|---|---|---|
BuiltQuery |
BuiltQuery
|
The built query with SQL and parameters. |
Raises:
| Type | Description |
|---|---|
SQLBuilderError
|
If the table is not specified. |
Merge
¶
Bases: QueryBuilder, MergeUsingClauseMixin, MergeOnClauseMixin, MergeMatchedClauseMixin, MergeNotMatchedClauseMixin, MergeIntoClauseMixin, MergeNotMatchedBySourceClauseMixin, ExplainMixin
Builder for MERGE statements.
Constructs SQL MERGE statements (also known as UPSERT in some databases) with parameter binding and validation.
Values
¶
Bases: QueryBuilder
Builder for SQL VALUES clauses.
Constructs parameterized VALUES expressions that can be executed directly, used as Common Table Expressions (CTEs), or embedded in FROM clauses.
Attributes:
| Name | Type | Description |
|---|---|---|
alias_name |
str | None
|
Get the table alias name if defined. |
columns |
list[str] | None
|
Get the column names if defined. |
as_(alias)
¶
set_columns(*columns)
¶
Set the column names for this VALUES clause.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
*columns
|
str
|
Column names to assign. |
()
|
Returns:
| Type | Description |
|---|---|
Self
|
Self for method chaining. |
Raises:
| Type | Description |
|---|---|
SQLBuilderError
|
If column count does not match row width. |
add_rows(rows)
¶
Add rows to the VALUES clause.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
rows
|
Sequence[Sequence[Any] | Mapping[str, Any]]
|
Sequence of row tuples, lists, or mappings. |
required |
Returns:
| Type | Description |
|---|---|
Self
|
Self for method chaining. |
Raises:
| Type | Description |
|---|---|
SQLBuilderError
|
If rows is empty, non-uniform, or column count mismatches. |
DML Clause Mixins¶
ReturningClauseMixin
¶
Mixin providing RETURNING clause support for DML builders.
dialect
¶
returning(*columns)
¶
Add RETURNING clause to the DML statement.
Each string is one SQL projection parsed with the builder dialect, so it must be trusted
text, as with Select.select. Other arguments must be SQLGlot nodes, SQLSpec expression
wrappers, SQL objects, or query builders.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
*columns
|
str | Expr | Column | ExpressionWrapper | Case
|
Columns or expressions to return. |
()
|
Returns:
| Type | Description |
|---|---|
Self
|
The builder instance for method chaining. |
Raises:
| Type | Description |
|---|---|
SQLBuilderError
|
If expression not initialized or not DML, if no column is given, if a string is not exactly one projection, or if an argument cannot render as SQL. |
InsertValuesMixin
¶
InsertFromSelectMixin
¶
UpdateTableClauseMixin
¶
UpdateSetClauseMixin
¶
get_expression()
¶
set_expression(expression)
¶
set(*args, **kwargs)
¶
set_from(data, *, exclude_unset=True)
¶
Set columns from a dict, dataclass, msgspec.Struct, Pydantic model, or attrs class.
Schema instances are normalised via :func:sqlspec.utils.serializers.schema_dump
with wire_format=False and dispatched to :meth:set via keyword unpack. The
dict shape uses Python attribute names regardless of msgspec rename= or
Pydantic Field(alias=...).
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
data
|
Any
|
A dict, dataclass instance, |
required |
exclude_unset
|
bool
|
If True, exclude fields that were never set (msgspec UNSET,
Pydantic |
True
|
Returns:
| Type | Description |
|---|---|
Self
|
The current builder instance for method chaining. |
UpdateFromClauseMixin
¶
dialect
¶
get_expression()
¶
set_expression(expression)
¶
from_(table, alias=None)
¶
Add a table or subquery to the UPDATE statement's FROM clause.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
table
|
str | Expr | Any
|
Target table name, expression, or builder instance. |
required |
alias
|
str | None
|
Optional alias for the source table or subquery. |
None
|
Returns:
| Type | Description |
|---|---|
Self
|
The current builder instance for method chaining. |
Raises:
| Type | Description |
|---|---|
SQLBuilderError
|
If called on a non-UPDATE expression or with an unsupported table type. |