Discord.py and SQL - Comparing between 2 tables in the same database file

user_14833752 0
Viewed 133

So I have two tables, Servers and Strikes. In the Servers table, I have a max strikes value. I want to compare the strikes a user has to the max strikes specified in the Servers table. I tried this:

@commands.command(name='strike', pass_ctx= True)
@commands.has_permissions(manage_messages=True)

async def strike(self, ctx, member : discord.Member):
    
    first_strike = 1
    
    
    cursor = await self.client.db.cursor()
    await cursor.execute(f"SELECT Strikes_Have FROM Strikes WHERE Guild_ID = {ctx.guild.id} AND User_ID = {member.id}")
    result = await cursor.fetchone()
    
    if result == None:
        
        cursor = await self.client.db.cursor()
            
        sql = f"INSERT INTO Strikes(Guild_ID, User_ID, Strikes_Have) VALUES({ctx.guild.id}, {member.id}, {first_strike})"
            
    
    else:
        
        cursor = await self.client.db.cursor()
            
        sql = f"UPDATE Strikes SET Strikes_Have = Strikes_Have + 1 where Guild_ID = {ctx.guild.id} AND User_ID = {member.id}"
        
        if result["Strikes_Have"] == result["MAX_STRIKES"]:
            await ctx.message.channel.send(f"{member} has the max amount of stirkes specified by the server")
            
            
    await cursor.execute(sql)
    await self.client.db.commit()
    await cursor.close()
            
    await ctx.message.channel.send(f"Striked {member}")`

But got this error:

Traceback (most recent call last):
  File "/home/pi/.local/lib/python3.7/site-packages/discord/ext/commands/core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "/home/pi/EndorCore/cogs/Mod.py", line 225, in strike
    if result["Strikes_Have"] == result["MAX_STRIKES"]:
IndexError: No item with that key
discord.py sqlite python
1 Answers

The error message you're getting currently is being caused by you trying to access a column of data which doesn't exist in the strikes table. To get the max strikes value, you need to query that table and get the number, then compare the two. For example:

cursor.execute(f"SELECT Strikes_Have FROM Strikes WHERE Guild_ID = {ctx.guild.id} AND User_ID = {member.id}")
user_result = cursor.fetchone()

cursor.execute(f"SELECT MAX_STRIKES FROM Servers WHERE Guild_ID = {ctx.guild.id}")
server_result = cursor.fetchone()

if user_result["Strikes_Have"] == server_result['MAX_STRIKES']:
    print("User has max strikes")
user_108855830
Related