使用Pandas与tksheet开发账本表格遇点击无响应及保存失败求助

你的代码中单元格编辑功能已恢复，但修改内容无法保存到SQLite数据库的核心原因，通常是数据类型不匹配、UPDATE语句未匹配到目标记录，或是事务执行逻辑存在隐性错误。以下是针对性的排查和修复步骤：

1. 强制数据类型转换与错误处理 #

从tksheet.get_sheet_data()获取的所有数据默认是字符串类型，直接转换数值型字段（如Posted、Amount）时，若单元格为空或格式错误会导致静默失败。修改save_data函数，添加类型校验与转换：

def save_data():
    try:
        sheet_data = sheet.get_sheet_data()
        new_data = pd.DataFrame(sheet_data, columns=columns)
        
        # 强制转换关键字段类型，兼容空值场景
        new_data['JournalId'] = new_data['JournalId'].astype(int)
        new_data['LedgerId'] = new_data['LedgerId'].astype(int)
        new_data['Posted'] = new_data['Posted'].astype(int)
        new_data['Amount'] = new_data['Amount'].astype(float)
        new_data['Fund_Id'] = new_data['Fund_Id'].apply(lambda x: int(x) if x.strip() else None)
        new_data['Account_Id'] = new_data['Account_Id'].apply(lambda x: int(x) if x.strip() else None)

        cursor = conn.cursor()
        journal_affected = 0
        ledger_affected = 0
        
        for _, row in new_data.iterrows():
            # 更新Journal表
            cursor.execute("""
                UPDATE Journal
                SET User_date = ?, Description = ?, Posted = ?
                WHERE Id = ?
            """, (row['User_date'], row['Description'], row['Posted'], row['JournalId']))
            journal_affected += cursor.rowcount
            
            # 更新Ledger表
            cursor.execute("""
                UPDATE Ledger
                SET Amount = ?, Fund_Id = ?, Account_Id = ?
                WHERE Id = ?
            """, (row['Amount'], row['Fund_Id'], row['Account_Id'], row['LedgerId']))
            ledger_affected += cursor.rowcount
        
        conn.commit()
        messagebox.showinfo("Success", f"Changes saved successfully.\nJournal updated: {journal_affected} rows\nLedger updated: {ledger_affected} rows")
    except ValueError as ve:
        conn.rollback()
        messagebox.showerror("Data Format Error", f"Please check input values: {str(ve)}")
    except Exception as e:
        conn.rollback()
        messagebox.showerror("Error", str(e))

2. 禁止主键列被误编辑 #

如果用户不小心修改了JournalId或LedgerId列，会导致UPDATE语句找不到目标记录。在创建sheet后添加以下代码，锁定主键列：

# 获取主键列索引
journal_id_col = columns.index('JournalId')
ledger_id_col = columns.index('LedgerId')
# 设置为不可编辑
sheet.set_column_editability(column=journal_id_col, editability=False)
sheet.set_column_editability(column=ledger_id_col, editability=False)

3. 显式控制事务提交 #

虽然SQLite的with conn上下文会自动提交事务，但显式使用cursor并手动提交/回滚，能更清晰地控制事务逻辑，避免隐性错误。

最终完整修复代码 #

整合以上修改后的完整代码如下：

import sqlite3
import pandas as pd
import tkinter as tk
from tksheet import Sheet
from tkinter import messagebox

# Connect to SQLite database
conn = sqlite3.connect("your_ledger.db")
cursor = conn.cursor()

# Load joined data from Journal, Ledger, Fund, and Chart
def load_data():
    query = """
        SELECT
            Ledger.Id AS LedgerId,
            Journal.Id AS JournalId,
            Journal.User_date,
            Journal.Description,
            Journal.Posted,
            Ledger.Amount,
            Fund.Id AS Fund_Id,
            Fund.Name AS Fund_Name,
            Chart.Id AS Account_Id,
            Chart.Name AS Account_Name
        FROM Ledger
        JOIN Journal ON Ledger.Tran_id = Journal.Id
        LEFT JOIN Fund ON Ledger.Fund_Id = Fund.Id
        LEFT JOIN Chart ON Ledger.Account_Id = Chart.Id
        WHERE Journal.Deleted = 0
    """
    df = pd.read_sql_query(query, conn)
    return df

# Save updated data from sheet to DB in one transaction
def save_data():
    try:
        sheet_data = sheet.get_sheet_data()
        new_data = pd.DataFrame(sheet_data, columns=columns)
        
        # 强制转换字段类型，兼容空值
        new_data['JournalId'] = new_data['JournalId'].astype(int)
        new_data['LedgerId'] = new_data['LedgerId'].astype(int)
        new_data['Posted'] = new_data['Posted'].astype(int)
        new_data['Amount'] = new_data['Amount'].astype(float)
        new_data['Fund_Id'] = new_data['Fund_Id'].apply(lambda x: int(x) if x.strip() else None)
        new_data['Account_Id'] = new_data['Account_Id'].apply(lambda x: int(x) if x.strip() else None)

        cursor = conn.cursor()
        journal_affected = 0
        ledger_affected = 0
        
        for _, row in new_data.iterrows():
            # 更新Journal表
            cursor.execute("""
                UPDATE Journal
                SET User_date = ?, Description = ?, Posted = ?
                WHERE Id = ?
            """, (row['User_date'], row['Description'], row['Posted'], row['JournalId']))
            journal_affected += cursor.rowcount
            
            # 更新Ledger表
            cursor.execute("""
                UPDATE Ledger
                SET Amount = ?, Fund_Id = ?, Account_Id = ?
                WHERE Id = ?
            """, (row['Amount'], row['Fund_Id'], row['Account_Id'], row['LedgerId']))
            ledger_affected += cursor.rowcount
        
        conn.commit()
        messagebox.showinfo("Success", f"Changes saved successfully.\nJournal updated: {journal_affected} rows\nLedger updated: {ledger_affected} rows")
    except ValueError as ve:
        conn.rollback()
        messagebox.showerror("Data Format Error", f"Please check input values: {str(ve)}")
    except Exception as e:
        conn.rollback()
        messagebox.showerror("Error", str(e))

# GUI setup
root = tk.Tk()
root.title("Ledger Entry Form")

df = load_data()
columns = df.columns.tolist()

sheet = Sheet(root,
              data=df.values.tolist(),
              headers=columns,
              editable=True)
# 启用必要绑定
sheet.enable_bindings(("single_select", "edit_cell"))
# 锁定主键列不可编辑
journal_id_col = columns.index('JournalId')
ledger_id_col = columns.index('LedgerId')
sheet.set_column_editability(column=journal_id_col, editability=False)
sheet.set_column_editability(column=ledger_id_col, editability=False)

sheet.pack(expand=True, fill="both")

save_button = tk.Button(root, text="Save Changes", command=save_data)
save_button.pack(pady=10)

root.mainloop()

# 关闭数据库连接
conn.close()

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