Flask Marshmallow与SQLAlchemy关联查询：获取含Admin信息的ProductType

问题背景 #

使用SQLAlchemy搭配PostgreSQL和Marshmallow开发，需要在获取ProductType数据时，同时返回添加该产品类型的Admin信息，期望输出格式如下：

{
    "created_at": "2023-01-21T07:55:14.773346+05:30",
    "descripton": "Product Type",
    "id": 6,
    "name": "dgd",
    "updated_at": null,
    "admin":{
        "full_name":"",
        "email":"",
        "mobile":""
    }
}

现有代码问题分析 #

1. ProductType外键错误：added_by_id的外键指向了不存在的added_by.id，正确应该指向Admin表的主键（默认表名为admin）
2. 关联关系定义冲突：ProductType的added_by关联中，backref命名为admin与Admin模型已有的关联冲突，逻辑不匹配
3. Schema字段不匹配：期望输出中Admin信息对应admin字段，但现有Schema用了added_by；同时AdminSchema默认会序列化敏感字段（如password），需要过滤

修正后的代码 #

1. Admin模型及Schema（调整Schema过滤字段） #

from datetime import datetime
from sqlalchemy import func
import sqlalchemy as db
from marshmallow_sqlalchemy import SQLAlchemyAutoSchema
import marshmallow as ma

class Admin(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    full_name = db.Column(db.String())
    email = db.Column(db.String(), unique=True)
    mobile = db.Column(db.String(), unique=True)
    password = db.Column(db.String())
    # 关联ProductType，backref指定为product_types，与ProductType的关联逻辑对应
    product_types = db.relationship("ProductType", backref="admin", lazy=True)
    created_at = db.Column(db.DateTime(timezone=True), server_default=func.now())
    updated_at = db.Column(db.DateTime(timezone=True), onupdate=func.now())

    def __repr__(self):
        return "<Admin %r>" % self.full_name


class AdminSchema(ma.SQLAlchemyAutoSchema):
    class Meta:
        model = Admin
        # 排除敏感/不需要返回的字段
        exclude = ("password", "created_at", "updated_at", "product_types")


admin_schema = AdminSchema()
admins_schema = AdminSchema(many=True)

2. ProductType模型及Schema（修正外键、关联关系和字段映射） #

class ProductType(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String())
    descripton = db.Column(db.String())
    # 修正外键，指向admin表的id字段
    added_by_id = db.Column(db.Integer, db.ForeignKey("admin.id"))
    # 定义关联，与Admin模型的product_types关联双向绑定
    added_by = db.relationship("Admin", back_populates="product_types")
    created_at = db.Column(db.DateTime(timezone=True), server_default=func.now())
    updated_at = db.Column(db.DateTime(timezone=True), onupdate=func.now())

    def __repr__(self):
        return "<ProductType %r>" % self.name


class ProductTypeSchema(ma.SQLAlchemyAutoSchema):
    class Meta:
        model = ProductType

    # 将added_by关联映射为输出中的admin字段，嵌套AdminSchema
    admin = ma.Nested(AdminSchema, attribute="added_by")


product_type_schema = ProductTypeSchema()
product_types_schema = ProductTypeSchema(many=True)

查询与序列化示例 #

为避免N+1查询问题，查询时使用预加载优化：

# 查询单个ProductType
product_type = db.session.query(ProductType).options(db.joinedload(ProductType.added_by)).get(1)
result = product_type_schema.dump(product_type)

# 查询所有ProductType
product_types = db.session.query(ProductType).options(db.joinedload(ProductType.added_by)).all()
result = product_types_schema.dump(product_types)

执行后即可得到符合期望格式的输出，其中admin字段包含指定的Admin信息，且过滤了敏感字段。

内容的提问来源于stack exchange，提问作者ninad muranjan