eFQj8Vd9

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