CODEWITHSUNDEEP
python-3.xpostgresqldiscord.py
Joe
Joe

Reputation: 25

Updating information in database issue

Hi I'm trying to update some entries in my database but I having a problem updating the correct information specified in a value.

My code block below shows a command that allows users to give a user XP. What I am trying to achieve is to update both values val_1 and val_2 however val_2 xp-amount, total_xp-amount is incorrectly deducting a bigger amount (number) from than the specified amount when the command is invoked. For example when !xp @user 5 is invoked it should give the mentioned @user 5 XP and also take 5XP from the user who invoked the command.

Here is what I'm working with:

async def xp(self, ctx, user: discord.Member, amount: int):

    conn = psycopg2.connect(DATABASE_URL, sslmode='require')
    cursor = conn.cursor()
    cursor.execute(f"SELECT lvl, xp, total_xp FROM levels WHERE guild_id = {ctx.guild.id} AND user_id = {user.id}")
    result2 = cursor.fetchone()
    xp = int(result2[1])
    total_xp = int(result2[2])

    if user.id == ctx.message.author.id:
        await ctx.send("You can't give yourself XP.")
        return

    if int(amount) > 50:
        await ctx.send("You can only give a maximum of 50 XP.")
        return

    #SQL 
    sql = ("UPDATE levels SET xp=%s, total_xp=%s WHERE guild_id=%s and user_id=%s")

    #The member recieving XP
    val_1 = (xp+amount, total_xp+amount, str(ctx.guild.id), str(user.id))
    cursor.execute(sql, val_1)

    #The member giving XP 
    val_2 = (xp-amount, total_xp-amount, str(ctx.guild.id), str(ctx.message.author.id))
    cursor.execute(sql, val_2)
    
    conn.commit()
    cursor.close()
    conn.close()

    #return something here
    await ctx.send(f"{ctx.message.author.name} has given {amount} XP to 
    {user.mention}.")

Help would be appreciated

Upvotes: 1

Views: 38

Answers (1)

YousefZ
YousefZ

Reputation: 345

first of all, you aren't getting the total-xp of both users, only the person who is invoking the command.

Before:

cursor.execute(f"SELECT lvl, xp, total_xp FROM levels WHERE guild_id = {ctx.guild.id} AND user_id = {user.id}")
    result2 = cursor.fetchone()
    xp = int(result2[1])
    total_xp = int(result2[2])

    if user.id == ctx.message.author.id:
        await ctx.send("You can't give yourself XP.")
        return

    if int(amount) > 50:
        await ctx.send("You can only give a maximum of 50 XP.")
        return

    #SQL 
    sql = ("UPDATE levels SET xp=%s, total_xp=%s WHERE guild_id=%s and user_id=%s")

    #The member recieving XP
    val_1 = (xp+amount, total_xp+amount, str(ctx.guild.id), str(user.id))
    cursor.execute(sql, val_1)

    #The member giving XP 
    val_2 = (xp-amount, total_xp-amount, str(ctx.guild.id), str(ctx.message.author.id))
    cursor.execute(sql, val_2)

Fixed:

cursor.execute(f"SELECT lvl, xp, total_xp FROM levels WHERE guild_id = {ctx.guild.id} AND user_id = {ctx.message.author.id}")
    result2 = cursor.fetchone()
    xp = int(result2[1])
    sender_total_xp = int(result2[2])

    if user.id == ctx.message.author.id:
        await ctx.send("You can't give yourself XP.")
        return

    if int(amount) > 50:
        await ctx.send("You can only give a maximum of 50 XP.")
        return

    cursor.execute(f"SELECT lvl, xp, total_xp FROM levels WHERE guild_id = {ctx.guild.id} AND user_id = {user.id}")

    result = cursor.fetchone()
    receiver_total_xp = int(result[2])

    #SQL 
    sql = ("UPDATE levels SET xp=%s, total_xp=%s WHERE guild_id=%s and user_id=%s")

    #The member recieving XP
    val_1 = (xp+amount, receiver_total_xp+amount, str(ctx.guild.id), str(user.id))
    cursor.execute(sql, val_1)

    #The member giving XP 
    val_2 = (xp-amount, sender_total_xp-amount, str(ctx.guild.id), str(ctx.message.author.id))
    cursor.execute(sql, val_2)

as you can see, you've been requesting the incorrect data from the database because you were using user.id (which is the target id, i.e. the person receiving the xp) instead of ctx.message.author.id which is the person who invoked the command.

there was also the issue of you setting the sender's xp to be -5 the receiver's xp.

Upvotes: 1

Related Questions