How to write a conditional filter query in SQLAlchemy-CodePudding

I am new to sqlAlchemy. I want to convert my existing SQL query to sqlAlchemy.

The condition is (If the first name variable is not null show the results with firstName else ignore)

...
AND (:FirstName is NULL 
   OR UPPER (employee.firstname) LIKE UPPER ('{firstName}'||'%'))
AND (:LasttName is NULL 
   OR UPPER (employee.firstname) LIKE UPPER ('{lastName}'||'%'))

CodePudding user response：

You can do this with sqlalchemy functions:

from sqlalchemy import func, or_, and_

db.session.query(
  employee
).filter(
  or_(
    employee.firstname == None,
    employee.firstname.ilike('...')
  )
  # add other filters if needed (and assumed)
)

Hope it helped.