在SQLAlchemy的hybrid_property中使用(加入和选择) Postgres CTE

from sqlalchemy import Column, Integer
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.postgresql import CTE
from sqlalchemy.ext.hybrid import hybrid_property

Base = declarative_base()

class Employee(Base):
    __tablename__ = 'employees'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    salary = Column(Integer)

    @hybrid_property
    def bonus(self):
        cte = CTE(
            'bonus_cte',
            select([func.row_number().over(order_by=self.salary.desc()).label('rank'), self.salary.label('bonus')])
            .where(self.salary > 5000)
        )

        return select([cte.c.bonus]).where(cte.c.rank == 1)

# 创建数据库连接和会话
engine = create_engine('postgresql://username:password@host:port/database')
Session = sessionmaker(bind=engine)
session = Session()

# 创建表
Base.metadata.create_all(engine)

# 插入数据
employee1 = Employee(name='John', salary=6000)
employee2 = Employee(name='Jane', salary=4000)
session.add_all([employee1, employee2])
session.commit()

# 查询数据
employees = session.query(Employee).all()
for employee in employees:
    print(employee.name, employee.bonus)

# 关闭会话和数据库连接
session.close()
engine.dispose()