使用Tkinter操作SQLite删除数据报错：绑定参数数量不匹配

问题描述 #

编写了一个基于Tkinter的GUI程序，用于管理SQLite数据库（accounts.db）中的账号信息，包含保存账号和删除账号功能。但点击Delete按钮时触发错误：

sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 4 supplied.

（错误中绑定参数的数量会随用户名的字符数变化）

程序代码 #

import tkinter as tk
import sqlite3 as sql

class main_window:
    def __init__(self, window):
        self.wind = window
        self.wind.geometry('500x500')
        self.wind.resizable(0, 0)
        self.draw()
        self.refresh()

    def draw(self):
        tk.Label(text= 'Usuario').pack()
        self.user = tk.Entry()
        self.user.pack()


        tk.Label(text= 'Password').pack()
        self.password = tk.Entry()
        self.password.pack()

        self.frame = tk.Frame(self.wind)
        self.frame.pack()

        tk.Button(self.frame, text= 'Save', width= 5, height= 1, command= self.save).pack(side= 'left')
        tk.Button(self.frame, text= 'Delete', width= 5, height= 1, command= self.delete).pack(side= 'top')

        self.user_list = tk.Listbox(width= 40, height= 100)
        self.user_list.pack(padx= 4, pady= 8, side= 'left')

        self.password_list = tk.Listbox(width= 40, height= 100)
        self.password_list.pack(padx= 4, pady= 8, side= 'left')

    def refresh(self):
        self.user.delete(0, tk.END)
        self.password.delete(0, tk.END)

        self.user_list.delete(0, tk.END)
        self.password_list.delete(0, tk.END)
        
        db = sql.connect('accounts.db')
        curs = db.cursor()

        db_accounts_users = curs.execute("SELECT user FROM tb_accounts")
        for user in db_accounts_users:
            for uss in user:
                self.user_list.insert(tk.END, uss)

        db_accounts_pass = curs.execute("SELECT pass FROM tb_accounts")
        for password in db_accounts_pass:
            for fpass in password:
                self.password_list.insert(tk.END, fpass)

        db.commit()
        db.close()

    def save(self):
        db = sql.connect('accounts.db')
        curs = db.cursor()
        curs.execute("INSERT INTO tb_accounts(user, pass) VALUES (?, ?)", (self.user.get(), self.password.get()))
        db.commit()
        db.close()
        self.refresh()

    def delete(self):
        for user in self.user_list.curselection():
            select_user = self.user_list.get(user)
        db = sql.connect('accounts.db')
        curs = db.cursor()
        curs.execute("DELETE FROM tb_accounts WHERE user= ?", (select_user))
        db.commit()
        db.close()
        self.refresh()


obj_main_window = main_window(tk.Tk())
obj_main_window.wind.mainloop()

错误原因 #

问题出在delete方法的SQL执行语句里：

curs.execute("DELETE FROM tb_accounts WHERE user= ?", (select_user))

这里的(select_user)并不是元组，Python会把单个字符串的括号解析成普通括号，直接传入字符串本身。而SQLite的参数绑定会把字符串当作可迭代对象，将每个字符视为一个独立参数，导致参数数量和SQL语句中的?数量不匹配（比如用户名是4个字符，就会传入4个参数，但SQL只需要1个）。

解决方法 #

把参数改为单元素元组，只需要在select_user后面加个逗号：

curs.execute("DELETE FROM tb_accounts WHERE user= ?", (select_user,))

逗号是Python识别单元素元组的关键，这样SQLite就能正确识别这是一个包含单个参数的元组，匹配语句中的?。

另外，还可以优化delete方法中选中用户的逻辑，避免没有选中时出现select_user未定义的问题：

def delete(self):
    selected_indices = self.user_list.curselection()
    if not selected_indices:
        return  # 如果没有选中项，直接返回
    select_user = self.user_list.get(selected_indices[0])
    db = sql.connect('accounts.db')
    curs = db.cursor()
    curs.execute("DELETE FROM tb_accounts WHERE user= ?", (select_user,))
    db.commit()
    db.close()
    self.refresh()

内容的提问来源于stack exchange，提问作者Kumar López