SQLAlchemy递归查询结果集排序

from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql import select, union_all

# 创建数据库引擎和会话
engine = create_engine('数据库连接字符串')
Session = sessionmaker(bind=engine)
session = Session()

# 创建基类
Base = declarative_base()

# 定义模型类
class Category(Base):
    __tablename__ = 'categories'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    parent_id = Column(Integer, ForeignKey('categories.id'))
    children = relationship('Category', backref='parent', remote_side=[id])

# 递归查询所有子分类
def recursive_query(category):
    query = select([Category.id, Category.name]).where(Category.id == category.id)
    for child in category.children:
        query = query.union_all(recursive_query(child))
    return query

# 查询结果集并排序
root_category = session.query(Category).filter_by(name='根分类').first()
query = recursive_query(root_category).order_by(Category.id)

# 执行查询并打印结果
result = session.execute(query)
for row in result:
    print(row)

# 关闭会话
session.close()