CODEWITHSUNDEEP
pythonsqlitepython-2.7
codemonkeyz
codemonkeyz

Reputation: 35

Creating a table inside a table in sqlite 3 (python)

I'm trying to create a sqlite database for a recipe storing program, that holds in the recipe table, the number of people, ingredients, cooking temperature and cooking time. I intend on creating another table inside the ingredients part, to store the 'id' (which will auto increment); the mass number (for example, 200); the mass type (for example, g) and the food type (for example, flour).

I'm using this code to try to get the variables and to store them:

    def setIngredientsInRecipe(recipeName):
        cursor.execute("""INSERT INTO %s(ingredients) CREATE TABLE\
                    (ID INT PRIMARY KEY AUTO_INCREMENT, MassNumber VARCHAR(10), MassType VARCHAR(50) FoodType VARCHAR(50))""" % (recipeName))
        print ""
        massNoInput = raw_input("Please enter your first ingredient mass(just the number): ")
        massTypeInput = raw_input("Now please input the mass type (grams etc): ")
        foodInput = raw_input("Please enter your first ingredient type: ")

        cursor.execute("""INSERT INTO %s(ingredients(MassNumber)) VALUES('%s')""" % (recipeName,massNoInput))
        cursor.execute("""INSERT INTO %s(ingredients(MassType)) VALUES('%s')""" % (recipeName,massTypeInput))
        cursor.execute("""INSERT INTO %s(ingredients(FoodType)) VALUES('%s')""" % (recipeName,foodInput))

        return recipeName

Obviously I'm going to add some number checks in later, to see if the input is valid first.

It's crashing out on the first cursor.execute line, saying that it's a syntax error...

If anyone could explain to my how to fix this, I'd be most grateful, I've been looking at this bit of code for ages!

Thanks, Ryan :)

Upvotes: 2

Views: 8716

Answers (1)

CL.
CL.

Reputation: 180060

Use two tables, one for recipes, and one for ingredients, and have for each ingredient record the ID of the recipe it belongs to:

CREATE TABLE recipe(ID, People, CookingTime);
CREATE TABLE ingredient(ID, RecipeID, MassNumber, MassType, FoodType);

To add one ingredient to a recipe, just insert one record into that table (here, 42 is the ID of the recipe):

INSERT INTO ingredient(RecipeID, MassNumber, MassType, FoodType) VALUES (42, ...)

Upvotes: 2

Related Questions