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.

I don't know if it's the cause of your problem, butI would close self.conn in the except block too. Or move self.conn.close() to a finally block. Or use the connection as a context manager, if you are using the latest mysql-connector-python package. — snakecharmerb
– snakecharmerb, Commented Sep 30, 2020 at 15:20
I would open the connection once during construction (__init__) and if you wanted, you could add a __del__ method to explicitly close the connection but if your Database instance is being garbage collected, then the connection instance would also be garbage collected and closed anyway. — Booboo
– Booboo, Commented Sep 30, 2020 at 15:38

juppys · Accepted Answer · 2020-09-30 15:50:16Z

2

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/

answered Sep 30, 2020 at 15:50

juppys

1505 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nero Over a year ago

Many thanks for the advice. I have already implemented a part of it and I am doing the first tests. How would you implement this with the commit? Would you run a counter that commits all 100 inserts?

Collectives™ on Stack Overflow

Python MySQL.connector - MySQL Connection not available

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related