具有多个多对多关系的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屋！