具有多个多对多关系的SQLAlchemy [英] SQLAlchemy with multiple Many to Many Relationships
问题描述
我想用SQLAlchemy为配方创建数据库,但是我不确定我的方法是否正确.如何将数据插入到recipe_ingredient表中?
I would like create a database for recipes with SQLAlchemy, however I am not sure if my approach is correct. How can I insert data to the recipe_ingredient table?
我的方法:
食谱有一个名字,可以有多种成分.一种成分由数量,单位和名称(成分表)组成,例如500毫升水.
A recipe has a name and can have multiple ingredients. One ingredient consists of an amount, an unit and a name (ingredients table), for example 500 ml water.
餐桌食谱 -ID,主键 - 姓名 (一种食谱可以包含多种成分,而一种成分可以包含在多种食谱中)
Table recipe - id, primary key - name (one recipe can have multiple ingredients and one ingredient can be in multiple recipes)
表格配方成分
- foreignkey(recipe.id)
- foreignkey(amounts.id)
- foreignkey(units.id)
- foreignkey(ingredients.id)
表格金额
- id,主键
- 金额(例如500)
表格单位
- id,主键
- 单位(例如ml)
餐桌配料
- id,主键
- 成分(例如水)
代码:
recipe_ingredient = db.Table('recipe_ingredient',
db.Column('idrecipe', db.Integer, db.ForeignKey('recipe.id')),
db.Column('idingredient', db.Integer, db.ForeignKey('ingredients.id')),
db.Column('idunit', db.Integer, db.ForeignKey('units.id')),
db.Column('idamount', db.Integer, db.ForeignKey('amounts.id'))
)
class Recipe(db.Model):
id= db.Column(db.Integer, primary_key=True, autoincrement=True)
name= db.Column(db.VARCHAR(70), index=True)
ingredient= db.relationship("Ingredients", secondary=recipe_ingredient, backref='recipe')
amounts = db.relationship("Amounts", secondary=recipe_ingredient, backref='recipe')
units= db.relationship("Units", secondary=recipe_ingredient , backref='recipe')
class Ingredients(db.Model):
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
ingredient = db.Column(db.VARCHAR(200))
class Units(db.Model):
id= db.Column(db.Integer, primary_key=True, autoincrement=True)
unit= db.Column(db.VARCHAR(45), nullable=False)
class Amounts(db.Model):
id= db.Column(db.Integer, primary_key=True, autoincrement=True)
amount= db.Column(db.VARCHAR(45), nullable=False)
更新:
class RecipeIngredient(db.Model):
__tablename__ = 'recipe_ingredient'
recipe_id = db.Column(db.Integer, db.ForeignKey('recipe.id'), primary_key=True)
ingredient_id = db.Column(db.Integer, db.ForeignKey('ingredient.id'), primary_key=True)
amount = db.Column(db.Integer, db.ForeignKey('amount.id'))
unit = db.Column(db.Integer, db.ForeignKey('unit.id'))
recipes = relationship("Recipe", back_populates="ingredients")
ingredients = relationship("Ingredient", back_populates="recipes")
class Recipe(db.Model):
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
name = db.Column(db.VARCHAR(70), index=True)
ingredients = relationship("RecipeIngredient", back_populates="recipes")
class Ingredient(db.Model):
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
name = db.Column(db.VARCHAR(200))
recipes = relationship("RecipeIngredient", back_populates="ingredients")
class Unit(db.Model):
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
name = db.Column(db.VARCHAR(45), nullable=False)
class Amount(db.Model):
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
number = db.Column(db.VARCHAR(45), nullable=False)
我添加了以下对象
pizza = Recipe(name='Pizza')
flour = Ingredient(name='Flour')
water = Ingredient(name='Water')
g = Unit(name='g')
ml = Unit(name='ml')
a500 = Amount(number='500')
a100 = Amount(number='100')
r1= RecipeIngredient(recipe_id=pizza.id, ingredient_id=flour.id, amount=a500.id, unit=g.id)
r2= RecipeIngredient(recipe_id=pizza.id, ingredient_id=water.id, amount=a100.id, unit=ml.id)
披萨成分的结果:
[<RecipeIngredient 1, 1>, <RecipeIngredient 1, 3>]
要使用以下哪种方法来获取成分名称,我需要添加什么模型:
pizza.ingredients[0].name
What do I have to add to that model to get the name of an ingredient with:
pizza.ingredients[0].name
推荐答案
因此,您的关系定义和多对多recipe_ingredient
表很好.您可以使用自己拥有的代码来完成您想做的事情.您有一些样式上的问题,使代码难以阅读.
So, your relationship definitions and your many-to-many recipe_ingredient
table is Fine. You can do what you want to do with the code you have. You have a few stylistic issues that make your code harder to read than it should be.
让我们先了解一下您拥有的功能:
Let's take a look at the functionality you have first:
Recipe
对象将具有一个类似于列表的ingredient
属性.您可以在其上附加Ingredients
对象,调用它时,您将拥有Ingredients
的常规Python列表:
Recipe
objects will have an ingredient
attribute that acts like a list. You can append Ingredients
objects to it, and when you call it you'll have a regular Python list of Ingredients
:
# Make a cake,
cake = Recipe(name='Cake')
flour = Ingredients(ingredient='Flour')
eggs = Ingredients(ingredient='Eggs')
cake.ingredient.append(flour)
cake.ingredient.append(eggs)
for i in cake.ingredient:
print(i.ingredient)
因为您已经定义了Recipe.amount
的secondary
关系,在secondary=recipe_ingredient
中,SQLAlchemy拥有了为您管理多对多关系所需的所有信息.
Because you already defined the Recipe.amount
's secondary
relationship, where secondary=recipe_ingredient
, SQLAlchemy has all the information it needs to manage the many-to-many relationship for you.
Ingredients
对象将具有recipe
属性,该属性的作用类似于列表,并引用相同的关系:
Ingredients
objects will have a recipe
attribute that acts like a list, and references the same relationships:
# Find recipes using flour
for r in flour.recipe:
print(r.name)
您甚至可以向配方中添加配方,而不是向配方中添加配方,并且效果相同:
You can even add recipes to an ingredient, rather than adding ingredients to a recipe, and it will work just the same:
# Make cookies
cookies = Recipe(name='Cookies')
eggs.recipe.append(cookies)
for i in cookies.ingredient:
print(i.ingredient)
阅读方式
您可能已经注意到,您对事物的命名方式使它读起来有些笨拙.当任何属性引用一对多关系时,使用复数时会更清楚.例如,如果Recipe
中的成分关系实际上被称为ingredients
而不是ingredient
,则其读起来会更好.这将使我们遍历cake.ingredients
.反之亦然:调用backref recipes
而不是配方将使flour.recipes
引用多个链接的配方变得更加清楚,其中flour.recipe
可能会引起误解.
How it reads
You may have noticed that the way you're naming things makes it read a little clunkily. When any attribute is referencing a one-to-many relationship, it's a lot clearer when a plural is used. For instance, the ingredients relationship in Recipe
would read a lot more nicely if it was actually called ingredients
rather than ingredient
. That would let us iterate through cake.ingredients
. The same goes in the reverse direction: calling the backref recipes
instead of recipe will make it a lot clearer that flour.recipes
refers to multiple linked recipes, where flour.recipe
might be a little misleading.
关于您的对象是复数还是单数也存在不一致之处. Recipe
是单数,但Ingredients
是复数.坦率地说,关于哪种样式正确的观点并不普遍-我更喜欢在我的所有模型Recipe
,Ingredient
,Amount
,Unit
中使用单数形式-但这仅仅是我自己.选择一个样式并坚持下去,而不是在它们之间切换.
There's also inconsistency about whether your objects are plural or singular. Recipe
is singular, but Ingredients
is plural. Honestly, opinions on which is the correct style aren't universal - I prefer using the singular for all my models, Recipe
, Ingredient
, Amount
, Unit
- but that's just me. Pick a single style and stick to it, rather than switching between them.
最后,您的属性名称有点多余. Ingredients.ingredient
有点多. Ingredient.name
更有意义,而且也更清楚.
Lastly, your attribute names are a little redundant. Ingredients.ingredient
is a bit much. Ingredient.name
makes a lot more sense, and it's clearer too.
这里还有另外一件事-在我看来,您想要存储有关配方/成分关系的其他信息,即成分的数量和单位.您可能需要2个鸡蛋或500克面粉,而我想这就是您的Amounts
和Units
表的用途.在这种情况下,这些附加信息是您关系中的关键部分,您可以尝试在关联表中直接对其进行叙述,而不必尝试在单独的表中进行匹配.这需要更多的工作-SQLAlchemy文档更深入地介绍了如何使用
There's one additional thing here - it looks to me like you want to store additional information about your recipes/ingredients relationship, which is the amount and unit of an ingredient. You might want 2 eggs or 500 grams of flour, and I'm guessing that's what your Amounts
and Units
tables are for. In this case, this additional information is a key part of your relationship, and instead of trying to match it up in separate tables you can story it directly in your association table. This requires a bit more work - The SQLAlchemy docs go deeper into how to use an Association object to manage this additional data. Suffice it to say that using this pattern will make your code a lot cleaner and easier to manage long term.
@ user7055220不需要单独的Amount
或Unit
表,因为这些值仅在RecipeIngredient
关系中具有含义.我将删除这些表,然后将RecipeIngredient
中的amount
和unit
属性更改为直接存储单位/金额的直接String
和Integer
值:
@user7055220 in my opinion you don't need the separate Amount
or Unit
tables, because these values only have meaning as part of theRecipeIngredient
relationship. I would remove those tables, and change the amount
and unit
attributes in RecipeIngredient
to be straight String
and Integer
values that store the unit/amount directly:
class RecipeIngredient(db.Model):
__tablename__ = 'recipe_ingredient'
recipe_id = db.Column(db.Integer, db.ForeignKey('recipe.id'), primary_key=True)
ingredient_id = db.Column(db.Integer, db.ForeignKey('ingredient.id'), primary_key=True)
amount = db.Column(db.Integer)
unit = db.Column(db.VARCHAR(45), nullable=False)
recipes = relationship("Recipe", back_populates="ingredients")
ingredients = relationship("Ingredient", back_populates="recipes")
在任一情况下,要回答有关如何访问成分值的问题-在您的示例中,pizza.ingredients
现在包含一个关联对象数组.成分是该关联对象的子代,可以通过其ingredients
属性进行访问.如果进行我上面建议的更改,则可以直接访问unit
和amount
值.访问该数据将如下所示:
In either case, to answer your question of how to access the ingredient values - in your example pizza.ingredients
now contains an array of association objects. The ingredient is a child of that association object, and can be accessed via its ingredients
attribute. You could access the unit
and amount
values directly if you make the change I suggested above. Accessing that data would look like this:
for i in pizza.ingredients:
print(i.amount) # This only works if you're storing the amount directly in the association proxy table as per my example above
print(i.unit) # Same goes for this
print(i.ingredients.name) # here, the ingredient is accessed via the "ingredients" attribute of the association object
或者,如果您只想使用所询问的语法:
Or, if you just want to use the syntax you were asking about:
print(pizza.ingredients[0].ingredients.name)
关于命名的另一件事:请注意,当关联对象中的backref对象仅映射到单个成分时,它被称为ingredients
,因此它应为单数-则上面的示例为pizza.ingredients[0].ingredient.name
,听起来好一点
One more thing on naming: Notice here that the backref objects in your association object is called ingredients
when it only ever maps to a single ingredient, so it should be singular - then the above example would be pizza.ingredients[0].ingredient.name
, which sound a little better
这篇关于具有多个多对多关系的SQLAlchemy的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!