SQLAlchemy with multiple Many to Many Relationships

Question

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?

My approach:

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.

Table recipe - id, primary key - name (one recipe can have multiple ingredients and one ingredient can be in multiple recipes)

table recipe_ingredient

• foreignkey(recipe.id)
• foreignkey(amounts.id)
• foreignkey(units.id)
• foreignkey(ingredients.id)

table amounts

• id, primary key
• amount (e.g. 500)

table units

• id, primary key
• unit (e.g. ml)

table ingredients

• id, primary key
• ingredient (e.g. water)

Code:

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)

UPDATE:

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)

I added the following objects

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)

Result of pizza.ingredients:

[<RecipeIngredient 1, 1>, <RecipeIngredient 1, 3>]

What do I have to add to that model to get the name of an ingredient with: pizza.ingredients[0].name

Answer 1

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.

How it works

Let's take a look at the functionality you have first:

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)

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

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.

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.

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.

One more thing

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.

Update

@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")

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)

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