Skip to main content

FastAPI-SQLAlchemy filter, transform request query string to SQLAlchemy orm query

Project description

FastAPI SQLAlchemy Filter

Package that helps to implement easy objects filtering and sorting for applications build on FastAPI and SQLAlchemy. For using you just need to define your custom filter with filtered fields and applied operators. Supported operators, datatypes and example of work you can find below.

Installation

pip install fastapi-sa-orm-filter-2

Quickstart

from fastapi import FastAPI
from fastapi.params import Query
from fastapi_sa_orm_filter.main import FilterCore
from fastapi_sa_orm_filter.operators import Operators as ops

from db.base import get_session
from db.models import MyModel


app = FastAPI()

# Define fields and operators for filter
my_objects_filter = {
    'my_model_field_name': [ops.eq, ops.in_],
    'my_model_field_name': [ops.between, ops.eq, ops.gt, ops.lt, ops.in_],
    'my_model_field_name': [ops.like, ops.startswith, ops.contains, ops.in_],
    'my_model_field_name': [ops.between, ops.not_eq, ops.gte, ops.lte]
}

@app.get("/")
async def get_filtered_objects(
    filter_query: str = Query(default=''),
    db: AsyncSession = Depends(get_session)
 ) -> List[MyModel]:
    my_filter = FilterCore(MyModel, my_objects_filter)
    query = my_filter.get_query(filter_query)
    res = await db.execute(query)
    return res.scalars().all()

Example of work

# Input query string
'''
salary_from__in_=60,70,80&
created_at__between=2023-05-01,2023-05-05|
category__eq=Medicine&
order_by=-id,category
'''

   
# Returned SQLAlchemy orm query exact as:
           
select(model)
    .where(
        or_(
            and_(
                model.salary_from.in_(60,70,80),
                model.created_at.between(2023-05-01, 2023-05-05)
            ),
            model.category == 'Medicine'
        ).order_by(model.id.desc(), model.category.asc())

Supported query string format

  • field_name__eq=value
  • field_name__in_=value1,value2
  • field_name__eq=value&field_name__in_=value1,value2
  • field_name__eq=value&field_name__in_=value1,value2&order_by=-field_name

Modify query for custom selection

# Create a class inherited from FilterCore and rewrite 'get_unordered_query' method.
# Original method is:
def get_unordered_query(self, conditions):
    unordered_query = select(self._model).filter(or_(*conditions))
    return unordered_query
    
# Rewrite example:
class CustomFilter(FilterCore):

    def get_unordered_query(self, conditions):
        unordered_query = select(
            self.model.field_name1,
            self.model.field_name2,
            func.sum(self.model.field_name3).label("field_name3"),
            self.model.field_name4
        ).filter(or_(*conditions)).group_by(self.model.field_name2)
        return unordered_query

Supported SQLAlchemy datatypes:

  • DATETIME
  • DATE
  • INTEGER
  • FLOAT
  • TEXT
  • VARCHAR
  • Enum(VARCHAR())
  • BOOLEAN

Available filter operators:

  • eq
  • gt
  • lt
  • gte
  • lte
  • in_
  • startswith
  • endswith
  • between
  • like
  • ilike
  • contains
  • icontains
  • not_eq
  • not_in
  • not_like
  • not_between

For suggestions and questions, feel free to contact me through email

zhydykalex@ukr.net

Project details


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Source Distribution

fastapi_sa_orm_filter_2-0.0.2.tar.gz (5.9 kB view details)

Uploaded Source

Built Distribution

fastapi_sa_orm_filter_2-0.0.2-py3-none-any.whl (7.4 kB view details)

Uploaded Python 3

File details

Details for the file fastapi_sa_orm_filter_2-0.0.2.tar.gz.

File metadata

File hashes

Hashes for fastapi_sa_orm_filter_2-0.0.2.tar.gz
Algorithm Hash digest
SHA256 3daefd3ba22fcf1907844f1a5b616eb9fbc8a2a46a990399bf56633c21de0e5d
MD5 fb364ae35a217d8de56f76d0866ef8b9
BLAKE2b-256 94c214b65bc712119ea18dbcf1ef46768867d3dd8b4c4f9ffe474172e58b5799

See more details on using hashes here.

File details

Details for the file fastapi_sa_orm_filter_2-0.0.2-py3-none-any.whl.

File metadata

File hashes

Hashes for fastapi_sa_orm_filter_2-0.0.2-py3-none-any.whl
Algorithm Hash digest
SHA256 2bd86325a594a840d7286304b7ee7a5f1dda48dd8511980d875a0590bd0fd5fe
MD5 29371e978724409463bce184bc59a4c7
BLAKE2b-256 fcbef7c37336cb3d5b0934ead721486571979d8b3dd46c3d0200d59299f9592d

See more details on using hashes here.

Supported by

AWS AWS Cloud computing and Security Sponsor Datadog Datadog Monitoring Fastly Fastly CDN Google Google Download Analytics Microsoft Microsoft PSF Sponsor Pingdom Pingdom Monitoring Sentry Sentry Error logging StatusPage StatusPage Status page