· 8 years ago · Mar 19, 2018, 12:16 PM
1import sqlite3
2import sys, time
3
4
5def createConnection():
6 """ create a database connection to the SQLite database
7 specified by db_file
8 :param db_file: database file
9 :return: Connection object or None
10 """
11 try:
12 return sqlite3.connect(db_file)
13 except:
14 return False
15
16def clearScreen():
17 print("\n" * 50)
18
19
20
21
22def init():
23 try:
24 cursor = conn.cursor()
25
26 cursor.execute('''
27 CREATE TABLE IF NOT EXISTS users(
28 userID INTEGER PRIMARY KEY,
29 username VARCHAR(20) NOT NULL,
30 firstname VARCHAR(20) NOT NULL,
31 surname VARCHAR(20) NOT NULL,
32 age INTEGER(2) NOT NULL,
33 yeargroup VARCHAR(10) NOT NULL,
34 password VARCHAR(20) NOT NULL);
35 ''')
36
37 cursor.execute('''
38 CREATE TABLE IF NOT EXISTS topics(
39 topicID INTEGER PRIMARY KEY,
40 topicName VARCHAR(30) NOT NULL);
41 ''')
42
43 cursor.execute('''
44 CREATE TABLE IF NOT EXISTS questions(
45 questionID INTEGER PRIMARY KEY,
46 topicID INTEGER NOT NULL,
47 question VARCHAR(100) NOT NULL,
48 option1 VARCHAR(50),
49 option2 VARCHAR(50),
50 option3 VARCHAR(50),
51 option4 VARCHAR(50),
52 answer VARCHAR(50),
53 FOREIGN KEY(topicID) REFERENCES topics(topicID));
54 ''')
55
56 cursor.execute('''
57 CREATE TABLE IF NOT EXISTS scores(
58 scoreID INTEGER PRIMARY KEY,
59 userID INTEGER NOT NULL,
60 topicID INTEGER NOT NULL,
61 score INTEGER NOT NULL,
62 difficulty VARCHAR(20),
63 grade VARCHAR(5),
64 FOREIGN KEY(userID) REFERENCES users(userID),
65 FOREIGN KEY(topicID) REFERENCES topics(topicID));
66 ''')
67
68# cursor.execute('''
69# SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name;
70# ''')
71#
72# print(cursor.fetchall())
73
74
75 except:
76 print("Couldn't create the tables in {}".format(db_file))
77
78
79def login():
80 cursor = conn.cursor()
81
82# username = input("Enter your username >> ")
83# password = input("Enter your password >> ")
84
85 username = input("input username: ")
86 password = input("input password: ")
87
88 find_user = ('SELECT * FROM users WHERE username = ? AND password = ?')
89 cursor.execute(find_user, [(username),(password)])
90 results = cursor.fetchall()
91
92 if results:
93 for user in results:
94 print("Welcome ", user[2], user[3])
95 time.sleep(1)
96 return user[0]
97 else:
98 return False
99
100def getGrade(score):
101 if score == 0:
102 grade = "E"
103 elif score <= 25:
104 grade = "D"
105 elif score <= 50:
106 grade = "C"
107 elif score <= 75:
108 grade = "B"
109 elif score <= 100:
110 grade = "A"
111 else:
112 grade = "U"
113
114 return grade
115
116def getDifficultyLevelName(difficulty):
117 if difficulty == 1:
118 difficultyLevelName = "Easy"
119 elif difficulty <= 2:
120 difficultyLevelName = "Medium"
121 elif difficulty <= 3:
122 difficultyLevelName = "Hard"
123 else:
124 difficultyLevelName = "Unknown"
125
126 return difficultyLevelName
127
128def getQuestionOptions(question, difficulty):
129 rightAnswerOption = question[7] # option number of the right answer
130 rightAnswerIndex = int(rightAnswerOption) + 2 # index number of the right answer
131
132 howManyOptions = difficulty + 1 #2 options for Easy, 3 options for Medium and 4 options for Hard
133
134 if howManyOptions == 2:
135 return ("1. {} \n2. {}\n".format(question[3],question[4]))
136 elif howManyOptions == 3:
137 return ("1. {} \n2. {} \n3. {} \n".format(question[3],question[4],question[5]))
138 else:
139 return ("1. {} \n2. {} \n3. {} \n4. {} \n".format(question[3],question[4],question[5],question[6]))
140
141
142# allows a user to select a topic and difficulty rating (Easy, Medium or Hard) and asks five
143# questions on that topic:
144# a. ‘Easy’ mode has a choice of two answers for each question
145# b. ‘Medium’ mode has a choice of three answers for each question
146# c. ‘Hard’ mode has a choice of four answers for each question
147# 4. loads the questions and answers from a file stored externally to the game.
148# 5. displays the user’s score, percentage and grade achieved for that quiz.
149
150
151def generateComputerQuestions():
152 cursor = conn.cursor()
153
154 cursor.execute('INSERT INTO topics (topicName) VALUES("History"), ("Music"), ("Computer Science");')
155 conn.commit()
156
157
158 cursor.execute ('''
159 INSERT INTO questions (topicID, question, option1, option2, option3, option4, answer) VALUES
160 ("1", "What type of storage is a Memory Stick?", "Solid State", "Magnetic", "Optical", "Volatile", "1"),
161 ("1", "What type of storage is a CD?", "Solid State", "Magnetic", "Optical", "Volatile","3"),
162 ("1", "What type of storage is a Hard Disk Drive?", "Solid State", "Magnetic", "Optical", "Volatile", "1"),
163 ("1", "What type of storage is a SSD?", "Solid State" , "Magnetic", "Optical", "Volatile", "1"),
164 ("2", "What is described as a network in one small geographical area?", "Ring" , "LAN", "Star", "WAN", "2"),
165 ("2", "What is described as a network in a large geographical area?", "Ring", "LAN", "Star", "WAN", "4"),
166 ("2", "Which topology requires a terminator?", "Bus", "Ring", "Star", "Mesh", "1"),
167 ("2", "What type of software is most likely to be free?", "Open Source", "Proprietary", "Utility", "System", "1"),
168 ("3", "What type of software is Automatic update?", "Open Source", "Proprietary", "Utility", "System", "3"),
169 ("3", "What type of software is an Operating System?", "Open Source", "Proprietary", "Utility", "System", "4"),
170 ("3", "Which of the following is sensitive data?", "DOB", "Name", "Political Opinion", "Address", "3"),
171 ("3", "What law covers sensitive data?", "Data Protection Act", "Copyright,Designs and Patents", "Computer Misuse", "Freedom of Information", "1");
172 ''')
173 conn.commit()
174
175
176# gives Fergus the option to generate and output the following reports:
177# a. a report that allows Fergus to choose a username, and outputs all of the quizzes
178# that they have taken, and the grade for each of those quizzes.
179# b. a report that outputs for a selected topic and difficulty: the average score achieved,
180# the highest score achieved, and the user details of the person that achieved the
181# highest score.
182
183
184def generateUserName(firstname, age):
185 try:
186 cursor = conn.cursor()
187 username_taken = True
188
189 while username_taken:
190 username = firstname[:3] + str(age)
191 print("Auto generated username: {}".format(username))
192
193 find_user = ('SELECT * FROM users WHERE username = ?')
194 cursor.execute(find_user, [(username)])
195
196 if cursor.fetchall():
197 print("Username taken.")
198 else:
199 username_taken = False
200 except:
201 print("Couldn't generate unique username.")
202
203 finally:
204 return username
205
206def Generate():
207 generateComputerQuestions()
208
209
210
211
212
213
214
215def register():
216 print("Sign up")
217
218 firstname = input("Please enter your first name >> ")
219 lastname = input("Please enter your last name >> ")
220 age = int(input("Enter your age: "))
221 while (age<10) and (age>19) :
222 print("invalid")
223 age = input("what is ur age")
224
225
226
227 username = generateUserName(firstname, age)
228 yeargroup = input("Please enter your year group >> ")
229 pwd1 = input("Please enter a password >> ")
230 pwd2 = input("Please re-enter your password >> ")
231
232 while pwd1 != pwd2:
233 print("Passwords did not match...")
234 pwd1 = input("Please enter a password >> ")
235 pwd2 = input("Please re-enter your password >> ")
236
237 insert_data = '''
238 INSERT INTO users(username, firstname, surname, age, yeargroup, password)
239 VALUES(?,?,?,?,?,?)'''
240
241 cursor = conn.cursor()
242 cursor.execute(insert_data, [(username), (firstname), (lastname), (age), (yeargroup), (pwd1)])
243 conn.commit()
244
245 print("New account created")
246 input("Press Enter to go back to main menu")
247
248
249
250
251
252
253def report():
254 pass
255
256
257def showMainMenu():
258 print(" " *10, "MAIN MENU")
259 print("-" * 32)
260 print('Please choose one of the following options \n')
261 print("1. Register")
262 print("2. Login")
263 print("3. Exit")
264
265
266def showUserMenu():
267 print(" " *10, "USER MENU")
268 print("-" * 32)
269 print('Please choose one of the following options \n')
270 print("1. History quiz")
271 print("2. Music quiz")
272 print("3. Computer Science quiz")
273 print("4. Show my scores")
274 print("5. Report")
275 print("6. Log out")
276
277
278def takeQuiz(userID, topicID):
279# loadQuestionsAnswers(topic, difficulty)
280# runQuiz()
281# storeResults()
282# displayResults()
283 cursor = conn.cursor()
284
285 cursor.execute("SELECT * FROM topics WHERE topicID = ?;", [(topicID)])
286 topicName = cursor.fetchall()
287 clearScreen()
288 print(topicName)
289 #print("{} QUIZ\n".format(topicName[0][1]).upper())
290
291 difficulty = int(input("Difficulty Level\n1. Easy\n2. Medium\n3. Hard\n>> "))
292 print()
293
294 score = 0
295 topicID = difficulty
296 cursor.execute("SELECT * FROM questions WHERE topicID = ?;", [(topicID)])
297 questions = cursor.fetchall()
298 #print(questions)
299 numberOfQuestions = 0 #used to help work out the score / percentage
300
301 for question in questions:
302 print("Question: {}".format(question[2]))
303 print(getQuestionOptions(question, difficulty +1))
304
305 choice = input("Answer >> ")
306 rightAnswerOption = question[7] # option number of the right answer
307 rightAnswerIndex = int(rightAnswerOption) + 2 # index number of the right answer
308
309 if choice == rightAnswerOption:
310 print("Correct.\n")
311 score += 1
312 time.sleep(1)
313 print()
314 else:
315 print("Incorrect. \n")
316 print("Right answer: {}".format(question[rightAnswerIndex]))
317 time.sleep(1)
318 input()
319
320 numberOfQuestions += 1
321
322 score = int((score / numberOfQuestions))
323 grade = getGrade(score)
324 print("Your score was: {} which is grade {}".format(score, grade))
325
326 insert_data = ("INSERT INTO scores(userID, topicID, score, difficulty, grade) VALUES (?,?,?,?,?);")
327 cursor.execute(insert_data,[(userID), (topicID), (score), (getDifficultyLevelName(difficulty)), (grade)])
328 conn.commit()
329 input("Press Enter to go back to main menu")
330
331
332
333def main():
334 if conn:
335 init()
336
337 while True:
338 clearScreen()
339 showMainMenu()
340 option = input(">> ")
341
342 if option == "1":
343 register()
344
345 elif option == "2":
346 user = login()
347 if user:
348 while True:
349 clearScreen()
350 showUserMenu()
351 user_option =input(">> ")
352 Generate()
353 if user_option == "1":
354 takeQuiz(user, 1)
355
356 elif user_option == "2":
357 takeQuiz(user, 2)
358
359 elif user_option == "3":
360 takeQuiz(user, 3)
361
362 elif user_option == "4":
363 showScores(user)
364
365 elif user_option == "5":
366 report(user)
367
368 elif user_option == "6":
369 #LOG OUT AND GO BACK TO MAIN MENU LOOP
370 break
371 else:
372 print("Invalid username or password.")
373 input("Press Enter to go back to main menu")
374
375 elif option == "3":
376# END OF MAIN MENU LOOP
377 confirm = input("Do you really want to exit? (y/n)").lower()[0]
378 if confirm == 'y':
379 sys.exit()
380
381 else:
382 print("Couldn't connect to {}".format(db_file))
383 print("Goodbye...")
384 sys.exit()
385
386
387if __name__ == '__main__':
388 db_file = "my_quiz.db"
389 conn = createConnection()
390 main()