Queries¶
DML query builders for SELECT, INSERT, UPDATE, DELETE, and MERGE operations.
Select¶
- class sqlspec.builder.Select[source]¶
Bases:
QueryBuilder,WhereClauseMixin,OrderByClauseMixin,LimitOffsetClauseMixin,SelectClauseMixin,JoinClauseMixin,HavingClauseMixin,SetOperationMixin,CommonTableExpressionMixin,PivotClauseMixin,UnpivotClauseMixin,ExplainMixinBuilder for SELECT queries.
Provides a fluent interface for constructing SQL SELECT statements with parameter binding and validation.
Example
>>> class User(BaseModel): ... id: int ... name: str >>> builder = Select("id", "name").from_("users") >>> result = driver.execute(builder)
- __init__(*columns, **kwargs)[source]¶
Initialize SELECT with optional columns.
- Parameters:
Examples
Select(“id”, “name”) # Shorthand for Select().select(“id”, “name”) Select() # Same as Select() - start empty
- with_hint(hint, *, location='statement', table=None, dialect=None)[source]¶
Attach an optimizer or dialect-specific hint to the query.
- Parameters:
- Return type:
Self- Returns:
The current builder instance for method chaining.
- build(dialect=None)[source]¶
Builds the SQL query string and parameters with hint injection.
- for_update(*, skip_locked=False, nowait=False, of=None)[source]¶
Add FOR UPDATE clause to SELECT statement for row-level locking.
Add FOR SHARE clause for shared row-level locking.
Add FOR KEY SHARE clause (PostgreSQL-specific).
FOR KEY SHARE is like FOR SHARE, but the lock is weaker: SELECT FOR UPDATE is blocked, but not SELECT FOR NO KEY UPDATE.
- Return type:
Self- Returns:
Self for method chaining
Select Clause Mixins¶
- class sqlspec.builder.SelectClauseMixin[source]¶
Bases:
objectMixin providing SELECT clause methods.
Window Functions¶
Case Expressions¶
Subqueries¶
Insert¶
- class sqlspec.builder.Insert[source]¶
Bases:
QueryBuilder,ReturningClauseMixin,InsertValuesMixin,InsertFromSelectMixin,InsertIntoClauseMixin,ExplainMixinBuilder for INSERT statements.
Constructs SQL INSERT queries with parameter binding and validation.
- values_from_dict(data)[source]¶
Adds a row of values from a dictionary.
This is a convenience method that automatically sets columns based on the dictionary keys and values based on the dictionary values.
- Parameters:
data¶ (
Mapping[str, typing.Any]) – A mapping of column names to values.- Return type:
Self- Returns:
The current builder instance for method chaining.
- Raises:
SQLBuilderError – If into() has not been called to set the table.
- values_from_dicts(data)[source]¶
Adds multiple rows of values from a sequence of dictionaries.
This is a convenience method for bulk inserts from structured data.
- Parameters:
data¶ (
Sequence[Mapping[str, typing.Any]]) – A sequence of mappings, each representing a row of data.- Return type:
Self- Returns:
The current builder instance for method chaining.
- Raises:
SQLBuilderError – If into() has not been called to set the table, or if dictionaries have inconsistent keys.
- on_conflict(*columns)[source]¶
Adds an ON CONFLICT clause with specified columns.
- on_conflict_do_nothing(*columns)[source]¶
Adds an ON CONFLICT DO NOTHING clause (convenience method).
- Parameters:
*columns¶ (
str) – Column names that define the conflict. If no columns provided, creates an ON CONFLICT without specific columns.- Return type:
- Returns:
The current builder instance for method chaining.
Note
This is a convenience method. For more control, use on_conflict().do_nothing().
- on_duplicate_key_update(**kwargs)[source]¶
Adds MySQL-style ON DUPLICATE KEY UPDATE clause.
- Parameters:
**kwargs¶ (
Any) – Column-value pairs to update on duplicate key.- Return type:
- Returns:
The current builder instance for method chaining.
Note
This method creates MySQL-specific ON DUPLICATE KEY UPDATE syntax. For PostgreSQL, use on_conflict() instead.
- class sqlspec.builder._insert.ConflictBuilder[source]¶
Bases:
objectBuilder for ON CONFLICT clauses in INSERT statements.
Constructs conflict resolution clauses using PostgreSQL-style syntax, which SQLGlot can transpile to other dialects.
Update¶
- class sqlspec.builder.Update[source]¶
Bases:
QueryBuilder,WhereClauseMixin,ReturningClauseMixin,UpdateSetClauseMixin,UpdateFromClauseMixin,UpdateTableClauseMixin,ExplainMixinBuilder for UPDATE statements.
Constructs SQL UPDATE statements with parameter binding and validation.
Example
Update() .table(“users”) .set_(name=”John Doe”) .set_(email=”john@example.com”) .where(“id = 1”)
)
- update_query = (
Update(“users”).set_(name=”John Doe”).where(“id = 1”)
)
- update_query = (
Update() .table(“users”) .set_(status=”active”) .where_eq(“id”, 123)
)
- update_query = (
Update() .table(“users”, “u”) .set_(name=”Updated Name”) .from_(“profiles”, “p”) .where(“u.id = p.user_id AND p.is_verified = true”)
- join(table, on, alias=None, join_type='INNER')[source]¶
Add JOIN clause to the UPDATE statement.
- Parameters:
- Return type:
Self- Returns:
The current builder instance for method chaining.
- Raises:
SQLBuilderError – If the current expression is not an UPDATE statement.
- build(dialect=None)[source]¶
Build the UPDATE query with validation.
- Parameters:
dialect¶ (
Union[str,Dialect,Type[Dialect],None]) – Optional dialect override for SQL generation.- Returns:
The built query with SQL and parameters.
- Return type:
- Raises:
SQLBuilderError – If no table is set or expression is not an UPDATE.
Delete¶
- class sqlspec.builder.Delete[source]¶
Bases:
QueryBuilder,WhereClauseMixin,ReturningClauseMixin,DeleteFromClauseMixin,ExplainMixinBuilder 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)[source]¶
Build the DELETE query with validation.
- Parameters:
dialect¶ (
Union[str,Dialect,Type[Dialect],None]) – Optional dialect override for SQL generation.- Returns:
The built query with SQL and parameters.
- Return type:
- Raises:
SQLBuilderError – If the table is not specified.
Merge¶
- class sqlspec.builder.Merge[source]¶
Bases:
QueryBuilder,MergeUsingClauseMixin,MergeOnClauseMixin,MergeMatchedClauseMixin,MergeNotMatchedClauseMixin,MergeIntoClauseMixin,MergeNotMatchedBySourceClauseMixin,ExplainMixinBuilder for MERGE statements.
Constructs SQL MERGE statements (also known as UPSERT in some databases) with parameter binding and validation.
Explain¶
- class sqlspec.builder.Explain[source]¶
Bases:
objectBuilder for EXPLAIN statements with dialect-aware rendering.
Provides a fluent API for constructing EXPLAIN statements with various options that are translated to dialect-specific syntax.
Examples
- Basic usage:
explain = Explain(“SELECT * FROM users”).build()
- With options:
- explain = (
Explain(“SELECT * FROM users”, dialect=”postgres”) .analyze() .format(“json”) .buffers() .build()
)
- From QueryBuilder:
- explain = (
Explain(select_builder, dialect=”postgres”) .analyze() .verbose() .build()
)
- settings(enabled=True)[source]¶
Enable SETTINGS option (show configuration parameters, PostgreSQL 12+).
- generic_plan(enabled=True)[source]¶
Enable GENERIC_PLAN option (ignore parameter values, PostgreSQL 16+).
- with_options(options)[source]¶
Replace all options with the provided ExplainOptions.
- Parameters:
options¶ (
ExplainOptions) – New options to use- Return type:
Self- Returns:
Self for method chaining
- property options: ExplainOptions¶
Get current ExplainOptions.
Joins¶
- class sqlspec.builder.JoinBuilder[source]¶
Bases:
objectBuilder for JOIN operations with fluent syntax.
Example
```python from sqlspec import sql
# sql.left_join_(“posts”).on(“users.id = posts.user_id”) join_clause = sql.left_join_(“posts”).on(
“users.id = posts.user_id”
)
# Or with query builder query = (
sql .select(“users.name”, “posts.title”) .from_(“users”) .join(
- sql.left_join_(“posts”).on(
“users.id = posts.user_id”
)
)