This is the query I'm trying to produce through sqlalchemy

SELECT "order".id AS "id", 
"order".created_at AS "created_at", 
"order".updated_at AS "updated_at", 
CASE 
WHEN box.order_id IS NULL THEN "special" 
ELSE "regular" AS "type"
FROM "order" LEFT OUTER JOIN box ON "order".id = box.order_id

Following sqlalchemy's documentation, I tried to achieve this using hybrid_property. This is what I have so far, and I'm not getting the right statement. It is not generating the case statement properly.

from sqlalchemy import (Integer, String, DateTime, ForeignKey, select, Column, create_engine)
from sqlalchemy.orm import relationship, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.hybrid import hybrid_propertyBase = declarative_base()
class Order(Base):__tablename__ = 'order'id = Column(Integer, primary_key=True)created_at = Column(DateTime)updated_at = Column(DateTime)order_type = relationship("Box", backref='order')@hybrid_propertydef type(self):if not self.order_type:return 'regular'else:return 'special'class Box(Base):__tablename__ = 'box'id = Column(Integer, primary_key=True)monthly_id = Column(Integer)order_id = Column(Integer, ForeignKey('order.id'))stmt = select([Order.id, Order.created_at, Order.updated_at, Order.type]).\select_from(Order.__table__.outerjoin(Box.__table__))
print(str(stmt))

The hybrid property must contain two parts for non-trivial expressions: a Python getter and a SQL expression. In this case, the Python side will be an if statement and the SQL side will be a case expression.

from sqlalchemy import case
from sqlalchemy.ext.hybrid import hybrid_property@hybrid_property
def type(self):return 'special' if self.order_type else 'regular'@type.expression
def type(cls):return case({True: 'special', False: 'regular'}, cls.order_type)