mysql建立的連接，在8小時(shí)內(nèi)都沒有訪問請求的話，mysql server將主動(dòng)斷開這條連接。在使用pymysql或MySQLdb操作數(shù)據(jù)庫連接時(shí)，當(dāng)cursor一直處于連接狀態(tài)，未及時(shí)close時(shí)，連接池被占用。查看后臺(tái)日志：
"MySQL server has gone away (%r)" % (e,)) pymysql.err.OperationalError: (2006, "MySQL server has gone away (TimeoutError(110, 'Connection timed out'))")
代碼中未在query操作及時(shí)close cursor，在每個(gè)連接中，均要有cursor.close() 和 conn.close()操作。即：
def db_execute(query): conn = MySQLdb.connect(*) cur = conn.cursor() cur.execute(query) res = cur.fetchall() cur.close() conn.close() return res
這樣的話會(huì)有性能問題，推薦使用SqlAlchemy.pool。那mysql中有辦法實(shí)現(xiàn)嗎？我們試試多線程和協(xié)程。
重慶分公司
重慶分公司
class MysqlConnect(object):
"""
mysql connect 基類
"""
def __init__(self, db_params=cmdb_test_params, maxconn=5):
self.db_params = db_params
self.maxconn = maxconn
self.pool = Queue(maxconn)
for i in range(maxconn):
self.connect = self._connect()
self.commit()
self.cursor = self._cursor()
def _connect(self):
"""
mysql connect
:return cursor:
"""
key = ['host', 'port', 'user', 'password', 'database', 'charset']
if not all([True if k in self.db_params else False for k in key]):
raise Exception(list(self.db_params.keys()), "數(shù)據(jù)庫連接失敗，請檢查配置參數(shù)")
try:
conn = pymysql.connect(**self.db_params)
conn.autocommit(True)
self.pool.put(self.connect)
except pymysql.Error as e:
logutil.Logger().error(e)
traceback.print_exc()
raise pymysql.Error("連接數(shù)據(jù)庫失敗 %s" % e)
self.connect = conn
return self.connect
def _cursor(self):
if self.connect:
conn = self.pool.get()
self.cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
else:
self._connect()
conn = self.pool.get()
self.cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
return self.cursor
def close(self):
if self.connect:
self.cursor.close() # 關(guān)閉游標(biāo)，未及時(shí)close時(shí)，連接池被占用 error code 2006
self.pool.put(self.connect)
self.connect = None
def commit(self):
try:
if self.connect:
self.connect.autocommit(True)
except pymysql.Error as e:
logutil.Logger().error(e)
traceback.print_exc()
raise pymysql.Error("數(shù)據(jù)庫提交失敗 %s" % e)
finally:
self.close()
def rollback(self):
try:
if self.connect:
self.connect.rollback()
except pymysql.Error as e:
logutil.Logger().error(e)
traceback.print_exc()
raise pymysql.Error("數(shù)據(jù)庫回滾失敗 %s" % e)
finally:
if self.connect:
self.close()
def __del__(self):
self.commit()
def query_execute(self, sql):
try:
if self.connect is None:
self._connect()
self._cursor()
result_list = []
self.cursor.execute(sql)
for row in self.cursor.fetchall():
result_list.append(list(row))
return result_list
except pymysql.Error as e:
logutil.Logger().error(e)
traceback.print_exc()
raise pymysql.Error("數(shù)據(jù)庫查詢失敗 %s" % e)
finally:
if self.connect:
self.close()
def dml_execute(self, sql):
try:
if self.connect is None:
self._connect()
self._cursor()
if self.cursor is None:
self._cursor()
self.cursor.execute(sql)
self.commit()
except pymysql.Error as e:
logutil.Logger().error(e)
traceback.print_exc()
self.rollback()
raise pymysql.Error("數(shù)據(jù)庫執(zhí)行dml失敗 %s" % e)
finally:
self.close()
def dml_execute_many(self, sql):
try:
if self.connect is None:
self._connect()
self._cursor()
if self.cursor is None:
self._cursor()
self.cursor.executemany(sql)
self.commit()
except pymysql.Error as e:
logutil.Logger().error(e)
traceback.print_exc()
self.rollback()
raise pymysql.Error("數(shù)據(jù)庫執(zhí)行dml失敗 %s" % e)
finally:
self.close()
def testmysqldb(self,ip,user,password,dbname,Strsql):
try:
self.connect = pymysql.connect(host=ip,user=user,passwd=password,charset='utf8')
self.connect.select_db(dbname)
self.query_execute(Strsql)
return True
except Exception as e:
print(("Error %d :%s" %(e.args[0],e.args[1])))
return False另外有需要云服務(wù)器可以了解下創(chuàng)新互聯(lián)scvps.cn，海內(nèi)外云服務(wù)器15元起步，三天無理由+7*72小時(shí)售后在線，公司持有idc許可證，提供“云服務(wù)器、裸金屬服務(wù)器、高防服務(wù)器、香港服務(wù)器、美國服務(wù)器、虛擬主機(jī)、免備案服務(wù)器”等云主機(jī)租用服務(wù)以及企業(yè)上云的綜合解決方案，具有“安全穩(wěn)定、簡單易用、服務(wù)可用性高、性價(jià)比高”等特點(diǎn)與優(yōu)勢，專為企業(yè)上云打造定制，能夠滿足用戶豐富、多元化的應(yīng)用場景需求。
