· 8 years ago · May 01, 2018, 06:52 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 = "Cen42"
84 password = "pass"
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[rightAnswerIndex],question[4]))
134 elif howManyOptions == 3:
135 return ("1. {} \n2. {} \n3. {} \n".format(question[rightAnswerIndex],question[4],question[5]))
136 else:
137 return ("1. {} \n2. {} \n3. {} \n4. {} \n".format(question[rightAnswerIndex],question[4],question[5],question[6]))
138
139# allows a user to select a topic and difficulty rating (Easy, Medium or Hard) and asks five
140# questions on that topic:
141# a. ‘Easy’ mode has a choice of two answers for each question
142# b. ‘Medium’ mode has a choice of three answers for each question
143# c. ‘Hard’ mode has a choice of four answers for each question
144# 4. loads the questions and answers from a file stored externally to the game.
145# 5. displays the user’s score, percentage and grade achieved for that quiz.
146
147def takeQuiz(userID, topicID):
148# loadQuestionsAnswers(topic, difficulty)
149# runQuiz()
150# storeResults()
151# displayResults()
152 cursor = conn.cursor()
153
154 cursor.execute("SELECT * FROM topics WHERE topicID = ?;", [(topicID)])
155 topicName = cursor.fetchall()
156 clearScreen()
157 print("{} QUIZ\n".format(topicName[0][1]).upper())
158
159 difficulty = int(input("Difficulty Level\n1. Easy\n2. Medium\n3. Hard\n>> "))
160 print()
161
162 score = 0
163
164 cursor.execute("SELECT * FROM questions WHERE topicID = ?;", [(topicID)])
165 questions = cursor.fetchall()
166 numberOfQuestions = 0 #used to help work out the score / percentage
167
168 for question in questions:
169 print("Question: {}".format(question[2]))
170 print(getQuestionOptions(question, difficulty))
171
172 choice = input("Answer >> ")
173 rightAnswerOption = question[7] # option number of the right answer
174 rightAnswerIndex = int(rightAnswerOption) + 2 # index number of the right answer
175
176 if choice == rightAnswerOption:
177 print("Correct.\n")
178 score += 1
179 time.sleep(1)
180 print()
181 else:
182 print("Incorrect. \n")
183 print("Right answer: {}".format(question[rightAnswerIndex]))
184 time.sleep(1)
185 input()
186
187 numberOfQuestions += 1
188
189 score = int((score / numberOfQuestions)*100)
190 grade = getGrade(score)
191 print("Your score was: {} which is grade {}".format(score, grade))
192
193 insert_data = ("INSERT INTO scores(userID, topicID, score, difficulty, grade) VALUES (?,?,?,?,?);")
194 cursor.execute(insert_data,[(userID), (topicID), (score), (getDifficultyLevelName(difficulty)), (grade)])
195 conn.commit()
196 input("Press Enter to go back to main menu")
197
198
199
200
201
202
203
204def generateQuestions():
205 cursor = conn.cursor()
206# cursor.execute('INSERT INTO topics (topicName) VALUES("History"), ("Music"), ("Computer Science");')
207# conn.commit()
208
209
210 cursor.execute ('''
211 INSERT INTO questions (topicID, question, option1, option2, option3, option4, answer) VALUES
212 ("1", "What type of storage is a Memory Stick?", "Solid State", "Magnetic", "Optical", "Volatile", "1"),
213 ("1", "What type of storage is a CD?", "Solid State", "Magnetic", "Optical", "Volatile","3"),
214 ("1", "What type of storage is a Hard Disk Drive?", "Solid State", "Magnetic", "Optical", "Volatile", "1"),
215 ("1", "What type of storage is a SSD?", "Solid State" , "Magnetic", "Optical", "Volatile", "1"),
216 ("2", "What is described as a network in one small geographical area?", "Ring" , "LAN", "Star", "WAN", "2"),
217 ("2", "What is described as a network in a large geographical area?", "Ring", "LAN", "Star", "WAN", "4"),
218 ("2", "Which topology requires a terminator?", "Bus", "Ring", "Star", "Mesh", "1"),
219 ("2", "What type of software is most likely to be free?", "Open Source", "Proprietary", "Utility", "System", "1"),
220 ("3", "What type of software is Automatic update?", "Open Source", "Proprietary", "Utility", "System", "3"),
221 ("3", "What type of software is an Operating System?", "Open Source", "Proprietary", "Utility", "System", "4"),
222 ("3", "Which of the following is sensitive data?", "DOB", "Name", "Political Opinion", "Address", "3"),
223 ("3", "What law covers sensitive data?", "Data Protection Act", "Copyright,Designs and Patents", "Computer Misuse", "Freedom of Information", "1");
224 ''')
225 conn.commit()
226
227
228# gives Fergus the option to generate and output the following reports:
229# a. a report that allows Fergus to choose a username, and outputs all of the quizzes
230# that they have taken, and the grade for each of those quizzes.
231# b. a report that outputs for a selected topic and difficulty: the average score achieved,
232# the highest score achieved, and the user details of the person that achieved the
233# highest score.
234def report():
235 pass
236
237
238def showMainMenu():
239 print(" " *10, "MAIN MENU")
240 print("-" * 32)
241 print('Please choose one of the following options \n')
242 print("1. Register")
243 print("2. Login")
244 print("3. Exit")
245
246
247def showUserMenu():
248 print(" " *10, "USER MENU")
249 print("-" * 32)
250 print('Please choose one of the following options \n')
251 print("1. History quiz")
252 print("2. Music quiz")
253 print("3. Computer Science quiz")
254 print("4. Show my scores")
255 print("5. Report")
256 print("6. Log out")
257
258def generateUserName(firstname, age):
259 try:
260 cursor = conn.cursor()
261 username_taken = True
262
263 while username_taken:
264 username = firstname[:3] + str(age)
265 print("Auto generated username: {}".format(username))
266
267 find_user = ('SELECT * FROM users WHERE username = ?')
268 cursor.execute(find_user, [(username)])
269
270 if cursor.fetchall():
271 print("Username taken.")
272 else:
273 username_taken = False
274 except:
275 print("Couldn't generate unique username.")
276
277 finally:
278 return username
279
280
281def register():
282 print("Sign up")
283
284 firstname = input("Please enter your first name >> ")
285 lastname = input("Please enter your last name >> ")
286 age = int(input("Please enter your age >> "))
287 username = generateUserName(firstname, age)
288 yeargroup = input("Please enter your year group >> ")
289 pwd1 = input("Please enter a password >> ")
290 pwd2 = input("Please re-enter your password >> ")
291
292 while pwd1 != pwd2:
293 print("Passwords did not match...")
294 pwd1 = input("Please enter a password >> ")
295 pwd2 = input("Please re-enter your password >> ")
296
297 insert_data = '''
298 INSERT INTO users(username, firstname, surname, age, yeargroup, password)
299 VALUES(?,?,?,?,?,?)'''
300
301 cursor = conn.cursor()
302 cursor.execute(insert_data, [(username), (firstname), (lastname), (age), (yeargroup), (pwd1)])
303 conn.commit()
304
305 print("New account created")
306 input("Press Enter to go back to main menu")
307
308
309
310
311def main():
312 if conn:
313 init()
314
315 while True:
316 clearScreen()
317 showMainMenu()
318 option = input(">> ")
319
320 if option == "1":
321 register()
322
323 elif option == "2":
324 user = login()
325 if user:
326 while True:
327 clearScreen()
328 showUserMenu()
329 user_option = input(">> ")
330
331 if user_option == "1":
332 takeQuiz(user, 1)
333
334 elif user_option == "2":
335 takeQuiz(user, 2)
336
337 elif user_option == "3":
338 takeQuiz(user, 3)
339
340 elif user_option == "4":
341 showScores(user)
342
343 elif user_option == "5":
344 report(user)
345
346 elif user_option == "6":
347 #LOG OUT AND GO BACK TO MAIN MENU LOOP
348 break
349 else:
350 print("Invalid username or password.")
351 input("Press Enter to go back to main menu")
352
353 elif option == "3":
354# END OF MAIN MENU LOOP
355 confirm = input("Do you really want to exit? (y/n)").lower()[0]
356 if confirm == 'y':
357 sys.exit()
358
359 else:
360 print("Couldn't connect to {}".format(db_file))
361 print("Goodbye...")
362 sys.exit()
363
364
365if __name__ == '__main__':
366 db_file = "my_quiz.db"
367 conn = createConnection()
368 main()