Recursive CTE

recursive_cte_bom.py

import sqlite3

import pandas as pd

conn = sqlite3.connect(':memory:')
c = conn.cursor()

c.execute("""
          CREATE TABLE food_labour (
              name TEXT,
              price FLOAT
          )
          """)

c.execute("""
          CREATE TABLE recipe (
              food TEXT,
              ingredient TEXT, 
              quantity FLOAT
          )
          """)
conn.commit()

c.executemany("""
              INSERT INTO food_labour VALUES (?, ?)
              """, [['cake', 4], 
                    ['egg', 8], 
                    ['frosting', 15], 
                    ['milk', 13], 
                    ['sugar', 23]]
              )

c.executemany("""
              INSERT INTO recipe VALUES (?, ?, ?)
              """, [['cake', 'egg', 7], 
                    ['cake', 'frosting', 9], 
                    ['cake', 'milk', 1], 
                    ['frosting', 'egg', 2], 
                    ['frosting', 'milk', 0.5],
                    ['frosting', 'sugar', 3],
                    ['something', 'something_else', 5]]
              )

conn.commit()

c.execute("""
          WITH RECURSIVE ingredients (food, ingredient, quantity) AS (
              SELECT food, ingredient, quantity
              FROM recipe
              WHERE food = ?
    
              UNION ALL
              
              SELECT recipe.food, 
                     recipe.ingredient, 
                     ingredients.quantity * recipe.quantity AS quantity
              FROM recipe, ingredients
              WHERE recipe.food = ingredients.ingredient
          )
          SELECT MIN(ingredients.ingredient) AS ingredient,
                 SUM(ingredients.quantity) AS quantity,
                 MIN(food_labour.price * ingredients.quantity) AS labour_cost
          FROM ingredients
          JOIN food_labour ON food_labour.name = ingredients.ingredient
          GROUP BY ingredient
          """, ('cake',))
cols = [item[0] for item in c.description]
df = pd.DataFrame(c.fetchall(), columns=cols)
print(df.head(20))

c.close()
conn.close()