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 customSelect, 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)
| 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)
| File | Interpreter | ABI | Platform | Actions Reset |
|---|---|---|---|---|
| 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 logRelease 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 logSecurity 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.
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 logA 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.