build dynamic filters in sqlalchemy python

Question

I need to generate/build sqlalchemy query dynamically using dynamic columns and their values.

Example - I have a table in SQL called "Convo" and it has columns like - UserID, ConvoID, ContactID.

I need to get rows based on the below criteria.

criteria = (('UserID', 2), ('ConvoID', 1) ,('ContactID', 353))

I have used "Baked query" logic for this. But Some how I am not able to run this query successfully.

Below is the my code.

criteria = (('UserID', 2), ('ConvoID', 1) ,('ContactID', 353))
baked_query = bakery(lambda session: session.query(tablename))
for key1 in condition:
    baked_query += lambda q: q.filter(tablename.key1 == condition[key1])
    result = baked_query(self.session).all()

I am getting error as -

AttributeError: type object 'Convo' has no attribute 'key1'

Please help me out with this

You probably want getattr(tablename, key1) instead of tablename.key1. — univerio
– univerio, Commented Aug 25, 2016 at 6:17

turkus · Accepted Answer · 2016-08-25 06:31:02Z

4

criteria = (('UserID', 2), ('ConvoID', 1) ,('ContactID', 353))

query = session.query(tablename)
for _filter, value in criteria:
    query = query.filter(getattr(tablename, _filter) == value)
result = query.all()

answered Aug 25, 2016 at 6:31

turkus

4,9633 gold badges28 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

sebastian · Accepted Answer · 2016-08-25 06:43:08Z

2

If you're using dynamic keys and "simple" equality checks, the filter_by method might be more convenient, as it takes keyword arguments that match you property names and assembles that into where clause.

So your iterative query construction could look like that:

baked_query = bakery(lambda session: session.query(tablename))
for key, value in condition.items():
    baked_query += lambda q: q.filter_by(key=value)

Plus, since filter_by takes mulitple keyword arguments, you can probably simplify your query construction to a single filter_by invocation:

baked_query = bakery(lambda session: session.query(tablename))
baked_query += lambda q: q.filter_by(**condition)

All of the above obviously assuming that your condition variable refers to a dictionary.

edited Aug 25, 2016 at 6:43

answered Aug 25, 2016 at 6:33

sebastian

9,71628 silver badges56 bronze badges

Collectives™ on Stack Overflow

build dynamic filters in sqlalchemy python

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related