Flask-marshmallow转储的flask-sqlalchemy外部连接返回空

Question

我有两个表，PackMatData和ColorData：

class PackMatData(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    unique_name = db.Column(db.String(20), index=True, unique=True)
    se_name = db.Column(db.String(20), index=True)
    timestamp = db.Column(db.DateTime, index=True, default=datetime.utcnow)
    labflex_name = db.Column(db.String(20))
    country = db.Column(db.String(20), index=True)
    color_measurements = db.relationship('ColorData', backref='entry', cascade="all, delete-orphan", lazy='dynamic')


class ColorData(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    color = db.Column(db.String(20), index=True)
    patch = db.Column(db.String(10), index=True)
    L = db.Column(db.String(10), index=True)
    a = db.Column(db.String(10), index=True)
    b = db.Column(db.String(10), index=True)
    pack_mat_data_id = db.Column(db.Integer, db.ForeignKey('pack_mat_data.id'))

我想连接这些表，然后序列化返回值。我正在使用flask-sqlalchemy left on join在PackMatData表的id上执行连接：

@app.route('/api/entries')
def entries():

    pmcd = db.session.query(PackMatData, ColorData).outerjoin(ColorData, PackMatData.id == ColorData.pack_mat_data_id).all()
    data = [{"packmatdata": x[0], "colordata": x[1]} for x in pmcd]
    output = NestedSchema(many=True).dump(data)
    return jsonify(output)

从连接中，我得到了我期望的元组列表形式的数据：

[...,(<PackMatData 138>, <ColorData 7272>), (<PackMatData 138>, <ColorData 7285>),(<PackMatData 138>, <ColorData 7286>), (<PackMatData 138>, <ColorData 7287>), (<PackMatData 138>, <ColorData 7284>), (<PackMatData 138>, <ColorData 7283>), (<PackMatData 139>, <ColorData 7321>), (<PackMatData 139>, <ColorData 7322>), (<PackMatData 139>, <ColorData 7323>), (<PackMatData 139>, <ColorData 7320>), (<PackMatData 139>, <ColorData 7319>), (<PackMatData 139>, <ColorData 7311>), ...]

我在stackoverflow flask-marshmallow two db objects in one schema中读到，棉花糖需要一个字典列表，这就是为什么我要在将其传递给dump之前进行转换。

下面是我为序列化定义的模式：

class ColorDataSchema(ma.ModelSchema):
    class Meta:
        model = ColorData

class PackMatDataSchema(ma.ModelSchema):  
    class Meta:
        model = PackMatData

class NestedSchema(ma.ModelSchema):  
    colordata = ma.Nested(ColorDataSchema)
    packmatdata = ma.Nested(PackMatDataSchema, many=True)

问题是转储函数总是返回以下内容：

[...,{}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {},...]

我也尝试了这个模式(结果相同)：

class PackMatDataSchema(ma.ModelSchema):  
    class Meta:
        model = PackMatData

class ColorDataSchema(ma.ModelSchema):
    packmatdata = ma.Nested(PackMatDataSchema, many=True)
    class Meta:
        model = ColorData

你能带我到正确的方向吗？很抱歉发了这么长的帖子，但我想尽可能多地向你提供细节。

Answer 1

这对我使用flask-sqlalchemy、flask-marshmallow和marshmallow-sqlalchemy很有效。

也许，您需要将表名包含到flask SQL Alchemy数据库模型中。

class PackMatData(db.Model):
    __tablename__ = 'packmatdata'
    id = db.Column(db.Integer, primary_key=True)
    unique_name = db.Column(db.String(20), index=True, unique=True)
    se_name = db.Column(db.String(20), index=True)
    timestamp = db.Column(db.DateTime, index=True, default=datetime.utcnow)
    labflex_name = db.Column(db.String(20))
    country = db.Column(db.String(20), index=True)
    color_measurements = db.relationship('ColorData', backref='entry', cascade="all, delete-orphan", lazy='dynamic')


class ColorData(db.Model):
    __tablename__ = 'colordata'
    id = db.Column(db.Integer, primary_key=True)
    color = db.Column(db.String(20), index=True)
    patch = db.Column(db.String(10), index=True)
    L = db.Column(db.String(10), index=True)
    a = db.Column(db.String(10), index=True)
    b = db.Column(db.String(10), index=True)
    pack_mat_data_id = db.Column(db.Integer, db.ForeignKey('pack_mat_data.id'))

我在模式类中包含了一些额外的东西。这对您来说可能不是必需的。

class PackMatDataSchema(ma.SQLAlchemyAutoSchema):  
    class Meta:
        model = PackMatData
        load_instance = True

class ColorDataSchema(ma.SQLAlchemyAutoSchema):
    class Meta:
        model = ColorData
        load_instance = True
        include_fk = True

为了反序列化SQL Alchemy查询，我使用了一个动态模式。(对于我的情况，动态模式是必需的。)

@app.route('/api/entries')
def entries():
    # SQL Alchemy automatically joins on foreign keys.
    pmcd = db.session.query(PackMatData, ColorData)\
        .outerjoin(ColorData).all()
    data = [
        {PackMatData.__tablename__: x[0],
         ColorData.__tablename__: x[1]} 
        for x in pmcd
        ]
    DynamicSchema = ma.Schema.from_dict({
        PackMatData.__tablename__: 
            ma.Nested(PackMatDataSchema, dump_only=True),
        ColorData.__tablename__: 
            ma.Nested(ColorDataSchema, dump_only=True)
        })
    dynamic_schema = DynamicSchema(many=True)
    return jsonify(dynamic_schema.dump(data))

这应该是可行的。您可以尝试创建"NestedSchema“，而不是使用动态模式。如前所述，我需要为自己的目的使用动态模式。我不认为这对你的目的是必要的。

?

Answer 2

Marshmallow dump(many=True)函数处理项目列表。它不能处理项目的元组列表。该项目可以是对象或dict。

L1 = [item1, item2, item3]
schema.dump(L1, many=True)
# returns [data1, data2, data3]

在这里，您有一个项目链接到数据库中的另一个项目。

PackMatData表：| id | unique_name || -- |-||1| name1 ||2| name2 |

ColorData表：| id | color | pack_mat_data_id || -- |1| color1 |1||2| color2 |1|3| color3 |2

您需要向Marshmallow指定如何处理它。

#output type 1 : dumping PackMatData items
[
  { 
    id: 1,
    unique_name: name1, 
    color_measurements: [{id: 1, color: color1}, {id: 2, color: color2}],
  },
  {
    id: 2,
    unique_name: name2, 
    color_measurements: [{id: 3, color: color3}],
  }
]

#output type 2 : dumping ColorData items
[
  { 
    id: 1,
    color: color1, 
    entries: {id: 1, unique_name: name1},
  },
  {
    id: 2,
    color: color2, 
    entry: {id: 1, unique_name: name1},
  },
  { 
    id: 3,
    color: color3, 
    entry: {id: 2, unique_name: name2},
  },
]

#output type 3: dumping PackMatData and ColorData "side by side"
[
  { 
    packMetaData: {id:1, unique_name: name1},
    colorData: {id: 1, color: color1}
  },
  {
    packMetaData: {id:1, unique_name: name1},
    colorData: {id: 2, color: color2}
  },
  { 
    packMetaData: {id:2, unique_name: name2},
    colorData: {id: 3, color: color3}
  },
]

要获取输出，请键入1

class ColorDataSchema(ma.ModelSchema):
    class Meta:
        model = ColorData
    fields = ('id', 'color')  

class PackMatDataSchema(ma.ModelSchema):  
    class Meta:
        model = PackMatData
    color_measurements = fields.Nested(ColorDataSchema) #the name is important

query = db.session.query(PackMatData).all()
data = PackMatDataSchema().dump(query, many=True)

要获取输出，请键入2

class PackMatDataSchema(ma.ModelSchema):  
    class Meta:
        model = PackMatData

class ColorDataSchema(ma.ModelSchema):
    class Meta:
        model = ColorData
    entry = fields.Nested(PackMatDataSchema) #the name is important 

query = db.session.query(ColorData).all()
data = ColorDataSchema().dump(query, many=True)

并获取输出类型3

class PackMatDataSchema(ma.ModelSchema):  
    class Meta:
        model = PackMatData

class ColorDataSchema(ma.ModelSchema):
    class Meta:
        model = ColorData
    fields = ('id', 'color')

class SideBySideSchema(ma.ModelSchema):
    packMetaData = fields.Nested(PackMatDataSchema)
    colorData = fields.Nested(ColorDataSchema)

query = db.session.query(PackMatData, ColorData).join(ColorData).all()
list_of_dict = [{packMetaData: p, colorData: c} for p, c in query]
data = SideBySideSchema().dump(list_of_dict, many=True)