Python Mysql (using Pymysql) Auto Reconnect

I'm not sure if this is possible, but I'm looking for a way to reconnect to mysql database when the connection is lost. All the connections are held in a gevent queue but that shou

Solution 1:

Finally got a working solution, might help someone.

from gevent import monkey
monkey.patch_socket()
import logging

import gevent
from gevent.queue import Queue
import pymysql as db

logging.basicConfig(level=logging.DEBUG)
LOGGER = logging.getLogger("connection_pool")


classConnectionPool:
    def__init__(self, db_config, time_to_sleep=30, test_run=False):
        self.username = db_config.get('user')
        self.password = db_config.get('password')
        self.host = db_config.get('host')
        self.port = int(db_config.get('port'))
        self.max_pool_size = 20
        self.test_run = test_run
        self.pool = None
        self.time_to_sleep = time_to_sleep
        self._initialize_pool()

    defget_initialized_connection_pool(self):
        return self.pool

    def_initialize_pool(self):
        self.pool = Queue(maxsize=self.max_pool_size)
        current_pool_size = self.pool.qsize()
        if current_pool_size < self.max_pool_size:  # this is a redundant check, can be removedfor _ in xrange(0, self.max_pool_size - current_pool_size):
                try:
                    conn = db.connect(host=self.host,
                                      user=self.username,
                                      passwd=self.password,
                                      port=self.port)
                    self.pool.put_nowait(conn)

                except db.OperationalError, e:
                    LOGGER.error("Cannot initialize connection pool - retrying in {} seconds".format(self.time_to_sleep))
                    LOGGER.exception(e)
                    break
        self._check_for_connection_loss()

    def_re_initialize_pool(self):
        gevent.sleep(self.time_to_sleep)
        self._initialize_pool()

    def_check_for_connection_loss(self):
        whileTrue:
            conn = Noneif self.pool.qsize() > 0:
                conn = self.pool.get()

            ifnot self._ping(conn):
                if self.test_run:
                    self.port = 3306

                self._re_initialize_pool()

            else:
                self.pool.put_nowait(conn)

            if self.test_run:
                break
            gevent.sleep(self.time_to_sleep)

    def_ping(self, conn):
        try:
            if conn isNone:
                conn = db.connect(host=self.host,
                                  user=self.username,
                                  passwd=self.password,
                                  port=self.port)
            cursor = conn.cursor()
            cursor.execute('select 1;')
            LOGGER.debug(cursor.fetchall())
            returnTrueexcept db.OperationalError, e:
            LOGGER.warn('Cannot connect to mysql - retrying in {} seconds'.format(self.time_to_sleep))
            LOGGER.exception(e)
            returnFalse# test (pytest compatible) -------------------------------------------------------------------------------------------import logging

from src.py.ConnectionPool import ConnectionPool

logging.basicConfig(level=logging.DEBUG)
LOGGER = logging.getLogger("test_connection_pool")


deftest_get_initialized_connection_pool():
    config = {
        'user': 'root',
        'password': '',
        'host': '127.0.0.1',
        'port': 3305
    }
    conn_pool = ConnectionPool(config, time_to_sleep=5, test_run=True)
    pool = conn_pool.get_initialized_connection_pool()
    # when in test run the port will be switched back to 3306# so the queue size should be 20 - will be nice to work # around this rather than test_run hackassert pool.qsize() == 20

Solution 2:

Well, I've got the same problem in my application and I found a method on the PyMySQL documentation that pings to the server and check if the connection was closed or not, if it was closed, then it reconnects again.

from pymysql import connectfrom pymysql.cursors import DictCursor

# create the connection
connection =connect(host='host', port='port', user='user', 
                     password='password', db='db', 
                     cursorclass=DictCursor)

# get the cursorcursor= connection.cursor()

# if the connection was lost, then it reconnects
connection.ping(reconnect=True)      

# execute the query
cursor.execute(query)

I hope it helps.

Solution 3:

The easiest way is to check the connection right before sending a query.

You can do this by creating a small class that contains two methods: connect and query:

import pymysql
import pymysql.cursors

classDB:
    defconnect(self):
        self.conn = pymysql.connect(
                             host=hostname,
                             user=username,
                             password=password,
                             db=dbname,
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor,
                             port=3306)

    defquery(self, sql):
        try:
            cursor = self.conn.cursor()
            cursor.execute(sql)
        except pymysql.OperationalError:
            self.connect()
            cursor = self.conn.cursor()
            cursor.execute(sql)
        return cursor

db = DB()

Now, whenever you send a query using db.query("example SQL") the request is automatically prepared to encounter a connection error and reconnects using self.connect() if it needs to.

Remember: This is a simplified example. Normally, you would want to let PyMySQL help you escape special characters in your queries. To do that, you would have to add a 2nd parameter in the query method and go from there.

Solution 4:

the logic is quite simple, if connection close then try to reconnect for several times in this case I use max tries for 15 times to reconnect or ping.

import pymysql, pymysql.cursors
conn = pymysql.connect(
                         host=hostname,
                         user=username,
                         password=password,
                         db=dbname,
                         charset='utf8mb4',
                         cursorclass=pymysql.cursors.DictCursor,
                         )
cursor = conn.cursor()
# you can do transactions to database and when you need conn later, just make sure the server is still connectedif conn.openisFalse:
   max_try = 15try = 0while conn.openisFalse:
       iftry < max_try:
           conn.ping() # autoreconnect is true by defaulttry +=1# check the conn again to make sure it connectedif conn.open:
    # statements when conn is successfully reconnect to the serverelse:
    # it must be something wrong : server, network etc