SQLALchemy dynamic filter_by

Question

I know you can build dynamic filters for queries for SQLAlchemy by supplying **kwargs to filter_by.

For example

    filters = {'id': '123456', 'amount': '232'}
    db.session.query(Transaction).filter_by(**filters)

Below is my question:

What if I need to query by "greater than" or "less than" clauses? For example (raw SQL):

 select * from transaction t 
 where t.amount > 10 and t.amount < 100;

score 48 · Accepted Answer · answered Apr 27 '15 at 07:23

48

Instead of using filter_by I would recommend using filter, it gives you a lot more options.

For example (from the manual):

db.session.query(MyClass).filter(
    MyClass.name == 'some name',
    MyClass.id > 5,
)

In relation to your case:

filters = (
    Transaction.amount > 10,
    Transaction.amount < 100,
)
db.session.query(Transaction).filter(*filters)

answered Apr 27 '15 at 07:23

Wolph

78,177
11
137
148

1

Do we have and opportunity to make list of filters such you mentioned but that operators will be filled in dynamically with placeholders, I.e something like: `op = ">=" value=10 filter1 = "f{Transaction.amount} {op} {value}" filters =( filter1) db.session.query(Transaction).filter(*filters)` – JavaSa Dec 02 '17 at 10:52
@Wolph: I will appreciate you answer too – JavaSa Dec 02 '17 at 10:56
5

@JavaSa all operators are available through the [operator](https://docs.python.org/3/library/operator.html) module in Python as well. So instead of `a >= b` you can also use `operator.ge(a, b)` – Wolph Dec 05 '17 at 20:44
Is there a way to do this with various tables objects? How do you properly get the table and column object from a query string in a Rest API? Like if you perform a join and then want to sometimes filter the joined table. I'm trying to find a way to dynamically set the joined table's filter based on a query string – Adventure-Knorrig Aug 06 '22 at 17:33
1

@DanielJerrehian There's no pretty way to get the table object from a string, but there are several solutions available: https://stackoverflow.com/questions/11668355/sqlalchemy-get-model-from-table-name-this-may-imply-appending-some-function-to Once you're at that point you should be able to do a `getattr(table, column) == value` to create the filter. – Wolph Aug 06 '22 at 21:08
Is there some good way available to achieve this in case of raw query – Shubham Jain Sep 23 '22 at 05:55

score 7 · Answer 2 · answered Apr 29 '20 at 09:56

def get_filter_by_args(dic_args: dict):
    filters = []
    for key, value in dic_args.items():  # type: str, any
        if key.endswith('___min'):
            key = key[:-6]
            filters.append(getattr(model_class, key) > value)
        elif key.endswith('___max'):
            key = key[:-6]
            filters.append(getattr(model_class, key) < value)
        elif key.endswith('__min'):
            key = key[:-5]
            filters.append(getattr(model_class, key) >= value)
        elif key.endswith('__max'):
            key = key[:-5]
            filters.append(getattr(model_class, key) <= value)
        else:
            filters.append(getattr(model_class, key) == value)
    return filters

dic_args = {
    'is_created': 1,
    'create_time__min': 1588125484029,
}
filters = get_filter_by_args(dic_args)
lst = session.query(model_class).filter(*filters)

SQLALchemy dynamic filter_by

2 Answers2

Linked

Related