如何为基于Tkinter的SQLite应用实现UPDATE命令

Hey there! I see you've got a solid start on your Tkinter SQLite data management app—insert and delete are working, but the update functionality is missing. The main issue right now is that your edit window only displays existing data without letting you save changes. Let's fix that step by step, plus we'll make your record operations more reliable by using the database's primary key (id) instead of relying on names (which might repeat).

Here's the full revised code with working edit/update functionality:

from tkinter import ttk
from tkinter import *
import sqlite3

class cadastro:
    # 数据库连接路径属性
    db_name = 'database.db'

    def __init__(self, window):
        # 初始化操作
        self.wind = window
        self.wind.title('cadastro DataSet')

        # 创建框架容器
        frame = LabelFrame(self.wind, text = '注册新记录')
        frame.grid(row = 0, column = 0, columnspan = 3, pady = 20, padx=20)

        # 名称输入框
        Label(frame, text = 'Name: ').grid(row = 1, column = 0, sticky=W)
        self.name = Text(frame, width=20,height=3)
        self.name.config(font=('consolas', 12), undo=True, wrap='word')
        self.name.focus()
        self.name.grid(row = 1, column = 1, sticky=W+E)
        self.scrollb_name = Scrollbar(frame, command=self.name.yview)
        self.scrollb_name.grid(row=1, column=2, sticky='ns')
        self.name['yscrollcommand'] = self.scrollb_name.set

        # 描述输入框
        Label(frame, text = 'Description: ').grid(row = 2, column = 0, sticky=W)
        self.description = Text(frame, width=20,height=3)
        self.description.config(font=('consolas', 12), undo=True, wrap='word')
        self.description.grid(row = 2, column = 1, sticky=W+E)
        self.scrollb_desc = Scrollbar(frame, command=self.description.yview)
        self.scrollb_desc.grid(row=2, column=2, sticky='ns')
        self.description['yscrollcommand'] = self.scrollb_desc.set

        # 位置输入框
        Label(frame, text = 'Local: ').grid(row = 3, column = 0, sticky=W)
        self.local = Text(frame,width=20,height=3)
        self.local.config(font=('consolas', 12), undo=True, wrap='word')
        self.local.grid(row = 3, column = 1, sticky=W+E)
        self.scrollb_local = Scrollbar(frame, command=self.local.yview)
        self.scrollb_local.grid(row=3, column=2, sticky='ns')
        self.local['yscrollcommand'] = self.scrollb_local.set

        # 树形视图组件
        self.tree = ttk.Treeview(frame,columns=('Description', 'Local'))
        self.tree.heading('#0', text='Name', anchor= CENTER)
        self.tree.heading('#1', text='Description', anchor= CENTER)
        self.tree.heading('#2', text='Local', anchor= CENTER)
        self.tree.grid(row = 4, column = 0, columnspan = 3, ipady=10, pady=10)
        self.scrollb_tree = Scrollbar(frame, command=self.tree.yview)
        self.scrollb_tree.grid(row=4, column=3, sticky='ns')
        self.tree['yscrollcommand'] = self.scrollb_tree.set

        # 添加记录按钮
        ttk.Button(frame, text = '插入', command = self.add_cadastro).grid(row = 5, column = 0, sticky = W + E, padx=5)
        # 删除记录按钮
        ttk.Button(frame, text = 'DELETE', command = self.delete_cadastro).grid(row = 5, column = 1, sticky = W + E, padx=5)
        # 编辑记录按钮
        ttk.Button(frame, text = 'EDIT', command = self.edit_cadastro).grid(row = 5, column = 2, sticky = W + E, padx=5)

        # 输出消息组件
        self.message = Label(text = '', fg = 'red')
        self.message.grid(row = 6, column = 0, columnspan = 3, sticky = W + E, padx=20)

        # 填充数据行
        self.get_cadastro()

    # 执行数据库查询的函数
    def run_query(self, query, parameters = ()):
        with sqlite3.connect(self.db_name) as conn:
            cursor = conn.cursor()
            cursor.execute('CREATE TABLE IF NOT EXISTS cadastro (id INTEGER PRIMARY KEY , name TEXT, description TEXT, local TEXT)')
            result = cursor.execute(query, parameters)
            conn.commit()
            return result

    # 获取记录
    def get_cadastro(self):
        # 清空表格
        records = self.tree.get_children()
        for element in records:
            self.tree.delete(element)
        query = 'SELECT * FROM cadastro ORDER BY name DESC'
        db_rows = self.run_query(query)
        for row in db_rows:
            # 将数据库id作为Treeview item的唯一标识（iid）
            self.tree.insert('', 0, iid=str(row[0]), text = row[1], values = (row[2],row[3]))

    # 用户输入验证
    def validation(self):
        # 去掉Text组件末尾的换行符后检查是否为空
        name = self.name.get(1.0, END).strip()
        description = self.description.get(1.0, END).strip()
        local = self.local.get(1.0, END).strip()
        return name and description and local

    # 添加记录
    def add_cadastro(self):
        if self.validation():
            query = 'INSERT INTO cadastro VALUES(NULL, ?, ?, ?)'
            parameters = (self.name.get(1.0, END).strip(), self.description.get(1.0, END).strip(), self.local.get(1.0, END).strip())
            self.run_query(query, parameters)
            self.message['text'] = f'记录{self.name.get(1.0, END).strip()}添加成功'
            self.name.delete(1.0, END)
            self.description.delete(1.0, END)
            self.local.delete(1.0, END)
        else:
            self.message['text'] = '名称、描述及位置为必填项'
        self.get_cadastro()

    # 删除记录（改用id确保唯一性）
    def delete_cadastro(self):
        self.message['text'] = ''
        try:
            selected_item = self.tree.selection()[0]
        except IndexError as e:
            self.message['text'] = '请选择一条记录'
            return
        
        record_id = selected_item
        name = self.tree.item(selected_item)['text']
        query = 'DELETE FROM cadastro WHERE id = ?'
        self.run_query(query, (record_id, ))
        self.message['text'] = f'记录{name}删除成功'
        self.get_cadastro()

    # 编辑记录：打开编辑窗口并填充现有数据
    def edit_cadastro(self):
        self.message['text'] = ''
        try:
            selected_item = self.tree.selection()[0]
        except IndexError as e:
            self.message['text'] = '请选择一条记录'
            return
        
        # 获取选中记录的id和现有数据
        record_id = selected_item
        current_name = self.tree.item(selected_item)['text']
        current_desc = self.tree.item(selected_item)['values'][0]
        current_local = self.tree.item(selected_item)['values'][1]

        # 创建编辑窗口
        self.edit_wind = Toplevel()
        self.edit_wind.title = '编辑记录'
        frame2 = LabelFrame(self.edit_wind, text = '编辑记录')
        frame2.grid(row = 0, column = 0, columnspan = 3, pady = 20, padx=20)

        # 名称输入框（可编辑）
        Label(frame2, text = 'Name: ').grid(row = 1, column = 0, sticky=W, pady=5)
        self.edit_name = Text(frame2, height=3, width=50)
        self.edit_name.config(font=('consolas', 12), undo=True, wrap='word')
        self.edit_name.insert(END, current_name)
        self.edit_name.grid(row = 1, column = 1, sticky=W+E, pady=5)
        scrollb = Scrollbar(frame2, command=self.edit_name.yview)
        scrollb.grid(row=1, column=2, sticky='ns')
        self.edit_name['yscrollcommand'] = scrollb.set

        # 描述输入框（可编辑）
        Label(frame2, text = 'Description: ').grid(row = 2, column = 0, sticky=W, pady=5)
        self.edit_description = Text(frame2, height=3, width=50)
        self.edit_description.config(font=('consolas', 12), undo=True, wrap='word')
        self.edit_description.insert(END, current_desc)
        self.edit_description.grid(row = 2, column = 1, sticky=W+E, pady=5)
        scrollb = Scrollbar(frame2, command=self.edit_description.yview)
        scrollb.grid(row=2, column=2, sticky='ns')
        self.edit_description['yscrollcommand'] = scrollb.set

        # 位置输入框（可编辑）
        Label(frame2, text = 'Local: ').grid(row = 3, column = 0, sticky=W, pady=5)
        self.edit_local = Text(frame2, height=3, width=50)
        self.edit_local.config(font=('consolas', 12), undo=True, wrap='word')
        self.edit_local.insert(END, current_local)
        self.edit_local.grid(row = 3, column = 1, sticky=W+E, pady=5)
        scrollb = Scrollbar(frame2, command=self.edit_local.yview)
        scrollb.grid(row=3, column=2, sticky='ns')
        self.edit_local['yscrollcommand'] = scrollb.set

        # 保存修改按钮
        ttk.Button(frame2, text='保存修改', command=lambda: self.save_edit(record_id)).grid(row=4, column=1, sticky=W+E, pady=10)

    # 保存编辑后的记录到数据库
    def save_edit(self, record_id):
        # 获取修改后的内容并去掉换行符
        new_name = self.edit_name.get(1.0, END).strip()
        new_desc = self.edit_description.get(1.0, END).strip()
        new_local = self.edit_local.get(1.0, END).strip()

        # 验证输入
        if not new_name or not new_desc or not new_local:
            self.message['text'] = '所有字段都不能为空'
            return
        
        # 执行UPDATE语句
        query = 'UPDATE cadastro SET name = ?, description = ?, local = ? WHERE id = ?'
        parameters = (new_name, new_desc, new_local, record_id)
        self.run_query(query, parameters)
        
        self.message['text'] = '记录已成功更新'
        self.edit_wind.destroy()  # 关闭编辑窗口
        self.get_cadastro()  # 刷新树形视图显示最新数据

if __name__ == '__main__':
    window = Tk()
    application = cadastro(window)
    window.mainloop()

Key Changes Explained: #

• Using Record ID for Reliability: We now store the database's unique id as the Treeview item's iid when populating the table. This avoids issues with duplicate names and ensures we always target the correct record for edit/delete.
• Editable Text Fields in Edit Window: The edit window uses instance attributes (self.edit_name, etc.) instead of local variables, so we can access the modified content later.
• Save Button & Update Logic: Added a save_edit method that grabs modified text (stripping extra newlines from Text components), validates input, runs the UPDATE SQL command, and refreshes the Treeview.
• Improved Validation: Updated validation to check for non-empty content after stripping newline characters (since Text.get(1.0, END) always includes a trailing newline).
• Cleaned Up UI: Adjusted padding and scrollbar placement for better usability.

内容的提问来源于stack exchange，提问作者András Pataki