[Python] I am using panda and tksheet to create a ledger table which I want to be able to edit...

my code is as follows -:

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:
new_data = pd.DataFrame(sheet.get_sheet_data(), columns=columns)

with conn: # Transaction context
for _, row in new_data.iterrows():
# Update Journal
conn.execute("""
UPDATE Journal
SET User_date = ?, Description = ?, Posted = ?
WHERE Id = ?
""", (row['User_date'], row['Description'], int(row['Posted']), row['JournalId']))

# Update Ledger
conn.execute("""
UPDATE Ledger
SET Amount = ?, Fund_Id = ?, Account_Id = ?
WHERE Id = ?
""", (row['Amount'], row['Fund_Id'], row['Account_Id'], row['LedgerId']))

messagebox.showinfo("Success", "Changes saved successfully.")
except Exception as e:
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)
sheet.pack(expand=True, fill="both")

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

root.mainloop()


As you can see I am loading the database query into a datafile and then into a sheet. When the button is pressed, the sheet is converted into a datafile and then the database is updated with its contents

The table does not respond to mouse clicks at all. I tried asking copilot, which suggested I set editable to True in the sheet constructor. This didn't work. It then suggested I enable the bindings in the sheet shown in my code. This didn't work either. I am at a loss, and don't understand. Could you help me?

Continue reading...