使用Join的SQLAlchemy查询的嵌套JSON输出

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm.util import class_mapper
from sqlalchemy.orm import joinedload
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm.attributes import QueryableAttribute
import json

# 创建数据库连接
engine = create_engine('mysql+pymysql://username:password@host:port/database')
Session = sessionmaker(bind=engine)
session = Session()

# 创建映射基类
Base = declarative_base()

# 定义数据表模型
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)

class Order(Base):
    __tablename__ = 'orders'
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer)

# 查询并输出嵌套JSON
result = session.query(User).join(Order, User.id == Order.user_id).options(joinedload(User.orders)).all()

# 转换为嵌套JSON格式
def to_json(obj):
    cls = type(obj)
    fields = {}
    for field in class_mapper(cls).column_attrs:
        fields[field.key] = getattr(obj, field.key)
    for field in class_mapper(cls).relationships:
        fields[field.key] = to_json(getattr(obj, field.key))
    return fields

output = [to_json(r) for r in result]
print(json.dumps(output))