Python MySQL.connector - MySQL Connection not available

Question

i am currently working on a Python project, which sometimes has to write several thousand entries to the DB one after the other. Unfortunately I often get a connection error.

At first I thought that it is because I do not close the DB connection after each query, but then the message comes after the first entry in the DB. Otherwise the error appears after a few hundred entries. What am I doing wrong when I call the DB?

class Database ():
    def __init__(self):
        pass

    def connect(self, log):
        try:
            self.conn = mysql.connector.connect(host=self.Adress,
                                                port=self.Port,
                                                database=self.Database,
                                                user=self.Username,
                                                password=self.Password)
        except Exception as e:
            log.bootLog(self, "Database Connection ---- FAILED: " + str(e), "Error")
            sys.exit(0)
        return

    def insert(self, query, args, log):
        cursor = self.conn.cursor()
        try:
            cursor.execute(query, args)
            self.conn.commit()
            cursor.close()
            self.conn.close()
        except Exception as e:
            log.writeLog(self, "Database Connection ---- FAILED: " + str(e) + "\n" + cursor.statement, "Error")
        return

Traceback (most recent call last):
  File "/home/tobi/PycharmProjects/TradingBot/Module/brokerAPI.py", line 27, in initialisierung
    tradingBot.Database.insert(query,args, tradingBot.log)
  File "/home/tobi/PycharmProjects/TradingBot/Module/MySQLConnector.py", line 32, in insert
    cursor = self.conn.cursor()
  File "/home/tobi/PycharmProjects/TradingBot/venv/lib/python3.7/site-packages/mysql/connector/connection.py", line 809, in cursor
    raise errors.OperationalError("MySQL Connection not available.")
mysql.connector.errors.OperationalError: MySQL Connection not available.

Answer 1

My initial thoughts are as follows:

1 - MySQL doesn't use cursors so you don't have to worry about closing the cursor.

2 - Do you need to commit after every insert? It may be better two batch insert and commit every 100 inserts or something, I don't know if trying to commit a transaction every millisecond could cause problems with table locking or anything.

3 - You definitely don't need to close the connection after every transaction. If it is not certain how often a transaction is coming in, I would probably put a sleep(5) or something before closing the transaction so it can remain open in case another transaction is incoming. Opening and closing the connection to the db thousands of times probably isn't very good for performance.

4 - Have you heard about connection pooling? That will probably solve most of your troubles here. It has python open a pool of connections and use them and close them at its discretion according to the needs.

https://dev.mysql.com/doc/connector-python/en/connector-python-connection-pooling.html https://pynative.com/python-database-connection-pooling-with-mysql/