discord.py - Bring a variable of type int to MySQL to Python

Question

I started using MySQL today. I am creating my own level system on a discord bot (I use discord.py) and I cannot import the number I need from my database.

import discord
import random
from discord import client
from discord.ext import commands
import mysql.connector
from discord.utils import get
from random import choice

token = 'token'
client = commands.Bot(command_prefix='°')

levelsystem_db = mysql.connector.connect(
    host="localhost",
    user="root",
    passwd="pass",
    database="userlevels",
    auth_plugin="mysql_native_password"
)

@client.event
async def on_ready():
    print('Bot online')
    print(levelsystem_db)

@client.event
async def on_message(message):
    if message.author.bot:
        return
    xp = generateXP()
    print(f"{message.author.name} ha ricevuto {str(xp)} xp")
    cursor = levelsystem_db.cursor()
    cursor.execute(f"SELECT user_xp FROM users WHERE client_id = {str(message.author.id)}")
    result = cursor.fetchall()
    print(result)
    print(len(result))
    if (len(result) == 0):
        print("L'utente non è stato aggiunto al database.")
        cursor.execute(f"INSERT INTO users VALUES({str(message.author.id)} ,{str(xp)} , 0)")
        levelsystem_db.commit()
        print("Aggiunta completata")
        await level_up(cursor, xp, message.author, message)
    else:
        newXP = result[0][0] + xp
        print(f"Gli xp di {message.author.name} sono aggiornati a {newXP}")
        cursor.execute(f"UPDATE users SET user_xp = {str(newXP)} WHERE client_id = {str(message.author.id)}")
        levelsystem_db.commit()
        print(f"Aggiornamento degli xs di {message.author.name} completato.")
        await level_up(cursor, newXP, message.author, message)

def generateXP():
    return random.randint(5,10)

async def level_up(cursor, NewXP, user, message):
    cursor.execute(f"SELECT user_level FROM users WHERE client_id = {str(message.author.id)}")
    lvl_start = cursor.fetchall()
    lvl_end = int(NewXP ** (1/4))
    print(str(lvl_start))
    print(str(lvl_end))
    if (str(lvl_start) < str(lvl_end)):
        await message.channel.send(f"{user.mention} è salito al livello {lvl_end}")
        print(f"Il livello di {message.author.name} si sta aggiornando al livello {lvl_end}")
        cursor.execute(f"UPDATE users SET user_level = {str(lvl_end)} WHERE client_id = {str(message.author.id)}")
        levelsystem_db.commit()
        print(f"Aggiornamento del livello di {message.author.name} completato.")
    else:
        print("Non è abbastanza!")
        pass

The part that gives me problems is this:

cursor.execute(f"SELECT user_level FROM users WHERE client_id = {str(message.author.id)}")
lvl_start = cursor.fetchall()
lvl_end = int(NewXP ** (1/4))
print(str(lvl_start))
print(str(lvl_end))

I would like the lvl_start variable to bring me back the integer and the list variable. I should get 0 from print (str (lvl_start)) not [(0,)]. I don't know if I made myself clear but I would like to solve this problem. Is there a way?

question from:https://stackoverflow.com/questions/65661942/bring-a-variable-of-type-int-to-mysql-to-python

Answer 1

As much I have used mysql-python, it returns a list nested with tuples. And if we use it to select a single row, it returns a list nested with tuples of length 2 where first element is the row value and the second is blank. So, I suggest you to select at least 2 rows and then use nested list's indexing list lvl_start[0][0].