sqlalchemy-filters 0.2.0 2017-02-17 ✔ PY3

sqlalchemy-filters on PyPI  

A library to filter SQLAlchemy queries.

LicenseApache License, Version 2.0

Filter, sort and paginate SQLAlchemy query objects. Ideal for exposing these actions over a REST API.


Assuming that we have a SQLAlchemy query that only contains a single model:

from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

class Base(object):
    id = Column(Integer, primary_key=True)
    name = Column(String(50), nullable=False)
    count = Column(Integer, nullable=True)

Base = declarative_base(cls=Base)

class Foo(Base):

    __tablename__ = 'foo'

# ...

query = self.session.query(Foo)

Then we can apply filters to that query object (multiple times):

from sqlalchemy_filters import apply_filters

# `query` should be a SQLAlchemy query object

filters = [{'field': 'name', 'op': '==', 'value': 'name_1'}]
filtered_query = apply_filters(query, filters)

more_filters = [{'field': 'foo_id', 'op': 'is_not_null'}]
filtered_query = apply_filters(filtered_query, more_filters)

result = filtered_query.all()


from sqlalchemy_filters import apply_sort

# `query` should be a SQLAlchemy query object

order_by = [
    {'field': 'name', 'direction': 'asc'},
    {'field': 'id', 'direction': 'desc'},
sorted_query = apply_sort(query, order_by)

result = sorted_query.all()

Filters format

Filters must be provided in a list and will be applied sequentially. Each filter will be a dictionary element in that list, using the following format:

filters = [
    {'field': 'field_name', 'op': '==', 'value': 'field_value'},
    {'field': 'field_2_name', 'op': '!=', 'value': 'field_2_value'},
    # ...

Where field is the name of the field that will be filtered using the operator provided in op and (optionally, depending on the operator) the provided value.

This is the list of operators that can be used:

  • is_null
  • is_not_null
  • ==, eq
  • !=, ne
  • >, gt
  • <, lt
  • >=, ge
  • <=, le
  • like
  • in
  • not_in

Sort format

Sort elements must be provided as dictionaries in a list and will be applied sequentially:

order_by = [
    {'field': 'name', 'direction': 'asc'},
    {'field': 'id', 'direction': 'desc'},
    # ...

Where field is the name of the field that will be sorted using the provided direction.

Running tests

There are some Makefile targets that can be used to run the tests. A test database will be created, used during the tests and destroyed afterwards.

The default configuration uses both SQLite and MySQL (if the driver is installed) to run the tests, with the following URIs:


Example of usage:

$ # using default settings
$ make test
$ make coverage

$ # overriding DB parameters
$ ARGS='--mysql-test-db-uri mysql+mysqlconnector://root:@' make test
$ ARGS='--sqlite-test-db-uri sqlite+pysqlite:///test_sqlalchemy_filters.db' make test

$ ARGS='--mysql-test-db-uri mysql+mysqlconnector://root:@' make coverage
$ ARGS='--sqlite-test-db-uri sqlite+pysqlite:///test_sqlalchemy_filters.db' make coverage


Apache 2.0. See LICENSE for details.