烧瓶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左外部联接对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棉花糖中读到，棉花糖在一个模式中有两个db对象，它需要一个dict列表，这就是为什么我在将其传递给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

棉花糖dump(many=True)函数处理项目列表。它无法处理项的元组列表。该项可以是对象或dict。

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

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

PackMatData表：|id|unique_name||--|------||1|名称1||2|name2|

ColorData表：|id | color | pack_mat_data_id||--|------|----------------||1|颜色1|1||2|颜色2|1||3|color3|2|

你需要指定棉花糖如何处理它。

#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)

Answer 2

这对我使用烧瓶sqlalchemy、烧瓶棉花糖和棉花糖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"；而不是使用动态模式。如前所述，我需要为自己的目的使用动态模式。我认为这对你的目的没有必要。

🤠