When reusing a mysql.connector connection in a WSGI application, should I call db.is_connected() before every SQL statement, or does the connector handle disconnected connections automatically? If the connection has dropped, should I catch the resulting error and reconnect or retry the operation? I'm also unsure whether a connection pool is necessary or appropriate for a WSGI server, since the application may process requests sequentially depending on its deployment configuration.
3 Answers
If you want database-independent application code, keep the database-specific handling in a small adapter or repository layer. That layer can translate driver-specific exceptions into your own application-level error types and implement reconnect logic, while the rest of the application remains unaware of whether the driver is for MySQL or PostgreSQL.
Whether you need pooling depends on the actual server configuration, not simply on the fact that it uses WSGI. A single-process, single-threaded deployment may work with one carefully managed connection, but production WSGI servers commonly use multiple worker processes or threads. In that case, each worker should have its own connection or obtain one from a pool rather than sharing a connection across threads or processes. Also make sure connections are returned or closed at the end of each request.
You generally don’t need to check is_connected() before every query. The check can become stale immediately: it may return true and then the connection can fail before the SQL statement runs. Execute the operation and be prepared to handle the connector’s exception. If reconnecting and retrying is safe for that particular operation, retry it once rather than repeatedly looping.
The small delay between the check and the query doesn’t remove the race condition. A connection check can be useful for diagnostics, but it shouldn’t replace exception handling around the actual database operation.