How to give column name dynamically from string variable in sql alchemy filter?

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)

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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 + "%"))

Method 2

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 + "%")
)

Method 3

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)

Method 4

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'))

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0