Jacobs "Blog"

MySQL with Discordpy

Holy. Fucking. Shit.

I want all the hours (sic days) I spent on this project back because honestly, it has been a total mind fuck. The worst part about all of this? Postgresql ended up saving me and providing me with an answer.

Needless to say; I rely on cogs. This article discusses discordpy with the expectation that your bot relies on cogs to do stuff.


The Problem

So when I started coding my bot, I realized that I needed to store data. Sqlite3 was the easiest way to do this so I used that to store things like channelids and roleids to handle things like temporary channel creation and react-for-role. The problem came when I decided that I wanted to eventaully develop a web interface for my bot, and run multiple tokens from the same codebase.

Because I'm very familiar with MySQL and it's my preferred option, I decided to opt for that, totally expecting a PHP PDO-like experience from Python. I was wrong.

Initially, I needed to get stuff done so my functions were basically this:

""" Database Querying Functions """
def insertQuery(self, query, qdata = None):
    mydb = mysql.connector.connect(host = config.mysqlhost, user = config.mysqluser, passwd = config.mysqlpass, database = config.mysqldatabase)
    c = mydb.cursor()
    if qdata is None:
        c.execute(query)
    else:
        c.execute(query, data)

    if mydb.commit():
        return True
    else:
        return False
    c.close()
    mydb.close()

Every cog had functions like this that handled various database-related tasks. Unfortunately, a cog such as one designed to identify the invite link used by a recent joiner became convoluted with mysql.connector lines and the majority of the code was dedicated to establishing and managing the database connection. This had to stop; for the sake of my sanity, for the sake of my code, and for the sake of the poor bastard that I will eventually hand it over to.

Ideally I wanted to create something that query in a single line and let it handle the connect, commit and closing of the cursor and connections. I worded that badly but I dont apologize. I started off by creating a class but how do I reference that in a cog? Do I have to instantiate that every time I need to make a call? That's not a one-line solution...

Well... neither is this one. But it's damn closer to it and it's actually super simple.

Finding the Solution

I think I need to talk about how I found this solution, so that you can adapt this to whatever you're doing.

The first step was to drop mysql.connector and pick up aiomysql. It might seem daunting however it's modeled after asyncpg apparently and their module seems to be pretty logical. It also operates in much the same way as mysql.connector (after all, it's built on mysql.connector).

I read through the docs here: https://aiomysql.readthedocs.io/en/latest/examples.html and created some tests to get the feel of it.

This is all well and good; but how do I bring this into my Discordpy project? Well that's where Asal#1502 from the Discord.py guild came in handy. Asal sent me this Github Gist which answered that question for me: https://gist.github.com/jegfish/cfc7b22e72426f5ced6f87caa6920fd6

Jegfish here uses pgsql for his database however we can adapt what he does for aiomysql very easily. Firstly, instead of defining our bot class then running it at the end of our script, like Danny suggests in the rtfm, we define a async function called "run" which creates the database connection and exposes it to the bot using kwargs.

We then change the bot to recognize the database connection, by "pop"ping it inside of init.

This means that when the bot starts, a database connection will be created and a bots cogs will be able to reference it via self.bot.db.

The last step comes from the aiomysql documentation; create a loop, and run the "run" function we created inside of the loop to start the bot.

Discordpy normally handles your loop for you, so I guess we'll be running a loop inside of a loop, however we capture keyboardinterrupt and kill everything off when we need to bring hte bot down, so it's no big deal. I also checked in with the Python Discord and they said "it should be fine" - good enough for a hobby bot.

The Big Problem With This

My biggest problem with this is that the bot will keep a database connection open - but sleeping. There are some arguments for and against this; people believing the number of connections to a database matters and some think it's fine to leave it open because it reduces latency when making requests.

It's worth noting too that Bitworks has written something up on this: https://bitworks.software/en/2019-03-12-tornado-persistent-mysql-connection-strategy.html

Basically they set the connection to automatically re-connect when the program called the get_cursor() function.

def get_cursor():
    try:
        connection.ping(reconnect=True, attempts=3, delay=5)
    except mysql.connector.Error as err:
        # reconnect your cursor as you did in __init__ or wherever    
        connection = init_db()
    return connection.cursor()

You can adapt this snippet for your aiomysql project very easily. If adapted for our project (which I may actually do after writing this - dw ill update the post), we could theoretically run the following inside of our cog when we need to perform a request against the database:

query = "SELECT * FROM testing WHERE tval = 1"
c = await self.bot.get_cursor()
await c.execute(query)

get_cursor would handle providing a cursor to c, and it would also handle re-connecting if our database drops it's connection.

This is one of those problems you will have to experiment with to solve. I might end up trying to use what I have learned today to make a solution, however I need to work on other things so I'm just blasting through this right now. You can always ping me on Discord about this.

The Bot Script

I wont post too much up here. However here's what I am using:

bot.py

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
from discord.ext import commands
import discord
import config
import asyncio
import aiomysql
prefixes = config.prefixes
defaultcogs = config.cogs
async def run(loop):
    db = await aiomysql.connect(**config.mysql, loop=loop)
    bot = Bot(db=db)
    try:
        await bot.start(config.token)
    except KeyboardInterrupt:
        await db.close()
        await bot.logout()
class Bot(commands.Bot):
    def __init__(self, **kwargs):
        prefixes = config.prefixes
        super().__init__(command_prefix=commands.when_mentioned_or(*prefixes), **kwargs)
        self.db = kwargs.pop("db")
        for cog in config.cogs:
            try:
                self.load_extension(cog)
            except Exception as exc:
                print('Could not load extension {0} due to {1.__class__.__name__}: {1}'.format(cog, exc))
    async def on_ready(self):
        print('Logged on as {0} (ID: {0.id})'.format(self.user))
loop = asyncio.get_event_loop()
loop.run_until_complete(run(loop))

cogs/test-db.py

# -*- coding: utf-8 -*-
from discord.ext import commands, tasks
import discord
import config
class DBTester(commands.Cog):
    def __init__(self, bot):
        self.bot = bot
    @commands.command()
    @commands.is_owner()
    async def test(self, ctx):
        await ctx.send("Message received. Processing.")
        with ctx.typing():
            query = "SELECT * FROM testing WHERE tval = 1"
            c = await self.bot.db.cursor()
            await c.execute(query)
            result = await c.fetchall()
            await ctx.send(result)
def setup(bot):
    bot.add_cog(DBTester(bot))

For the record, config.py has the following mysql config:

mysql           =  {
    'user':         '',
    'password':     '',
    'db':           '',
    'host':         ''
}

When you import config, you can then reference it as config.mysql like i have done.