我不确定这是否可行，但我正在寻找一种在连接丢失时重新连接到 mysql 数据库的方法。所有连接都保存在 gevent 队列中，但我认为这不重要...
我不确定这是否可行，但我正在寻找一种在连接丢失时重新连接到 mysql 数据库的方法。所有连接都保存在 gevent 队列中，但我认为这不重要。我确信如果我花点时间，我可以想出一种重新连接到数据库的方法。然而，我浏览了 pymysql 代码，发现 Connection 类中有一个“ping”方法，我不确定如何使用它。
该方法看起来第一次会重新连接，但之后它又将重新连接标志切换为 False？我可以使用这种方法吗？或者如果丢失连接，是否有其他方法建立连接？即使不是 pymysql，人们如何应对数据库服务器宕机并必须重新建立与 mysql 服务器的连接？
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
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
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)
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
def _reconnect(func):
@wraps(func)
def rec(self,*args,**kwargs):
try:
result = func(self,*args,**kwargs)
return result
except (mysql.connector.Error, mysql.connector.Warning) as e:
self.connect()
result = func(self,*args,**kwargs)
return result
return rec
这样任何使用连接的函数现在都可以被修饰成这样
@_reconnect
def check_user_exists(self,user_id):
self.cursor.execute("SELECT COUNT(*) FROM _ where user_id={};".format(user_id))
if self.cursor.fetchall()[0][0]==0:
return False
else:
return True
