Skip to main content

Team Hermes SQLAlchemy Faceted Search

SQLAlchemy 2.x faceting helpers for Python 3.12+, using shared filtering and query contracts. No repository or FastAPI dependency, database driver, engine creation, session management, or transaction handling is included.

For local development, follow the workspace setup. Install a database driver separately when using a database other than SQLite.

Filtering and Faceting

>>> from sqlalchemy import create_engine, select
>>> from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column
>>> from team_hermes.odata_query import FilterExpression, FilterOperator, LogicalFilter, LogicalOperator
>>> from team_hermes.sqlalchemy_core import apply_filters
>>> from team_hermes.sqlalchemy_faceted_search import FacetConfig, FacetCountsResponse, serialize_value
>>> class Base(DeclarativeBase):
...     pass
>>> class Item(Base):
...     __tablename__ = "items"
...     id: Mapped[int] = mapped_column(primary_key=True)
...     name: Mapped[str]
...     category: Mapped[str]
...     price: Mapped[int]
>>> filters = LogicalFilter(
...     operator=LogicalOperator.and_,
...     filters=[
...         FilterExpression(key="category", operator=FilterOperator.eq, value="tools"),
...         FilterExpression(key="price", operator=FilterOperator.gt, value=10),
...     ],
... )
>>> engine = create_engine("sqlite://")
>>> Base.metadata.create_all(engine)
>>> with Session(engine) as session:
...     session.add_all([
...         Item(name="Wrench", category="tools", price=25),
...         Item(name="Screw", category="tools", price=5),
...         Item(name="Manual", category="books", price=20),
...     ])
...     session.flush()
...     query = apply_filters(Item, select(Item), filters)
...     names = [item.name for item in session.scalars(query)]
...     facet_query = FacetConfig().resolve_query("category", Item, filters)
...     counts = {serialize_value(value): count for value, count in session.execute(facet_query)}
...     response = FacetCountsResponse(facets={"category": counts})
>>> names
['Wrench']
>>> response.facets == {"category": {"books": 1, "tools": 1}}
True
>>> engine.dispose()

apply_filters(entity, query, filters) adds a WHERE clause to an existing SQLAlchemy Select. Passing None returns the query unchanged. Existing WHERE clauses, ordering, and pagination remain in place. The helper does not execute the query; applications can execute the statements with their own synchronous or asynchronous SQLAlchemy sessions.

Filters support comparisons, null checks, string matching, comma-separated lists, and nested AND/OR groups. Dotted relationship paths such as children.name use correlated EXISTS expressions through SQLAlchemy's any() or has(), including multiple relationship levels. Missing attributes raise team_hermes.core.ValidationException.

Facet Configuration

FacetConfig.resolve_query(facet_id, entity, filters) returns a grouped count statement. It removes filters on the current facet before applying the remaining filters. This allows other values of the selected facet to remain visible.

  • cross_filter_exclusions: additional filter keys to remove when counting.
  • column_override: SQLAlchemy expression to group by instead of resolving the facet ID.
  • scope: fixed WHERE condition applied to the default grouped query.
  • build_query: callable accepting the entity and returning a custom Select, for joins or distinct counts.

A custom build_query replaces the default query, including column_override and scope. It must add its own joins, grouping, and mandatory scope conditions; remaining cross-filters are still applied afterward. Default counts are row counts, not distinct entity counts. Queries return the value and count columns without an implicit sort or limit.

serialize_value converts None to "null", booleans to lowercase strings, and other values using str. FacetCountsResponse contains facets: dict[str, dict[str, int]]. Values with identical serialized strings (for example, None and the string "null") need application-specific handling if they must remain distinct.

Compatibility and Boundaries

The extracted implementations retain their existing behavior:

  • LIKE variants surround the input with %, translate * to % and ? to _, and double _. They do not escape SQL wildcard characters for literal matching.
  • List operators split on commas without trimming whitespace or coercing values.
  • Each relationship filter uses its own EXISTS; separate conditions may match different related rows. Relationship-only paths are not scalar filter targets.
  • Facet pruning removes matching keys from both AND and OR groups, collapses single-child groups, and does not mutate the original filter tree.
  • Facet column resolution uses attribute traversal, not the relationship-aware filter resolver. Relationship facets generally need a custom query and joins.

Applications must validate allowed fields and enforce authorization. Facet filter removal is not suitable for mandatory security or tenant restrictions: use scope for the default query, or enforce them inside build_query. SQLAlchemy binds filter values, but these helpers do not impose an attribute allow-list or limit filter depth or query cost.

Ordering, selection, pagination, and database configuration stay with the application. The independent Repository package provides CRUD operations; this package only builds facet-count queries and response models, not a complete search-results workflow. FastAPI Query can parse HTTP filters into the same shared contracts without being a dependency of this package.

Development

From the repository root:

mise run python:test -- sqlalchemy-faceted-search/tests
mise run python:test:sqlalchemy
mise run python:typecheck
mise run python:build:sqlalchemy

Tests execute facet counts on SQLite. They do not require a live PostgreSQL server.

The python:test:sqlalchemy task builds the packages and runs the faceting tests and README doctest against installed wheels in an isolated environment, without repository, FastAPI, or application packages. Dependencies are resolved automatically.

License

See LICENSE.

Metadata

Release files for team-hermes.sqlalchemy-faceted-search 1.0.1

For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.

Source distribution (sdist)

Source distribution for team-hermes.sqlalchemy-faceted-search 1.0.1
File Size Uploaded Provenance Actions
team_hermes_sqlalchemy_faceted_search-1.0.1.tar.gz 7.6 kB With provenance Details for team_hermes_sqlalchemy_faceted_search-1.0.1.tar.gz

Built distribution (wheel)

Table of built distributions (wheels) for team-hermes.sqlalchemy-faceted-search 1.0.1
File Interpreter ABI Platform Actions
team_hermes_sqlalchemy_faceted_search-1.0.1-py3-none-any.whl Python 3 none any Details for team_hermes_sqlalchemy_faceted_search-1.0.1-py3-none-any.whl

Total release size: 15.5 kB

Release files / team_hermes_sqlalchemy_faceted_search-1.0.1.tar.gz

Download URL team_hermes_sqlalchemy_faceted_search-1.0.1.tar.gz
Size 7.6 kB
Tags Source
SHA-256 checksum
How to use checksums
96ea6b96395533fb25cc70cad362211bcd402d778666f3e62951a690119f64bc
BLAKE2b-256 checksum
How to use checksums
1f8570ad8600617db35e75d97b9e1f2106927377f562a38284bff6f028193bd3
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
Yes
Uploaded via twine/7.0.0 CPython/3.13.16

Provenance

Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.

PyPI Publish Attestation

PyPI verified that this artifact, at this checksum, originated from the publisher listed below.

Signed by GitHub Actions, verified by PyPI on Nov 19, 2026.

Transparency log

Release files / team_hermes_sqlalchemy_faceted_search-1.0.1-py3-none-any.whl

Download URL team_hermes_sqlalchemy_faceted_search-1.0.1-py3-none-any.whl
Size 7.9 kB
Tags Python 3
SHA-256 checksum
How to use checksums
465ee4e35b73b7a3ab72933b9ea9776847d1f2256ea49094440342d85bb838da
BLAKE2b-256 checksum
How to use checksums
0eeb634f8c5b665c7600bfa5f87f203db0cdf79534fcec07f1fbce87720766cb
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
Yes
Uploaded via twine/7.0.0 CPython/3.13.16

Provenance

Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.

PyPI Publish Attestation

PyPI verified that this artifact, at this checksum, originated from the publisher listed below.

Signed by GitHub Actions, verified by PyPI on Nov 19, 2026.

Transparency log

Security for team-hermes.sqlalchemy-faceted-search 1.0.1

Provenance

Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.

Full provenance

Both files in this release have been uploaded with provenance metadata.

Select from 2 files

Attestation for team_hermes_sqlalchemy_faceted_search-1.0.1.tar.gz

PyPI Publish Attestation

PyPI verified that this artifact, at this checksum, originated from the publisher listed below.

Signed by GitHub Actions, verified by PyPI on Nov 19, 2026.

Transparency log

A note on package security

PyPI is a repository for distributing packages, not a service for auditing them. While third-party security researchers and our staff do their best to detect and remove malicious packages, PyPI cannot guarantee the quality, security or suitability of any given package.

To learn more about protecting yourself from malicious actors, visit our package security documentation.

Release history Release notifications | RSS feed

This release

1.0.1 This release

2 release files Full provenance

Anthropic, PBC Visionary sponsor Bloomberg Visionary sponsor Hudson River Trading Visionary sponsor Meta Visionary sponsor NVIDIA Visionary sponsor Microsoft Sustainability sponsor Depot Continuous Integration AWS Cloud computing and Security Sponsor Datadog Monitoring Fastly CDN Google Download Analytics Sentry Error logging StatusPage Status page