python - 在 SQLAlchemy 中建模关系以使用 Marshmallow 显示嵌套的“集合”
问题描述
我试图弄清楚如何对(flask-)SQLAlchemy 和(flask-)Marshamallow 进行建模以提供以下 JSON 输出。
父级是产品,子级是“变体集”......它们是选项的类型,例如“颜色”或“尺寸”。嵌套在这些集合中,我想要选项本身(S、M、L 等)
好像我错过了一些明显的东西。
期望的输出:
{
"skuid": "B1234",
"name": "Test Product 1",
"variant_sets": [
{
"variant_set": "B1234_1",
"variants": [
{
"code": "S",
"variant_type": "size",
"description": "Small
},
{
"code": "M",
"variant_type": "size",
"description": "Medium
},
{
"code": "L",
"variant_type": "size",
"description": "Large
}
]
},
{
"variant_set": "B1234_2",
"variants": [
{
"code": "RD",
"variant_type": "color",
"description": "Small
},
{
"code": "GR",
"variant_type": "color",
"description": "Green
},
{
"code": "YL",
"variant_type": "color",
"description": "Yellow
}
]
}
]
}
到目前为止,我拥有的棉花糖模式:
class ProductToOptionSchema(ma.ModelSchema):
variants = ma.Nested(OptionSchema, many=True)
class Meta:
model = ProductToOption
class ProductSchema(ma.ModelSchema):
variant_sets = ma.Nested(ProductToOptionSchema, many=True)
class Meta:
model = Product
当我尝试这段代码时:
product = Product.query.filter_by(skuid="B1234").first()
product_schema = ProductSchema()
result = product_schema.jsonify(product)
我得到的错误是:
TypeError: 'Option' object is not iterable
产品通过辅助表与变体(选项)相关。我到目前为止的模型是:
产品
-----------------------------
| skuid | name |
-----------------------------
| B1234 | Test Product 1 |
-----------------------------
| B1235 | Test Product 2 |
-----------------------------
class Product(db.Model):
__tablename__ = 'products'
skuid = db.Column(db.String(16), primary_key=True)
name = db.Column(db.String(128))
variants = db.relationship("Option", secondary="products_to_options")
products_to_options
------------------------
| skuid | variant_set |
------------------------
| B1234 | B1234_1 |
------------------------
| B1234 | B1234_2 |
------------------------
| B1235 | B1235_1 |
------------------------
class ProductToOption(db.Model):
__tablename__ = 'products_to_options'
skuid = db.Column(db.String(16), db.ForeignKey('products.skuid'), nullable=False)
variant_set = db.Column(db.String(16), db.ForeignKey('options.variant_set'), nullable=False)
products = db.relationship('Product', foreign_keys="ProductToOption.skuid")
variants = db.relationship('Option', foreign_keys="ProductToOption.variant_set")
选项
-----------------------------------------------------
| variant_set | code | variant_type | description |
-----------------------------------------------------
| B1234_1 | S | size | Small |
-----------------------------------------------------
| B1234_1 | M | size | Medium |
-----------------------------------------------------
| B1234_1 | L | size | Large |
-----------------------------------------------------
| B1234_2 | RD | color | Red |
-----------------------------------------------------
| B1234_2 | GR | color | Green |
-----------------------------------------------------
| B1234_2 | YL | color | Yellow |
-----------------------------------------------------
| B1235_1 | OK | wood | Oak |
-----------------------------------------------------
| B1235_1 | CH | wood | Cherry |
-----------------------------------------------------
class Option(db.Model):
__tablename__ = 'options'
variant_set = db.Column(db.String(16), nullable=False)
code = db.Column(db.String(8), nullable=False)
variant_type = db.Column(db.String(16), nullable=False)
description = db.Column(db.String(16), nullable=False)
product = db.relationship("Product", secondary="products_to_options")
解决方案
想我想通了。我修改了表之间的 SQLAlchemy 关系并删除了“辅助”属性。现在的关系是:
skuid -> variant_set -> 变种
这似乎奏效了。或者至少,我得到了我想要的输出。
class Product(db.Model):
__tablename__ = 'products'
skuid = db.Column(db.String(16), primary_key=True)
name = db.Column(db.String(128))
variant_sets = db.relationship("ProductToOption")
class ProductToOption(db.Model):
__tablename__ = 'products_to_options'
id = db.Column(db.INTEGER, primary_key=True)
skuid = db.Column(db.String(36), db.ForeignKey('products.skuid'), nullable=False)
options_code = db.Column(db.String(36), nullable=False)
variants = db.relationship('Option')
class Option(db.Model):
__tablename__ = 'options'
variant_set = db.Column(db.String(16), db.ForeignKey('products_to_options.options_code'), nullable=False)
code = db.Column(db.String(8), nullable=False)
variant_type = db.Column(db.String(16), nullable=False)
description = db.Column(db.String(16), nullable=False)
推荐阅读
- c++ - 减少抖动应该重点关注哪个部分?
- sockets - 为什么 BlueZ 中有两种不同的套接字选项(RFCOMM 和 L2CAP)?
- javascript - DOM 选择器在 Vanilla JavaScript 中给出 null 但在 jquery 中工作
- c++ - 如何将外部库链接到 msvc cl 编译器
- javascript - Javascript UTC日期不同的值
- c# - Asp.Net Core 5.0 中 EntityState.Detached 的空异常
- spring-boot - 在 Maven 或 Spring Boot 项目中手动添加 JAR 文件
- xamarin.forms - Xamarin Essentials Google 登录 Auth,发给我 AuthToken 工作只需一小时
- javascript - 当 id 为字符串时,jstree disable_node 不起作用
- javascript - 为什么 howSum 解决方案在 Javascript 中有效,但在 Python 中无效?(动态编程)