如何在CustomTkinter表头框架插入数据库记录并实现排序？

我是GUI开发新手，现有基于CustomTkinter编写的代码，希望实现两个功能：

• 将SQLite数据库中的记录插入到DatabaseFrame的对应列中（此前尝试遍历SQLite记录列表但仅能访问第二个索引）
• 实现按Subject或Date字段对记录进行排序

完整修改代码 #

import tkinter as tk
from tkinter import messagebox
import customtkinter as ctk
import sqlite3
from datetime import datetime

class FieldsFrame(ctk.CTkFrame):
    def __init__(self, parent, **kwargs):
        super().__init__(parent, **kwargs)

        # Grid Configuration
        self.rowconfigure(0, weight=1)
        for col in range(7):
            self.columnconfigure(col, weight=1)

        self.grid(row=0, column=0, columnspan=3, sticky="WES", padx=10, pady=0)
        
        # Create Widgets
        self._create_widgets()

    def _create_widgets(self):
        options = {'padx':5, 'pady':5, 'font':("Century Gothic", 20)}
        labels = [
            ('rowid', 0), ('Subject', 1), ('Score', 2),
            ('Total', 3), ('Worktype',4), ('Date',5), ('Evaluation',6)
        ]
        for text, col in labels:
            label = ctk.CTkLabel(self, text=text, **options)
            label.grid(row=0, column=col, sticky="N")

class DatabaseFrame(ctk.CTkScrollableFrame):
    def __init__(self, parent, db_conn, **kwargs):
        super().__init__(parent, **kwargs)
        self.db_conn = db_conn
        self.current_sort = None
        self.current_order = "ASC"
        
        # Grid Config
        for col in range(7):
            self.columnconfigure(col, weight=1)

        self.grid(row=1, column=0, columnspan=3, sticky="NWES", padx=10, pady=10)

        # 存储所有行的标签，方便清空和更新
        self.row_labels = []

    def clear_data(self):
        # 清空现有数据标签
        for labels in self.row_labels:
            for label in labels:
                label.destroy()
        self.row_labels.clear()

    def load_data(self, sort_by=None):
        # 切换排序顺序
        if sort_by == self.current_sort:
            self.current_order = "DESC" if self.current_order == "ASC" else "ASC"
        else:
            self.current_sort = sort_by
            self.current_order = "ASC"
        
        # 清空旧数据
        self.clear_data()
        
        cursor = self.db_conn.cursor()
        # 构建查询语句，支持排序和自动计算得分率
        query = "SELECT rowid, subject, score, total, worktype, date, " \
                "CASE WHEN total > 0 THEN ROUND((score*100.0)/total, 2) || '%' ELSE 'N/A' END AS evaluation " \
                "FROM scores"
        if sort_by and sort_by in ["subject", "date"]:
            query += f" ORDER BY {sort_by} {self.current_order}"
        
        cursor.execute(query)
        records = cursor.fetchall()

        # 遍历所有记录创建标签
        for row_idx, record in enumerate(records):
            row_label_list = []
            for col_idx, value in enumerate(record):
                # 格式化日期显示
                if col_idx == 5:
                    try:
                        value = datetime.strptime(value, '%Y-%m-%d').strftime('%Y-%m-%d')
                    except:
                        pass
                label = ctk.CTkLabel(self, text=str(value), font=("Century Gothic", 16))
                label.grid(row=row_idx, column=col_idx, sticky="NSEW", padx=2, pady=2)
                row_label_list.append(label)
            self.row_labels.append(row_label_list)

class InputFrame(ctk.CTkFrame):
    def __init__(self, parent, db_conn, db_frame, **kwargs):
        super().__init__(parent, **kwargs)
        self.db_conn = db_conn
        self.db_frame = db_frame

        # Grid Layout Configuration
        for row in range(3):
            self.rowconfigure(row, weight=1)
        self.columnconfigure(0, weight=1)
        self.columnconfigure(1, weight=2)
        self.columnconfigure(2, weight=1)
        self.columnconfigure(3, weight=2)
        self.columnconfigure(4, weight=1)

        self.configure(fg_color="#f0f0f0")
        self._create_widgets()
        self.grid(row=2, column=1, columnspan=1, sticky="nsew", ipadx=20, ipady=20, padx=30, pady=10)

    def _create_widgets(self):
        _label_options = {'padx':10, 'pady':5, 'font':("Century Gothic", 20)}
        _entry_options = {'fg_color':"white", 'corner_radius':10, 'font':("Century Gothic", 16)}

        # 存储输入控件，方便获取值
        self.entries = {}

        # Subject输入
        self.entries['subject_label'] = ctk.CTkLabel(self, text="Enter subject:", **_label_options)
        self.entries['subject_label'].grid(row=0, column=0, sticky="w")
        self.entries['subject'] = ctk.CTkEntry(self, placeholder_text="e.g. Math", **_entry_options)
        self.entries['subject'].grid(row=0, column=1, sticky="ew")

        # Score输入
        self.entries['score_label'] = ctk.CTkLabel(self, text="Enter score:", **_label_options)
        self.entries['score_label'].grid(row=0, column=2, sticky="w")
        self.entries['score'] = ctk.CTkEntry(self, placeholder_text="e.g. 85", **_entry_options)
        self.entries['score'].grid(row=0, column=3, sticky="ew")

        # Total输入
        self.entries['total_label'] = ctk.CTkLabel(self, text="Score over total:", **_label_options)
        self.entries['total_label'].grid(row=1, column=2, sticky="w")
        self.entries['total'] = ctk.CTkEntry(self, placeholder_text="e.g. 100", **_entry_options)
        self.entries['total'].grid(row=1, column=3, sticky="ew")

        # Worktype输入
        self.entries['worktype_label'] = ctk.CTkLabel(self, text="Enter worktype:", **_label_options)
        self.entries['worktype_label'].grid(row=1, column=0, sticky="w")
        self.entries['worktype'] = ctk.CTkEntry(self, placeholder_text="e.g. Exam", **_entry_options)
        self.entries['worktype'].grid(row=1, column=1, sticky="ew")

        # Date输入
        self.entries['date_label'] = ctk.CTkLabel(self, text="Enter date:", **_label_options)
        self.entries['date_label'].grid(row=2, column=0, sticky="w")
        self.entries['date'] = ctk.CTkEntry(self, placeholder_text="YYYY-MM-DD", **_entry_options)
        self.entries['date'].grid(row=2, column=1, sticky="ew")

        # 提交按钮
        self.submit_btn = ctk.CTkButton(
            self,
            text="Add Record",
            font=("Century Gothic", 18),
            command=self._submit_record
        )
        self.submit_btn.grid(row=2, column=3, sticky="ew", padx=10)

    def _submit_record(self):
        # 获取输入值并去空格
        subject = self.entries['subject'].get().strip()
        score = self.entries['score'].get().strip()
        total = self.entries['total'].get().strip()
        worktype = self.entries['worktype'].get().strip()
        date = self.entries['date'].get().strip()

        # 空值验证
        if not all([subject, score, total, worktype, date]):
            messagebox.showwarning("Warning", "Please fill all fields!")
            return
        
        # 数据格式验证
        try:
            score = int(score)
            total = int(total)
            datetime.strptime(date, '%Y-%m-%d')
        except ValueError:
            messagebox.showwarning("Warning", "Invalid input: score/total must be numbers, date must be YYYY-MM-DD!")
            return

        # 插入数据库
        cursor = self.db_conn.cursor()
        cursor.execute("""
            INSERT INTO scores (subject, score, total, worktype, date)
            VALUES (?, ?, ?, ?, ?)
        """, (subject, score, total, worktype, date))
        self.db_conn.commit()

        # 清空输入框
        for key in ['subject', 'score', 'total', 'worktype', 'date']:
            self.entries[key].delete(0, tk.END)

        # 重新加载数据
        self.db_frame.load_data(self.db_frame.current_sort)

class MainApp(ctk.CTk):
    def __init__(self):
        super().__init__()
        self.width = self.winfo_screenwidth()
        self.height = self.winfo_screenheight()
        self.title("Score Tracker")
        self.geometry(f"{self.width}x{self.height}")
        self.state("zoomed")
        
        # 数据库初始化
        self.db_conn = sqlite3.connect("scores.db")
        self._init_database()

        # 主窗口网格配置
        self.rowconfigure(0, weight=1)
        self.rowconfigure(1, weight=4)
        self.rowconfigure(2, weight=1)
        self.columnconfigure(0, weight=1)
        self.columnconfigure(1, weight=2)
        self.columnconfigure(2, weight=1)

        # 创建各组件
        self.fields_frame = FieldsFrame(self)
        self.db_frame = DatabaseFrame(self, self.db_conn)
        self.input_frame = InputFrame(self, self.db_conn, self.db_frame)

        # 排序按钮区域
        self.sort_frame = ctk.CTkFrame(self)
        self.sort_frame.grid(row=0, column=2, sticky="E", padx=10, pady=5)

        self.sort_subject_btn = ctk.CTkButton(
            self.sort_frame,
            text="Sort by Subject",
            font=("Century Gothic", 16),
            command=lambda: self.db_frame.load_data("subject")
        )
        self.sort_subject_btn.grid(row=0, column=0, padx=5, pady=5)

        self.sort_date_btn = ctk.CTkButton(
            self.sort_frame,
            text="Sort by Date",
            font=("Century Gothic", 16),
            command=lambda: self.db_frame.load_data("date")
        )
        self.sort_date_btn.grid(row=0, column=1, padx=5, pady=5)

        # 加载初始数据
        self.db_frame.load_data()

    def _init_database(self):
        # 创建分数表
        cursor = self.db_conn.cursor()
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS scores (
                subject TEXT NOT NULL,
                score INTEGER NOT NULL,
                total INTEGER NOT NULL,
                worktype TEXT NOT NULL,
                date TEXT NOT NULL
            )
        """)
        # 首次运行时添加测试数据
        cursor.execute("SELECT COUNT(*) FROM scores")
        if cursor.fetchone()[0] == 0:
            test_data = [
                ("Math", 85, 100, "Exam", "2024-05-15"),
                ("English", 92, 100, "Quiz", "2024-05-10"),
                ("Physics", 78, 100, "Homework", "2024-05-20")
            ]
            cursor.executemany("INSERT INTO scores VALUES (?, ?, ?, ?, ?)", test_data)
            self.db_conn.commit()

if __name__ == "__main__":
    Main_app = MainApp()
    Main_app.mainloop()

关键功能说明 #

1. 解决数据遍历显示问题 #

之前只能访问第二个索引，是因为循环中复用变量导致标签引用被覆盖。修改后：

• 用self.row_labels存储每一行的所有标签，确保每条记录的控件独立
• 遍历所有SQLite查询结果，为每个字段创建单独的CTkLabel并绑定到对应网格位置
• 自动计算Evaluation字段的得分率，无需手动输入

2. SQLite数据库集成 #

• 在主窗口初始化数据库连接，自动创建scores表
• 首次运行时插入测试数据，方便快速验证功能
• 输入框添加提交按钮，完成数据验证后插入数据库，自动刷新列表

3. 排序功能实现 #

• DatabaseFrame的load_data方法支持按指定字段排序，点击排序按钮可切换升序/降序
• 基于SQL的ORDER BY子句实现高效排序，避免在Python中处理大量数据
• 记录当前排序状态，重复点击同一按钮自动切换排序方向

4. 用户体验优化 #

• 添加输入验证，防止空值、非法数字和错误日期格式插入数据库
• 提交成功后自动清空输入框，提升操作流畅度
• 用消息框提示错误信息，明确告知用户问题所在

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