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)
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 + "%"))
Notice is a python class, and is a mapped sqlalchemy model. So in the first example from Classical Mapping it would be User, not user.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 + "%")
)
.columns instead of .c, which is perhaps clearer.
Column objects from the underlying Table, instead of the instrumented attributes of the mapped class. This might not even work, if the mapped attribute is named differently from the column.
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)
Table instance.The accepted answer has a solution, but an outdated one.
SQLAlchemy 1.3 suggests putting all text filters in text(), joined with and.
Docs
Example: session.query(User).filter(text("id<224")).order_by(text("id")).all()
Another example from docs on a different query
>>> s = select([
... text("users.fullname || ', ' || addresses.email_address AS title")
... ]).\
... where(
... and_(
... text("users.id = addresses.user_id"),
... text("users.name BETWEEN 'm' AND 'z'"),
... text(
... "(addresses.email_address LIKE :x "
... "OR addresses.email_address LIKE :y)")
... )
... ).select_from(text('users, addresses'))
text()...