Python中实现单选按钮与SQLite3数据库的关联

开发者阿泽

2026-7-28

问题描述

需要将Tkinter中的图书分类单选按钮与SQLite3数据库关联，实现图书分类的存储、读取和修改。以下是现有前端（Tkinter）和后端（SQLite3）代码，仅需完成单选按钮与数据库的关联部分。

前端代码

import tkinter as tk
from tkinter import *
from tkinter import ttk
#import backend

root = tk.Tk()
root.title("Libreria Trivium")
root.option_add("*tearOff", False)

# Stile
style = ttk.Style(root)

# Import stile(forest-dark.tcl)
root.tk.call("source", "forest-dark.tcl")

# Set dello stile
style.theme_use("forest-dark")

# Menubar
menubar = Menu(root)
file_menu = Menu(menubar, tearoff=0)
file_menu.add_command(label="Exit", command=root.quit)
menubar.add_cascade(label="Opzioni", menu=file_menu)
root.config(menu=menubar)

# Libreria
paned = ttk.PanedWindow(root)
paned.grid(row=1, column=3, sticky="nsew")

pane = ttk.Frame(paned)
paned.add(pane, weight=1)

# Frame libreria
LibFrame = ttk.Frame(pane)
LibFrame.pack(expand=True, fill='both', padx=5, pady=5, anchor='n')

# Scrollbar
Scroll = ttk.Scrollbar(LibFrame)
Scroll.pack(side="right", fill="y")

# Libreria
Libreria = ttk.Treeview(LibFrame, selectmode="extended", yscrollcommand=Scroll.set, columns=(1, 2, 3, 4),
                        height=12)
Libreria.pack(expand=True)
Scroll.config(command=Libreria.yview)

# Colonne libreria
Libreria.column("#0", anchor="n", width=150)
Libreria.column(1, anchor="n", width=150)
Libreria.column(2, anchor="n", width=120)
Libreria.column(3, anchor="n", width=50)
Libreria.column(4, anchor="n", width=50)

# Heading della libreria
Libreria.heading("#0", text="Categoria", anchor="center")
Libreria.heading(1, text="Titolo", anchor="center")
Libreria.heading(2, text="Autore", anchor="center")
Libreria.heading(3, text="Anno", anchor="center")
Libreria.heading(4, text="Volume", anchor="center")

# Definire dati nella libreria
Libreria_data = [
    ("", "end", 1, "Medicina Olistica", ("Il Giornale dei Misteri", "A. Crowley", "1768", "34")),
]

# Dati della Libreria
for item in Libreria_data:
    Libreria.insert(parent=item[0], index=item[1], iid=item[2], text=item[3], values=item[4])

# Creazione variabili delle categorie
a = tk.IntVar(value=2)
b = tk.IntVar(value=2)
c = tk.IntVar(value=2)
d = tk.IntVar(value=2)
e = tk.IntVar(value=2)
f = tk.IntVar(value=2)
g = tk.IntVar(value=2)
h = tk.IntVar(value=2)

# Frame Categorie
categ_frame = ttk.LabelFrame(root, text="Categorie", padding=(20, 10))
categ_frame.grid(row=1, column=0, padx=(20, 10), pady=(20, 10), sticky="nsew")

selected_size = tk.StringVar()
sizes = (('Astrologia', 'Astrologia'),
         ('Alchimia', 'Alchimia'),
         ('Misticismo', 'Misticismo'),
         ('Para/Parapsicologia', 'Para/Parapsicologia'),
         ('Letteratura', 'Letteratura'),
         ('Antropologia', 'Antropologia'),
         ('Magia/Occulto', 'Magia/Occulto'),
         ('Tradizione/Storia', 'Tradizione/Storia'))

# radio buttons
for size in sizes:
    r = ttk.Radiobutton(
        categ_frame,
        text=size[0],
        value=size[1],
        variable=selected_size
    )
    r.pack(fill='both', padx=5, pady=5)

# Input frame
input_frame = ttk.Frame(root, padding=(0, 0, 0, 10))
input_frame.grid(row=1, column=2, padx=10, pady=(30, 10), sticky="ew")
input_frame.columnconfigure(index=0, weight=1)

# Enter1
entry = ttk.Entry(input_frame)
entry.insert(0, "")
entry.grid(row=1, column=2, padx=5, pady=(0, 10), sticky="ew")

# Label1
label = ttk.Label(input_frame, text="Titolo", anchor='w')
label.grid(row=1, column=1, sticky="ew")

# Entry2
entry2 = ttk.Entry(input_frame)
entry2.insert(0, "")
entry2.grid(row=2, column=2, padx=5, pady=(0, 10), sticky="ew")

# Label2
label2 = ttk.Label(input_frame, text="Autore", anchor='w')
label2.grid(row=2, column=1, sticky="ew")

# Entry3
entry3 = ttk.Entry(input_frame)
entry3.insert(0, "")
entry3.grid(row=3, column=2, padx=5, pady=(0, 10), sticky="ew")

# Label3
label3 = ttk.Label(input_frame, text="Anno", anchor='w')
label3.grid(row=3, column=1, sticky="ew")

# Entry4
entry4 = ttk.Entry(input_frame)
entry4.insert(0, "")
entry4.grid(row=4, column=2, padx=5, pady=(0, 10), sticky="ew")

# Label4
label4 = ttk.Label(input_frame, text="Volume", anchor='w')
label4.grid(row=4, column=1, sticky="ew", )

# Button insert alla libreria
send = ttk.Button(input_frame, text="Inserisci", style="Accent.TButton")
send.grid(row=5, column=2, padx=5, pady=10, sticky="nsew")

# Button invio alla libreria
send = ttk.Button(input_frame, text="Modifica", style="Accent.TButton")
send.grid(row=6, column=2, padx=5, pady=10, sticky="nsew")

# Button invio alla libreria
send = ttk.Button(input_frame, text="Elimina", style="Accent.TButton")
send.grid(row=7, column=2, padx=5, pady=10, sticky="nsew")

# Letto si/no
switch = ttk.Checkbutton(input_frame, text="Letto", style="Switch")
switch.grid(row=8, column=2, padx=5, pady=10, sticky="ew")

#Search button
search_butt = PhotoImage(file='search.png')
button = Button(input_frame, image=search_butt, borderwidth=0)
view_butt = PhotoImage(file='view all.png')
button2 = Button(input_frame, image=view_butt, borderwidth=0)
button.grid(row=8, column=2, padx=(50, 0))
button2.grid(row=8, column=2, padx=(125, 0))
# Dimensione finestra
sizegrip = ttk.Sizegrip(root)
sizegrip.grid(row=100, column=100, padx=(0, 5), pady=(0, 5))

# Centramento della finestra e minsize
root.update()
root.minsize(root.winfo_width(), root.winfo_height())
x_coordinate = int((root.winfo_screenwidth() / 2) - (root.winfo_width() / 2))
y_coordinate = int((root.winfo_screenheight() / 2) - (root.winfo_height() / 2))
root.geometry("+{}+{}".format(x_coordinate, y_coordinate))

# Start the main loop
root.mainloop()

后端代码

import sqlite3

def connect():
    conn = sqlite3.connect('libri.db')
    curs = conn.cursor()
    curs_execute = """ 
        CREATE TABLE IF NOT EXISTS libro 
        (id INTEGER PRIMARY KEY, 
        categoria text, 
        titolo text, 
        autore text, 
        anno integer, 
        volume integer)
        """
    curs.execute(curs_execute)
    conn.commit()
    conn.close()

def insert(categoria, titolo, autore, anno, volume):
    conn = sqlite3.connect('libri.db')
    curs = conn.cursor()
    curs.execute("INSERT  INTO libro VALUES(NULL, ?, ?, ?, ?, ?)", (categoria, titolo, autore, anno, volume))
    conn.commit()
    conn.close()

def view():
    conn = sqlite3.connect('libri.db')
    curs = conn.cursor()
    curs.execute("SELECT * FROM libro")
    rows = curs.fetchall()
    conn.close()
    return rows

def search(categoria='', titolo='', autore='', anno='', volume=''):
    conn = sqlite3.connect('libri.db')
    curs = conn.cursor()
    curs.execute("SELECT * FROM libro WHERE categoria=? OR titolo=? OR autore=? OR anno=? OR volume=?")
    rows = curs.fetchall()
    conn.close()
    return rows

def delete(id):
    conn = sqlite3.connect('libri.db')
    curs = conn.cursor()
    curs.execute("DELETE FROM libro WHERE id=?", (id,))
    conn.commit()
    conn.close()

def update(id, categoria, titolo, autore, anno, volume):
    conn = sqlite3.connect('libri.db')
    curs = conn.cursor()
    curs.execute("UPDATE libro SET categoria=? OR titolo=? OR autore=? OR anno=? OR volume=? WHERE id=?", (id, titolo, autore, anno, volume))
    conn.commit()
    conn.close()

connect()
print(view())

解决方案

1. 修复后端代码错误

原后端的search和update函数存在SQL语法问题，先修正：

import sqlite3

def connect():
    conn = sqlite3.connect('libri.db')
    curs = conn.cursor()
    curs_execute = """ 
        CREATE TABLE IF NOT EXISTS libro 
        (id INTEGER PRIMARY KEY, 
        categoria text, 
        titolo text, 
        autore text, 
        anno integer, 
        volume integer)
        """
    curs.execute(curs_execute)
    conn.commit()
    conn.close()

def insert(categoria, titolo, autore, anno, volume):
    conn = sqlite3.connect('libri.db')
    curs = conn.cursor()
    curs.execute("INSERT INTO libro VALUES(NULL, ?, ?, ?, ?, ?)", (categoria, titolo, autore, anno, volume))
    conn.commit()
    conn.close()

def view():
    conn = sqlite3.connect('libri.db')
    curs = conn.cursor()
    curs.execute("SELECT * FROM libro")
    rows = curs.fetchall()
    conn.close()
    return rows

def search(categoria='', titolo='', autore='', anno='', volume=''):
    conn = sqlite3.connect('libri.db')
    curs = conn.cursor()
    # 修复：传入查询参数
    curs.execute("SELECT * FROM libro WHERE categoria=? OR titolo=? OR autore=? OR anno=? OR volume=?", 
                 (categoria, titolo, autore, anno, volume))
    rows = curs.fetchall()
    conn.close()
    return rows

def delete(id):
    conn = sqlite3.connect('libri.db')
    curs = conn.cursor()
    curs.execute("DELETE FROM libro WHERE id=?", (id,))
    conn.commit()
    conn.close()

def update(id, categoria, titolo, autore, anno, volume):
    conn = sqlite3.connect('libri.db')
    curs = conn.cursor()
    # 修复：SET子句用逗号分隔，参数顺序匹配
    curs.execute("UPDATE libro SET categoria=?, titolo=?, autore=?, anno=?, volume=? WHERE id=?", 
                 (categoria, titolo, autore, anno, volume, id))
    conn.commit()
    conn.close()

connect()

2. 前端关联单选按钮与数据库

2.1 导入后端模块

取消前端代码开头的注释：

import backend  # 取消这一行的注释

2.2 实现插入功能（获取单选按钮值）

替换原"Inserisci"按钮代码，添加点击事件：

def insert_book():
    categoria = selected_size.get()
    titolo = entry.get()
    autore = entry2.get()
    anno = entry3.get()
    volume = entry4.get()
    
    # 简单输入验证
    if categoria and titolo and autore and anno.isdigit() and volume.isdigit():
        backend.insert(categoria, titolo, autore, int(anno), int(volume))
        refresh_treeview()
        # 清空输入框
        entry.delete(0, END)
        entry2.delete(0, END)
        entry3.delete(0, END)
        entry4.delete(0, END)
        selected_size.set('')

send = ttk.Button(input_frame, text="Inserisci", style="Accent.TButton", command=insert_book)
send.grid(row=5, column=2, padx=5, pady=10, sticky="nsew")

2.3 实现Treeview刷新函数

从数据库读取数据并更新Treeview：

def refresh_treeview():
    # 清空现有内容
    for item in Libreria.get_children():
        Libreria.delete(item)
    # 加载数据库数据
    rows = backend.view()
    for row in rows:
        # row格式：(id, categoria, titolo, autore, anno, volume)
        Libreria.insert(parent="", index="end", iid=row[0], text=row[1], values=(row[2], row[3], row[4], row[5]))

2.4 初始化加载数据

在root.mainloop()前调用刷新函数：

# 初始化加载数据库数据
refresh_treeview()

# Start the main loop
root.mainloop()

2.5 选中行时自动填充单选按钮

添加Treeview选中事件绑定：

def select_item(event):
    selected = Libreria.selection()
    if selected:
        item = Libreria.item(selected[0])
        # 填充输入框
        entry.delete(0, END)
        entry.insert(0, item['values'][0])
        entry2.delete(0, END)
        entry2.insert(0, item['values'][1])
        entry3.delete(0, END)
        entry3.insert(0, str(item['values'][2]))
        entry4.delete(0, END)
        entry4.insert(0, str(item['values'][3]))
        # 选中对应分类单选按钮
        selected_size.set(item['text'])

# 绑定选中事件
Libreria.bind('<<TreeviewSelect>>', select_item)

2.6 实现修改功能

替换原"Modifica"按钮代码：

def update_book():
    selected = Libreria.selection()
    if selected:
        book_id = selected[0]
        categoria = selected_size.get()
        titolo = entry.get()
        autore = entry2.get()
        anno = entry3.get()
        volume = entry4.get()
        
        if categoria and titolo and autore and anno.isdigit() and volume.isdigit():
            backend.update(book_id, categoria, titolo, autore, int(anno), int(volume))
            refresh_treeview()
            # 清空输入
            entry.delete(0, END)
            entry2.delete(0, END)
            entry3.delete(0, END)
            entry4.delete(0, END)
            selected_size.set('')

send = ttk.Button(input_frame, text="Modifica", style="Accent.TButton", command=update_book)
send.grid(row=6, column=2, padx=5, pady=10, sticky="nsew")

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

Python中实现单选按钮与SQLite3数据库的关联

前端代码 #

后端代码 #

1. 修复后端代码错误 #

2. 前端关联单选按钮与数据库 #

2.1 导入后端模块 #

2.2 实现插入功能（获取单选按钮值） #

2.3 实现Treeview刷新函数 #

2.4 初始化加载数据 #

2.5 选中行时自动填充单选按钮 #

2.6 实现修改功能 #