4NwakNPp

1import sqlite3
2
3def create_table(db_name, table_name, sql):
4    with sqlite3.connect(db_name) as db:
5        cursor = db.cursor()
6        cursor.execute("select name from sqlite_master where name=?",(table_name,))
7        result = cursor.fetchall()
8        keep_table = True
9        if len(result) == 1:
10            response = input("\"{0}\" already exists, recreate (y/n)?".format(table_name))
11            if response == "y":
12                keep_table = False
13                print("Table \"{0}\" has been recreated, all data is lost".format(table_name))
14                cursor.execute("drop table if exists {0}".format(table_name))
15                db.commit()
16            else:
17                print("The table was kept.")
18        else:
19            keep_table = False
20        if not keep_table:
21            cursor.execute(sql)
22            db.commit()
23
24def delete_product(data):
25    with sqlite3.connect("coffee_shop.db")as db:
26        cursor = db.cursor()
27        sql = "delete from Product where Name=?"
28        cursor.execute(sql,data)
29        db.commit()
30
31def insert_data(values):
32    with sqlite3.connect("coffee_shop.db") as db:
33        cursor = db.cursor()
34        sql = "insert into Product (Name, Price) values(?,?)"
35        cursor.execute(sql,values)
36        db.commit()
37
38def select_all_products():
39    with sqlite3.connect("coffee_shop.db") as db:
40        cursor = db.cursor()
41        cursor.execute("select * from Product")
42        products = cursor.fetchall()
43        return products
44
45def select_product(id):
46    with sqlite3.connect("coffee_shop.db") as db:
47        cursor = db.cursor()
48        cursor.execute("select * from Product where ProductId=?",(id,))
49        product = cursor.fetchone()
50        return product
51
52def update_product(data):
53    with sqlite3.connect("coffee_shop.db")as db:
54        cursor = db.cursor()
55        sql = "update Product set Name=?, Price=? where ProductID=?"
56        cursor.execute(sql,data)
57        db.commit()
58
59if __name__ == "__main__":
60    print("===========================================================")
61    print("=== Welcome to the Coffee Shop Database Management Tool ===")
62    print("===========================================================")
63    print("Please select an option:")
64    print()
65    print("\t 1) - Create/Recreate table")
66    print("\t 2) - Delete an item from the database")
67    print("\t 3) - Insert an item into the database")
68    print("\t 4) - Select all items from the database")
69    print("\t 5) - Select a specific item from the database")
70    print("\t 6) - Update an item in the database")
71    print()
72    while True:
73        c = input("Enter anything else to quit:") #change c to choice
74        
75        if c == "1":
76            db_name = "coffee_shop.db"
77            sql ="""create table Product
78                    (ProductID integer,
79                    Name text,
80                    Price real,
81                    primary key(ProductID))"""
82            create_table(db_name, "Product", sql)
83            
84        elif c == "2":
85            data = ("Latte",)
86            delete_product(data)
87            
88
89        elif c == "3":
90            product = ("Espresso",1.5)
91            insert_data(product)
92            product = ("Latte",1.35)
93            insert_data(product)
94            product = ("Mocha",2.4)
95            insert_data(product)
96            product = ("Green Tea",1.2)
97            insert_data(product)
98            product = ("Black Tea",1.0)
99            insert_data(product)
100            product = ("Americano",1.5)
101            insert_data(product)
102
103        elif c == "4":
104            products = select_all_products()
105            print(products)
106
107        elif c == "5":    
108            for n in range(5):
109                product = select_product(n+1)
110                print(product)
111
112        elif c == "6":
113            data = ("Latte", 2.45, 1)
114            update_product(data)
115
116        else:
117            quit()