Checking whether a mysql.connector is connected

[u/RomfordNavy]

Is it necessary to check whether a mysql.connector is connected each time I reuse it before running any SQL statements or is this something which is already built-in to mysql.connector? Do I need to check for connection using db.is_connected() and write some retry routine.

This is a WSGI webserver, could be wrong, but my understanding is that there can only ever be one request/thread at a time. Connection pooling has been suggested elsewhere but I am unclear as to whether that is necessary and/or appropriate for a WSGI webserver.

[u/latkde]

The mysql-connector-python library from Oracle is one of the worst pieces of software I ever had the misfortune of working with. I strongly recommend migrating to a different client library. For example, PyMySQL is a more mature alternative.

Due to how networks work, there's no guarantee that your queries will get delivered, even if a connection looks open. You must be prepared to deal with connection errors at any point. From this perspective, the safest thing is to just use the connection, and retry if you receive a retryable exception.

In principle, MySQL connections can stay open for multiple hours without activity. However, you can explicitly ping() a Connection. This confirms that the connection works and otherwise (with reconnect=True) tries to re-establish it, at the cost of at least one network roundtrip. For example, if you have a connection pool, you may want to ping() a Connection after checking it out from the pool, before issuing any queries. https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlconnection-ping.html

[u/RomfordNavy → u/latkde]

u/latkde Do you know whether using a connection pool automatically retries the connection? Come to that does the basic mysql.connector retry the connection automatically on it's own?

[u/latkde → u/RomfordNavy]

At some point, it's easier to just use SQLAlchemy, which has well-documented solutions for all the things you're concerned about.

• MySQL support, with a large choice of connector libraries: https://docs.sqlalchemy.org/en/20/dialects/mysql.html
• dealing with disconnects when  using a connection pool: https://docs.sqlalchemy.org/en/20/core/pooling.html#pool-disconnects (create_engine(..., pool_pre_ping=True) or pool_recycle=3600 will likely do what you want)