1

import sqlite3

with sqlite3.connect("Quiz.db") as db:
    cursor = db.cursor()

cursor.execute('''
create table if not exists user(
userID integer primary key,
username varchar(20) not null,
firstname varchar(20) not null,
surname varchar(20) not null,
password varchar(20) not null);
''')

cursor.execute('''
insert into user(username,firstname,surname,password)
values ("test_User","Bob","Smith","MrBob")
''')
db.commit()

cursor.execute("select * from user")
rows = cursor.fetchall()
for row in rows:
    print (row)

def userMenu(user):
    while True:
        print("Welcome to the system")
        menu = ('''
            1 - Secondary Storage Quiz
            2 - Network Quiz
            3 - Systems Software Quiz
            4 - Ethical and Legal Quiz
            5 - Show my scores
            6 - Graph
            7 - Exit \n ''')
        userChoice = input (menu)

        if userChoice == "1":
            quiz(user,1)
        elif userChoice == "2":
            quiz(user,2)
        elif userChoice == "3":
            quiz(user,3)
        elif userChoice == "4":
            quiz(user,4)
        elif userChoice == "5":
            stats.showScores(user)
        elif userChoice == "6":
            break

def login():
    while True:
        username = input("Enter your username:")
        password = input("Enter your password:")

        with sqlite3.connect("Quiz.db") as db:
            cursor = db.cursor()
        find_user = ('SELECT * FROM user WHERE username = ? AND password = ?')
        cursor.execute(find_user, [(username),(password)]) #[] replaces the values of the ?
        results = cursor.fetchall()

        if results:
            for i in results:
                print("Welcome "+i[2])

            return (i[2])
        else:
            print("Username and password not recognised")
            again = input("Do you want to retry? (Y/N)")
            if again.lower() == "n":
                print ("Goodbye")
                return ("Exit")

def newUser():
    print("Add a new user")
    #check username is taken
    found = 0
    while found == 0:
        username = input("Enter a username: ")
        with sqlite3.connect("Quiz.db") as db:
            cursor = db.cursor()
        find_user = ('SELECT * FROM user WHERE username = ?')#?stops SQL injection
        cursor.execute(find_user,[(username)])#[] replaces the values of the ?

        if cursor.fetchall():
            print("Usename taken")
        else:
            found = 1

    firstname = input("Please enter your first name: ")
    surname = input ("Please enter your last name: ")
    password = input ("Please enter a password: ")
    password1 = input ("Please re-enter your password: ")
    while password != password1:
        print("Passwords do not match")
        password = input("Please enter a password: ")
        password1 = input ("Please re-enter a password: ")

    insertData = '''INSERT INTO user(username,firstname,surname,password)
    VALUES(?,?,?,?)'''
    cursor.execute(insertData,[(username),(firstname),(surname),(password)])
    db.commit()#saves the results to the database



#Table

cursor.execute('''
CREATE TABLE IF NOT EXISTS topics(
topicTD INTEGER PRIMARY KEY,
topicName VARCHAR(20) NOT NULL) ;''')

cursor.execute('''
CREATE TABLE IF NOT EXISTS scores(
scoreID INTEGER PRIMARY KEY,
userID INTEGER NOT NULL ,
score INTEGER NOT NULL,
topicID INTEGER NOT NULL,
FOREIGN KEY(userID) REFERENCES users (userID)
FOREIGN KEY(topicID) REFERENCES topics(topicID));''')

cursor.execute('''
CREATE TABLE IF NOT EXISTS questions(
questionID INTEGER PRIMARY KEY,
topicID INTEGER NOT NULL,
question VARCHAR(50),
option1 VARCHAR(50),
option2 VARCHAR(50),
option3 VARCHAR(350),
option4 VARCHAR(50),
answer VARCHAR(50),
FOREIGN KEY(topicID) REFERENCES topics(topicID))
;''')

tables = cursor.execute('''
SELECT name FROM sqlite_master
WHERE type='table'
ORDER BY name;''')
print(cursor.fetchall())

with sqlite3.connect("Quiz.db") as db:
    cursor = db.cursor()

cursor.execute('''
INSERT INTO topics (topicName)
VALUES ("Secondary Storage"),("Networks"),("System Software"),("Ethical and Legal");
''')
db.commit()

cursor.execute('''
INSERT INTO questions (topicID,question,option1,option2,option3,option4,answer)
VALUES ("1","What type of storage is a Memory Stick?","Solid State","Magnetic","Optical","Volatile","1"),
("1","What type of storage is a CD?","Solid State","Magnietic","Optical","Volatile","3"),
("1","What type of storage is a Hard Disk Drive?","Solid State","Magnetic","Optical","Volatile","2"),
("1","What type of storage is a SSD?","Volatile","Optical","Solid State","Magnetic","3");
''')
db.commit()

cursor.execute('''
INSERT INTO questions (topicID,question,option1,option2,option3,option4,answer)
VALUES ("2","What is described as a network in one small geographical area?","Ring","Star","Wan","Lan","4"),
("2","What is described as a network in one large geographical area?","Star","Wan","Ring","Lan","2"),
("2","Which topology requires a terminator?","Bus","Ring","Star","Mesh","1");
''')
db.commit()

def quiz (userID,topicID):
    with sqlite3.connect("Quiz.db") as db:
        cursor.execute("SELECT * FROM questions WHERE topicID=?;",[(topicID)])
    questions = cursor.fetchall()
    print(questions)
    score = 0
    numofQuestions = 0 #used to help work out the score/percentage
    for question in questions:
            topic = question[1]
            print(question[2])
            print("1. %s \n 2. %s \n 3. %s \n 4. %s" % (question[3], question[4], question[5], question[6]))
            choice = input("Answer: ")
            if choice == question[7]:
                    print ("Correct")
                    score += 1
                    print("")
            else:
                    print("Incorrect")
            numofQuestions +=1
            #works out percentage to keep all quiz scores consistent despite number of questions in topic
    score = int((score/numofQuestions)*100)
    print("Your score was:",score)
    #stores results of quiz in scores table
    insertData = ("INSERT INTO scores (userID,score,topicID) VALUES(?,?,?);")
    cursor.execute(insertData,[(userID),(score),(topicID)])
    db.commit()

def showScores(user):
    with sqlite3.connect("Quiz.db") as db:
        cursor = db.cursor()

    query = ("""SELECT topics.topicName, scores.score, users.userID
    FROM users INNER JOIN (topics INNER JOIN scores ON topics.topicID = scores.topicID) ON users.userID = scores.userID
    WHERE ((users.userID))=?));""")
    cursor.execute(query, [(user)])
    results = cursor.fetchall()
    for line in results:
        print(line[0], str(line[1]) + "%")

while True:
    print("Welcome to the system ")
    menu =('''
    1 - Create New User
    2 - Login
    3 - Exit \n ''')

    userChoice = input(menu)

    if userChoice == "1":
        newUser()
    elif userChoice == "2":
        enter = login()
        if enter == "exit":
            break
        else:
            print("Starting Quiz")
            userMenu(enter)

    elif userChoice == "3":
        print("Goodbye")
        break
    else:
        print("Input not recognised, please try again")