Python package for easy sqlalchemy filtering databases using dataclasses or without them
MIT License
python >= 3.8.1
pydantic >= 1.9
sqlalchemy >= 1.4.0
# without extras
pip install dataclass-sqlalchemy-mixins
# with pydantic
pip install dataclass-sqlalchemy-mixins[pydantic]
This package consists of the several important parts:
It is important to set the SQLAlchemy
model in the ConverterConfig
that you are going to query in the dataclass you are creating.
This package supports sql operations: eq
, in
, not_in
, gt
, lt
, gte
, lte
, not
, is
, is_not
, like
, ilike
, isnull
.
To apply a filter to a field, the filter should be formatted like field_name__{sql_op}
. Filtering though a foreign key supported using field_name__foreigh_key_field__{sql_op}
.
To perform sorting the value should be passed as id
to ASC and -name
to DESC. Inner joins will be applied automatically if using pydantic
mixins.
Direct
Starting from version 0.2.0
, get_binary_expressions
and get_unary_expressions
were introduced,
allowing filters and ordering to be obtained without inheriting from dataclasses.
You just need to pass filters
/order_by
and a model you want to apply them to.
There are located in utils
.
Filter:
from dataclass_sqlalchemy_mixins.base import utils
filters = {
'id__gte': 1,
'name__in': ['abc', 'def'],
'object__place': 1,
}
binary_expressions = utils.get_binary_expressions(
filters=filters,
model=SomeModel
)
query = query.filter(*binary_expressions)
Order by:
from dataclass_sqlalchemy_mixins.base import utils
order_by = ['id', '-name']
unary_expressions = utils.get_unary_expressions(
order_by=order_by,
model=SomeModel,
)
query = query.order_by(*unary_expressions)
Custom dataclasses
It is possible to apply mixins to custom dataclasses by inheriting from either SqlAlchemyFilterConverterMixin
for filters or SqlAlchemyOrderConverterMixin
for orderings.
Filter:
import typing
from dataclasses import dataclass, asdict
from dataclass_sqlalchemy_mixins.base.mixins import SqlAlchemyFilterConverterMixin
@dataclass
class CustomDataclass(SqlAlchemyFilterConverterMixin):
id__gte: int = None
name__in: typing.List[str] = None
object__place: int = None
class ConverterConfig:
model = SomeModel
def dict(self):
return {k: str(v) for k, v in asdict(self).items() if v is not None}
custom_dataclass = CustomDataclass(
id__gte=1,
name__in=['abc', 'def'],
object__place=1,
)
binary_expressions = custom_dataclass.get_binary_expressions(custom_dataclass.dict())
query = query.filter(*binary_expressions)
Order by:
import typing
from dataclasses import dataclass
from dataclass_sqlalchemy_mixins.base.mixins import SqlAlchemyOrderConverterMixin
@dataclass
class CustomDataclass(SqlAlchemyOrderConverterMixin):
order_by: typing.Optional[typing.Union[str, typing.List[str]]] = None
class ConverterConfig:
model = SomeModel
custom_dataclass = CustomDataclass(
order_by=['id', '-name']
)
unary_expressions = custom_dataclass.get_unary_expressions(custom_dataclass.order_by)
query = query.order_by(*unary_expressions)
Pydantic
Filter:
import typing
from dataclass_sqlalchemy_mixins.pydantic_mixins.sqlalchemy_base_models import SqlAlchemyFilterBaseModel
class CustomBaseModel(SqlAlchemyFilterBaseModel):
id__gte: int = None
name__in: typing.List[str] = None
object__place: int = None
class ConverterConfig:
model = SomeModel
custom_basemodel = CustomBaseModel(
id__gte=1,
name__in=['abc', 'def'],
object__place=1,
)
binary_expressions = custom_basemodel.to_binary_expressions()
query = query.filter(*binary_expressions)
# or
query = custom_basemodel.apply_filters(query=query)
Sometimes, it is necessary to manipulate sent data before applying filters.
For example, a field should not be directly converted to a filter; instead, custom logic should be applied.
As of version 0.1.3
, the to_binary_expressions
and apply_filters
methods accept the export_params
argument to address this situation.
Values mentioned in the Pydantic dictionary export section can be sent as export_params
.
import typing
from dataclass_sqlalchemy_mixins.pydantic_mixins.sqlalchemy_base_models import SqlAlchemyFilterBaseModel
class CustomBaseModel(SqlAlchemyFilterBaseModel):
id__gte: int = None
name__in: typing.List[str] = None
filter_to_exclude: typing.Any = None
class ConverterConfig:
model = SomeModel
custom_basemodel = CustomBaseModel(
id__gte=1,
name__in=['abc', 'def'],
filter_to_exclude="filter_value",
)
# filter_to_exclude field will be excluded from converting basemodel to sqlalchemy filters
binary_expression = custom_basemodel.to_binary_expressions(
export_params={'exclude': {'filter_to_exclude'}, }
)
query = query.filter(*binary_expression)
# or
query = custom_basemodel.apply_filters(
query=query,
export_params={'exclude': {'filter_to_exclude'}, }
)
Order by:
import typing
from dataclass_sqlalchemy_mixins.pydantic_mixins.sqlalchemy_base_models import SqlAlchemyOrderBaseModel
class CustomBaseModel(SqlAlchemyOrderBaseModel):
id__gte: int = None
name__in: typing.List[str] = None
object__place: int = None
class ConverterConfig:
model = SomeModel
custom_basemodel = CustomBaseModel(
order_by=['id', '-name']
)
unary_expressions = custom_basemodel.get_unary_expressions(custom_dataclass.order_by)
query = query.order_by(*unary_expressions)
# or
query = custom_basemodel.apply_order_by(query)
Dataclasses inherited from SqlAlchemyFilterBaseModel
or SqlAlchemyOrderBaseModel
normally produce the correct documentation.
However, there is one issue that should be mentioned:
FastAPI
has trouble creating documentation when a complex type is set as an annotation for Query
parameters.
This includes lists.
The extra
parameter was introduced to address these situations which can be set in ConverterConfig
.
Currently, this parameter only accepts a dictionary with one key: BaseModelConverterExtraParams.LIST_AS_STRING
.
This key instructs the converter to treat the passed string as a list in the context of filtering and ordering.
For example, a class defined like this will convert the value passed for field__in
into a list when applying filters and orderings.
The value passed for another_field__in
won't be treated a list because the field wasn't included in the fields
set in extra
.
Another parameter can be used is expected_types
.
It is used to define which as which type should be elements of the list treated as when a str converted to a list.
If an expected type is not passed for a field it will be converted to a str.
from fastapi import Query
from dataclass_sqlalchemy_mixins.pydantic_mixins.sqlalchemy_base_models import BaseModelConverterExtraParams
from dataclass_sqlalchemy_mixins.pydantic_mixins.sqlalchemy_base_models import SqlAlchemyFilterBaseModel
class SomeSqlAlchemyFilterModel(SqlAlchemyFilterBaseModel):
field__in: str = Query(None)
another_field__in: str = Query(None)
class ConverterConfig:
model = SomeModel
extra = {
BaseModelConverterExtraParams.LIST_AS_STRING: {
'fields': ['field__in', ],
'expected_types': {
'field__in': int,
}
}
}
The same applies to the classes inherited from SqlAlchemyOrderBaseModel
,
except that since the model accepts only the order_by
field, it is not necessary to specify specific fields.
from dataclass_sqlalchemy_mixins.pydantic_mixins.sqlalchemy_base_models import BaseModelConverterExtraParams
from dataclass_sqlalchemy_mixins.pydantic_mixins.sqlalchemy_base_models import SqlAlchemyOrderBaseModel
class SomeSqlAlchemyOrderModel(SqlAlchemyOrderBaseModel):
order_by: str = None
class ConverterConfig:
model = SomeModel
extra = {
BaseModelConverterExtraParams.LIST_AS_STRING: True
}
Another possible solution
Also, it is possible not use ConverterConfig
to correctly display lists in Query
parameters using FastApi
import typing
from fastapi import Query
from dataclass_sqlalchemy_mixins.pydantic_mixins.sqlalchemy_base_models import SqlAlchemyFilterBaseModel
class SomeSqlAlchemyFilterModel(SqlAlchemyFilterBaseModel):
field__in: str
def __init__(self, field__in: typing.List[str] = Query(), **kwargs) -> None:
super().__init__(field__in=field__in, **kwargs)
To run tests on your local machine
cd tests
docker compose up