CASE WHEN with ORM (SQLalchemy)

Question

I am using SQLAlchemy with the ORM paragdim. I don't manage to find a way to do a CASE WHEN instruction. I don't find info about this on the web.

Is it possible ?

Answer 1

See sqlalchemy.sql.expression.case function and more examples on the documentation page. But it would look like this (verbatim from the documentation linked to):

case([(orderline.c.qty > 100, item.c.specialprice),       (orderline.c.qty > 10, item.c.bulkprice)     ], else_=item.c.regularprice) case(value=emp.c.type, whens={         'engineer': emp.c.salary * 1.1,         'manager':  emp.c.salary * 3,     }) 

edit-1: (answering the comment) Sure you can, see example below:

class User(Base):     __tablename__ = 'users'     id = Column(Integer, primary_key=True, autoincrement=True)     first_name = Column(String)     last_name = Column(String)  xpr = case([(User.first_name != None, User.first_name + " " + User.last_name),],         else_ = User.last_name).label("full_name")  qry = session.query(User.id, xpr) for _usr in qry:     print _usr.fullname 

Also see Using a hybrid for an example of case used in the hybrid properties.