· 4 years ago · Feb 21, 2021, 07:40 PM
1import sqlite3
2
3DB_FILE = "NetworkedDatabase.db"
4
5
6class DBClass:
7 def __init__(self):
8 self.conn = sqlite3.connect(DB_FILE)
9 self.cursor = self.conn.cursor()
10 self.column_names = ""
11 self.name = ""
12 self.column_sql = ""
13
14 def create_tables(self):
15 ####create_tables####
16 # Parameters:-None#
17 # Return Type:-None#
18 # Purpose:-This method creates the database if it#
19 # does not exist already#
20 #####################
21 self.cursor.execute('CREATE TABLE IF NOT EXISTS ' + self.name + ' (' + self.column_sql + ')')
22 self.conn.commit()
23
24 def select_data(self, clause):
25 ####select_data####
26 # Parameters:-clause:string#
27 # Return Type:-results:list#
28 # Purpose:-This method will select data from every#
29 # column in the instance of the class' table#
30 ###################
31 self.cursor.execute("SELECT " + self.column_names + " FROM " + self.name + " WHERE " + clause)
32 results = self.cursor.fetchall()
33 return results
34
35 def insert_data(self, values):
36 ####insert_data####
37 # Parameters:-values:String#
38 # Return Type:-None#
39 # Purpose:-This method will insert data into every#
40 # column in the instance of the class' table#
41 ###################
42 self.cursor.execute(
43 "INSERT INTO " + self.name + " (" + str(self.column_names) + ") " + "VALUES" + " (" + str(values) + ")")
44 self.conn.commit()
45
46 def update_data(self, values):
47 ####update_data####
48 # Parameters:-values:tuple#
49 # Return Type:-None#
50 # Purpose:-This method will update data for every#
51 # column in the instance of the class' table#
52 ###################
53 index = 0
54 for column in self.column_names:
55 self.cursor.execute("UPDATE " + self.name + " SET " + column + " = " + values[index])
56 self.conn.commit()
57 index += 1
58
59 def delete_data(self, clause):
60 ####delete_data####
61 # Parameters:-clause:String#
62 # Return Type:-None#
63 # Purpose:-This method will delete datd from every#
64 # column in the instance of the class' table where#
65 # a condition, this is the parameter clause#
66 ###################
67 print("DELETE from " + self.name + " WHERE " + clause)
68 self.cursor.execute("DELETE from " + self.name + " WHERE " + clause)
69 self.conn.commit()
70
71 def custom_query(self, query, command):
72 ####custom_query####
73 # Parameters:-query:String, command:String#
74 # Return Type:-results:list, l_i_v:integer#
75 # Purpose:-This method allows the server program#
76 # to run more complex queries, it also allows#
77 # only desired columns to be selected from the#
78 # database so the query passed in can produce more#
79 # specific results. The command parameter is used#
80 # so that the method can understand the intentions of#
81 # query and either commit changes, or fetch data etc#
82 # respectively.#
83 ###################
84 if command == "SELECT":
85 self.cursor.execute(query)
86 results = self.cursor.fetchall()
87 return results
88 elif command == "INSERT":
89 self.cursor.execute(query)
90 self.conn.commit()
91 l_i_v = self.cursor.lastrowid
92 return l_i_v
93 # l_i_v is the last row id from the insert statement#
94 # that would have just been executed. This is used#
95 # because when the program tries to insert orders, it#
96 # needs the primary key of the order to insert the#
97 # individual items into the linking table#
98 elif command == "UPDATE" or command == "DELETE":
99 self.cursor.execute(query)
100 self.conn.commit()
101
102
103# All of the classes are defined in terms of column_names, name#
104# and column_sql#. The column_names and name attributes are used #
105# in the methods inherited from the DBClass super class to perform#
106# SQL operations where the data wanted concerns all columns of the#
107# table. The name and column_sql attributes are used to create their#
108# respective database tables if they do not exist using the create_tables#
109# method#
110class Customers(DBClass):
111 def __init__(self):
112 super().__init__()
113 self.column_names = "CustomerName,CustomerPostcode,CustomerAddress,CustomerEmail"
114 self.name = "Customers"
115 self.column_sql = """CustomerID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
116 "CustomerName" TEXT, "CustomerPostcode" TEXT, "CustomerAddress" TEXT, "CustomerEmail" TEXT """
117 self.create_tables()
118
119
120class Staff(DBClass):
121 def __init__(self):
122 super().__init__()
123 self.column_names = "StaffName,StaffPosition,StaffContactNo,StaffEmergencyContactNo," \
124 "StaffBirthDate,StaffPostcode,StaffAddress,AmountOfSales, " \
125 "DateEmployed,DateUnemployed,Username,Password,LevelOfAccess"
126 self.name = "Staff"
127 self.column_sql = """"StaffID" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "StaffName" TEXT,
128 "StaffPosition" TEXT, "StaffContactNo" TEXT, "StaffEmergencyContactNo" TEXT,
129 "StaffBirthDate" TEXT, "StaffPostcode" TEXT, "StaffAddress" TEXT, "AmountOfSales" INTEGER,
130 "DateEmployed" TEXT, "DateUnemployed" TEXT, "Username" TEXT, "Password" TEXT, LevelOfAccess INTEGER """
131 self.create_tables()
132
133
134class Orders(DBClass):
135 def __init__(self):
136 super().__init__()
137 self.column_names = "OrderID,StaffID,CollectionOrDelivery," \
138 "DateOfSale,DateOfCollectionDelivery,Completed,CustomerID"
139 self.name = "Orders"
140 self.column_sql = """"OrderID" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "StaffID" INTEGER,
141 "CollectionOrDelivery" TEXT, "DateOfSale" TEXT, "DateOfCollectionDelivery" TEXT,
142 "Completed" TEXT, "CustomerID" INTEGER,
143 FOREIGN KEY ("StaffID") REFERENCES "Staff"("StaffID"),
144 FOREIGN KEY ("CustomerID") REFERENCES "Customers"("CustomerID")
145 ON UPDATE CASCADE"""
146 self.create_tables()
147
148
149class ProductsInOrder(DBClass):
150 def __init__(self):
151 super().__init__()
152 self.column_names = "OrderID,ProductID,NumberOfItemOrdered,ProductPriceWhenSale,BusinessCostWhenSale"
153 self.name = "ProductsInOrder"
154 self.column_sql = """"OrderID" INTEGER PRIMARY KEY, "ProductID" INTEGER PRIMARY KEY,
155 "NumberOfItemOrdered" INTEGER, "ProductPriceWhenSale" REAL, "BusinessCostWhenSale" REAL,
156 FOREIGN KEY ("OrderID") REFERENCES "Orders"("OrderID"),
157 FOREIGN KEY ("ProductID") REFERENCES "Stock"("ProductID")
158 ON UPDATE CASCADE"""
159 self.create_tables()
160
161
162class Stock(DBClass):
163 def __init__(self):
164 super().__init__()
165 self.column_names = "ProductName,AmountInStock,BusinessCostPerPack, " \
166 "NumberInAPack,CustomerCostPerItem,TypeOfProduct,SupplierID"
167 self.name = "Stock"
168 self.column_sql = """"ProductID" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "ProductName" TEXT,
169 "AmountInStock" INTEGER, "BusinessCostPerPack" REAL, "NumberInAPack" INTEGER,
170 "CustomerCostPerItem" REAL, "TypeOfProduct" TEXT, "SupplierID" INTEGER,
171 FOREIGN KEY ("SuppliersID") REFERENCES "Suppliers"("SupplierID")
172 ON UPDATE CASCADE"""
173 self.create_tables()
174
175
176class Suppliers(DBClass):
177 def __init__(self):
178 super().__init__()
179 self.column_names = "SupplierName,SupplierContactNumber"
180 self.name = "Suppliers"
181 self.column_sql = """"SupplierID" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "SupplierName" TEXT,
182 "SupplierContactNumber" TEXT"""
183 self.create_tables()
184
185
186def initialise_classes():
187 ####initialise_classes####
188 # Parameters:-None#
189 # Return Type:-db:DBClass object, cus:Customers object, sta:Staff object,#
190 # ord:Orders object, pin:ProductsInOrder object, sto:Stock object, sup:Suppliers object#
191 # Purpose:-To initialise all of the database classes and returning them from the#
192 # function, this is useful for when queries on multiple tables are going to be used#
193 # in a function.#
194 ##########################
195 db = DBClass()
196 cus = Customers()
197 sta = Staff()
198 ord = Orders()
199 pin = ProductsInOrder()
200 sto = Stock()
201 sup = Suppliers()
202 return db, cus, sta, ord, pin, sto, sup