storing 3 values in MYSQL

Question

My problem is that i have created a maths quiz that will ask the user 10 random questions and then it will output their score out of 10 at the end.I have done this art of the task but i struggle with the last part.The last part is that i need to create something that will store the last 3 scores for each user.I decided to use mysql but my code will only let me store 1 score for each user.I use python 3.4.Here is the code.

import random
import operator
import mysql.connector
cnx = mysql.connector.connect(user='root', password='password',
                             host='localhost',
                             database='mydb')

cursor = cnx.cursor()


ID = input("what is your ID?")


OPERATIONS = [
    (operator.add, "+"),
    (operator.mul, "x"),
    (operator.sub, "-")
    ]

NB_QUESTIONS = 10

def get_int_input(prompt=''):
    while True:
      try:
        return int(input(prompt))
      except ValueError:
        print("Sorry,but we need a number")

if __name__ == '__main__':
   name = input("What is your name?").title()
   Class=input("Which class do you wish to input results for 1,2 or 3?")                       
   print(name, ", Welcome to the Maths Test")

    score = 0
    for _ in range(NB_QUESTIONS):
        num1 = random.randint(1,10)
        num2 = random.randint(1,10)
        op, symbol = random.choice(OPERATIONS)
        print("What is", num1, symbol, num2)
        if get_int_input() == op(num1, num2):
            print("Correct")
            score += 1
        else:
            print("Incorrect")

print("Well done", name, "you scored", score, "/", NB_QUESTIONS)

print ("Thank you for doing this mathamatical quiz , goodbye ")


if "ID" in "Class1":
    if "score"  in "Score1":
        add_record = ("INSERT INTO Class1"
                      "(Score2)"
                      "VALUES(%s)")
        data_record = (score)


    if  "score"   in "Score2":
        add_record = ("INSERT INTO Class1"
                    "(Score3)"
                    "VALUES(%s)")
        data_record = (score)

    else:
        add_record = ("INSERT INTO Class1"
                     "(ID, Name, Score1) "
                      "VALUES (%s, %s, %s)")
        data_record = (ID, name, score)

cursor.execute(add_record, data_record)
cnx.commit()

cursor.close()
cnx.close()

In my databse i have the columns ID,name,score1,score2,score3 when i complete the quiz the score,name and ID will be input into the table.But once the user with the same ID does the quiz there is a error.I want the code to store 3 scores for each user but there is a error.The error is:

cursor.execute(add_record, data_record) NameError: name 'add_record' is not defined

Thank you for reading this and thank you also for the help.I look forward to hearing the replies.

Answer 1

Alright, we are going to step through my solution in the smallest steps possible to reach your solution. Note: All this code in a single file works for me.

First I create a table in the database from Python. I'm not sure why your Average column is an INT type, so I changed that. Also, my ID is an INT just for simplicity.

import mysql.connector
cnx = mysql.connector.connect(user='root', password='password',
                             host='localhost',
                             database='mydb')
cursor = cnx.cursor()
# cursor.execute("DROP TABLE IF EXISTS Class1")
cursor.execute('''
    CREATE TABLE IF NOT EXISTS Class1
    ( ID INT PRIMARY KEY
    , Name VARCHAR(10) NOT NULL
    , Score1 INT
    , Score2 INT
    , Score3 INT
    , Average DECIMAL(9, 5)
    );
''')
cnx.commit()

Next, I create a User class in order to hold all the important information and contain the logic to go to & from the database. This way, you only require a single User object and a single method to go each way. This approach is preferred over your multiple INSERT queries.

class User:
    def __init__(self, _id, name, score1=None, score2=None, score3=None):
        self._id = _id
        self.name = name
        self.score1 = score1
        self.score2 = score2
        self.score3 = score3

    ''' set the score of the given or next available class '''
    def add_score(self, score, Class=None):
        if not Class or (Class < 0 or Class > 3):
            if all((self.score1, self.score2, self.score3)):
                return # can't update
            elif all((self.score1, self.score2)):
                Class = 3
            elif self.score1:
                Class = 2
            else:
                Class = 1

        if Class and 0 < Class <= 3: # if a position is given and valid
            setattr(self, 'score' + str(Class), score)

    def to_tuple(self):
        return (self._id, self.name, self.score1, self.score2, self.score3)

    ''' make it possible to see this object when printed '''
    def __repr__(self):
        return self.__class__.__name__+ str(self.to_tuple())

    ''' insert or update this user object in the database '''
    def insert_to_db(self, db):
        crsr = db.cursor()
        data = list(self.to_tuple())
        data.append(self.get_average_score())
        if User.get_by_id(self._id):
            data = data[1:]
            data.append(self._id)
            crsr.execute('''
                UPDATE Class1 SET
                    Name = %s,
                    Score1 = %s,
                    Score2 = %s,
                    Score3 = %s,
                    Average = %s
                WHERE ID = %s;
            ''', data)
        else:
            crsr.execute("INSERT INTO Class1 VALUES (%s,%s,%s,%s,%s,%s)", data)
        db.commit()
        crsr.close()

    @staticmethod
    def get_by_id(_id):
        cursor.execute("SELECT * FROM Class1 WHERE ID = %s", [_id])
        row = cursor.fetchone()
        return User.from_tuple(row)

    @staticmethod
    def get_by_name(name):
        cursor.execute("SELECT * FROM Class1 WHERE Name = %s", [name])
        row = cursor.fetchone()
        return User.from_tuple(row)

    ''' Get the average score from the object. No need to query the database '''
    def get_average_score(self):
        from statistics import mean
        scores = list(self.to_tuple())[2:4]
        scores = list(filter(None.__ne__, scores))
        return mean(scores) if len(scores) > 0 else 0

    @staticmethod
    def from_tuple(tup, min_elems=2, max_elems=6):
        user = None
        if tup:
            num_elems = len(tup)
            if num_elems < min_elems or num_elems > max_elems:
                raise Exception('invalid tuple given', tup)
            # know there is at least 2 elements here
            user = User(tup[0], tup[1])
            if num_elems >= 3:
                user.score1 = tup[2]
            if num_elems >= 4:
                user.score2 = tup[3]
            if num_elems >= 5:
                user.score3 = tup[4]
        return user

    @staticmethod
    def from_cursor(cursor):
        if cursor:
            return (User.from_tuple(row) for row in cursor.fetchall())
        return iter(()) # Return empty generator if cursor == None

Next, a quiz method is defined that returns the score and name of the person taking the quiz. The parameters are optional and have default values. It is a good habit to define many small methods in order to test your code and logic.

def quiz(num_questions=10, name=None):
    if not name:
        name = input("Enter your name: ").title()
    print(name, ", Welcome to the Maths Test")

    score = 0
    for _ in range(num_questions):
        num1 = random.randint(1,10)
        num2 = random.randint(1,10)
        op, symbol = random.choice(OPERATIONS)
        print("What is", num1, symbol, num2)
        if get_int_input() == op(num1, num2):
            print("Correct")
            score += 1
        else:
            print("Incorrect")
    return name, score

And finally (along with your other methods), this is the main method that will run with the program. This prompts for an ID, tries to find it in the database, then quizzes either an existing user and updates their score, or creates a new user, then inserts the user into the database.

def main():
    user_id = get_int_input("Enter your ID: ")
    Class = get_int_input("Which class do you wish to input results for 1, 2, or 3? ")
    user = User.get_by_id(user_id)
    if not user:
        print("User with id %d not found" % user_id)
        print("Creating new user")
        name, score = quiz(NB_QUESTIONS)
        user = User(user_id, name)
    else:
        print("Found user %s" % user.name)
        _, score = quiz(NB_QUESTIONS, user)

    user.add_score(score, Class)
    print("\nWell done", user.name, "you scored", score, "/", NB_QUESTIONS)
    print("Thank you for doing this mathamatical quiz , goodbye ")

    user.insert_to_db(cnx) # Remember to update the user in the database
    cnx.close()

if __name__ == '__main__':
    main()

Answer 2

if "ID" in "Class1":

if "score"  in "Score1":
    add_record = ("INSERT INTO Class1"
                  "(Score2)"
                  "VALUES(%s)")
    data_record = (score)

if  "score"   in "Score2":
    add_record = ("INSERT INTO Class1"
                "(Score3)"
                "VALUES(%s)")
    data_record = (score)

else:
    add_record = ("INSERT INTO Class1"
                 "(ID, Name, Score1) "
                  "VALUES (%s, %s, %s)")
    data_record = (ID, name, score)
    cursor.execute(add_record, data_record)
    cnx.commit()

cursor.close() cnx.close()