SQLAlchemy中带子句的递归CTE

from sqlalchemy import create_engine, select, text
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine('数据库连接字符串')
Session = sessionmaker(bind=engine)
session = Session()

Base = declarative_base()

class Employee(Base):
    __tablename__ = 'employees'
    
    id = Column(Integer, primary_key=True)
    name = Column(String)
    manager_id = Column(Integer, ForeignKey('employees.id'))

# 定义递归CTE公共表达式
cte = select(Employee.id, Employee.name, Employee.manager_id).cte(recursive=True)

# 定义递归CTE查询的初始部分
initial_query = cte.union_all(
    select(Employee.id, Employee.name, Employee.manager_id)
    .where(Employee.manager_id == cte.c.id)
)

# 定义最终的递归CTE查询
final_query = select(cte.c.id, cte.c.name).select_from(initial_query)

# 执行递归CTE查询
result = session.execute(final_query).fetchall()