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