DsK3PmvG

1import sqlite3
2
3with sqlite3.connect("Quiz.db") as db:
4    cursor = db.cursor()
5
6cursor.execute('''
7create table if not exists user(
8userID integer primary key,
9username varchar(20) not null,
10firstname varchar(20) not null,
11surname varchar(20) not null,
12password varchar(20) not null);
13''')
14
15cursor.execute('''
16insert into user(username,firstname,surname,password)
17values ("test_User","Bob","Smith","MrBob")
18''')
19db.commit()
20
21cursor.execute("select * from user")
22rows = cursor.fetchall()
23for row in rows:
24    print (row)
25
26def userMenu(user):
27    while True:
28        print("Welcome to the system")
29        menu = ('''
30            1 - Secondary Storage Quiz
31            2 - Network Quiz
32            3 - Systems Software Quiz
33            4 - Ethical and Legal Quiz
34            5 - Show my scores
35            6 - Graph
36            7 - Exit \n ''')
37        userChoice = input (menu)
38
39        if userChoice == "1":
40            quiz(user,1)
41        elif userChoice == "2":
42            quiz(user,2)
43        elif userChoice == "3":
44            quiz(user,3)
45        elif userChoice == "4":
46            quiz(user,4)
47        elif userChoice == "5":
48            stats.showScores(user)
49        elif userChoice == "6":
50            break
51
52def login():
53    while True:
54        username = input("Enter your username:")
55        password = input("Enter your password:")
56
57        with sqlite3.connect("Quiz.db") as db:
58            cursor = db.cursor()
59        find_user = ('SELECT * FROM user WHERE username = ? AND password = ?')
60        cursor.execute(find_user, [(username),(password)]) #[] replaces the values of the ?
61        results = cursor.fetchall()
62
63        if results:
64            for i in results:
65                print("Welcome "+i[2])
66
67            return (i[2])
68        else:
69            print("Username and password not recognised")
70            again = input("Do you want to retry? (Y/N)")
71            if again.lower() == "n":
72                print ("Goodbye")
73                return ("Exit")
74
75def newUser():
76    print("Add a new user")
77    #check username is taken
78    found = 0
79    while found == 0:
80        username = input("Enter a username: ")
81        with sqlite3.connect("Quiz.db") as db:
82            cursor = db.cursor()
83        find_user = ('SELECT * FROM user WHERE username = ?')#?stops SQL injection
84        cursor.execute(find_user,[(username)])#[] replaces the values of the ?
85
86        if cursor.fetchall():
87            print("Usename taken")
88        else:
89            found = 1 
90
91    firstname = input("Please enter your first name: ")
92    surname = input ("Please enter your last name: ")
93    password = input ("Please enter a password: ")
94    password1 = input ("Please re-enter your password: ")
95    while password != password1:
96        print("Passwords do not match")
97        password = input("Please enter a password: ")
98        password1 = input ("Please re-enter a password: ")
99
100    insertData = '''INSERT INTO user(username,firstname,surname,password)
101    VALUES(?,?,?,?)'''
102    cursor.execute(insertData,[(username),(firstname),(surname),(password)])
103    db.commit()#saves the results to the database
104
105
106
107#Table
108
109cursor.execute('''
110CREATE TABLE IF NOT EXISTS topics(
111topicTD INTEGER PRIMARY KEY,
112topicName VARCHAR(20) NOT NULL) ;''')
113
114cursor.execute('''
115CREATE TABLE IF NOT EXISTS scores(
116scoreID INTEGER PRIMARY KEY,
117userID INTEGER NOT NULL ,
118score INTEGER NOT NULL,
119topicID INTEGER NOT NULL,
120FOREIGN KEY(userID) REFERENCES users (userID)
121FOREIGN KEY(topicID) REFERENCES topics(topicID));''')
122
123cursor.execute('''
124CREATE TABLE IF NOT EXISTS questions(
125questionID INTEGER PRIMARY KEY,
126topicID INTEGER NOT NULL,
127question VARCHAR(50),
128option1 VARCHAR(50),
129option2 VARCHAR(50),
130option3 VARCHAR(350),
131option4 VARCHAR(50),
132answer VARCHAR(50),
133FOREIGN KEY(topicID) REFERENCES topics(topicID))
134;''')
135
136tables = cursor.execute('''
137SELECT name FROM sqlite_master
138WHERE type='table'
139ORDER BY name;''')
140print(cursor.fetchall())
141
142with sqlite3.connect("Quiz.db") as db:
143    cursor = db.cursor()
144 
145cursor.execute('''
146INSERT INTO topics (topicName)
147VALUES ("Secondary Storage"),("Networks"),("System Software"),("Ethical and Legal");
148''')
149db.commit()
150 
151cursor.execute('''
152INSERT INTO questions (topicID,question,option1,option2,option3,option4,answer)
153VALUES ("1","What type of storage is a Memory Stick?","Solid State","Magnetic","Optical","Volatile","1"),
154("1","What type of storage is a CD?","Solid State","Magnietic","Optical","Volatile","3"),
155("1","What type of storage is a Hard Disk Drive?","Solid State","Magnetic","Optical","Volatile","2"),
156("1","What type of storage is a SSD?","Volatile","Optical","Solid State","Magnetic","3");
157''')
158db.commit()
159 
160cursor.execute('''
161INSERT INTO questions (topicID,question,option1,option2,option3,option4,answer)
162VALUES ("2","What is described as a network in one small geographical area?","Ring","Star","Wan","Lan","4"),
163("2","What is described as a network in one large geographical area?","Star","Wan","Ring","Lan","2"),
164("2","Which topology requires a terminator?","Bus","Ring","Star","Mesh","1");
165''')
166db.commit()
167
168def quiz (userID,topicID):
169    with sqlite3.connect("Quiz.db") as db:
170        cursor.execute("SELECT * FROM questions WHERE topicID=?;",[(topicID)])
171    questions = cursor.fetchall()
172    print(questions)
173    score = 0
174    numofQuestions = 0 #used to help work out the score/percentage
175    for question in questions:
176            topic = question[1]
177            print(question[2])
178            print("1. %s \n 2. %s \n 3. %s \n 4. %s" % (question[3], question[4], question[5], question[6]))
179            choice = input("Answer: ")
180            if choice == question[7]:
181                    print ("Correct")
182                    score += 1
183                    print("")
184            else:
185                    print("Incorrect")
186            numofQuestions +=1
187            #works out percentage to keep all quiz scores consistent despite number of questions in topic
188    score = int((score/numofQuestions)*100)
189    print("Your score was:",score)
190    #stores results of quiz in scores table
191    insertData = ("INSERT INTO scores (userID,score,topicID) VALUES(?,?,?);")
192    cursor.execute(insertData,[(userID),(score),(topicID)])
193    db.commit()
194
195def showScores(user):
196    with sqlite3.connect("Quiz.db") as db:
197        cursor = db.cursor()
198
199    query = ("""SELECT topics.topicName, scores.score, users.userID
200    FROM users INNER JOIN (topics INNER JOIN scores ON topics.topicID = scores.topicID) ON users.userID = scores.userID
201    WHERE ((users.userID))=?));""")
202    cursor.execute(query, [(user)])
203    results = cursor.fetchall()
204    for line in results:
205        print(line[0], str(line[1]) + "%")
206        
207while True:
208    print("Welcome to the system ")
209    menu =('''
210    1 - Create New User
211    2 - Login
212    3 - Exit \n ''')
213
214    userChoice = input(menu)
215
216    if userChoice == "1":
217        newUser()
218    elif userChoice == "2":
219        enter = login()
220        if enter == "exit":
221            break
222        else:
223            print("Starting Quiz")
224            userMenu(enter)
225
226    elif userChoice == "3":
227        print("Goodbye")
228        break
229    else:
230        print("Input not recognised, please try again")