MQXAp0Jk

1import sqlite3
2
3
4def openDatabase():
5    with sqlite3.connect("CTMD.db")as db:
6        cursor = db.cursor()
7        
8        
9        sql = """
10        CREATE TABLE IF NOT EXISTS Meats(
11        meatName TEXT PRIMARY KEY);
12        """
13        cursor.execute(sql)
14        
15        sql = """
16        CREATE TABLE IF NOT EXISTS Cuts(
17        cutName TEXT PRIMARY KEY);
18        """
19        cursor.execute(sql)
20        
21        sql = """
22        CREATE TABLE IF NOT EXISTS Methods(
23        methodName TEXT PRIMARY KEY);
24        """
25        cursor.execute(sql)
26        
27        sql = """
28        CREATE TABLE IF NOT EXISTS Meatcuts(
29        MeatcutsID INTEGER PRIMARY KEY AUTOINCREMENT,
30        meatName TEXT,
31        cutName TEXT,
32        FOREIGN KEY (meatName) REFERENCES Meats(meatName),
33        FOREIGN KEY (cutName) REFERENCES Cuts(cutName));
34        """
35        cursor.execute(sql)
36        
37        sql = """
38        CREATE TABLE IF NOT EXISTS Recipe(
39        recipeID INTEGER PRIMARY KEY AUTOINCREMENT,
40        MeatcutsID INTEGER,
41        methodName TEXT,
42        temperature INTEGER,
43        time INTEGER,
44        FOREIGN KEY (MeatcutsID) REFERENCES Meatcuts(MeatcutsID),
45        FOREIGN KEY (methodName) REFERENCES Methods(methodName));
46        """
47        cursor.execute(sql)
48        
49        meats = ["Chicken", "Beef", "Pork", "Lamb"]
50        methods = ["Boiling (Pot)", "Roasting (Oven)", "Grilling (Grill)"]
51        chicken = ["Breast", "Thigh", "Drumstick", "Wing"]
52        beef = ["Chuck", "Shank", "Brisket", "Beef Rib", "Short Plate", "Flank", "Beef Loin", "Beef Sirloin", "Beef Round"]
53        pork = ["Pork Rib", "Leg (Ham)", "Chump Chop", "Pork Belly", "Pork Loin", "Bacon", "Pork Fillet", "Pork Cheek", "Pork Shoulder"]
54        lamb = ["Lamb shoulder", "Lamb Loin", "Lamb Rack", "Lamb Ribs", "Lamb Shank", "Leg of Lamb", "Lamb Sirloin"]
55        
56        for meat in meats:
57            sql = "INSERT INTO Meats VALUES(\"{}\");".format(meat)
58            cursor.execute(sql)
59            
60        for cut in chicken:
61            sql = "INSERT INTO Cuts VALUES(\"{}\");".format(cut)
62            cursor.execute(sql)
63            
64        for cut in beef:
65            sql = "INSERT INTO Cuts VALUES(\"{}\");".format(cut)
66            cursor.execute(sql)
67            
68        for cut in pork:
69            sql = "INSERT INTO Cuts VALUES(\"{}\");".format(cut)
70            cursor.execute(sql)
71            
72        for cut in lamb:
73            sql = "INSERT INTO Cuts VALUES(\"{}\");".format(cut)
74            cursor.execute(sql)
75            
76        for method in methods:
77            sql = "INSERT INTO Methods VALUES(\"{}\");".format(method)
78            cursor.execute(sql)
79            
80        for cut in chicken:
81            sql = "INSERT INTO Meatcuts VALUES(null,\"Chicken\",\"{}\");".format(cut)
82            cursor.execute(sql)
83
84        for cut in beef:
85            sql = "INSERT INTO Meatcuts VALUES(null,\"Beef\",\"{}\");".format(cut)
86            cursor.execute(sql)
87            
88        for cut in pork:
89            sql = "INSERT INTO Meatcuts VALUES(null,\"Pork\",\"{}\");".format(cut)
90            cursor.execute(sql)
91            
92        for cut in lamb:
93            sql = "INSERT INTO Meatcuts VALUES(null,\"Lamb\",\"{}\");".format(cut)
94            cursor.execute(sql)
95       
96
97def writeSQL():
98    with sqlite3.connect("CTMD.db") as db:
99        cursor = db.cursor()
100        sql = input()
101        cursor.execute(sql)
102        result = cursor.fetchall()
103        for each in result:
104            print(each)
105            
106def popRecipe():
107    myFile = open("meat.txt","r")
108    for row in myFile:
109        row = row.split(",")
110        with sqlite3.connect("CTMD.db")as db:
111            cursor = db.cursor()
112            sql = "SELECT MeatcutsID FROM Meatcuts WHERE meatName = \"{}\" AND cutName = \"{}\";".format(row[1],row[2])
113            print(sql)
114            cursor.execute(sql)
115            result = cursor.fetchall()
116            print(result[0][0])
117            methodlist = ["Boiling (Pot)", "Roasting (Oven)", "Grilling (Grill)"]
118            temp = 3
119            time = 4
120            for method in methodlist:
121                sql = "INSERT INTO Recipe VALUES(null,{},\"{}\",{},{});".format(result[0][0],method,row[temp],row[time])
122                print(sql)
123                cursor.execute(sql)
124                temp += 1
125                time += 1