Filters

Composable SQL statement filters for building WHERE clauses, pagination, ordering, and search conditions. Filters can be applied to any SQL statement via apply_filter().

apply_filter

sqlspec.core.filters.apply_filter(statement, filter_obj)

Apply a statement filter to a SQL query object.

Parameters:

• statement (SQL) -- The SQL query object to modify.

• filter_obj (StatementFilter) -- The filter to apply.

Return type:

SQL

Returns:

The modified query object.

Base

class sqlspec.core.filters.StatementFilter

Bases: ABC

Abstract base class for filters that can be appended to a statement.

abstractmethod append_to_statement(statement)

Append the filter to the statement.

This method modifies the SQL expression and adds parameters via add_named_parameter() on the returned statement.

Return type:

SQL

extract_parameters()

Return the parameters this filter would contribute.

This is an introspection helper and is not called during the normal filter-application path (append_to_statement handles both expression modification and parameter addition).

Returns:

• positional_parameters: List of positional parameter values

• named_parameters: Dict of parameter name to value

Return type:

tuple[list[typing.Any], dict[str, typing.Any]]

abstractmethod get_cache_key()

Return a cache key for this filter's configuration.

Return type:

tuple[typing.Any, ...]

Returns:

Tuple of hashable values representing the filter's configuration

Date Filters

class sqlspec.core.filters.BeforeAfterFilter

Bases: StatementFilter

Filter for datetime range queries.

Applies WHERE clauses for before/after datetime filtering.

__init__(field_name, before=None, after=None)

get_param_names()

Get parameter names without storing them.

Return type:

list[str]

extract_parameters()

Extract filter parameters.

Return type:

tuple[list[typing.Any], dict[str, typing.Any]]

append_to_statement(statement)

Apply filter to SQL expression only.

Return type:

SQL

get_cache_key()

Return cache key for this filter configuration.

Return type:

tuple[typing.Any, ...]

class sqlspec.core.filters.OnBeforeAfterFilter

Bases: StatementFilter

Filter for inclusive datetime range queries.

Applies WHERE clauses for on-or-before/on-or-after datetime filtering.

__init__(field_name, on_or_before=None, on_or_after=None)

get_param_names()

Get parameter names without storing them.

Return type:

list[str]

extract_parameters()

Extract filter parameters.

Return type:

tuple[list[typing.Any], dict[str, typing.Any]]

append_to_statement(statement)

Append the filter to the statement.

This method modifies the SQL expression and adds parameters via add_named_parameter() on the returned statement.

Return type:

SQL

get_cache_key()

Return cache key for this filter configuration.

Return type:

tuple[typing.Any, ...]

Collection Filters

class sqlspec.core.filters.InCollectionFilter

Bases: InAnyFilter[T]

Filter for IN clause queries.

Constructs WHERE ... IN (...) clauses.

__init__(field_name, values=None)

get_param_names()

Get parameter names without storing them.

Return type:

list[str]

extract_parameters()

Extract filter parameters.

Return type:

tuple[list[typing.Any], dict[str, typing.Any]]

append_to_statement(statement)

Append the filter to the statement.

This method modifies the SQL expression and adds parameters via add_named_parameter() on the returned statement.

Return type:

SQL

get_cache_key()

Return cache key for this filter configuration.

Return type:

tuple[typing.Any, ...]

class sqlspec.core.filters.NotInCollectionFilter

Bases: InAnyFilter[T]

Filter for NOT IN clause queries.

Constructs WHERE ... NOT IN (...) clauses.

__init__(field_name, values=None)

get_param_names()

Get parameter names without storing them.

Return type:

list[str]

extract_parameters()

Extract filter parameters.

Return type:

tuple[list[typing.Any], dict[str, typing.Any]]

append_to_statement(statement)

Append the filter to the statement.

This method modifies the SQL expression and adds parameters via add_named_parameter() on the returned statement.

Return type:

SQL

get_cache_key()

Return cache key for this filter configuration.

Return type:

tuple[typing.Any, ...]

class sqlspec.core.filters.AnyCollectionFilter

Bases: InAnyFilter[T]

Filter for PostgreSQL-style ANY clause queries.

Constructs WHERE column_name = ANY (array_expression) clauses.

__init__(field_name, values=None)

get_param_names()

Get parameter names without storing them.

Return type:

list[str]

extract_parameters()

Extract filter parameters.

Return type:

tuple[list[typing.Any], dict[str, typing.Any]]

append_to_statement(statement)

Append the filter to the statement.

This method modifies the SQL expression and adds parameters via add_named_parameter() on the returned statement.

Return type:

SQL

get_cache_key()

Return cache key for this filter configuration.

Return type:

tuple[typing.Any, ...]

class sqlspec.core.filters.NotAnyCollectionFilter

Bases: InAnyFilter[T]

Filter for PostgreSQL-style NOT ANY clause queries.

Constructs WHERE NOT (column_name = ANY (array_expression)) clauses.

__init__(field_name, values=None)

get_param_names()

Get parameter names without storing them.

Return type:

list[str]

extract_parameters()

Extract filter parameters.

Return type:

tuple[list[typing.Any], dict[str, typing.Any]]

append_to_statement(statement)

Append the filter to the statement.

This method modifies the SQL expression and adds parameters via add_named_parameter() on the returned statement.

Return type:

SQL

get_cache_key()

Return cache key for this filter configuration.

Return type:

tuple[typing.Any, ...]

Null Filters

class sqlspec.core.filters.NullFilter

Bases: StatementFilter

Filter for IS NULL queries.

Constructs WHERE field_name IS NULL clauses.

__init__(field_name)

extract_parameters()

Extract filter parameters.

Returns empty parameters since IS NULL requires no values.

Return type:

tuple[list[typing.Any], dict[str, typing.Any]]

append_to_statement(statement)

Apply IS NULL filter to SQL expression.

Return type:

SQL

get_cache_key()

Return cache key for this filter configuration.

Return type:

tuple[typing.Any, ...]

class sqlspec.core.filters.NotNullFilter

Bases: StatementFilter

Filter for IS NOT NULL queries.

Constructs WHERE field_name IS NOT NULL clauses.

__init__(field_name)

extract_parameters()

Extract filter parameters.

Returns empty parameters since IS NOT NULL requires no values.

Return type:

tuple[list[typing.Any], dict[str, typing.Any]]

append_to_statement(statement)

Apply IS NOT NULL filter to SQL expression.

Return type:

SQL

get_cache_key()

Return cache key for this filter configuration.

Return type:

tuple[typing.Any, ...]

Pagination

class sqlspec.core.filters.LimitOffsetFilter

Bases: PaginationFilter

Filter for LIMIT and OFFSET clauses.

Adds pagination support through LIMIT/OFFSET SQL clauses.

__init__(limit, offset)

get_param_names()

Get parameter names without storing them.

Return type:

list[str]

extract_parameters()

Extract filter parameters.

Return type:

tuple[list[typing.Any], dict[str, typing.Any]]

append_to_statement(statement)

Append the filter to the statement.

This method modifies the SQL expression and adds parameters via add_named_parameter() on the returned statement.

Return type:

SQL

get_cache_key()

Return cache key for this filter configuration.

Return type:

tuple[typing.Any, ...]

class sqlspec.core.filters.OffsetPagination

Bases: Generic[T]

Container for data returned using limit/offset pagination.

Parameters:

• items (Sequence[TypeVar(T)]) -- List of data being sent as part of the response.

• limit (int) -- Maximal number of items to send.

• offset (int) -- Offset from the beginning of the query. Identical to an index.

• total (int) -- Total number of items.

__init__(items, limit, offset, total)

Ordering

class sqlspec.core.filters.OrderByFilter

Bases: StatementFilter

Filter for ORDER BY clauses.

Adds sorting capability to SQL queries.

__init__(field_name, sort_order='asc')

extract_parameters()

Extract filter parameters.

Return type:

tuple[list[typing.Any], dict[str, typing.Any]]

append_to_statement(statement)

Append the filter to the statement.

This method modifies the SQL expression and adds parameters via add_named_parameter() on the returned statement.

Return type:

SQL

get_cache_key()

Return cache key for this filter configuration.

Return type:

tuple[typing.Any, ...]

Search

class sqlspec.core.filters.SearchFilter

Bases: StatementFilter

Filter for text search queries.

Constructs WHERE field_name LIKE '%value%' clauses.

__init__(field_name, value, ignore_case=False)

property like_pattern: str | None

Return the search value wrapped in LIKE wildcards.

Returns:

The pattern for a LIKE operation, or None if no value.

static escape_like_value(value)

Escape % and _ metacharacters for safe LIKE/ILIKE pattern matching.

Backslash is the default LIKE escape character in PostgreSQL (with standard_conforming_strings on), SQLite, and MySQL, so the produced pattern is safe without an explicit ESCAPE clause on those engines.

Parameters:

value (str) -- Raw user input to escape before wrapping in wildcards.

Return type:

str

Returns:

The input with \, %, and _ escaped using \.

get_param_name()

Get parameter name without storing it.

Return type:

str | None

extract_parameters()

Extract filter parameters.

Return type:

tuple[list[typing.Any], dict[str, typing.Any]]

append_to_statement(statement)

Append the filter to the statement.

This method modifies the SQL expression and adds parameters via add_named_parameter() on the returned statement.

Return type:

SQL

get_cache_key()

Return cache key for this filter configuration.

Return type:

tuple[typing.Any, ...]

class sqlspec.core.filters.NotInSearchFilter

Bases: SearchFilter

Filter for negated text search queries.

Constructs WHERE field_name NOT LIKE '%value%' clauses.

get_param_name()

Get parameter name without storing it.

Return type:

str | None

extract_parameters()

Extract filter parameters.

Return type:

tuple[list[typing.Any], dict[str, typing.Any]]

append_to_statement(statement)

Append the filter to the statement.

This method modifies the SQL expression and adds parameters via add_named_parameter() on the returned statement.

Return type:

SQL

get_cache_key()

Return cache key for this filter configuration.

Return type:

tuple[typing.Any, ...]

Type Aliases

sqlspec.core.filters.FilterTypes

Union type of all filter classes.