Menu
I want to create query in sql alchemy filter, but column is(dynamic) in variable/specified in variable.
Original Query:
db_session.query(Notice).filter(Notice.subject.like("%" +query+ "%"))
I want to do query like this:
col_name='subject'
db_session.query(Notice).filter(Notice.col_name.like("%" +query+ "%"))
col_name='status'
status=0
db_session.query(Notice).filter(Notice.col_name != 1)
960
To access the column names we can use the method keys() on the result. It returns a list of column names. Since, we queried only three columns, we can view the same columns on the output as well.
lazy = 'dynamic': When querying with lazy = 'dynamic', however, a separate query gets generated for the related object. If you use the same query as 'select', it will return: You can see that it returns a sqlalchemy object instead of the city objects.
Metadata contains definitions of tables and associated objects such as index, view, triggers, etc. Hence an object of MetaData class from SQLAlchemy Metadata is a collection of Table objects and their associated schema constructs.
Just use getattr standard python library function to get an attribute by name:
col_name = 'subject' db_session.query(Notice).filter(getattr(Notice, col_name).like("%" + query + "%")) In newer sqlalchemy version, it should be done this way:
Notice.__table__.c[col_name]
So:
(db_session
.query(Notice)
.filter(Notice.__table__.c[col_name].like("%" + query + "%")
)
I tried @vans solution but it didn't work. I always got an AttributeError complaining that my table didn't have that column. What worked for me was table.columns:
getattr(Notice.columns,col_name)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
