Query Modifiers

Helpers for modifying SQL statements by appending WHERE, LIMIT, OFFSET clauses and building condition expressions programmatically.

ConditionFactory

sqlspec.core.query_modifiers.ConditionFactory

alias of Callable[[Expression, Placeholder], Expression]

Statement Modifiers

sqlspec.core.query_modifiers.apply_where(expression, condition)

Apply WHERE condition to an expression using AND.

Works with SELECT, UPDATE, and DELETE expressions.

Parameters:

• expression (Expression) – Base expression to modify (will be copied)

• condition (Expression) – WHERE condition to add

Return type:

Expression

Returns:

Modified expression with WHERE condition

Raises:

SQLSpecError – If expression type doesn’t support WHERE

sqlspec.core.query_modifiers.apply_or_where(expression, condition)

Apply WHERE condition to an expression using OR.

Combines the new condition with any existing WHERE clause using OR.

Parameters:

• expression (Expression) – Base expression with existing WHERE

• condition (Expression) – New condition to add with OR

Return type:

Expression

Returns:

Modified expression with OR condition

Raises:

SQLSpecError – If expression type doesn’t support WHERE or has no existing WHERE

sqlspec.core.query_modifiers.apply_limit(expression, limit_value)

Apply LIMIT clause to expression.

Parameters:

• expression (Expression) – Base expression (must be SELECT or set operation)

• limit_value (int) – LIMIT value

Return type:

Expression

Returns:

Modified expression with LIMIT

Raises:

SQLSpecError – If expression does not support LIMIT

sqlspec.core.query_modifiers.apply_offset(expression, offset_value)

Apply OFFSET clause to expression.

Parameters:

• expression (Expression) – Base expression (must be SELECT or set operation)

• offset_value (int) – OFFSET value

Return type:

Expression

Returns:

Modified expression with OFFSET

Raises:

SQLSpecError – If expression does not support OFFSET

sqlspec.core.query_modifiers.apply_select_only(expression, columns)

Replace SELECT clause with only specified columns.

Parameters:

• expression (Expression) – Base expression (must be SELECT)

• columns (tuple[str | Expression, ...]) – Column names or expressions to select

Return type:

Expression

Returns:

Modified expression with new SELECT columns

Raises:

SQLSpecError – If expression is not SELECT

sqlspec.core.query_modifiers.safe_modify_with_cte(expression, modification_fn)

Safely apply a modification, preserving CTEs at top level.

This ensures CTEs stay at the outermost level even when the modification would normally wrap them in a subquery. This fixes issue #301 where CTEs inside subqueries generate invalid SQL.

Parameters:

• expression (Expression) – Expression that may contain CTEs

• modification_fn (Callable[[Expression], Expression]) – Function to apply to the expression

Return type:

Expression

Returns:

Modified expression with CTE preserved at top level

Condition Builders

sqlspec.core.query_modifiers.create_condition(column, param_name, condition_factory)

Create parameterized condition expression.

This is a pure function - parameter value binding happens in the caller.

Parameters:

• column (str | Column | Expression) – Column name or expression

• param_name (str) – Pre-generated unique parameter name

• condition_factory (Callable[[Expression, Placeholder], Expression]) – Factory function for the condition type

Return type:

Expression

Returns:

Condition expression with placeholder

sqlspec.core.query_modifiers.create_between_condition(column, low_param, high_param)

Create BETWEEN condition.

Parameters:

• column (str | Column | Expression) – Column name or expression

• low_param (str) – Parameter name for low bound

• high_param (str) – Parameter name for high bound

Return type:

Expression

Returns:

BETWEEN expression with placeholders

sqlspec.core.query_modifiers.create_in_condition(column, param_names)

Create IN condition with multiple placeholders.

Parameters:

• column (str | Column | Expression) – Column name or expression

• param_names (list[str]) – Pre-generated parameter names (one per value)

Return type:

Expression

Returns:

IN expression with placeholders

sqlspec.core.query_modifiers.create_not_in_condition(column, param_names)

Create NOT IN condition with multiple placeholders.

Parameters:

• column (str | Column | Expression) – Column name or expression

• param_names (list[str]) – Pre-generated parameter names (one per value)

Return type:

Expression

Returns:

NOT IN expression with placeholders

sqlspec.core.query_modifiers.create_exists_condition(subquery)

Create EXISTS condition.

Parameters:

subquery (Expression) – Subquery expression

Return type:

Expression

Returns:

EXISTS expression

sqlspec.core.query_modifiers.create_not_exists_condition(subquery)

Create NOT EXISTS condition.

Parameters:

subquery (Expression) – Subquery expression

Return type:

Expression

Returns:

NOT EXISTS expression

Expression Helpers

sqlspec.core.query_modifiers.expr_eq(col, placeholder)

Create equality expression: column = :param.

Return type:

Expression

sqlspec.core.query_modifiers.expr_neq(col, placeholder)

Create not-equal expression: column != :param.

Return type:

Expression

sqlspec.core.query_modifiers.expr_gt(col, placeholder)

Create greater-than expression: column > :param.

Return type:

Expression

sqlspec.core.query_modifiers.expr_gte(col, placeholder)

Create greater-than-or-equal expression: column >= :param.

Return type:

Expression

sqlspec.core.query_modifiers.expr_lt(col, placeholder)

Create less-than expression: column < :param.

Return type:

Expression

sqlspec.core.query_modifiers.expr_lte(col, placeholder)

Create less-than-or-equal expression: column <= :param.

Return type:

Expression

sqlspec.core.query_modifiers.expr_like(col, placeholder)

Create LIKE expression: column LIKE :param.

Return type:

Expression

sqlspec.core.query_modifiers.expr_not_like(col, placeholder)

Create NOT LIKE expression: NOT (column LIKE :param).

Return type:

Expression

sqlspec.core.query_modifiers.expr_ilike(col, placeholder)

Create case-insensitive LIKE expression: column ILIKE :param.

Return type:

Expression

sqlspec.core.query_modifiers.expr_is_null(col, _placeholder)

Create IS NULL expression: column IS NULL.

Note: placeholder is ignored but kept for consistent factory signature.

Return type:

Expression

sqlspec.core.query_modifiers.expr_is_not_null(col, _placeholder)

Create IS NOT NULL expression: column IS NOT NULL.

Note: placeholder is ignored but kept for consistent factory signature.

Return type:

Expression

Utilities

sqlspec.core.query_modifiers.extract_column_name(column)

Extract column name from column expression for parameter naming.

Parameters:

column (str | Column | Expression) – Column expression (string or SQLGlot Column)

Return type:

str

Returns:

Column name as string for use as parameter name base

sqlspec.core.query_modifiers.parse_column_for_condition(column)

Parse column specification for use in conditions.

Handles various input formats:

• “column_name” -> exp.Column

• “table.column” -> exp.Column with table

• exp.Column -> returned as-is

• Other exp.Expression -> returned as-is

Parameters:

column (str | Column | Expression) – Column specification

Return type:

Expression

Returns:

SQLGlot column expression