SQLAlchemy add condition to query

Question

Beginning of my raw sql statement looks like this:

select if(substr(Table.order_id,1,8)='STRING', Table1.name, t=Table2.type)

Tried to rewrite it in SQLAlchemy query:

query = db_session.query(Table,\ 
                         Table1.name if Table.order_id[1:8] == 'STRING' else Table2.type)

But it returned Operator 'getitem' is not supported on this expression.

How can I add this condition to my ORM query without touching models?

Or how can I add raw sql statement in query parameters?

PS: I would prefer not to make any changes to models.

Answer 1

You need to use Functions:

from sqlalchemy import func

q = db_session.query(
    func.IF(func.substr(Table.order_id, 1, 8) == 'STRING', Table1.name, Table2.type)
)