Python Tkinter连接SQLite3删除数据异常原因排查

背景 #

通过以下create_table函数创建了students和counseling_records两张表：

def create_table(conn):
    cursor = conn.cursor()
    cursor.execute('''CREATE TABLE IF NOT EXISTS students
                   (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT UNIQUE)''')
    cursor.execute('''CREATE TABLE IF NOT EXISTS counseling_records
                      (id INTEGER PRIMARY KEY AUTOINCREMENT, student_id INTEGER, title TEXT, counseling_text TEXT, record_datetime TEXT,
                       FOREIGN KEY(student_id) REFERENCES students(id))''')
    conn.commit()

调用中间函数后，使用以下delete_student函数删除学生及关联的咨询记录时，首次运行提示删除成功，但再次操作时发现数据库中仍有残留数据，无法正常完成删除。

def delete_student():
    selected_index = student_listbox.curselection()
    if selected_index:
        student_name = student_listbox.get(selected_index)
        confirmed = messagebox.askyesno("确认", f"{student_name}을(를) 삭제하시겠습니까?")
        if confirmed:
            cursor = conn.cursor()
            # 按学生姓名查询学生id
            cursor.execute("SELECT id FROM students WHERE name=?", (student_name,))
            student_id = cursor.fetchone()
            if student_id:
                # 删除学生关联的咨询记录
                cursor.execute("DELETE FROM counseling_records WHERE student_id=?", (student_id[0],))
                # 删除学生
                cursor.execute("DELETE FROM students WHERE id=?", (student_id[0],))
                conn.commit()
                student_listbox.delete(selected_index)
                messagebox.showinfo("通知", f"{student_name}이(가) 성공적으로 삭제되었습니다.")
            else:
                messagebox.showwarning("警告", f"{student_name}을(를) 찾을 수 없습니다.")

异常原因分析 #

最可能的几个原因：

1. SQLite外键约束未启用 #

SQLite默认关闭外键约束检查，即便表结构中定义了外键关联，默认情况下这个约束不会生效。你的代码逻辑是先删关联记录再删学生，本身不依赖外键约束，但如果后续有其他操作插入关联记录，或者删除过程中出现约束相关的隐性错误，可能导致数据残留。

解决方法：创建数据库连接后立即开启外键约束：

conn.execute("PRAGMA foreign_keys = ON")

2. 数据库连接对象不一致 #

如果create_table使用的conn和delete_student中的conn不是同一个实例，会导致删除操作的提交只在当前连接会话中生效，其他连接读取数据库时仍会看到旧数据。比如你在某个函数中创建了局部连接并关闭，后续delete_student又重新创建另一个连接，或者全局conn变量被意外重新赋值，都可能出现这种情况。

3. 事务未正确提交或存在未处理异常 #

虽然代码中调用了conn.commit()，但如果commit()执行前发生未捕获的异常（比如UI操作报错），会导致事务回滚，删除操作并未真正写入数据库。另外，程序中若存在其他未提交的事务，也可能干扰当前删除操作的生效。

4. 多线程共享同一个数据库连接 #

SQLite的连接对象不是线程安全的，如果程序中存在多线程同时操作同一个conn，可能导致事务执行异常，出现数据删除不彻底的情况。

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