import sqlite3

def create_connection(db_file, delete_db=False):
    import os
    if delete_db and os.path.exists(db_file):
        os.remove(db_file)

    conn = None
    try:
        conn = sqlite3.connect(db_file)
        conn.execute(“PRAGMA foreign_keys = 1”)
    except Error as e:
        print(e)

    return conn


def create_table(conn, create_table_sql):
    try:
        c = conn.cursor()
        c.execute(create_table_sql)
    except Error as e:
        print(e)
        
def execute_sql_statement(sql_statement, conn):
    cur = conn.cursor()
    cur.execute(sql_statement)

    rows = cur.fetchall()

    return rows

def normalize_database(non_normalized_db_filename):
    # Normalize ‘non_normalized.db’
    # Call the normalized database ‘normalized.db’
    # Function Output: No outputs
    # Requirements:
    # Create four tables
    # Degrees table has one column:
    #     [Degree] column is the primary key
    
    # Exams table has two columns:
    #     [Exam] column is the primary key column
    #     [Year] column stores the exam year
    
    # Students table has four columns:
    #     [StudentID] primary key column 
    #     [First_Name] stores first name
    #     [Last_Name] stores last name
    #     [Degree] foreign key to Degrees table
        
    # StudentExamScores table has four columns:
    #     [PK] primary key column,
    #     [StudentID] foreign key to Students table,
    #     [Exam] foreign key to Exams table ,
    #     [Score] exam score

    conn_non_normalized = create_connection(non_normalized_db_filename)
    conn_normalized = create_connection(“normalized.db”, delete_db=True)

    create_table_sql = “”“
    CREATE TABLE IF NOT EXISTS Degrees (
        Degree TEXT PRIMARY KEY
    );
    “””
    create_table(conn_normalized, create_table_sql)

    create_table_sql = “”“
    CREATE TABLE IF NOT EXISTS Exams (
        Exam TEXT PRIMARY KEY,
        Year INTEGER
    );
    “””
    create_table(conn_normalized, create_table_sql)

    create_table_sql = “”“
    CREATE TABLE IF NOT EXISTS Students (
        StudentID INTEGER PRIMARY KEY AUTOINCREMENT,
        First_Name TEXT,
        Last_Name TEXT,
        Degree TEXT,
        FOREIGN KEY (Degree) REFERENCES Degrees(Degree) ON DELETE CASCADE
    );
    “””
    create_table(conn_normalized, create_table_sql)

    create_table_sql = “”“
    CREATE TABLE IF NOT EXISTS StudentExamScores (
        PK INTEGER PRIMARY KEY AUTOINCREMENT,
        StudentID INTEGER,
        Exam TEXT,
        Score INTEGER,
        FOREIGN KEY (StudentID) REFERENCES Students(StudentID) ON DELETE CASCADE,
        FOREIGN KEY (Exam) REFERENCES Exams(Exam) ON DELETE CASCADE
    );
    “””
    create_table(conn_normalized, create_table_sql)

    select_degrees = “SELECT DISTINCT Degree FROM Students”
    degrees = execute_sql_statement(select_degrees, conn_non_normalized)
    for degree in degrees:
        insert_into_degrees = “INSERT INTO Degrees (Degree) VALUES (‘{}’)”.format(degree[0].strip())
        execute_sql_statement(insert_into_degrees, conn_normalized)

    select_exams = “SELECT DISTINCT Exam, Year FROM StudentExamScores”
    exams = execute_sql_statement(select_exams, conn_non_normalized)
    for exam in exams:
        insert_into_exams = “INSERT INTO Exams (Exam, Year) VALUES (‘{}’, {})”.format(exam[0].strip(), exam[1])
        execute_sql_statement(insert_into_exams, conn_normalized)

    select_students = “SELECT First_Name, Last_Name, Degree FROM Students”
    students = execute_sql_statement(select_students, conn_non_normalized)
    for student in students:
        insert_into_students = “INSERT INTO Students (First_Name, Last_Name, Degree) VALUES (‘{}’, ‘{}’, ‘{}’)”.format(student[0].strip(), student[1].strip(), student[2].strip())
        execute_sql_statement(insert_into_students, conn_normalized)

    select_scores = “SELECT StudentID, Exam, Score FROM StudentExamScores”
    scores = execute_sql_statement(select_scores, conn_non_normalized)
    for score in scores:
        select_student_id = “SELECT StudentID FROM Students WHERE First_Name = ‘{}’ AND Last_Name = ‘{}’ AND Degree = ‘{}’”.format(students[score[0]-1][0].strip(), students[score[0]-1][1].strip(), students[score[0]-1][2].strip())
        student_id = execute_sql_statement(select_student_id, conn_normalized)[0][0]
        insert_into_scores = “INSERT INTO StudentExamScores (StudentID, Exam, Score) VALUES ({}, ‘{}’, {})”.format(student_id, score[1], score[2])
        execute_sql_statement(insert_into_scores, conn_normalized)


def ex30(conn):
    # Write an SQL statement that SELECTs all rows from the Exams table and sort the exams by Year
    # output columns: exam, year
    sql_statement = “SELECT Exam, Year FROM Exams ORDER BY Year”
    return sql_statement
No it is still giving the following error:

raceback (most recent call last):
File “C:\Users\Tejesh Pawar\AppData\Local\Programs\Python\Python311\Lib\unittest\suite.py”, line 166, in _handleClassSetUp
setUpClass()
File “C:\Users\Tejesh Pawar\Documents\Spring23\Python_ZIA\assignment6\tests\test_assignment.py”, line 13, in setUpClass
assignment.normalize_database(‘non_normalized.db’)
File “c:\Users\Tejesh Pawar\Documents\Spring23\Python_ZIA\assignment6\assignment.py”, line 497, in normalize_database
execute_sql_statement(insert_into_degrees, conn_normalized)
File “c:\Users\Tejesh Pawar\Documents\Spring23\Python_ZIA\assignment6\assignment.py”, line 421, in execute_sql_statement
cur.execute(sql_statement)
sqlite3.OperationalError: no such column: ‘graduate’

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File “c:\Users\Tejesh Pawar\Documents\Spring23\Python_ZIA\assignment6\run_tests.py”, line 13, in <module>
JSONTestRunner(visibility=‘visible’, stream=f).run(suite)
File “C:\Users\Tejesh Pawar\AppData\Local\Programs\Python\Python311\Lib\site-packages\gradescope_utils\autograder_utils\json_test_runner.py”, line 196, in run
test(result)
File “C:\Users\Tejesh Pawar\AppData\Local\Programs\Python\Python311\Lib\unittest\suite.py”, line 84, in call
return self.run(*args, **kwds)
^^^^^^^^^^^^^^^^^^^^^^^
File “C:\Users\Tejesh Pawar\AppData\Local\Programs\Python\Python311\Lib\unittest\suite.py”, line 122, in run
test(result)
File “C:\Users\Tejesh Pawar\AppData\Local\Programs\Python\Python311\Lib\unittest\suite.py”, line 84, in call
return self.run(*args, **kwds)
^^^^^^^^^^^^^^^^^^^^^^^
File “C:\Users\Tejesh Pawar\AppData\Local\Programs\Python\Python311\Lib\unittest\suite.py”, line 122, in run
test(result)
File “C:\Users\Tejesh Pawar\AppData\Local\Programs\Python\Python311\Lib\unittest\suite.py”, line 84, in call
return self.run(*args, **kwds)
^^^^^^^^^^^^^^^^^^^^^^^
File “C:\Users\Tejesh Pawar\AppData\Local\Programs\Python\Python311\Lib\unittest\suite.py”, line 114, in run
self._handleClassSetUp(test, result)
File “C:\Users\Tejesh Pawar\AppData\Local\Programs\Python\Python311\Lib\unittest\suite.py”, line 176, in _handleClassSetUp
self._createClassOrModuleLevelException(result, e,
File “C:\Users\Tejesh Pawar\AppData\Local\Programs\Python\Python311\Lib\unittest\suite.py”, line 236, in _createClassOrModuleLevelException
self._addClassOrModuleLevelException(result, exc, errorName, info)
File “C:\Users\Tejesh Pawar\AppData\Local\Programs\Python\Python311\Lib\unittest\suite.py”, line 246, in _addClassOrModuleLevelException
result.addError(error, sys.exc_info())
File “C:\Users\Tejesh Pawar\AppData\Local\Programs\Python\Python311\Lib\site-packages\gradescope_utils\autograder_utils\json_test_runner.py”, line 136, in addError
self.processResult(test, err)
File “C:\Users\Tejesh Pawar\AppData\Local\Programs\Python\Python311\Lib\site-packages\gradescope_utils\autograder_utils\json_test_runner.py”, line 123, in processResult
if self.getLeaderboardData(test)[0]:
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File “C:\Users\Tejesh Pawar\AppData\Local\Programs\Python\Python311\Lib\site-packages\gradescope_utils\autograder_utils\json_test_runner.py”, line 51, in getLeaderboardData
column_name = getattr(getattr(test, test._testMethodName), ‘leaderboard_column’, None)
^^^^^^^^^^^^^^^^^^^^
AttributeError: ‘_ErrorHolder’ object has no attribute ‘_testMethodName’

FIx it in code and send the updated one
raceback (most recent call last):
File “C:\Users\Tejesh Pawar\AppData\Local\Programs\Python\Python311\Lib\unittest\suite.py”, line 166, in _handleClassSetUp
setUpClass()
File “C:\Users\Tejesh Pawar\Documents\Spring23\Python_ZIA\assignment6\tests\test_assignment.py”, line 13, in setUpClass
assignment.normalize_database(‘non_normalized.db’)
File “c:\Users\Tejesh Pawar\Documents\Spring23\Python_ZIA\assignment6\assignment.py”, line 497, in normalize_database
execute_sql_statement(insert_into_degrees, conn_normalized)
File “c:\Users\Tejesh Pawar\Documents\Spring23\Python_ZIA\assignment6\assignment.py”, line 421, in execute_sql_statement
cur.execute(sql_statement)
sqlite3.OperationalError: no such column: ‘graduate’

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File “c:\Users\Tejesh Pawar\Documents\Spring23\Python_ZIA\assignment6\run_tests.py”, line 13, in <module>
JSONTestRunner(visibility=‘visible’, stream=f).run(suite)
File “C:\Users\Tejesh Pawar\AppData\Local\Programs\Python\Python311\Lib\site-packages\gradescope_utils\autograder_utils\json_test_runner.py”, line 196, in run
test(result)
File “C:\Users\Tejesh Pawar\AppData\Local\Programs\Python\Python311\Lib\unittest\suite.py”, line 84, in call
return self.run(*args, **kwds)
^^^^^^^^^^^^^^^^^^^^^^^
File “C:\Users\Tejesh Pawar\AppData\Local\Programs\Python\Python311\Lib\unittest\suite.py”, line 122, in run
test(result)
File “C:\Users\Tejesh Pawar\AppData\Local\Programs\Python\Python311\Lib\unittest\suite.py”, line 84, in call
return self.run(*args, **kwds)
^^^^^^^^^^^^^^^^^^^^^^^
File “C:\Users\Tejesh Pawar\AppData\Local\Programs\Python\Python311\Lib\unittest\suite.py”, line 122, in run
test(result)
File “C:\Users\Tejesh Pawar\AppData\Local\Programs\Python\Python311\Lib\unittest\suite.py”, line 84, in call
return self.run(*args, **kwds)
^^^^^^^^^^^^^^^^^^^^^^^
File “C:\Users\Tejesh Pawar\AppData\Local\Programs\Python\Python311\Lib\unittest\suite.py”, line 114, in run
self._handleClassSetUp(test, result)
File “C:\Users\Tejesh Pawar\AppData\Local\Programs\Python\Python311\Lib\unittest\suite.py”, line 176, in _handleClassSetUp
self._createClassOrModuleLevelException(result, e,
File “C:\Users\Tejesh Pawar\AppData\Local\Programs\Python\Python311\Lib\unittest\suite.py”, line 236, in _createClassOrModuleLevelException
self._addClassOrModuleLevelException(result, exc, errorName, info)
File “C:\Users\Tejesh Pawar\AppData\Local\Programs\Python\Python311\Lib\unittest\suite.py”, line 246, in _addClassOrModuleLevelException
result.addError(error, sys.exc_info())
File “C:\Users\Tejesh Pawar\AppData\Local\Programs\Python\Python311\Lib\site-packages\gradescope_utils\autograder_utils\json_test_runner.py”, line 136, in addError
self.processResult(test, err)
File “C:\Users\Tejesh Pawar\AppData\Local\Programs\Python\Python311\Lib\site-packages\gradescope_utils\autograder_utils\json_test_runner.py”, line 123, in processResult
if self.getLeaderboardData(test)[0]:
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File “C:\Users\Tejesh Pawar\AppData\Local\Programs\Python\Python311\Lib\site-packages\gradescope_utils\autograder_utils\json_test_runner.py”, line 51, in getLeaderboardData
column_name = getattr(getattr(test, test._testMethodName), ‘leaderboard_column’, None)
^^^^^^^^^^^^^^^^^^^^
AttributeError: ‘_ErrorHolder’ object has no attribute ‘_testMethodName’

it is giving the above error, please fix it in code and send the updated code