Generates dialect-specific SQL for vector distance calculation: - PostgreSQL (pgvector): Operators <->, <=>, <#> - MySQL 9+: DISTANCE(col, vec, ‘METRIC’) function - Oracle 23ai+: VECTOR_DISTANCE(col, vec, METRIC) function

Parameters:

other_vector¶ (list[float] | Column | Expression) – Vector to compare against (list, Column, or SQLGlot expression).
metric¶ (str) – Distance metric to use. Options: - “euclidean”: L2 distance (default) - “cosine”: Cosine distance - “inner_product”: Negative inner product - “euclidean_squared”: L2² distance (Oracle only)

Return type:

FunctionColumn

Returns:

FunctionColumn expression for use in SELECT, WHERE, ORDER BY.

Examples

Basic distance query with threshold:

>>> query = (
...     sql
...     .select("*")
...     .from_("docs")
...     .where(
...         Column("embedding").vector_distance(
...             [0.1, 0.2], metric="euclidean"
...         )
...         < 0.5
...     )
... )

Distance in SELECT clause with alias:

>>> query = (
...     sql
...     .select(
...         "id",
...         Column("embedding")
...         .vector_distance([0.1, 0.2])
...         .as_("dist"),
...     )
...     .from_("docs")
...     .order_by("dist")
... )

Compare two vector columns:

>>> query = (
...     sql
...     .select("*")
...     .from_("pairs")
...     .where(
...         Column("vec1").vector_distance(
...             Column("vec2"), metric="cosine"
...         )
...         < 0.3
...     )
... )

cosine_similarity(other_vector)[source]¶

Calculate cosine similarity (1 - cosine_distance).

Convenience method that computes similarity instead of distance. Returns values in range [-1, 1] where 1 = identical vectors.

Parameters:: other_vector¶ (list[float] | Column | Expression) – Vector to compare against (list, Column, or expression).
Returns:: 1 - cosine_distance(self, other_vector).
Return type:: FunctionColumn expression

Examples

Find most similar documents:

>>> query = (
...     sql
...     .select(
...         "id",
...         Column("embedding")
...         .cosine_similarity([0.1, 0.2])
...         .as_("score"),
...     )
...     .from_("docs")
...     .order_by(sql.column("score").desc())
...     .limit(10)
... )

alias(alias_name)[source]¶

Create an aliased column expression.

Return type:: Expression

asc()[source]¶

Create an ASC ordering expression.

Return type:: Ordered

desc()[source]¶

Create a DESC ordering expression.

Return type:: Ordered

as_(alias)[source]¶

Create an aliased expression.

Return type:: Alias

__hash__()[source]¶

Hash based on table and column name.

Return type:: int

property sqlglot_expression: Expression¶

Get the underlying SQLGlot expression (public API).

Returns:: The SQLGlot expression for this column

class sqlspec.builder.ColumnExpression[source]¶

Bases: object

Base class for column expressions that can be combined with operators.

__init__(expression)[source]¶

__and__(other)[source]¶

Combine with AND operator (&).

Return type:: ColumnExpression

__or__(other)[source]¶

Combine with OR operator (|).

Return type:: ColumnExpression

__invert__()[source]¶

Apply NOT operator (~).

Return type:: ColumnExpression

__bool__()[source]¶

Prevent accidental use of ‘and’/’or’ keywords.

Return type:: bool

property sqlglot_expression: Expression¶: Get the underlying SQLGlot expression.

class sqlspec.builder.FunctionColumn[source]¶

Bases: object

Represents the result of a SQL function call on a column.

__init__(expression)[source]¶

property sqlglot_expression: Expression¶: Return underlying SQLGlot expression.

__gt__(other)[source]¶

Greater than (>).

Return type:: ColumnExpression

__ge__(other)[source]¶

Greater than or equal (>=).

Return type:: ColumnExpression

__lt__(other)[source]¶

Less than (<).

Return type:: ColumnExpression

__le__(other)[source]¶

Less than or equal (<=).

Return type:: ColumnExpression

ilike(pattern)[source]¶

Case-insensitive LIKE.

Return type:: ColumnExpression

in_(values)[source]¶

SQL IN clause.

Return type:: ColumnExpression

not_in_(values)[source]¶

SQL NOT IN clause.

Return type:: ColumnExpression

not_like(pattern)[source]¶

SQL NOT LIKE.

Return type:: ColumnExpression

not_ilike(pattern)[source]¶

Case-insensitive NOT LIKE.

Return type:: ColumnExpression

between(start, end)[source]¶

SQL BETWEEN clause.

Return type:: ColumnExpression

is_null()[source]¶

SQL IS NULL.

Return type:: ColumnExpression

is_not_null()[source]¶

SQL IS NOT NULL.

Return type:: ColumnExpression

any_(values)[source]¶

SQL = ANY(…) clause.

Return type:: ColumnExpression

not_any_(values)[source]¶

SQL <> ANY(…) clause.

Return type:: ColumnExpression

alias(alias_name)[source]¶

Create an aliased function expression.

Return type:: Expression

as_(alias)[source]¶

Create an aliased expression using sqlglot helper.

Return type:: Alias

cast(data_type)[source]¶

SQL CAST() function.

Return type:: FunctionColumn

asc()[source]¶

Create an ASC ordering expression.

Return type:: Ordered

desc()[source]¶

Create a DESC ordering expression.

Return type:: Ordered

__hash__()[source]¶

Hash based on the expression identity.

Return type:: int

Expression Wrappers¶

class sqlspec.builder._expression_wrappers.ExpressionWrapper[source]¶

Bases: object

Base wrapper for SQLGlot expressions.

__init__(expression)[source]¶

as_(alias)[source]¶

Create an aliased expression.

Return type:: Alias

property expression: Expression¶: Get the underlying SQLGlot expression.

class sqlspec.builder.AggregateExpression[source]¶

Bases: ExpressionWrapper

Aggregate functions like COUNT, SUM, AVG.

class sqlspec.builder.FunctionExpression[source]¶

Bases: ExpressionWrapper

General SQL functions.

class sqlspec.builder.MathExpression[source]¶

Bases: ExpressionWrapper

Mathematical functions like ROUND.

class sqlspec.builder.StringExpression[source]¶

Bases: ExpressionWrapper

String functions like UPPER, LOWER, LENGTH.

class sqlspec.builder.ConversionExpression[source]¶

Bases: ExpressionWrapper

Conversion functions like CAST, COALESCE.

Vector Expressions¶

class sqlspec.builder.VectorDistance[source]¶

Bases: Expression

Vector distance expression with dialect-specific generation.

Generates database-specific SQL for vector distance calculations: - PostgreSQL (pgvector): Operators <->, <=>, <#> - MySQL 9+: DISTANCE(col, vec, ‘METRIC’) function - Oracle 23ai+: VECTOR_DISTANCE(col, vec, METRIC) function - Generic: VECTOR_DISTANCE(col, vec, ‘METRIC’) function

The metric is stored as a raw string attribute (not parametrized) and drives dialect-specific generation at SQL build time.

__init__(**args)[source]¶: Initialize VectorDistance with metric stored in args.

property left: Expression¶: Get the left operand (column).

property right: Expression¶: Get the right operand (vector value).

property metric: str¶: Get the distance metric as raw string (not parametrized).

sql(dialect=None, **opts)[source]¶

Generate dialect-specific SQL.

This overrides the default sql() method to provide custom dialect-specific generation for vector distance operations.

Parameters:

dialect¶ (Optional[typing.Any]) – Target SQL dialect (postgres, mysql, oracle, bigquery, duckdb, etc.)
**opts¶ (Any) – Additional SQL generation options

Return type:

str

Returns:

Dialect-specific SQL string

Base Classes¶

class sqlspec.builder.QueryBuilder[source]¶

Bases: ABC

Abstract base class for SQL query builders.

Provides common functionality for dialect handling, parameter management, and query construction using SQLGlot.

__init__(dialect=None, schema=None, enable_optimization=True, optimize_joins=True, optimize_predicates=True, simplify_expressions=True)[source]¶

get_expression()[source]¶

Get expression reference (no copy).

Return type:: Expression | None
Returns:: The current SQLGlot expression or None if not set

set_expression(expression)[source]¶

Set expression with validation.

Parameters:: expression¶ (Expression) – SQLGlot expression to set
Return type:: None

has_expression()[source]¶

Check if expression exists.

Return type:: bool
Returns:: True if expression is set, False otherwise

add_parameter_for_expression(value, context=None)[source]¶

Add a parameter for expression parameterization.

Parameters:

value¶ (Any) – The value of the parameter.
context¶ (str | None) – Optional context hint for parameter naming.

Return type:

str

Returns:

Parameter placeholder name.

add_parameter(value, name=None)[source]¶

Explicitly adds a parameter to the query.

This is useful for parameters that are not directly tied to a builder method like where or values.

Parameters:

value¶ (Any) – The value of the parameter.
name¶ (str | None) – Optional explicit name for the parameter. If None, a name will be generated.

Returns:

The builder instance and the parameter name.

Return type:

tuple[Self, str]

load_parameters(parameters)[source]¶

Load a parameter mapping into the builder.

Parameters:: parameters¶ (Mapping[str, typing.Any]) – Mapping of parameter names to values.
Return type:: None

load_ctes(ctes)[source]¶

Load SQLGlot CTE nodes into the builder.

Parameters:: ctes¶ (Iterable[CTE]) – Iterable of CTE expressions to register.
Return type:: None

create_placeholder(value, base_name)[source]¶

Create placeholder expression with a unique parameter name.

Parameters:

value¶ (Any) – Parameter value to bind.
base_name¶ (str) – Seed for parameter naming.

Return type:

tuple[Placeholder, str]

Returns:

Tuple of placeholder expression and the final parameter name.

with_cte(alias, query)[source]¶

Adds a Common Table Expression (CTE) to the query.

Parameters:

alias¶ (str) – The alias for the CTE.
query¶ (QueryBuilder | Select | str) – The CTE query, which can be another QueryBuilder instance, a raw SQL string, or a sqlglot Select expression.

Returns:

The current builder instance for method chaining.

Return type:

Self

build(dialect=None)[source]¶

Builds the SQL query string and parameters.

Parameters:: dialect¶ (Union[str, Dialect, Type[Dialect], None]) – Optional dialect override. If provided, generates SQL for this dialect instead of the builder’s default dialect.
Returns:: A dataclass containing the SQL string and parameters.
Return type:: BuiltQuery

Examples

# Use builder’s default dialect query = sql.select(“*”).from_(“products”) result = query.build()

# Override dialect at build time postgres_sql = query.build(dialect=”postgres”) mysql_sql = query.build(dialect=”mysql”)

to_sql(show_parameters=False, dialect=None)[source]¶

Return SQL string with optional parameter substitution.

Parameters:

show_parameters¶ (bool) – If True, replace parameter placeholders with actual values (for debugging). If False (default), return SQL with parameter placeholders.
dialect¶ (Union[str, Dialect, Type[Dialect], None]) – Optional dialect override. If provided, generates SQL for this dialect instead of the builder’s default dialect.

Return type:

str

Returns:

SQL string with or without parameter values filled in

Examples

Get SQL with placeholders (for execution):: sql_str = query.to_sql() # “SELECT * FROM products WHERE id = :id”
Get SQL with values (for debugging):: sql_str = query.to_sql(show_parameters=True) # “SELECT * FROM products WHERE id = 123”
Override dialect at output time:: postgres_sql = query.to_sql(dialect=”postgres”) mysql_sql = query.to_sql(dialect=”mysql”)

Warning

SQL with show_parameters=True is for debugging ONLY. Never execute SQL with interpolated parameters directly - use parameterized queries.

to_statement(config=None)[source]¶

Converts the built query into a SQL statement object.

Parameters:: config¶ (StatementConfig | None) – Optional SQL configuration.
Returns:: A SQL statement object.
Return type:: SQL

__str__()[source]¶

Return the SQL string representation of the query.

Returns:: The SQL string for this query.
Return type:: str

property dialect_name: str | None¶: Returns the name of the dialect, if set.

property parameters: dict[str, Any]¶: Public access to query parameters.

set_parameters(parameters)[source]¶

Set query parameters (public API).

Return type:: None

property with_ctes: dict[str, CTE]¶: Get WITH clause CTEs (public API).

generate_unique_parameter_name(base_name)[source]¶

Generate unique parameter name (public API).

Return type:: str

build_static_expression(expression=None, parameters=None, *, cache_key=None, expression_factory=None, copy=True, optimize_expression=None, dialect=None)[source]¶

Compile a pre-built expression with optional caching and parameters.

Designed for hot paths that construct an AST once and reuse it with different parameters, avoiding repeated parse/optimize cycles.

Parameters:

expression¶ (Expression | None) – Pre-built sqlglot expression to render (required when cache_key is not provided).
parameters¶ (dict[str, Any] | None) – Optional parameter mapping to include in the result.
cache_key¶ (str | None) – When provided, the expression will be cached under this key.
expression_factory¶ (Callable[[], Expression] | None) – Factory used to build the expression on cache miss.
copy¶ (bool) – Copy the expression before rendering to avoid caller mutation.
optimize_expression¶ (bool | None) – Override builder optimization toggle for this call.
dialect¶ (Union[str, Dialect, Type[Dialect], None]) – Optional dialect override for SQL generation.

Return type:

BuiltQuery

Returns:

BuiltQuery containing SQL and parameters.

class sqlspec.builder.ExpressionBuilder[source]¶

Bases: QueryBuilder

Builder wrapper for a pre-parsed SQLGlot expression.

__init__(expression, **kwargs)[source]¶

class sqlspec.builder.BuiltQuery[source]¶

Bases: object

SQL query with bound parameters.

__init__(sql, parameters=None, dialect=None)[source]¶

Parsing Utilities¶

sqlspec.builder.extract_expression(value)[source]¶

Extract SQLGlot expression from value, handling wrapper types.

Parameters:: value¶ (Any) – String, SQLGlot expression, or wrapper type.
Return type:: Expression
Returns:: Raw SQLGlot expression.

sqlspec.builder.parse_column_expression(column_input, builder=None)[source]¶

Parse a column input that might be a complex expression.

Handles cases like: - Simple column names: “name” -> Column(this=name) - Qualified names: “users.name” -> Column(table=users, this=name) - Aliased columns: “name AS user_name” -> Alias(this=Column(name), alias=user_name) - Function calls: “MAX(price)” -> Max(this=Column(price)) - Complex expressions: “CASE WHEN … END” -> Case(…) - Custom Column objects from our builder - SQL objects with raw SQL expressions

Parameters:

column_input¶ (str | Expression | Any) – String, SQLGlot expression, SQL object, or Column object
builder¶ (Any | None) – Optional builder instance for parameter merging

Returns:

Parsed SQLGlot expression

Return type:

exp.Expression

sqlspec.builder.parse_condition_expression(condition_input, builder=None)[source]¶

Parse a condition that might be complex SQL.

Handles cases like: - Simple conditions: “name = ‘John’” -> EQ(Column(name), Literal(‘John’)) - Tuple format: (“name”, “John”) -> EQ(Column(name), Literal(‘John’)) - Complex conditions: “age > 18 AND status = ‘active’” -> And(GT(…), EQ(…)) - Function conditions: “LENGTH(name) > 5” -> GT(Length(Column(name)), Literal(5))

Parameters:

condition_input¶ (str | Expression | tuple[str, Any]) – String, tuple, or SQLGlot expression for condition
builder¶ (typing.Any) – Optional builder instance for parameter binding

Returns:

Parsed SQLGlot expression (usually a comparison or logical op)

Return type:

exp.Expression

Notes

Database-specific parameter placeholders such as $1, %s, and :1 are rewritten to :param_N format so SQLGlot can parse them consistently.

sqlspec.builder.parse_order_expression(order_input)[source]¶

Parse an ORDER BY expression that might include direction.

Handles cases like: - Simple column: “name” -> Column(this=name) - With direction: “name DESC” -> Ordered(this=Column(name), desc=True) - Qualified: “users.name ASC” -> Ordered(this=Column(table=users, this=name), desc=False) - Function: “COUNT(*) DESC” -> Ordered(this=Count(this=Star), desc=True)

Parameters:: order_input¶ (str | Expression) – String or SQLGlot expression for ORDER BY
Returns:: Parsed SQLGlot expression (usually Ordered or Column)
Return type:: exp.Expression

sqlspec.builder.parse_table_expression(table_input, explicit_alias=None)[source]¶

Parses a table string that can be a name, a name with an alias, or a subquery string.

Return type:: Expression

sqlspec.builder.to_expression(value)[source]¶

Convert a Python value to a raw SQLGlot expression.

Parameters:: value¶ (Any) – Python value or SQLGlot expression to convert.
Return type:: Expression
Returns:: Raw SQLGlot expression.