import sqlite3

def create_table():
    """connect to database"""
    with sqlite3.connect("DSUCollege.db") as conn:
        """create cursor object"""
        cur = conn.cursor()
        """Drops students table if already exists"""
        cur.execute("DROP TABLE IF Exists students")
        """creates table"""
        cur.execute("CREATE TABLE students(student_id INTEGER, first_name TEXT, last_name TEXT, major TEXT, gpa REAL)")
print("The database and table has been created successfully")

def populate_table():
    """connect to database"""
    with sqlite3.connect("DSUCollege.db") as conn:
        """create cursor object"""
        cur = conn.cursor()
        """Information to be inserted to database table"""
        table = [
                [1111,"Dave","Grogl","Music",4.0],
                [2222,"Belinda","Carlisle","Accounting",3.5],
                [3333,"Joe","Elliot","Computer Science",2.8],
                [4444,"Angus","Young","Accounting",2.1],
                [5555,"Susanna","Hoffs","Music",3.1],
                [6666,"Debbie","Harry","Computer Science",3.4],
                [7777,"Saul","Husdon","Music",2.7],
                [8888,"Paul","Hewson","Computer Science",1.8]
                ]
        cur.executemany("INSERT INTO students VALUES(?,?,?,?,?)", table)
print("The data was inserted successfully")

CodePudding user response：

When I ran your code, I was able to query the database. It correctly inserted the items.

C:\Old_Data\python>/sqlite/sqlite3 DSUCollege.db
SQLite version 3.30.1 2019-10-10 20:19:45
Enter ".help" for usage hints.
sqlite> select * from students;
1111|Dave|Grogl|Music|4.0
2222|Belinda|Carlisle|Accounting|3.5
3333|Joe|Elliot|Computer Science|2.8
4444|Angus|Young|Accounting|2.1
5555|Susanna|Hoffs|Music|3.1
6666|Debbie|Harry|Computer Science|3.4
7777|Saul|Husdon|Music|2.7
8888|Paul|Hewson|Computer Science|1.8
sqlite> select major, avg(gpa)
   ...> from students
   ...> group by major;
Accounting|2.8
Computer Science|2.66666666666667
Music|3.26666666666667