Solving “ERROR 2006 (HY000): MySQL server has gone away" in cyclical programs (mysql-connector-python)

Question

I provided solution to similar problems with the one I met recently:

I have Telegram (messenger) bot with MySQL user DB. There is connection to it in start of code causing connection was going away later about 10 hours. Therefore bot was returning errors because couldn`t get information about user.

I use mysql-connector-python framework. Solution is to use class for DB queries. You can see it and examples of using below.

Answer 1

The class:

import logging

import mysql.connector

class DB():
    def __init__(self, **kwargs):
        self.conn = mysql.connector.connect(
            host="host",
            user="user",
            passwd="password",
            database="name"
        )
        try:
            self.cursor = self.conn.cursor()
        except Exception as e:
            print(str(e))

    def execute(self, query, data=None, ret1=False):
        try:
            if not self.conn:
                self.__init__()
            else:
                if data:
                    self.cursor.execute(query, data)
                else:
                    self.cursor.execute(query)

                if 'INSERT' in query or 'UPDATE' in query or 'DELETE' in query or 'DROP' in query:
                    self.conn.commit()

                if ret1:
                    return self.cursor.fetchone()
                else:
                    return self.cursor.fetchall()

        except:
            logging.error('end', exc_info=True)
            return 'error'

Table example:

Table example

Query Structure:

res = DB().execute(query, protected data) # fetchall
res = DB().execute(query, protected data, True) # fetchone

Examples of using:

> DB().execute("CREATE TABLE users (r_id INT AUTO_INCREMENT PRIMARY KEY, id INT UNIQUE, name VARCHAR(255), role INT NULL DEFAULT 3)")
> DB().execute("INSERT INTO users (id, name, role) VALUES (%s, %s, %s)", (65453, "Mike", 1,))
> res = DB().execute(f"SELECT * FROM users")
res = [(1, 146, "Nick", 3,), (2, 155, "John", 1,), (3, 678, "Michelle", 2,)]
> res = DB().execute(f"SELECT * FROM users WHERE name = %s", ("John",), ret1=True)
res = (2, 155, "John", 1,)

If you have some optimisation offers, write its!