mysql auto reconnect_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 shouldn't matter I think. I'm sure if I put some time in, I can come up with a way to reconnect to the database. However I was glancing pymysql code and I saw that there is a 'ping' method in Connection class, which I'm not sure exactly how to use.

The method looks like it will reconnect first time but after that it switched the reconnect flag to False again? Can I use this method, or is there a different way to establish connection if it is lost? Even if it is not pymysql how do people tackle, database servers going down and having to re-establish connection to mysql server?

def ping(self, reconnect=True):

''' Check if the server is alive '''

if self.socket is None:

if reconnect:

self._connect()

reconnect = False

else:

raise Error("Already closed")

try:

self._execute_command(COM_PING, "")

return self._read_ok_packet()

except Exception:

if reconnect:

self._connect()

return self.ping(False)

else:

raise

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")

class ConnectionPool:

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()

def get_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 removed

for _ 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):

while True:

conn = None

if self.pool.qsize() > 0:

conn = self.pool.get()

if not 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 is None:

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())

return True

except db.OperationalError, e:

LOGGER.warn('Cannot connect to mysql - retrying in {} seconds'.format(self.time_to_sleep))

LOGGER.exception(e)

return False

# test (pytest compatible) -------------------------------------------------------------------------------------------

import logging

from src.py.ConnectionPool import ConnectionPool

logging.basicConfig(level=logging.DEBUG)

LOGGER = logging.getLogger("test_connection_pool")

def test_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 hack

assert pool.qsize() == 20

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

from pymysql import connect

from pymysql.cursors import DictCursor

# create the connection

connection = connect(host='host', port='port', user='user',

password='password', db='db',

cursorclass=DictCursor)

# get the cursor

cursor = connection.cursor()

# if the connection was lost, then it reconnects

connection.ping(reconnect=True)

# execute the query

cursor.execute(query)

I hope it helps.

Seth Connell

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

class DB:

def connect(self):

self.conn = pymysql.connect(

host=hostname,

user=username,

password=password,

db=dbname,

charset='utf8mb4',

cursorclass=pymysql.cursors.DictCursor,

port=3306)

def query(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.

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 connected

if conn.open is False:

max_try = 15

try = 0

while conn.open is False:

if try < max_try:

conn.ping() # autoreconnect is true by default

try +=1

# check the conn again to make sure it connected

if conn.open:

# statements when conn is successfully reconnect to the server

else:

# it must be something wrong : server, network etc

来源：https://stackoverflow.com/questions/22699807/python-mysql-using-pymysql-auto-reconnect