在sqlite 3(python)中的表内创建表 [英] Creating a table inside a table in sqlite 3 (python)

问题描述

我正在尝试为食谱存储程序创建一个 sqlite 数据库，该数据库在食谱表中保存人数、成分、烹饪温度和烹饪时间.我打算在成分部分内创建另一个表，以存储id"(将自动递增)；质量数(例如，200)；质量类型(例如，g)和食物类型(例如，面粉).

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.

它在第一个 cursor.execute 行崩溃了，说这是一个语法错误...

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!

谢谢，瑞安:)

推荐答案

使用两张表，一张是食谱，一张是配料，并为每个配料记录其所属配方的 ID:

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

要将一种成分添加到配方中，只需在该表中插入一条记录(这里，42 是配方的 ID):

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

这篇关于在sqlite 3(python)中的表内创建表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！