Discord.py车辆注册Bot的Sqlite3查询绑定错误求助

问题背景 #

开发基于Discord.py的车辆注册Bot，包含/register（车辆注册）和/plate（车牌查询）两个命令，/register功能正常，但/plate命令持续报错。

错误信息 #

Traceback (most recent call last):
  File "C:\Users\Mali Towers\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.10_qbz5n2kfra8p0\LocalCache\local-packages\Python310\site-packages\discord\app_commands\commands.py", line 842, in _do_call
    return await self._callback(interaction, **params)  # type: ignore
  File "c:\Users\Mali Towers\Desktop\Python\moreboredstuff\licenseplate.py", line 40, in plate
    c.execute('select rblx_name from registrations where license_plate = ?', (license_plate))
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 6 supplied.

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\Users\Mali Towers\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.10_qbz5n2kfra8p0\LocalCache\local-packages\Python310\site-packages\discord\app_commands\tree.py", line 1248, in _call
    await command._invoke_with_namespace(interaction, namespace)
  File "C:\Users\Mali Towers\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.10_qbz5n2kfra8p0\LocalCache\local-packages\Python310\site-packages\discord\app_commands\commands.py", line 867, in _invoke_with_namespace
    return await self._do_call(interaction, transformed_values)
  File "C:\Users\Mali Towers\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.10_qbz5n2kfra8p0\LocalCache\local-packages\Python310\site-packages\discord\app_commands\commands.py", line 860, in _do_call
    raise CommandInvokeError(self, e) from e
discord.app_commands.errors.CommandInvokeError: Command 'plate' raised an exception: ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 6 supplied.

原始代码 #

import discord, sqlite3
from discord import app_commands
from discord.ext import commands

intents = discord.Intents.default()
client = discord.Client(intents=intents)
tree = app_commands.CommandTree(client)

conn = sqlite3.connect('regos.db')
c = conn.cursor()

c.execute("""CREATE TABLE IF NOT EXISTS registrations (
             rblx_name string NOT NULL,
             license_plate string NOT NULL,
             vehicle_name string NOT NULL,
             colour string NOT NULL
             )""")

@client.event
async def on_ready():
    print(f"Logged in as {client.user}")
    try:
        synced = await tree.sync()
        print(f"Synced {len(synced)} command(s)")
    except Exception as e:
        print(e)

@tree.command(name = "register", description = "Register your vehicle in-game using it's plate number")
async def register(interaction: discord.Interaction, rblx_name: str, license_plate: str, vehicle_name: str, colour: str):
    embed = discord.Embed(title=f"{vehicle_name} added to vehicle library", description="Vehicle successfully registered")
    embed.add_field(name="Vehicle Name:", value=vehicle_name, inline=True)
    embed.add_field(name="Vehicle Colour:", value=colour)
    embed.add_field(name="Vehicle License Plate:", value=license_plate)
    await interaction.response.send_message(embed=embed, ephemeral=True)
    c.execute('insert into registrations(rblx_name, license_plate, vehicle_name, colour) values(?, ?, ?, ?)', (rblx_name, license_plate, vehicle_name, colour))


@tree.command(name="plate", description="Search up a registered plate")
async def plate(interaction: discord.Interaction, license_plate: str):
    c.execute('select rblx_name, license_plate, vehicle_name, colour from registrations where license_plate = ?', (license_plate))
    result = c.fetchall()
    embed = discord.Embed(title=f"{license_plate} lookup successful")
    embed.add_field(name="result", value=result)
    await interaction.response.send_message(embed=embed, ephemeral=True)

（注：client.run("token")已在代码末尾，未展示）

错误原因分析 #

报错的核心原因是SQLite参数传递格式错误：

• sqlite3的execute()方法要求第二个参数是元组、列表或其他序列类型
• 代码中(license_plate)不是元组，Python会把字符串license_plate当作字符序列处理，将每个字符视为一个独立的绑定参数。如果车牌是6位字符，就会被当成6个参数，和SQL语句中仅有的1个?占位符不匹配，从而触发Incorrect number of bindings supplied错误。

修复步骤 #

• 修正参数传递方式：把plate命令里的execute调用修改为：

c.execute('select rblx_name, license_plate, vehicle_name, colour from registrations where license_plate = ?', (license_plate,))

注意(license_plate,)后面的逗号，这才是定义单元素元组的正确方式，否则字符串会被拆分成单个字符作为参数。

• 补充数据库操作的必要步骤：
  代码还有两个潜在问题：

  • 执行insert后没有调用conn.commit()，数据不会真正写入数据库
  • 没有处理查询不到结果的情况，会导致Embed显示空内容

  修改后的关键代码片段：

@tree.command(name = "register", description = "Register your vehicle in-game using it's plate number")
async def register(interaction: discord.Interaction, rblx_name: str, license_plate: str, vehicle_name: str, colour: str):
    embed = discord.Embed(title=f"{vehicle_name} added to vehicle library", description="Vehicle successfully registered")
    embed.add_field(name="Vehicle Name:", value=vehicle_name, inline=True)
    embed.add_field(name="Vehicle Colour:", value=colour)
    embed.add_field(name="Vehicle License Plate:", value=license_plate)
    await interaction.response.send_message(embed=embed, ephemeral=True)
    c.execute('insert into registrations(rblx_name, license_plate, vehicle_name, colour) values(?, ?, ?, ?)', (rblx_name, license_plate, vehicle_name, colour))
    conn.commit()  # 提交数据到数据库

@tree.command(name="plate", description="Search up a registered plate")
async def plate(interaction: discord.Interaction, license_plate: str):
    c.execute('select rblx_name, license_plate, vehicle_name, colour from registrations where license_plate = ?', (license_plate,))
    result = c.fetchall()
    if not result:
        embed = discord.Embed(title=f"{license_plate} lookup failed", description="No vehicle found with this license plate")
    else:
        embed = discord.Embed(title=f"{license_plate} lookup successful")
        for idx, (rblx, plate, vehicle, color) in enumerate(result, 1):
            embed.add_field(name=f"Vehicle {idx}", value=f"Roblox Name: {rblx}\nPlate: {plate}\nVehicle: {vehicle}\nColour: {color}", inline=False)
    await interaction.response.send_message(embed=embed, ephemeral=True)

• 额外优化建议：
  • 可以在Bot关闭时调用conn.close()来关闭数据库连接，避免资源泄漏
  • 给license_plate字段添加唯一约束，避免重复注册同一车牌：

CREATE TABLE IF NOT EXISTS registrations (
    rblx_name string NOT NULL,
    license_plate string NOT NULL UNIQUE,
    vehicle_name string NOT NULL,
    colour string NOT NULL
)

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