sqlalchemy-filters

0.13.0 verified Mon Apr 27 auth: no python

A library to filter SQLAlchemy queries using a JSON-like filter specification. Current version: 0.13.0, supports SQLAlchemy 1.4, Python 3.7+. Release cadence is irregular.

pip install sqlalchemy-filters

Common errors

error AttributeError: module 'sqlalchemy_filters' has no attribute 'apply_filters' ↓

cause Incorrect import (e.g., `import sqlalchemy_filters` and then using `sqlalchemy_filters.apply_filters` but the function is not exposed at top level).

fix

Use from sqlalchemy_filters import apply_filters.

error sqlalchemy.exc.ArgumentError: filter expression is not a valid SQL expression ↓

cause The filter specification uses an invalid operator or field name.

fix

Check that the field exists on the model and the operator is one of: eq, ne, gt, lt, ge, le, like, ilike, in, not_in, any, not_any.

error sqlalchemy.exc.InvalidRequestError: Can't join table/selectable 'some_table' to itself ↓

cause Auto-join tries to join a table that is already in the query, or the filtering causes a self-referential join.

fix

Disable auto-join with auto_join=False or explicitly add the join in the query.

Warnings

deprecated Python 2.7, 3.5, 3.6 support dropped in v0.13.0. ↓

fix Upgrade to Python 3.7+ and sqlalchemy-filters >=0.13.0.

breaking Filter operators '==', '!=' were renamed to 'eq', 'ne' in early versions. Check operator names. ↓

fix Use 'eq', 'ne', 'gt', 'lt', 'ge', 'le', 'like', 'ilike', 'in', 'not_in', 'any', 'not_any'.

gotcha Auto-join behavior: filters on related models automatically join the related table; use the 'auto_join' parameter to disable. ↓

fix Pass `auto_join=False` to `apply_filters` to prevent automatic joins.

gotcha Sorting on hybrid attributes requires the attribute to be defined on the model. ↓

fix Ensure hybrid attributes are properly defined with expressions.

Imports

apply_filters

from sqlalchemy_filters import apply_filters

apply_sort

from sqlalchemy_filters import apply_sort

Quickstart

Filters a SQLAlchemy query with a simple filter specification.

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import declarative_base, sessionmaker
from sqlalchemy_filters import apply_filters, apply_sort

Base = declarative_base()
class Book(Base):
    __tablename__ = 'book'
    id = Column(Integer, primary_key=True)
    title = Column(String)
    price = Column(Integer)

engine = create_engine('sqlite:///:memory:', echo=False)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
session.add_all([Book(title='A', price=10), Book(title='B', price=20)])
session.commit()

filter_spec = [{'field': 'price', 'op': 'gt', 'value': 15}]
query = session.query(Book)
filtered_query = apply_filters(query, filter_spec)
results = filtered_query.all()
print([(b.title, b.price) for b in results])