使用executemany批量插入数据后数据库无新增行的问题

你尝试使用executemany方法向SQLite数据库插入多行数据，但执行后数据库中无新增行，问题出在以下核心点：

原始代码 #

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

核心问题：函数未被调用 #

你定义了create_table()和populate_table()两个核心函数，但代码末尾没有触发调用，导致创建表和插入数据的逻辑完全没有执行。这是导致数据未插入的最关键原因。

解决步骤 #

1. 添加函数调用
   在代码末尾添加执行逻辑，确保定义的函数能被运行：

   if __name__ == "__main__":
       create_table()
       populate_table()
   

2. 确认事务提交
   你使用的with sqlite3.connect(...)上下文管理器会在代码块正常结束时自动提交事务，这部分逻辑是正确的，无需额外修改。如果需要调试，也可以在executemany后显式调用conn.commit()，但这不是必需的。

修正后的完整代码 #

import sqlite3

def create_table():
    """连接数据库"""
    with sqlite3.connect("DSUCollege.db") as conn:
        cur = conn.cursor()
        # 若students表已存在则删除
        cur.execute("DROP TABLE IF EXISTS students")
        # 创建学生表
        cur.execute("CREATE TABLE students(student_id INTEGER, first_name TEXT, last_name TEXT, major TEXT, gpa REAL)")
    print("数据库和表创建成功")

def populate_table():
    """连接数据库并插入数据"""
    with sqlite3.connect("DSUCollege.db") as conn:
        cur = conn.cursor()
        # 待插入的学生数据
        student_data = [
                [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(?,?,?,?,?)", student_data)
    print("数据插入成功")

# 执行核心逻辑
if __name__ == "__main__":
    create_table()
    populate_table()

内容的提问来源于stack exchange，提问作者Zues300