How to Enable Automatic Client Re-connection in MySQLdb for Persistent Connections in Python?

MySQLdb: Enabling Automatic Client Re-connection

In Python, the MySQLdb library provides a convenient way to interact with MySQL databases. One common challenge is maintaining a persistent connection despite temporary interruptions.

To address this, a solution exists in PHP involving MySQL options, but users face difficulties replicating it with MySQLdb.

Solution:

The key to enabling automatic re-connection in MySQLdb lies in wrapping the cursor.execute() method.

The following Python code demonstrates a solution:

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

# Example usage
db = DB()
sql = "SELECT * FROM foo"
cur = db.query(sql)
# Sleep for a long time to simulate connection timeout
time.sleep(60 * 60 * 24)
cur = db.query(sql)
# Connection is still valid and usable

In this approach, the query() function manages the connection on the user's behalf, ensuring that any lost connections are re-established automatically.

The above is the detailed content of How to Enable Automatic Client Re-connection in MySQLdb for Persistent Connections in Python?. For more information, please follow other related articles on the PHP Chinese website!