How to Enable Automatic Client Reconnection in MySQLdb?

MySQLdb: Enabling Automatic Client Reconnection

In MySQLdb, establishing connection auto-reconnection is not a straightforward task compared to PHP's MySQL extension. This article addresses this issue and provides a working solution.

Background

Maintaining active connections with MySQL is crucial in web applications. Network issues, timeouts, or database restarts can disrupt these connections, leading to errors. To handle such situations, database clients should be equipped with auto-reconnection capabilities.

MySQLdb Solution

MySQLdb does not provide a direct option for auto-reconnection like MySQL's MYSQL_OPT_RECONNECT. However, a workaround involves wrapping the cursor.execute() method, which typically triggers connection errors when MySQL is unreachable.

import MySQLdb

class DB:
  conn = None

  def connect(self):
    self.conn = MySQLdb.connect()

  def query(self, sql):
    try:
      cursor = self.conn.cursor()
      cursor.execute(sql)
    except (AttributeError, MySQLdb.OperationalError):
      self.connect()
      cursor = self.conn.cursor()
      cursor.execute(sql)
    return cursor

db = DB()
sql = "SELECT * FROM foo"
cur = db.query(sql)
# wait a long time for the MySQL connection to timeout
cur = db.query(sql)
# still works

Explanation

This method creates a DB class with a connect() method to establish the initial connection. The query() method attempts to execute a query using a cursor. If a connection error occurs (AttributeError or MySQLdb.OperationalError), it automatically reconnects by calling connect() and then retries the query with a new cursor. This approach ensures that your application can handle temporary connection issues and maintain seamless database access.

The above is the detailed content of How to Enable Automatic Client Reconnection in MySQLdb?. For more information, please follow other related articles on the PHP Chinese website!