1
votes

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

2
You probably want getattr(tablename, key1) instead of tablename.key1. - univerio

2 Answers

2
votes
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()
2
votes

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.