Cz1qvGLg

1import sqlite3
2with sqlite3.connect("Quiz.db")as db:
3    cursor = db.cursor()
4
5cursor.execute('''
6CREATE TABLE IF NOT EXISTS user(
7userID INTEGER PRIMARY KEY,
8username VARCHAR(20) NOT NULL,
9firstname VARCHAR(20) NOT NULL,
10surname VARCHAR(20) NOT NULL,
11password VARCHAR(20) NOT NULL);
12''')
13
14cursor.execute(cvfb"""
15INSERT INTO user(username,firstname,surname,password)
16VALUES ("test_User","Bob","Smith","MrBob")
17""")
18db.commit
19
20cursor.execute("""
21INSERT INTO user(username,firstname,surname,password)
22VALUES ("test_User","John","Smitt","MrJohn")
23""")
24db.commit
25
26cursor.execute("SELECT * FROM user")
27rows = cursor.fetchall()
28for row in rows:
29    print(row)
30
31def login():
32    while True:
33        username = input("Enter your username here:")
34        password = input("Enter your password here:")
35        with sqlite3.connect("Quiz.db") as db:
36            cursor = db.cursor()
37        find_user = ("SELECT * FROM user WHERE username = ? AND password =?")
38        cursor.execute(find_user,[(username),(password)])
39        results = cursor.fetchall()
40
41        if results:
42            for i in results:
43                print("Welcome"+i[2])
44
45                return("exit")
46
47        else:
48            print("Username and password not recognised")
49            again = input ("DO you want to retry? (Y/N) ")
50            if again.lower() == "n":
51                print("Goodbye")
52                return("exit")
53
54def newUser():
55    print("Add a new user")
56    found = 0
57    while found == 0:
58        username =input("Enter username:")
59        with sqlite3.connect("Quiz.db")as db:
60            cursor = db.cursor()
61        find_user = ("SELECT * FROM users WHERE username = ?")
62        cursor.execute(find_user,[(username)])
63
64        if cursor.fetchall():
65            print("Username Taken")
66        else:
67            found = 1
68
69    firstname = input("Please enter your first name:")
70    surname = input("Please enter your last name:")
71    password = input("Please enter your password:")
72    password1 = input("Please re-enter your password:")
73    while password != password1:
74        print("Password did not match:")
75        password = input("Please enter a password:")
76        password1 = input("Please re-enter a password:")
77
78    InsertData ="""INSERT INTO user(username,firstname,surname, password)
79    VALUES (?,?,?)"""
80    cursor.execute(InsertData,[(username),(firstname),(surname),(password)])
81    db.commit()
82
83while True:
84    print("Welcome to the system")
85    menu =("""
86    1 - Create New User
87    2 - Login
88    3 - Exit /n """)
89
90    userChoice = input(menu)
91
92    if userChoice == "1":
93        newUser() 
94    elif userChoice =="2":
95        enter = login()
96        enter == "exit"
97        break
98
99    elif userChoice == "3":
100        print("Goodbye")
101        break
102    else:
103        print("Input not recognised, please try again")
104
105cursor.execute('''
106CREATE TABLE IF NOT EXISTS topics(
107topicID INTEGER PRIMARY KEY,
108topicName VARCHAR(20) NOT NULL);''')
109
110cursor.execute('''
111CREATE TABLE IF NOT EXISTS scores(
112scoreID INTEGER PRIMARY KEY,
113userID INTEGER NOT NULL ,
114score INTEGER NOT NULL,
115topicID INTEGER NOT NULL,
116FOREIGN KEY(userID)REFERENCES users(userID),
117FOREIGN KEY(topicID) REFERENCES topics(topicID));''')
118
119cursor.execute('''
120CREATE TABLE IF NOT EXISTS questions(
121questionID INTEGER PRIMARY KEY,
122topicID INTEGER NOT NULL,
123question VARCHAR(50),
124option1 VARCHAR(50),
125option2 VARCHAR(50),
126option3 VARCHAR(50),
127option4 VARCHAR(50),
128answer VARCHAR(50),
129FOREIGN KEY(topicID)REFERENCES topics(topicID));''')
130
131cursor.execute("select * from user")
132rows = cursor.fetchall()
133for row in rows:
134    print (row)
135
136                    
137def userMenu(user):
138    while True:
139        print("Welcome to the system:")
140        menu = ('''
141             1 - Secondary Storage Quiz
142             2 - Networks Quiz
143             3 - Systems Software Quiz
144             4 - Ethical and Legal Quiz
145             5 - Show my scores
146             6 - Graph
147             7 - Exit \n ''')
148        userChoice = input(menu)
149
150        if userChoice == "1":
151            quiz.quiz(user,1)
152        elif userChoice == "2":
153            quiz.quiz(user,2)
154        elif userChoice == "3":
155            quiz.quiz(user,3)
156        elif userChoice == "4":
157            quiz.quiz(user,4)
158        elif userChoice == "5":
159            stats.showScores(user)
160        elif userChoice == "6":
161            break
162
163def quiz(userID,topicID):
164    with splite3.connect("Quiz.db") as db:
165        cursor = db.cursor()
166    score = 0
167    cursor.execute("SELECT * FROM questions WHERE topicID=?;",[(topicID)])
168    questions = cursor.fetchall()
169    numofQuestions = 0 #used to help work out the score/percentage
170    for question in questions:
171        topic = question[1]
172        print(question[2])
173        print("1, %s /n 2, %s /n 3. %3 /n 4. %s" % (question[3], question[4], question[5], question[6]))
174        choice = input("Answer:")
175        if choice == question[7]:
176            print("Correct")
177            score +=1
178            print("")
179        else:
180            print("Incorrect")
181        numofQuestions +=1
182        #works out percentage to keep all quiz scores consistent despite number of questions in topic
183    score = int((score/numofQuestions)*100)
184    print("Your score was:",score)
185    #stores results of quiz in scores table
186    insertData = ("INSERT INTO scores(userID,score,topicID) VAlUES(?,?,?);")
187    cursor.execute(insertData,[(userID),(score),(topic)])
188    db.commit()