mysql建立的连接，在8小时内都没有访问请求的话，mysql server将主动断开这条连接。在使用pymysql或MySQLdb操作数据库连接时，当cursor一直处于连接状态，未及时close时，连接池被占用。查看后台日志：

"MySQLserverhasgoneaway(%r)"%(e,))pymysql.err.OperationalError:(2006,"MySQLserverhasgoneaway(TimeoutError(110,'Connectiontimedout'))")

代码中未在query操作及时closecursor，在每个连接中，均要有cursor.close()和conn.close()操作。即：

defdb_execute(query):conn=MySQLdb.connect(*)cur=conn.cursor()cur.execute(query)res=cur.fetchall()cur.close()conn.close()returnres

这样的话会有性能问题，推荐使用SqlAlchemy.pool。那mysql中有办法实现吗？我们试试多线程和协程。

classMysqlConnect(object):"""mysqlconnect基类"""def__init__(self,db_params=cmdb_test_params,maxconn=5):self.db_params=db_paramsself.maxconn=maxconnself.pool=Queue(maxconn)foriinrange(maxconn):self.connect=self._connect()self.commit()self.cursor=self._cursor()def_connect(self):"""mysqlconnect:returncursor:"""key=['host','port','user','password','database','charset']ifnotall([Trueifkinself.db_paramselseFalseforkinkey]):raiseException(list(self.db_params.keys()),"数据库连接失败，请检查配置参数")try:conn=pymysql.connect(**self.db_params)conn.autocommit(True)self.pool.put(self.connect)exceptpymysql.Errorase:logutil.Logger().error(e)traceback.print_exc()raisepymysql.Error("连接数据库失败%s"%e)self.connect=connreturnself.connectdef_cursor(self):ifself.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)returnself.cursordefclose(self):ifself.connect:self.cursor.close()#关闭游标，未及时close时，连接池被占用errorcode2006self.pool.put(self.connect)self.connect=Nonedefcommit(self):try:ifself.connect:self.connect.autocommit(True)exceptpymysql.Errorase:logutil.Logger().error(e)traceback.print_exc()raisepymysql.Error("数据库提交失败%s"%e)finally:self.close()defrollback(self):try:ifself.connect:self.connect.rollback()exceptpymysql.Errorase:logutil.Logger().error(e)traceback.print_exc()raisepymysql.Error("数据库回滚失败%s"%e)finally:ifself.connect:self.close()def__del__(self):self.commit()defquery_execute(self,sql):try:ifself.connectisNone:self._connect()self._cursor()result_list=[]self.cursor.execute(sql)forrowinself.cursor.fetchall():result_list.append(list(row))returnresult_listexceptpymysql.Errorase:logutil.Logger().error(e)traceback.print_exc()raisepymysql.Error("数据库查询失败%s"%e)finally:ifself.connect:self.close()defdml_execute(self,sql):try:ifself.connectisNone:self._connect()self._cursor()ifself.cursorisNone:self._cursor()self.cursor.execute(sql)self.commit()exceptpymysql.Errorase:logutil.Logger().error(e)traceback.print_exc()self.rollback()raisepymysql.Error("数据库执行dml失败%s"%e)finally:self.close()defdml_execute_many(self,sql):try:ifself.connectisNone:self._connect()self._cursor()ifself.cursorisNone:self._cursor()self.cursor.executemany(sql)self.commit()exceptpymysql.Errorase:logutil.Logger().error(e)traceback.print_exc()self.rollback()raisepymysql.Error("数据库执行dml失败%s"%e)finally:self.close()deftestmysqldb(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)returnTrueexceptExceptionase:print(("Error%d:%s"%(e.args[0],e.args[1])))returnFalse