Columns & Expressions¶

from sqlspec.builder import (
    AggregateExpression,
    Column,
    ColumnExpression,
    ConversionExpression,
    ExpressionWrapper,
    FunctionColumn,
    FunctionExpression,
    MathExpression,
    StringExpression,
    VectorDistance,
    extract_expression,
    parse_column_expression,
    parse_condition_expression,
    parse_order_expression,
    parse_table_expression,
    to_expression,
)

Typed column objects, aggregate/math/string/conversion expression wrappers, vector distance expressions, and SQLGlot expression parsing helpers.

Column Descriptors¶

Column ¶

Bases: _ScalarExpressionMixin

Represents a database column with Python operator support.

name ¶

name = name

table ¶

table = table

lower ¶

lower()

SQL LOWER() function.

upper ¶

upper()

SQL UPPER() function.

length ¶

length()

SQL LENGTH() function.

trim ¶

trim()

SQL TRIM() function.

abs ¶

abs()

SQL ABS() function.

round ¶

round(decimals=0)

SQL ROUND() function.

floor ¶

floor()

SQL FLOOR() function.

ceil ¶

ceil()

SQL CEIL() function.

substring ¶

substring(start, length=None)

SQL SUBSTRING() function.

coalesce ¶

coalesce(*values)

SQL COALESCE() function.

count ¶

count()

SQL COUNT() function.

sum ¶

sum()

SQL SUM() function.

avg ¶

avg()

SQL AVG() function.

min ¶

min()

SQL MIN() function.

max ¶

max()

SQL MAX() function.

count_distinct ¶

count_distinct()

SQL COUNT(DISTINCT column) function.

count_all ¶

count_all()

SQL COUNT(*) function.

vector_distance ¶

vector_distance(other_vector, metric='euclidean')

Calculate vector distance using specified metric.

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:

Name	Type	Description	Default
`other_vector`	`list[float] \| Column \| Expr`	Vector to compare against (list, Column, or SQLGlot expression).	required
`metric`	`str`	Distance metric to use (`"euclidean"`, `"cosine"`, `"inner_product"`, or `"euclidean_squared"`).	`'euclidean'`

Returns:

Type	Description
`FunctionColumn`	FunctionColumn expression for use in SELECT, WHERE, ORDER BY.

cosine_similarity ¶

cosine_similarity(other_vector)

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:

Name	Type	Description	Default
`other_vector`	`list[float] \| Column \| Expr`	Vector to compare against (list, Column, or expression).	required

Returns:

Type	Description
`FunctionColumn`	FunctionColumn expression: 1 - cosine_distance(self, other_vector).

ColumnExpression ¶

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

sqlglot_expression ¶

sqlglot_expression

Get the underlying SQLGlot expression.

FunctionColumn ¶

Bases: _ScalarExpressionMixin

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

Expression Wrappers & Vector Distance¶

ExpressionWrapper ¶

Base wrapper for SQLGlot expressions.

expression ¶

expression

Get the underlying SQLGlot expression.

as_ ¶

as_(alias)

Create an aliased expression.

AggregateExpression ¶

Bases: ExpressionWrapper

Aggregate functions like COUNT, SUM, AVG.

FunctionExpression ¶

Bases: ExpressionWrapper

General SQL functions.

MathExpression ¶

Bases: ExpressionWrapper

Mathematical functions like ROUND.

StringExpression ¶

Bases: ExpressionWrapper

String functions like UPPER, LOWER, LENGTH.

ConversionExpression ¶

Bases: ExpressionWrapper

Conversion functions like CAST, COALESCE.

VectorDistance ¶

VectorDistance(*, this, expression, metric='euclidean')

Build a SQLSpec vector-distance expression.

Expression Parsing Helpers¶

to_expression ¶

to_expression(value)

Convert a Python value to a raw SQLGlot expression.

Parameters:

Name	Type	Description	Default
`value`	`Any`	Python value or SQLGlot expression to convert.	required

Returns:

Type	Description
`Expr`	Raw SQLGlot expression.

extract_expression ¶

extract_expression(value)

Extract SQLGlot expression from value, handling wrapper types.

Parameters:

Name	Type	Description	Default
`value`	`Any`	String, SQLGlot expression, or wrapper type.	required

Returns:

Type	Description
`Expr`	Raw SQLGlot expression.

parse_column_expression ¶

parse_column_expression(column_input, builder=None)

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:

Name	Type	Description	Default
`column_input`	`str \| Expr \| Any`	String, SQLGlot expression, SQL object, or Column object	required
`builder`	`Any \| None`	Optional builder instance for parameter merging	`None`

Returns:

Type	Description
`Expr`	exp.Expr: Parsed SQLGlot expression

parse_condition_expression ¶

parse_condition_expression(condition_input, builder=None)

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:

Name	Type	Description	Default
`condition_input`	`str \| Expr \| tuple[str, Any]`	String, tuple, or SQLGlot expression for condition	required
`builder`	`Any`	Optional builder instance for parameter binding	`None`

Returns:

Type	Description
`Expr`	exp.Expr: Parsed SQLGlot expression (usually a comparison or logical op)

parse_order_expression ¶

parse_order_expression(order_input)

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:

Name	Type	Description	Default
`order_input`	`str \| Expr`	String or SQLGlot expression for ORDER BY	required

Returns:

Type	Description
`Expr`	exp.Expr: Parsed SQLGlot expression (usually Ordered or Column)

parse_table_expression ¶

parse_table_expression(table_input, explicit_alias=None, dialect=None)

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

The dialect selects the identifier-quoting rules so dialect-quoted identifiers such as BigQuery's `project.dataset.table\ are parsed into their qualified parts instead of a single literal name.