在Flask中具有附加字段的多对多关联表

huangapple
go评论105阅读模式
英文:

Many-to-many join table with additional field in Flask

问题

  1. 我有两个表，`Products` 和 `Orders`，在我的 Flask-SqlAlchemy 设置中，它们相互关联，一个订单可以有多个产品：
  3. ```python
  4. class Products(db.Model):
  5.     id = db.Column(db.Integer, primary_key=True)
  6.     ....
  8. class Orders(db.Model):
  9.     guid = db.Column(db.String(36), default=generate_uuid, primary_key=True)
  10.     products = db.relationship(
  11.         "Products", secondary=order_products_table, backref="orders")
  12.     ....

通过以下方式关联：

  1. order_products_table = db.Table("order_products_table",
  2.     db.Column('orders_guid', db.String(36), db.ForeignKey('orders.guid')),
  3.     db.Column('products_id', db.Integer, db.ForeignKey('products.id'))
  4.     # db.Column('license', dbString(36))
  5. )

对于我的需求，订单中的每个产品都将获得一个唯一的许可证字符串，逻辑上应该将其添加到每个订单中的产品的 order_products_table 行中。

如何声明在连接表 order_products_table 上的第三个 license 列，以便在插入订单时填充它？

  2. <details>
  3. <summary>英文:</summary>
  5. I have two tables, `Products` and `Orders`, inside my Flask-SqlAlchemy setup, and they are linked so an order can have several products:

class Products(db.Model):
id = db.Column(db.Integer, primary_key=True)
....

class Orders(db.Model):
guid = db.Column(db.String(36), default=generate_uuid, primary_key=True)
products = db.relationship(
"Products", secondary=order_products_table, backref="orders")
....

  2. linked via:

order_products_table = db.Table("order_products_table",
db.Column('orders_guid', db.String(36), db.ForeignKey('orders.guid')),
db.Column('products_id', db.Integer, db.ForeignKey('products.id'))
# db.Column('license', dbString(36))
)

  2. For my purposes, each product in an order will receive a unique license string, which logically should be added to the `order_products_table` rows of each product in an order.
  4. How do I declare this third `license` column on the join table `order_products_table` so it gets populated it as I insert an Order?
  6. </details>
  9. # 答案1
  10. **得分**: 2
  12. 我后来找到了SQLAlchemy文档中关于[Association Object](https://docs.sqlalchemy.org/en/13/orm/basic_relationships.html#association-object)的文档，它允许对连接表进行扩展。 
  14. **更新后的设置:**
  16. ```python
  17. # 不再使用表，提供一个具有额外字段和显式键和back_populates的JOIN表模型：
  18. class OrderProducts(db.Model):
  19.     __tablename__ = 'order_products_table'
  20.     orders_guid = db.Column(db.String(36), db.ForeignKey(
  21.         'orders.guid'), primary_key=True)
  22.     products_id = db.Column(db.Integer, db.ForeignKey(
  23.         'products.id'), primary_key=True)
  25.     order = db.relationship("Orders", back_populates="products")
  26.     products = db.relationship("Products", back_populates="order")
  28.     licenses = db.Column(db.String(36), nullable=False)
  30. class Products(db.Model):
  31.     id = db.Column(db.Integer, primary_key=True)
  32.     order = db.relationship(OrderProducts, back_populates="order")
  33.     ....
  35. class Orders(db.Model):
  36.     guid = db.Column(db.String(36), default=generate_uuid, primary_key=True)
  37.     products = db.relationship(OrderProducts, back_populates="products")
  38.     ....

真正棘手的部分（但也在文档页面上显示）是如何插入数据。在我的情况下，操作如下：

  1. o = Orders(...) # 插入其他数据
  2. for id in products:
  3.     # 创建带有额外数据（如licenses）的OrderProducts连接行
  4.     join = OrderProducts(licenses="Foo")
  5.     # 向JOIN添加产品
  6.     join.products = Products.query.get(id)
  7.     # 将填充的JOIN添加为订单产品
  8.     o.products.append(join)
  10. # 最后提交到数据库
  11. db.session.add(o)
  12. db.session.commit()

我最初试图直接填充Order.products（或示例代码中的o.products），但这将导致错误，因为它期望一个OrderProducts类而不是Products类。

我还遇到了字段命名和引用back_populates的问题。再次注意，上面的示例以及文档中都展示了这一点。请注意，复数形式的命名完全取决于您希望字段如何命名。

英文:

I've since found the documentation for the Association Object from the SQLAlchemy docs, which allows for exactly this expansion to the join table.

Updated setup:

  1. # Instead of a table, provide a model for the JOIN table with additional fields 
  2. # and explicit keys and back_populates:
  3. class OrderProducts(db.Model):
  4.     __tablename__ = &#39;order_products_table&#39;
  5.     orders_guid = db.Column(db.String(36), db.ForeignKey(
  6.         &#39;orders.guid&#39;), primary_key=True)
  7.     products_id = db.Column(db.Integer, db.ForeignKey(
  8.         &#39;products.id&#39;), primary_key=True)
  10.     order = db.relationship(&quot;Orders&quot;, back_populates=&quot;products&quot;)
  11.     products = db.relationship(&quot;Products&quot;, back_populates=&quot;order&quot;)
  13.     licenses = db.Column(db.String(36), nullable=False)
  15. class Products(db.Model):
  16.     id = db.Column(db.Integer, primary_key=True)
  17.     order = db.relationship(OrderProducts, back_populates=&quot;order&quot;)
  18.     ....
  20. class Orders(db.Model):
  21.     guid = db.Column(db.String(36), default=generate_uuid, primary_key=True)
  22.     products = db.relationship(OrderProducts, back_populates=&quot;products&quot;)
  23.     ....

What is really tricky (but also shown on the documentation page), is how you insert the data. In my case it goes something like this:

  1. o = Orders(...) # insert other data
  2. for id in products:
  3.     # Create OrderProducts join rows with the extra data, e.g. licenses
  4.     join = OrderProducts(licenses=&quot;Foo&quot;)
  5.     # To the JOIN add the products 
  6.     join.products = Products.query.get(id)
  7.     # Add the populated JOIN as the Order products
  8.     o.products.append(join)
  10. # Finally commit to database
  11. db.session.add(o)
  12. db.session.commit()

I was at first trying to populate the Order.products (or o.products in the example code) directly, which will give you an error about using a Products class when it expects a OrderProducts class.

I also struggled with the whole field naming and referencing of the back_populates. Again, the example above and on the docs show this. Note the pluralization is entirely to do with how you want your fields named.

huangapple
  • 本文由 发表于 2020年1月3日 19:21:43
  • 转载请务必保留本文链接：https://go.coder-hub.com/59577714.html
  • flask
  • flask-sqlalchemy
  • many-to-many
  • python
  • sqlalchemy
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定