Local variable referenced before assignment in class? with python, discordpy

Question

i'm having some trouble in order to make a cog with the discordpy rewrite branch in python.

I'm trying to make a command to start a connection to a database using mysql connector and to create a simple table. The problem is that when i define a cursor variable like stated in the official mysql docs i get an error: "local variable 'cnx' referenced before assignment"

Now this is the code:

import discord
from discord.ext import commands
import json
import asyncio
import mysql.connector
from mysql.connector import errorcode

with open("config.json") as configfile:
    config = json.load(configfile)

class testcog:

    def __init__(self, client):
        self.client = client


    @commands.command()
    async def dbconnect(self, ctx):
        await ctx.message.author.send('I\'m connecting to the database, please be patient.')

        try:
            cnx = mysql.connector.connect(user=config['sqlconfig']['user'], password=config['sqlconfig']['password'],
                                          host=config['sqlconfig']['host'],
                                          database=config['sqlconfig']['database'])
        except mysql.connector.Error as err:
            if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
                print("Something is wrong with your user name or password")
            elif err.errno == errorcode.ER_BAD_DB_ERROR:
                print("Database does not exist")
            else:
                print(err)
        else:
            cnx.close()

        cursor = cnx.cursor()

        TABLES = {}
        TABLES['employee'] = (
            "CREATE TABLE `employee` ("
            "  `emp_no` int(11) NOT NULL AUTO_INCREMENT,"
            "  `birth_date` date NOT NULL,"
            "  `first_name` varchar(14) NOT NULL,"
            "  `last_name` varchar(16) NOT NULL,"
            "  `gender` enum('M','F') NOT NULL,"
            "  `hire_date` date NOT NULL,"
            "  PRIMARY KEY  (`emp_no`)"
            ") ENGINE=InnoDB")

        for table_name in TABLES:
            table_description = TABLES[table_name]
            try:
                print("Creating table {}: ".format(table_name), end='')
                cursor.execute(table_description)
            except mysql.connector.Error as err:
                if err.errno == errorcode.ER_TABLE_EXISTS_ERROR:
                    print("already exists.")
                else:
                    print(err.msg)
            else:
                print("OK")

        cursor.close()
        cnx.close()


def setup(client):
    client.add_cog(testcog(client))

The table and the code to create it was copied directly from the official docs. The piece of code that gives me the error is : cursor = cnx.cursor() just before the TABLES dictionary is created.

I don't understand what i'm doing wrong, help is much appreciated.

Answer 1

I think I can provide some help for you!

When working in a cog file, you need to inherit commands.Cog in your main class. In addition to this, you should async be opening and closing your json file.

We use async with discord.py to make it so if multiple people use your commands, the bot wont get backed up (It's so the bot can do multiple things at one time). There is an async library for MySql, and async libraries for opening json files, so let's look into using them.

You can check out the aiomysql documentation here: https://aiomysql.readthedocs.io/en/latest/

Let's work on setting up your problem. In order to do this, we need to make sure our bot is setup for our db. We setup something called a "pool", that changes as the DB changes.

Im going to show the file structure I have in this example for you:

main.py
/cogs
   testcog.py

# When creating our bot, we want to setup our db (database) connection, so we can reference it later

from discord.ext import commands
import discord
import aiomysql
import asyncio
import aiofiles, json

loop = asyncio.get_event_loop()

bot = commands.Bot(command_prefix = "!", intents=discord.Intents.all())

@bot.event
async def on_ready():
    config = json.loads(await(await aiofiles.open("/home/pi/Desktop/Experimental/prestagingapi.json")).read())
    bot.pool = await aiomysql.create_pool(host=config['sqlconfig']['host'], port = 0000, user = config['sqlconfig']['user'], 
                                        password = config['sqlconfig']['password'], 
                                        db = config['sqlconfig']['database'], loop=loop)
    print("Bot is online!")


# We need to load our cogs and setup our db loop to reference it later
initial_extension = (
    "cogs.testcog",
)

for extension in initial_extension:
    bot.load_extension(extension)

bot.run("YOUR_TOKEN", reconnect=True)

Now, we can work inside of our cog to set everything up. I named the file of this cog, testcog.py inside of the folder, cogs.

import discord
from discord.ext import commands


class testCog(commands.Cog): # I defined that our class inherits the cog for discords
    def __init__(self, bot):
        self.bot = bot

    @commands.command()
    async def create_table(self, ctx):
        await ctx.author.send('I\'m connecting to the database, please be patient.') #ctx.message.author is ctx.author

        # now you can create your db connection here:
        # looking at the aiomysql documentation, we can create a connection and execute what we need
        async with self.bot.pool.acquire() as conn:
            async with conn.cursor() as cur:
                # in order to execute something (creaing a table for ex), we can do this:
                await cur.execute()

def setup(bot): # every cog needs a setup function
    bot.add_cog(testCog(bot))