· 8 years ago · Jul 16, 2018, 01:46 PM
1#Import MySQL module
2import _mysql
3
4def addStudent():
5 #Ask for new credentials for entry
6 name = input("Please enter the new students full name: ")
7 phone = input("Please enter the new students phone number: ")
8 email = input("Please enter the new students email address: ")
9
10 #Format the query
11 query1 = 'INSERT INTO students VALUES ("' + name + '",0,"' + phone + '","' + email + '")'
12
13 #Perform the query
14 normalquery(query1)
15
16 #Format the query to get the new students' id
17 query2 = 'SELECT id FROM students WHERE name = "' + name + '"
18
19 #Perform the query
20 x = returnquery(query2)
21
22 #Print the current student id
23 print("Your student id is: " + x)
24 print("Please write this id down!")
25
26
27
28
29def addClass():
30 #Ask for new credentials for entry
31 name = input("Please enter the new classes name: ")
32 teacher = input("Please enter the new classes teacher: ")
33 roomnum = input("Please enter the new classes room number: ")
34 teacheremail = input("Please enter the new teachers email address: ")
35
36 #Format the query
37 query = 'INSERT INTO classes VALUES ("' + name + '",0,"' + teacher + '","' + roomnum + '","' + teacheremail + '")'
38
39 #Perform the query
40 normalquery(query)
41
42 #Format the query to get the new students' id
43 query2 = 'SELECT id FROM classes WHERE name = "' + name + '"'
44
45 #Perform the query
46 x = returnquery(query2)
47
48 #Print the current student id
49 print("Your class id is: " + x)
50 print("Please write this id down!")
51
52def addEnrollment():
53 #Ask for new credentials for entry
54 studentid = int(input("Please enter the students ID number: "))
55 classid = int(input("Please enter the classes ID number: "))
56 term = input("Please enter the term: ")
57 grade = input("Please enter the grade the student received: ")
58
59 #Format the query to check if student id exists
60 query1 = 'SELECT id FROM students WHERE id = ' + str(studentid)
61
62 #Check if id exists
63 x = returnquery(query1)
64 if not x:
65 print("Invalid student ID of: " + str(studentid))
66 print("Please add the student before adding to enrollment")
67 return
68
69 #Format the query to check if class id exists
70 query2 = 'SELECT id FROM classes WHERE id = ' + str(classid)
71
72 #Check if id exists
73 y = returnquery(query2)
74 if not y:
75 print("Invalid class ID of: " + str(classid))
76 print("Please add the class before adding to enrollment")
77 return
78
79
80 #Format the query
81 query = 'INSERT INTO enrollment VALUES (' + str(studentid) + ',' + str(classid) + ',"' + term + '","' + grade + '")'
82
83 #Perform the query
84 normalquery(query)
85 print("Enrollment added")
86
87
88
89def editStudent():
90 #Ask for ID
91 studentid = int(input("Please enter the students ID number: "))
92
93 #Format the query to check if id exists
94 query2 = 'SELECT id FROM students WHERE id = ' + str(studentid)
95
96 #Check if id exists
97 x = returnquery(query2)
98 if not x:
99 print("Invalid ID of: " + str(studentid))
100 return
101
102 #Ask for credentials
103 name = input("Please enter the students new full name: ")
104 phone = input("Please enter the students new phone number: ")
105 email = input("Please enter the students new email adress: ")
106
107
108 #Format the query
109 query1 = 'UPDATE students SET name = "' + name + '", phone = "' + phone + '", email = "' + email + '" WHERE id = ' + str(studentid)
110
111
112 #Perform the query
113 normalquery(query1);
114
115
116
117def editClass():
118 #Ask for ID
119 classid = int(input("Please enter the class ID number: "))
120
121 #Format the query to check if id exists
122 query2 = 'SELECT id FROM classes WHERE id = ' + str(classid)
123
124 #Check if id exists
125 x = returnquery(query2)
126 if not x:
127 print("Invalid ID of: " + str(classid))
128 return
129
130 #Ask for credentials
131 name = input("Please enter the new class name: ")
132 teacher = input("Please enter the new class teacher: ")
133 roomnum = input("Please enter the new class room number: ")
134 teacheremail = input("Please enter the new teacher's email address: ")
135
136
137 #Format the query
138 query1 = 'UPDATE classes SET name = "' + name + '", teacher = "' + teacher + '", roomnum = "' + roomnum + '", email = "' + teacheremail + '" WHERE id = ' + str(classid)
139
140 #Perform the query
141 normalquery(query1);
142
143def editEnrollment():
144 #Ask for ID
145 studentid = int(input("Please enter the students ID number: "))
146
147 #Format the query to check if id exists
148 query2 = 'SELECT studentid FROM enrollment WHERE studentid = ' + str(studentid)
149
150 #Check if id exists
151 x = returnquery(query2)
152 if not x:
153 print("Invalid ID of: " + str(studentid))
154 return
155
156 #Ask for credentials
157 studentid = int(input("Please enter the new student ID: "))
158 classid = int(input("Please enter the new classid: "))
159 term = input("Please enter the new term: ")
160 grade = input("Please enter the new grade: ")
161
162 #Format the query
163 query1 = 'UPDATE enrollment SET studentid = ' + str(studentid) + ', classid = ' + str(classid) + ', term = "' + term + '", grade = "' + grade + '" WHERE studentid = ' + str(studentid)
164
165 #Perform the query
166 normalquery(query1);
167
168
169
170def delStudent():
171 #Ask for ID
172 studentid = int(input("Please enter the students ID number: "))
173
174 #Format the query to check if id exists
175 query1 = 'SELECT id FROM students WHERE id = ' + str(studentid)
176
177 #Check if id exists
178 x = returnquery(query1)
179 if not x:
180 print("Invalid ID of: " + str(studentid))
181 return
182 print("-----------------------------------------------")
183 user_choice = input("Are you sure you want to delete this student? This will also remove them from the enrollment table (Y/N)")
184
185 if user_choice == 'Y' or user_choice == 'y':
186
187 #Format the query to delete from students table
188 query2 = 'DELETE FROM students WHERE id = ' + str(studentid)
189
190 #Perform the query to delete from students
191 normalquery(query2)
192
193 #Format the query to delete from enrollment table
194 query3 = 'DELETE FROM enrollment WHERE studentid = ' + str(studentid)
195
196 #Perform the query to delete from enrollment
197 normalquery(query3)
198
199 print("Student deleted")
200 else:
201 print("Student record with ID " + str(studentid) + " not deleted")
202
203
204
205def delClass():
206 #Ask for ID
207 classid = int(input("Please enter the class ID number: "))
208
209 #Format the query to check if id exists
210 query1 = 'SELECT id FROM classes WHERE id = ' + str(classid)
211
212 #Check if id exists
213 x = returnquery(query1)
214 if not x:
215 print("Invalid ID of: " + str(classid))
216 return
217 print("-----------------------------------------------")
218 user_choice = input("Are you sure you want to delete this class? This will also remove any records for studens enrolled in this class from the enrollment table (Y/N)")
219
220 if user_choice == 'Y' or user_choice == 'y':
221
222 #Format the query to delete from students table
223 query2 = 'DELETE FROM classes WHERE id = ' + str(classid)
224
225 #Perform the query to delete from students
226 normalquery(query2)
227
228 #Format the query to delete from enrollment table
229 query3 = 'DELETE FROM enrollment WHERE classid = ' + str(classid)
230
231 #Perform the query to delete from enrollment
232 normalquery(query3)
233
234 print("Class deleted")
235 else:
236 print("Class record with ID " + str(classid) + " not deleted")
237
238def delEnrollment():
239 #Ask for student ID
240 studentid = int(input("Please enter the students ID number: "))
241
242 #Ask for class ID
243 classid = int(input("Please enter the class ID number: "))
244
245 #Format the query to check if student id exists
246 query1 = 'SELECT * FROM enrollment WHERE classid = ' + str(classid) + 'AND studentid = ' + str(studentid)
247
248
249 #Check if id exists
250 x = returnquery(query1)
251 if not x:
252 print("Invalid ID given")
253 return
254 print("-----------------------------------------------")
255 user_choice = input("Are you sure you want to delete this enrollment record? (Y/N)")
256
257 if user_choice == 'Y' or user_choice == 'y':
258
259 #Format the query to delete from students table
260 query2 = 'DELETE FROM enrollment WHERE classid = ' + str(classid) + 'AND studentid = ' + str(studentid)
261
262 #Perform the query to delete from students
263 normalquery(query2)
264
265 print("Enrollment deleted")
266
267 else:
268 print("Enrollment record with student ID: " + str(studentid) + " and class ID: " + str(classid) + " not deleted")
269
270def findStudent():
271 #Ask for student ID
272 studentid = int(input("Please enter the students ID number: "))
273
274 #Format the query to check if id exists
275 query1 = 'SELECT id FROM students WHERE id = ' + str(studentid)
276
277 #Check if id exists
278 x = returnquery(query1)
279 if not x:
280 print("Invalid ID of: " + str(studentid))
281 return
282
283 #Format the query to display all information on students table
284 query2 = 'SELECT * FROM students WHERE id = ' + str(studentid)
285
286 #Format the query to display all information on enrollment table
287 query3 = 'SELECT * FROM enrollment WHERE studentid = ' + str(studentid)
288
289 #Retrieve values
290 y = returnquery(query2)
291 z = returnquery(query3)
292
293 #Decode from byte array to string and retrieve values from the query
294 name = y[0][0].decode()
295 phone = y[0][2].decode()
296 email = y[0][3].decode()
297
298 #Amount of classes student is in
299 classAmount = len(z)
300
301 print("***************************")
302 print("Student name: " + name)
303 print("Student phone number: " + phone)
304 print("Student email: " + email)
305 print()
306
307 #Print all student records for each class
308 for i in range(classAmount):
309 print("Class #" + str(i + 1))
310
311 classid = z[i][1]
312 term = z[i][2].decode()
313 grade = z[i][3].decode()
314
315 print("Class ID: " + classid)
316 print("Term: " + term)
317 print("Grade: " + grade)
318 print()
319
320 print("***************************")
321
322
323def findClass():
324 #Ask for ID
325 classid = int(input("Please enter the class ID number: "))
326
327 #Format the query to check if id exists
328 query1 = 'SELECT id FROM classes WHERE id = ' + str(classid)
329
330 #Check if id exists
331 x = returnquery(query1)
332 if not x:
333 print("Invalid ID of: " + str(classid))
334 return
335
336 #Format the query to check all classes with id
337 query2 = 'SELECT * FROM classes where id = ' + str(classid)
338
339 #Format the query to find all students within a class
340 query3 = 'SELECT * from enrollment where classid = '+ str(classid)
341
342 #Retrieve values
343 y = returnquery(query2)
344 z = returnquery(query3)
345
346 #Decode from byte array to string and retrieve values from the query
347 name = y[0][0].decode()
348 teacher = y[0][2].decode()
349 roomnum = y[0][3].decode()
350 email = y[0][4].decode()
351
352 #Amount of students in class
353 studentAmount = len(z)
354
355 print("***************************")
356 print("Class name: " + name)
357 print("Class teacher: " + teacher)
358 print("Class room number: " + roomnum)
359 print("Class teacher email: " + email)
360 print()
361
362 #Print all student records for each class
363 for i in range(studentAmount):
364 print("Student #" + str(i + 1))
365
366 studentid = z[i][0]
367 term = z[i][2].decode()
368 grade = z[i][3].decode()
369
370 print("Student ID: " + studentid)
371 print("Term: " + term)
372 print("Grade: " + grade)
373 print()
374
375 print("***************************")
376
377
378
379
380
381def checkOption(option):
382 while(
383 option != 1 and
384 option != 2 and
385 option != 3 and
386 option != 4 and
387 option != 5 and
388 option != 6 and
389 option != 7 and
390 option != 8 and
391 option != 9 and
392 option != 10 and
393 option != 11 and
394 option != 12
395 ):
396
397 print("Invalid option of: " + str(option))
398 option = printoptions()
399
400def printoptions():
401 print("-----------------------------------------------")
402 print("(1): Add student")
403 print("(2): Add class")
404 print("(3): Add student to class")
405 print("(4): Edit student")
406 print("(5): Edit class")
407 print("(6): Edit enrollment")
408 print("(7): Delete student")
409 print("(8): Delete class")
410 print("(9): Remove student from class")
411 print("(10): Find student")
412 print("(11): Find class")
413 print("(12): Initialize DB")
414 print("-----------------------------------------------")
415 user_choice = int(input("Please select your option: "))
416 return user_choice
417
418def returnquery(query):
419 #Performs a single query and return something
420 #Used for viewing data already present in DB
421
422 #Perform the query
423 conn.query(query)
424
425 #Save the result
426 result = conn.store_result()
427
428 #Return it
429 return result.fetch_row()
430
431
432
433
434def normalquery(query):
435 #Performs a single query without returning anything
436 #Used for adding new data to the DB
437
438 #Perform the query
439 conn.query(query)
440
441
442def init():
443 #Create all necessary tables
444 #Use only ONCE at DB creation
445
446 #Create 'students' table
447 conn.query("""CREATE TABLE IF NOT EXISTS students (
448 name VARCHAR(20),
449 id INT(12) AUTO_INCREMENT,
450 phone VARCHAR(15),
451 email VARCHAR(30),
452 PRIMARY KEY (id)
453 )ENGINE=InnoDB""")
454
455
456 #Create 'classes' table
457 conn.query("""CREATE TABLE IF NOT EXISTS classes (
458 name VARCHAR(20),
459 id INT(12) AUTO_INCREMENT,
460 teacher VARCHAR(20),
461 roomnum VARCHAR(5),
462 email VARCHAR(30),
463 PRIMARY KEY (id)
464 )ENGINE=InnoDB""")
465
466 #Create 'enrollment' table
467 conn.query("""CREATE TABLE IF NOT EXISTS enrollment (
468 studentid INT(12),
469 classid INT(12),
470 term VARCHAR(5),
471 grade VARCHAR(5)
472 )ENGINE=InnoDB""")
473
474
475#Connect to DB
476if __name__ == "__main__":
477
478 conn = _mysql.connect(host="mysql.wpi.edu",user="dtavana",passwd="YSc778",db="frontiersfinal")
479
480 while(1==1):
481 option = printoptions()
482 checkOption(option)
483 if(option == 1):
484 addStudent()
485 print("Student added")
486 if(option == 2):
487 addClass()
488 print("Class added")
489 if(option == 3):
490 addEnrollment()
491 if(option == 4):
492 editStudent()
493 if(option == 5):
494 editClass()
495 if(option == 6):
496 editEnrollment()
497 if(option == 7):
498 delStudent()
499 if(option == 8):
500 delClass()
501 if(option == 9):
502 delEnrollment()
503 if(option == 10):
504 findStudent()
505 if(option == 11):
506 findClass()
507 if(option == 12):
508 init()