SQLite3 UPDATE未更新数据库 PySimpleGUI表格不刷新排查

• 系统：最新版Mac OS
• 运行时：最新版Python 3
• 依赖：Python标准库SQLite3、GUI库PySimpleGUI

执行UPDATE语句后数据库无实际更新，GUI中PySimpleGUI表格元素无法同步刷新最新数据。
本人有2个月PySimpleGUI开发经验，已查阅PySimpleGUI、SQLite官方文档，清楚当前CRUD功能未开发完成（DELETE功能计划在UPDATE问题修复后调试），本次需要明确UPDATE失效的根因，掌握正确SQL写法，支撑后续DELETE功能开发、多表自定义查询实现。

import PySimpleGUI as sg
import sqlite3
from sqlite3 import Error

sg.Theme=('Purple')

def Customer_CRUD():
    
    def retrieve_customer():

        cust_details=[]
        conn=sqlite3.connect('Accounts.db')
        cursor=conn.execute('SELECT ID,Name,Bill,Site,Phone,Email FROM Customers')
        for row in cursor:
            cust_details.append(list(row))
        return cust_details

    table_data=retrieve_customer()

    headings=['ID','Name','Bill Address','Site Address','Phone','Email']

    cust_window_layout=[
        [sg.T('Customer CRUD For',font=('ariel',16))],
        [sg.T('Customer ID:',font=('ariel',14)),sg.InputText(font=('ariel',14),key='-ID-',disabled_readonly_background_color='Grey',disabled=True)],
        [sg.T('Customer Name:',font=('ariel',14)),sg.InputText(font=('ariel',14),size=(50,1),key='-Name-')],
        [sg.T('Billing Address:',font=('ariel',14)),sg.InputText(font=('ariel',14),size=(50,1),key='-Bill-')],
        [sg.T('Site Address:',font=('ariel',14)),sg.InputText(font=('ariel',14),size=(50,1),key='-Site-')],
        [sg.T('Phone:',font=('ariel',14)),sg.InputText(font=('ariel',14),size=(10,1),key='-Phone-'),sg.T('Email:',font=('ariel',14)),sg.InputText(font=('ariel',14),size=(25,1),key='-Email-')],
        [sg.Button('Submit',font=('ariel',16)),sg.Button('Update',font=('ariel',16)),sg.Button('Delete',font=('ariel',16))],
        [sg.Table(values=table_data,headings=headings,
                  font=('ariel',14),
                  max_col_width=50,
                  display_row_numbers=False,
                  justification='center',
                  vertical_scroll_only=False,
                  select_mode=sg.TABLE_SELECT_MODE_BROWSE,
                  num_rows=10,
                  enable_events=True,
                  key='-Table-')],
        [sg.Button('Main',font=('ariel',16)),sg.Button('Back',font=('riel',16))],
         ]
                  
    cust_window=sg.Window('Pool Resurfacing NQ Accounts',cust_window_layout)

    #define all sub processes here

    def clear_input():
        for key in values:
            cust_window[key]('')
            return None
        
    def add_customer(Name,Bill,Site,Phone,Email):

        conn=sqlite3.connect('Accounts.db')
        cursor=conn.execute('INSERT INTO Customers(Name,Bill,Site,Phone,Email) \
            VALUES(?,?,?,?,?)', (Name,Bill,Site,Phone,Email))
        conn.commit()
        conn.close()

        sg.Popup('Values successfully added',font=('ariel',16))

        cust_window['-Table-'].update(table_data)
        cust_window.refresh()

    def update_customer(Name,Bill,Site,Phone,Email,ID):

            conn=sqlite3.connect('Accounts.db')
            cursor=conn.execute('UPDATE Customers SET Name=?,Bill=?,Site=?,Phone=?,Email=? WHERE ID=?', (str(Name),str(Bill),str(Site),str(Phone),str(Email),str(ID)))
            conn.commit()
            conn.close()

            sg.Popup('Values successfully updated',font=('ariel',16))

            retrieve_customer()
            cust_window['-Table-'].update(table_data)
            cust_window.refresh()
            
    def delete_customer():

        conn=sqlite3.connect('Accounts.db')
        cursor=conn.execute('Delete FROM Customers \
            WHERE ID=cust_window["-ID-"]')
        conn.commit()
        conn.close()
        sg.Popup('Values successfully deleted',font=('ariel',16))
        cust_window['-Table-'].update(table_data)
        cust_window.refresh()

    while True:

        event,values=cust_window.read()

        if event==sg.WIN_CLOSED:
            break

        if event=='Submit':

            name=values['-Name-']
            if name=='':
                sg.popup('Missing Information:','Customer Name')

            bill=values['-Bill-']
            if bill=='':
                sg.popup('Missing Information:','Billing Address')

            site=values['-Site-']
            if site=='':
                sg.popup('Missing Information:','Site Address')

            phone=values['-Phone-']
            if phone=='':
                sg.popup('Missing Information:','Phone Number')

            email=values['-Email-']
            if email=='':
                sg.popup('Missing Information:','Email Address')

            else:
                add_customer(values['-Name-'],values['-Bill-'],values['-Site-'],values['-Phone-'],values['-Email-'])
                sg.popup('Your record has been saved successfully',font=('ariel',16))
                clear_input()

        if event=='-Table-':

            try:
                row=values['-Table-'][0]
                table_value=table_data[row]

                for i in table_value:
                    cust_window['-ID-'].update(table_value[0])
                    cust_window['-Name-'].update(table_value[1])
                    cust_window['-Bill-'].update(table_value[2])
                    cust_window['-Site-'].update(table_value[3])
                    cust_window['-Phone-'].update(table_value[4])
                    cust_window['-Email-'].update(table_value[5])
            except IndexError:
                continue

        if event=='Update':

            NAME=cust_window['-Name-']
            BILL=cust_window['-Bill-']
            SITE=cust_window['-Site-']
            PHONE=cust_window['-Phone-']
            EMAIL=cust_window['-Email-']
            IDENT=cust_window['-ID-']
            
            update_customer(NAME,BILL,SITE,PHONE,EMAIL,IDENT)

        if event=='Delete':
            delete_customer()


        if event=='Main':
            None#main_menu()

        if event=='Back':
            None#income()

    cust_window.close()
            
Customer_CRUD()

一共两个核心问题导致UPDATE失效、表格不刷新，另外还有多个遗留bug会影响后续功能：

1. 传参类型错误：Update按钮事件中，直接通过cust_window['key']取到的是输入控件对象，不是输入框内的文本值。把控件对象转成字符串传入UPDATE语句后，WHERE条件匹配不到任何对应ID的记录，数据库自然不会更新。
2. 表格数据源未更新：update_customer中虽然调用了retrieve_customer()查询最新数据，但没有把返回结果赋值给全局的table_data变量，表格更新时用的还是窗口初始化时的旧数据，看不到变化。
3. 其他遗留问题：
   • 主题设置写法错误，sg.Theme=('Purple')是属性赋值的错误写法，不会生效
   • Back按钮字体名拼写错误，'riel'应为'ariel'
   • clear_input函数中写了return None，清空第一个输入框后就会直接退出，无法清空全部输入项
   • add_customer插入新数据后同样没有更新table_data，新增记录后表格也不会刷新
   • delete_customer的SQL语句写法错误，直接把控件引用写在SQL字符串中，既拿不到正确ID值，还存在SQL注入风险

1. 基础配置修正 #

# 修正主题设置
sg.theme('Purple')
# Back按钮字体名修正
[sg.Button('Main',font=('ariel',16)),sg.Button('Back',font=('ariel',16))]

2. Update事件取值逻辑修正 #

和Submit按钮保持一致，通过values字典取输入框的实际值：

if event=='Update':
    NAME=values['-Name-']
    BILL=values['-Bill-']
    SITE=values['-Site-']
    PHONE=values['-Phone-']
    EMAIL=values['-Email-']
    IDENT=values['-ID-']
    update_customer(NAME,BILL,SITE,PHONE,EMAIL,IDENT)

3. update_customer函数修正 #

通过nonlocal引用外层的table_data变量，查询到最新数据后重新赋值再刷新表格，不需要手动转字符串类型，sqlite3会自动适配：

def update_customer(Name,Bill,Site,Phone,Email,ID):
    nonlocal table_data
    conn=sqlite3.connect('Accounts.db')
    conn.execute('UPDATE Customers SET Name=?,Bill=?,Site=?,Phone=?,Email=? WHERE ID=?', (Name,Bill,Site,Phone,Email,ID))
    conn.commit()
    conn.close()
    sg.Popup('Values successfully updated',font=('ariel',16))
    table_data = retrieve_customer()
    cust_window['-Table-'].update(values=table_data)
    cust_window.refresh()

4. 其他关联函数同步修正 #

add_customer刷新逻辑修正 #

def add_customer(Name,Bill,Site,Phone,Email):
    nonlocal table_data
    conn=sqlite3.connect('Accounts.db')
    conn.execute('INSERT INTO Customers(Name,Bill,Site,Phone,Email) VALUES(?,?,?,?,?)', (Name,Bill,Site,Phone,Email))
    conn.commit()
    conn.close()
    sg.Popup('Values successfully added',font=('ariel',16))
    table_data = retrieve_customer()
    cust_window['-Table-'].update(values=table_data)
    cust_window.refresh()

clear_input逻辑修正 #

def clear_input():
    for key in ['-ID-','-Name-','-Bill-','-Site-','-Phone-','-Email-']:
        cust_window[key]('')

DELETE功能参考写法 #

后续开发DELETE功能时，同样使用?占位符传参，更新后重新拉取数据刷新表格即可：

# 函数定义
def delete_customer(cust_id):
    nonlocal table_data
    conn=sqlite3.connect('Accounts.db')
    conn.execute('DELETE FROM Customers WHERE ID=?', (cust_id,))
    conn.commit()
    conn.close()
    sg.Popup('Values successfully deleted',font=('ariel',16))
    table_data = retrieve_customer()
    cust_window['-Table-'].update(values=table_data)
    cust_window.refresh()

# 事件调用
if event=='Delete':
    if values['-ID-']:
        delete_customer(values['-ID-'])
        clear_input()
    else:
        sg.popup('请先选择要删除的记录')

SQLite的CRUD语句统一使用?作为占位符传入参数，不要直接拼接字符串，既能避免类型转换错误，也能防范SQL注入风险。

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