SQLAlchemy Filterset. An easy way to filter, sort, paginate SQLAlchemy queries
MIT License
Documentation: https://sqlalchemy-filterset.github.io/sqlalchemy-filterset
Source Code: https://github.com/sqlalchemy-filterset/sqlalchemy-filterset
The library provides a convenient and organized way to filter your database records.
By creating a FilterSet
class, you can declaratively define the filters you want to apply to your SQLAlchemy
queries.
This library is particularly useful in web applications, as it allows users to easily search, filter, sort, and paginate data.
The key features are:
pip install sqlalchemy-filterset
Requirements: Python 3.7+
SQLAlchemy 2.0+
In this example we specify criteria for filtering the database records
by simply setting the attributes of the ProductFilterSet
class.
This is more convenient and easier to understand than writing raw SQL queries, which
can be more error-prone and difficult to maintain.
from sqlalchemy_filterset import BaseFilterSet, Filter, RangeFilter, BooleanFilter
from myapp.models import Product
class ProductFilterSet(BaseFilterSet):
id = Filter(Product.id)
price = RangeFilter(Product.price)
is_active = BooleanFilter(Product.is_active)
import uuid
from pydantic import BaseModel
class ProductFilterSchema(BaseModel):
id: uuid.UUID | None
price: tuple[float, float] | None
is_active: bool | None
# Connect to the database
engine = create_engine("postgresql://user:password@host/database")
Base.metadata.create_all(bind=engine)
SessionLocal = sessionmaker(bind=engine)
session = SessionLocal()
# Define sqlalchemy query
query = select(Product)
# Define parameters for filtering
filter_params = ProductFilterSchema(price=(10, 100), is_active=True)
# Create the filterset object
filter_set = ProductFilterSet(query)
# Apply the filters to the query
query = filter_set.filter_query(filter_params.dict(exclude_unset=True))
# Execute the query
session.execute(query).unique().scalars().all()
This example will generate the following query:
select product.id, product.title, product.price, product.is_active
from product
where product.price >= 10
and product.price <= 100
and product.is_active = true;
This project is licensed under the terms of the MIT license.