· 6 years ago · Mar 25, 2019, 06:20 PM
1import sqlite3, datetime
2
3with sqlite3.connect("BMS.db") as db:
4 cursor = db.cursor()
5 sql = """CREATE Table IF NOT EXISTS BookInformation(
6 BookID integer PRIMARY KEY AUTOINCREMENT,
7 bookName text,
8 Genre text,
9 DateReleased text,
10 ReadingLevel integer,
11 Barcode text,
12 Size integer,
13 ISBN text
14 ); """
15 cursor.execute(sql)
16
17 #FOR STUDENT INFOATION
18 sql = """CREATE Table IF NOT EXISTS StudentInformation(
19 StudentID integer PRIMARY KEY AUTOINCREMENT,
20 FirstName text,
21 SurName text,
22 Form text
23 ); """
24 cursor.execute(sql)
25
26 #FOR BOOK RENTING SERVICES
27 sql = """CREATE Table IF NOT EXISTS StudentBookInformation(
28 transactionID integer PRIMARY KEY AUTOINCREMENT,
29 BookID integer,
30 StudentID integer,
31 DateOUT text,
32 DateIN text,
33 returned boolean,
34 FOREIGN KEY (BookID) REFERENCES BookInformation(BookID),
35 FOREIGN KEY (StudentID) REFERENCES StudentInformation(StudentID)
36 ); """
37 cursor.execute(sql)
38
39
40 #Add a table for the records of books being rentened so that the origional table can be taken out
41
42 '''Sam's todo
43 1. Get data in database (this could be made up if you can't get the data from Mr Johnson before Friday)
44 2. Write function to add student info
45 - accepts a name, form and email (prefferably validated) and writes that to the database
46 3. free entry of SQL to test'''
47
48def studentInfomationValidation(): #Add pass funciton to this funciton, WORKS
49 valid = False
50
51
52 print('Does the students have a student ID number?')
53 print("If Yes, enter 'Y' to input the ID number, or enter 'N' if there isnt am ID and you want one to be allocated to the inserted book")
54
55 while valid == False:
56 valid = False
57 addStudentID = passInfo('>>> ')
58 addStudentID = addStudentID.upper()
59
60
61 if addStudentID == 'Y':
62 valid = True
63 print('Please enter the students personal ID number: ')
64 studentID = validateNumber('>>> ')
65
66
67 elif addStudentID == 'N':
68 valid = True
69 studentID = ""
70
71 else:
72 print('That is not an option, please Try again...')
73 valid = False
74 while not valid:
75 FirstName = input('First Name of Student: ')
76 fValid = validateName(FirstName)
77 SurName = input('Surname of student: ')
78 sValid = validateName(SurName)
79 if FirstName and SurName: #Number version of .isaplhpa
80 valid = True
81
82 else:
83 print("Invaid Input, Names dont contain numbers (unless your agent 47), try again")
84
85 valid = False
86
87 while not valid:
88 yearGroup = validateNumber('Year Group: ')
89 if (yearGroup >= 7) and (yearGroup <= 13):
90 valid = True
91 else:
92 print('Error, Year Group is out of range')
93
94 valid = False
95 while not valid:
96 print('e.g A for Ash')
97 formGroup = input('Form: ')
98 if (yearGroup < 12) and (formGroup in ['A','B', 'C', 'E', 'M', 'W', 'O', 'R']):
99 valid = True
100 elif (yearGroup > 11) and (formGroup in ['A','B','C','D','E','F']):
101 valid = True
102
103 else:
104 print('Error, That was not a valid form group')
105
106 tutor = str(yearGroup)+formGroup
107
108 #valdiate email
109
110 with sqlite3.connect("BMS.db")as db:
111 cursor = db.cursor()
112 try:
113 studentID.isdigit()
114 sql = "INSERT INTO StudentInformation VALUES (null,\"{}\",\"{}\",\"{}\")".format(FirstName,SurName,tutor)
115 except AttributeError:
116 sql = "INSERT INTO StudentInformation VALUES ({},\"{}\",\"{}\",\"{}\")".format(studentID,FirstName,SurName,tutor) #solve student ID problem, allow them to enter ID
117 cursor.execute(sql)
118
119#needs to return validated results or write to database here
120#return name, tutor, etc
121def validateNumber(message):
122 valid = False
123 while not valid:
124 try:
125 temp = int(input(message))
126 valid = True
127 except ValueError:
128 print("That is not a number please enter a number")
129 return temp
130
131def validateName(name):
132 valid = True
133 for letter in name:
134 if not letter.isalpha():
135 if letter not in [" ","-"]: #list of characters you will allow i.e. " " - accented letters
136 valid = False
137 return valid
138
139def validateDate(): #Works
140 valid = False
141 while not valid:
142 print('Syntax is e.g January = 01 and November = 11')
143 month = int(input('What Month: '))
144 if month > 0 and month < 13:
145 valid = True
146 else:
147 ("That is an invalid month please try again")
148
149 print('Day of the month = e.g: 1,2...11,29,30')
150
151 daysInMonth = [31,29,31,30,31,30,31,31,30,31,20,31] #Number of days in each month for each respective month.
152
153 Valid = False
154
155 while Valid == False:
156 day = validateNumber('Day of the Month: ')
157 if (day <= (daysInMonth[month-1])) and (day > 0):
158 Valid = True
159 else:
160 print('Date is out of range of that month, please try again')
161
162
163 print('format: e.g "18" = 2018')
164 yearInput = validateNumber('Year')
165 year = 2000+yearInput
166 dateOUT = str(day)+'/'+str(month)+'/'+str(year)
167 print(dateOUT)
168
169#email = input() #Validate email against school email database, could be a future imporvement
170#if the user wants to add another account ADD
171
172def BookInformation():
173 #studentInfomationValidation()
174 with sqlite3.connect("BMS.db")as db:
175 cursor = db.cursor()
176
177 print("Type 'pass' at anytime to skip entering infomation")
178
179 bookName = passInfo("Book Title: ")
180
181 print('Does the book have a dedicated local ID number?')
182 print("If Yes, enter 'Y' to input the ID number, or enter 'N' if there isnt am ID and you want one to be allocated to the inserted book")
183 valid = False
184 while valid == False:
185
186 bookid_ques = passInfo('>>>' )
187 bookid_ques = bookid_ques.upper()
188
189
190 if bookid_ques == 'Y':
191 valid = True
192 print('Please enter the books personal ID number: ')
193 bookID = validateNumber('>>> ')
194 '''try and except'''
195
196
197 elif bookid_ques == 'N':
198 valid = True
199 bookID = ""
200
201 else:
202 print('That is not an option, please Try again...')
203
204 print('Please enter a vaild Genre..')
205 Genre = passInfo('>>> ')
206 #Validate genre against database
207
208 print('Please enter the year that the book was released...')
209 dateReleased = validateNumber('>>> ')
210
211
212 print('Please enter the reading level')
213 valid = False
214 while valid == False:
215 readingLevel = passInfoInt('>>> ')
216 if (readingLevel > 0) and (readingLevel < 10): #Check reading level
217 print('Value accepted')
218 valid = True
219 else:
220 print("Value out of 'reading level' range, please try again")
221
222
223 barcode_true = False
224 while not barcode_true:
225 barcode_inp = passInfo('barcode:')
226 barcode_true = True
227 for digit in barcode_inp:
228 if digit.isalpha():
229 barcode_true = False
230 if not barcode_true:
231 print('Error, this barcode contains a letter, please Try again')
232
233 print('Please enter the size of the book in pages')
234 valid = False
235 while valid == False:
236 size = passInfoInt('>>> ')
237 if (size < 0):
238 print('That is not a valid input, please try again')
239 else:
240 valid = True
241
242 print('Please input the ISBN of the book')
243 valid = False
244 while valid == False:
245 ISBN = passInfoInt('>>>')
246 if (len(str(abs(ISBN))) != 13):
247 valid = False
248 print('Invalid, too few digits, Please Try again... ')
249 else:
250 valid = True
251
252 #valdiation, iterate though the string and see if there are any letters, is so falaise. .isalpha()
253 #add inputs here Sam (listed below)
254
255 #BookID, Genre, DateReleased, ReadingLevel, Barcode text, Size, ISBN
256 """CREATE Table IF NOT EXISTS BookInformation(
257 BookID integer PRIMARY KEY AUTOINCREMENT,
258 bookName text,
259 Genre text,
260 DateReleased text,
261 ReadingLevel integer,
262 Barcode text,
263 Size integer,
264 ISBN text); """
265
266 with sqlite3.connect("BMS.db")as db:
267 cursor = db.cursor()
268 if bookID == "":
269 sql = "INSERT INTO BookInformation VALUES (null,\"{}\",\"{}\",\"{}\",{},\"{}\",{},\"{}\")".format(bookName,Genre,dateReleased,readingLevel,barcode_inp,size,ISBN)
270 else:
271 sql = "INSERT INTO BookInformation VALUES ({},\"{}\",\"{}\",\"{}\",{},\"{}\",{},\"{}\")".format(bookID,bookName,Genre,dateReleased,readingLevel,barcode_inp,size,ISBN)
272
273 print(sql)
274 cursor.execute(sql)
275
276def passInfo(message):
277 value = input(message)
278 if value.lower() == 'pass':
279 return ""
280 else:
281 return value
282
283def passInfoInt(message):
284 valid = False
285 value = input(message)
286 while not valid:
287 try:
288 if value.lower() == 'pass':
289 return ""
290 else:
291 valid = True
292 except AttributeError:
293 pass
294 valid = False
295 while not valid:
296 try:
297 value = int(value)
298 return value
299 except ValueError:
300 print("Error please type pass or a number")
301 value = passInfoInt(message)
302
303
304"""def studentInfomation():
305 name, form, email = studentInfomationValidation()
306 with sqlite3.connect("BMS.db")as db:
307 cursor = db.cursor()
308 sql = "INSERT INTO StudentInformation VALUES (null,\"{}\",\"{}\",\"{}\")".format(name,form,email) #solve student ID problem, allow them to enter ID
309 cursor.execute(sql)"""
310
311
312# ADD RESULT TO THE DATABASE
313
314def writeSQL():
315 with sqlite3.connect("BMS.db") as db:
316 cursor = db.cursor()
317 sql = input()
318 cursor.execute(sql)
319 result = cursor.fetchall()
320 for each in result:
321 print(each)
322
323def searching():
324 print('Enter a search item')
325 search_item = input('>>> ')
326
327 if search_item.isdigit():
328 '''list of attributes within studentInformationTable
329 check if input is in list
330 then build SQL'''
331 with sqlite3.connect("BMS.db") as db:
332 cursor = db.cursor()
333 sql = 'select * FROM StudentInformation WHERE studentName LIKE \"{}\"'.format(search_item)
334 cursor.execute(sql)
335
336def bookRentingOUT():
337
338
339 #input student name, form
340 #what do you want the students to search by name / form?
341 #valdiate with .isaplha
342
343 #searching the database for whatever is enter in sql
344
345 ''' with sqlite3.connect("BMS.db") as db:
346 cursor = db.cursor()
347 sql = 'select * FROM StudentInformation \"{}\"'.format(search_item)
348 cursor.execute(sql)'''
349
350 #Searching for student via surname to initate a book rental
351 print('Search for student via surname')
352 SurName = input('>>> ')
353 with sqlite3.connect("BMS.db") as db:
354 cursor = db.cursor()
355
356 sql = 'select * FROM StudentInformation WHERE SurName LIKE \"{}\"'.format(SurName) #Desplys all infos on kids that are returned with the same surname
357 cursor.execute(sql)
358 result = cursor.fetchall()
359 for record in result:
360 print(record)
361
362 print('enter the Student ID number who is taking the book out')
363 studentID = input('>>> ')
364
365 print('enter the Book ID number to be taken out')
366 print('Enter "L" to lookup all books')
367 bookID = input('>>> ')
368 if bookID == 'L':
369 sql = 'select * FROM BookInformation'
370 cursor.execute(sql)
371 result = cursor.fetchall()
372 for record in result:
373 print(record)
374 print('enter the Book ID number for the book that is being taken out')
375 bookID = input(">>> ")
376
377 #Ouput results
378
379 print('Take a current time stamp? "Y" for yes, "N" to manually alocate a date.')
380 TimeStamp = passInfo('>>> ')
381 valid = False
382 while valid ==False:
383
384 if TimeStamp.upper() == 'Y':
385 dateOUT = str(datetime.date.today().strftime("%d/%m/%y"))
386 valid = True
387 elif TimeStamp.upper() == 'N':
388 day = int(input('Day of the Month: '))
389 month = input('What Month: ')
390 print('if you want a key, type: "Y"')
391 month = month.upper
392 if month == 'Y': #FINISH
393 print('1 = January')
394 yearInput = validateNumber('Year')
395 print('format: e.g "18" = 2018')
396 year = 2000+yearInput
397 dateOUT = str(day)+'/'+str(month)+'/'+str(year)
398 valid = True
399
400 else:
401 print('Input not valid please try again...')
402 valid = False
403
404 #allocate transaction ID
405 returned = 0
406 #return data to database
407
408 with sqlite3.connect("BMS.db") as db:
409 cursor = db.cursor()
410 sql = "INSERT INTO StudentBookInformation VALUES (null,{},{},\"{}\",null,{})".format(studentID,bookID,dateOUT,returned)
411 print(sql)
412 cursor.execute(sql)
413
414def bookIN():
415 print("Search by student ID Number, please ensure that this student has been registerd to be currently renting a book")
416 print("Or lookup all students who are currently renting a book, press 'L'")
417 valid = False
418 while not valid:
419 searchBy = input('>>> ')
420 if searchBy == 'L':
421 '''output transactions currently in place'''
422 sql = 'select * FROM StudentBookInformation WHERE returned = 0'
423 with sqlite3.connect("BMS.db") as db:
424 cursor = db.cursor()
425 cursor.execute(sql)
426 result = cursor.fetchall()
427 for i in result:
428 print(i)
429 print('Eneter the StudentID')
430 studentID = input('>>> ')
431 valid = True
432 else:
433 try:
434 int(searchBy)
435 valid = True
436 studentID = searchBy
437 except ValueError:
438 print("That is not a number please enter a number")
439
440 sql = 'select * FROM StudentBookInformation WHERE StudentID = {} AND returned = 0'.format(studentID)
441
442
443 with sqlite3.connect("BMS.db") as db:
444 cursor = db.cursor()
445 cursor.execute(sql)
446 result = cursor.fetchall()
447 for i in result:
448 print(i)
449
450 print('Enter the transactionID ')
451 transactionID = validateNumber('>>> ')
452
453 print('Taking date of return ')
454 print("Press '1' to take a time stamp now or press '2' to enter (manully) a different date")
455 valid = False
456 while valid == False:
457 choice = passInfoInt('>>> ')
458 if choice == 1:
459 dateINnow = str(datetime.date.today().strftime("%d/%m/%y"))
460 valid = True
461 elif choice == 2:
462 day = validateNumber('Day of the Month: ')
463 month = input('What Month: ')
464 print('if you want a key, type: "Y"')
465 month = month.upper
466 if month == 'Y': #FINISH
467 print('1 = January')
468 yearInput = validateNumber('Year')
469 print('format: e.g "18" = 2018')
470 year = 2000+yearInput
471 dateINnow = str(day)+'/'+str(month)+'/'+str(year)
472 valid = True
473 else:
474 print('That is an invalid option, please try again')
475 valid = False
476
477 with sqlite3.connect("BMS.db") as db:
478 cursor = db.cursor()
479 sql = "UPDATE StudentBookInformation SET returned = 1 WHERE transactionID ={}".format(transactionID)
480
481 cursor.execute(sql)
482 sql = "UPDATE StudentBookInformation SET DateIN = \"{}\" WHERE transactionID = {}".format(dateINnow,transactionID)
483 cursor.execute(sql)