问在MYSQL中存储3个值

EN

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()

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

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

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()

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()