Nextcord机器人中aiosqlite第二个表无法更新/创建求助

我用Nextcord开发Discord机器人，采用aiosqlite作为数据库。其中econ表运行正常，但daily表既无法更新，我甚至不确定它是否成功创建。怀疑该问题与Nextcord Cog类的使用有关。已经折腾3个月，试过各种方法包括ChatGPT，因对SQL不熟悉一直未解决，恳请帮助。

1. 数据库初始化与建表代码 #

async def create_tables(self):
    if self.db is not None:
        await self.db.execute('CREATE TABLE IF NOT EXISTS econ (spin_tokens INTEGER, tokens INTEGER, slash_cmds INTEGER, status INTEGER, dank INTEGER, user INTEGER)')
        await self.db.execute('CREATE TABLE IF NOT EXISTS daily (daily_claim INTEGER, daily_claimed_stamp INTEGER, daily_streak INTEGER, user INTEGER)')

async def commit(self):
    await self.connect.commit()

@commands.Cog.listener()
async def on_ready(self):
    self.bot.db = await aiosqlite.connect("econ.db")
    await asyncio.sleep(3)
    await self.create_tables()
    await self.bot.db.commit()
    print("DB ready...")
    print("-----------")

2. 数据查询与更新代码 #

async def get_dval(self, user):
    async with self.bot.db.cursor() as cursor:
        await cursor.execute("SELECT daily_claim, daily_claimed_stamp, daily_streak FROM daily WHERE user = ?", (user.id,))
        data = await cursor.fetchone()
        print(data)
        if data is None:
            await self.daily_make(user)
            return 0, 0, 1, 0
        (daily_claim, daily_claimed_stamp, daily_streak) = data[0], data[1], data[2]
        return daily_claim, daily_claimed_stamp, daily_streak

async def dclaimed_update(self, user, mode="daily_claim"):
    now = datetime.now()
    now_time = now.strftime("%H.%M")
    print(now_time)

    if self.db is not None:
        await self.db.execute(f'''UPDATE daily SET daily_claim = now_time WHERE user = (user.id)''')
        await self.db.commit()

3. Daily命令代码 #

@nextcord.slash_command(description="adds to bal")
async def daily(self, interaction : Interaction):
    now = datetime.now()
    now_time = now.strftime("%H.%M")
    print(now_time)
    daily_claim, daily_claimed_stamp, daily_streak = await self.get_dval(interaction.user)

    if daily_claim == 0:
        resK = await self.dclaimed_update(interaction.user)
        daily_claim, daily_claimed_stamp, daily_streak = await self.get_dval(interaction.user)
        await interaction.send("Sugg")
    else:
        await interaction.send("No")

1. 核心变量不匹配：daily表未创建的原因 #

建表方法create_tables中判断的是self.db is not None，但on_ready里初始化的数据库实例是self.bot.db，两者不是同一个变量，导致daily表的创建语句根本没执行。

• 修复代码：

async def create_tables(self):
    if self.bot.db is not None:
        await self.bot.db.execute('CREATE TABLE IF NOT EXISTS econ (spin_tokens INTEGER, tokens INTEGER, slash_cmds INTEGER, status INTEGER, dank INTEGER, user INTEGER)')
        await self.bot.db.execute('CREATE TABLE IF NOT EXISTS daily (daily_claim INTEGER, daily_claimed_stamp INTEGER, daily_streak INTEGER, user INTEGER)')

• 额外：on_ready里的await asyncio.sleep(3)完全没必要，可直接删除。

2. SQL更新语句的语法错误 #

dclaimed_update方法用f-string拼接SQL时存在两个问题：

• now_time是字符串，未加引号会触发SQL语法错误
• user = (user.id)中的user.id未被Python解析，会被当作字符串传入SQL，导致找不到目标用户
• 修复代码（改用参数化查询，避免SQL注入和语法错误）：

async def dclaimed_update(self, user, mode="daily_claim"):
    now = datetime.now()
    now_time = now.strftime("%H.%M")
    print(now_time)

    async with self.bot.db.cursor() as cursor:
        await cursor.execute('UPDATE daily SET daily_claim = ? WHERE user = ?', (now_time, user.id))
        await self.bot.db.commit()

3. 返回值数量不匹配错误 #

get_dval方法中，当用户数据不存在时返回了4个值，但调用时只接收3个变量，会触发解包错误。

• 修复代码：

if data is None:
    await self.daily_make(user)
    return 0, 0, 1  # 保持与后续返回值数量一致

4. 缺失的daily_make方法实现 #

调用了daily_make但未提供代码，需补充该方法以初始化新用户的daily数据：

async def daily_make(self, user):
    async with self.bot.db.cursor() as cursor:
        await cursor.execute('INSERT INTO daily (daily_claim, daily_claimed_stamp, daily_streak, user) VALUES (?, ?, ?, ?)', (0, 0, 1, user.id))
        await self.bot.db.commit()

5. 无效的commit方法 #

commit方法中使用的self.connect未定义，需修正为与数据库实例一致：

async def commit(self):
    await self.bot.db.commit()

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