Python RadioButton使用问题：Treeview重复插入报错与数据筛选需求

错误根源 #

1. Treeview未清空旧数据：切换年月时没有删除已有的条目，插入相同iid（交易ID）的记录时触发Item already exists错误。
2. 查询逻辑遗漏年份过滤：原代码仅过滤月份，未结合选中的年份，导致返回所有年份的该月份数据，加剧重复ID问题。
3. 未处理未选月份的情况：用户未选择月份就切换年份时，会触发KeyError。

修改后的完整代码 #

import tkinter as tk
from tkinter import ttk
import sqlite3
import datetime

today = datetime.datetime.now()
year = today.year
year1 = today.year + 1
year2 = today.year - 1

root = tk.Tk()
root.geometry("1000x500")
root.title("practice")

my_path = r'E:\Sales-and-Inventory-Management-System-master\login.db'

r1_v = tk.IntVar(value=year)
my_conn = sqlite3.connect(my_path)

tracing = tk.StringVar()
my_dictionary = {'Jan': '01', 'Feb': '02', 'Mar': '03', 'Apr': '04', 'May': '05', 'Jun': '06', 
                 'July': '07', 'Aug': '08', 'Sep': '09', 'Oct': '10', 'Nov': '11', 'Dec': '12'}

def my_function(*args):
    # 清空Treeview所有旧条目
    for item in trv.get_children():
        trv.delete(item)
    l2.config(text="Total: 0 ssp")  # 重置总计显示
    
    # 检查月份是否已选择
    selected_month = tracing.get()
    if not selected_month:
        return
    
    selected_year = str(r1_v.get())
    month_num = my_dictionary[selected_month]
    
    # 同时过滤年份和月份的参数化查询（避免SQL注入）
    query = """SELECT Trans_id, invoice, Product_id, Quantity, strftime('%m-%Y', Date) as month, Time 
               FROM sales 
               WHERE strftime('%Y', Date) = ? AND strftime('%m', Date) = ?"""
    r_set = my_conn.execute(query, (selected_year, month_num))
    saleslist = r_set.fetchall()
    
    total = 0
    for dt in saleslist:
        # 查询对应产品详情
        prod_result = my_conn.execute("select product_desc, units, product_price from products where product_id=?",
                                  (int(dt[2]),)).fetchone()
        if not prod_result:
            continue  # 无对应产品则跳过该记录
        
        # 组装显示数据
        trans_id = dt[0]
        invoice = dt[1]
        prod_id = dt[2]
        desc = prod_result[0]
        qty = dt[3]
        units = prod_result[1]
        price = round(prod_result[2] * int(qty), 2)
        month = dt[4]
        
        # 插入Treeview
        trv.insert("", 'end', iid=trans_id, values=(trans_id, invoice, prod_id, desc, qty, units, price, month))
        total += price
    
    l2.config(text=f"Total: {round(total, 2)} ssp")

months = list(my_dictionary.keys())

cb1 = ttk.Combobox(root, values=months, width=10, textvariable=tracing)
cb1.grid(row=1, column=0, padx=10, pady=20, sticky='w')

r1 = tk.Radiobutton(root, text=year2, variable=r1_v, value=year2)
r1.grid(row=1, column=1, padx=10, pady=10, sticky='w')

r2 = tk.Radiobutton(root, text=year, variable=r1_v, value=year)
r2.grid(row=1, column=2, padx=10, sticky='w')

r3 = tk.Radiobutton(root, text=year1, variable=r1_v, value=year1)
r3.grid(row=1, column=3, padx=10, sticky='w')

scrollbary = ttk.Scrollbar(root, orient=VERTICAL)
trv = ttk.Treeview(root, columns=("Transaction ID","Invoice No.", "Product ID", "Description",
                                  "Quantity", "Units", "Price", "Month"), selectmode="browse", height=22,
                   yscrollcommand=scrollbary.set)
trv.column('#0', stretch=tk.NO, minwidth=0, width=0)
trv.column('#1', stretch=tk.NO, minwidth=0, width=120)
trv.column('#2', stretch=tk.NO, minwidth=0, width=120)
trv.column('#3', stretch=tk.NO, minwidth=0, width=140)
trv.column('#4', stretch=tk.NO, minwidth=0, width=160)
trv.column('#5', stretch=tk.NO, minwidth=0, width=120)
trv.column('#6', stretch=tk.NO, minwidth=0, width=120)
trv.column('#7', stretch=tk.NO, minwidth=0, width=120)

trv.heading('Transaction ID', text="Transaction ID", anchor=tk.W)
trv.heading('Invoice No.', text="Invoice No.", anchor=tk.W)
trv.heading('Product ID', text="Product ID", anchor=tk.W)
trv.heading('Description', text="Description", anchor=tk.W)
trv.heading('Quantity', text="Quantity", anchor=tk.W)
trv.heading('Units', text="Units", anchor=tk.W)
trv.heading('Price', text="Price", anchor=tk.W)
trv.heading('Month', text="Month", anchor=tk.W)

trv.grid(row=2, column=0, padx=10, pady=10)
scrollbary.config(command=trv.yview)
scrollbary.grid(row=2, column=1, sticky="ns", pady=10)

tracing.trace('w', my_function)
r1_v.trace('w', my_function)

l2 = tk.Label(root, font=('Times', 22, 'bold'), bg="#63B8FF", fg="red")
l2.grid(row=3, column=0, sticky='ne', pady=0)

root.mainloop()

核心修改说明 #

• 清空Treeview：每次查询前删除所有旧条目，彻底避免重复ID冲突。
• 修正查询逻辑：同时添加年份和月份过滤条件，使用参数化查询杜绝SQL注入风险，确保仅返回指定年月的数据。
• 增加边界处理：判断月份是否选择、产品是否存在，避免运行时错误。
• 代码优化：用fetchone()获取单条产品数据，简化代码结构；使用f-string格式化文本，提升可读性。

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