When to close MySQL connection discord.py

Question

I want to make a discord bot that saves data to a MySQL Database (currently localhost) but the problem is I don't know when to close the database connection. Currently, when the user enters the command it always creates a new connection to the database but as you can imagine it's very slow to always connect then execute the query then close the connection, and finally after then returning the data.

Here is an example:

def open_case(case_id):
        search_query = f"SELECT `CASE_ID`, `USER_ID`, `LINK_REASON`, `LINK_SCREENSHOT` FROM `Report` WHERE CASE_ID ='{case_id}'"`

        mydb = mysql.connector.connect(
            host = "localhost",
            database = "report",
            password = "root",
            username = "root"
        )

        cursor = mydb.cursor()
        try:
            cursor.execute(search_query)
            result = cursor.fetchall()
            mydb.close()

        return result
    except:
        return print("Error case not found")
        mydb.close()

But I'm afraid if I connect to the DB at the beginning the bot crashers or so and then I've never closed the connection to the database. Is there a way to make it better?

Answer 1

every connection has an idle timeout, that would detect a unused open connection and close it.

But your approach is very good and clean, as it closes the connection.

A much simpler approach is to add finallyas all try catch and except will run through it.

Also use prepared statements, when using variables:

def open_case(case_id):
        search_query = "SELECT `CASE_ID`, `USER_ID`, `LINK_REASON`, `LINK_SCREENSHOT` FROM `Report` WHERE CASE_ID =%s"

        mydb = mysql.connector.connect(
            host = "localhost",
            database = "report",
            password = "root",
            username = "root"
        )

        cursor = mydb.cursor()
        try:
            cursor.execute(search_query,(case_id,))
            result = cursor.fetchall()
            return result      
        except:
            result = print("Error case not found")
        finally:
            mydb.close()
open_case(1)