How to implement a Python MySQL connection pool and join cache expiration

MySQL server will actively disconnect this connection if it has no access request within 8 hours. When you use Pymysql or MYSQLDB to manipulate a database connection, the connection pool is occupied when the cursor is in a connected state and is not close in time. To view the background log:

"MySQL server has gone away (%r)"% (e,)) Pymysql.err.OperationalError: (2006, "MySQL server has gone away (Timeouterror (11 0, ' Connection timed out '))

The code does not immediately close the cursor in the query operation, with Cursor.close () and Conn.close () operations in each connection. That

def db_execute (query):      conn = mysqldb.connect (*)       cur = conn.cursor ()      cur.execute (query)       res = cur.fetchall ()      cur.close ()       conn.close ()      return res 

In this case there will be performance problems, we recommend the use of Sqlalchemy.pool. Is there a way to do it in MySQL? Let's try multithreading and co-scheduling.

Class mysqlconnect (object):     "" "    mysql connect  base class      "" "    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):          "" "    &NBsp;   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 ()),  "Database connection failed, check configuration parameters")          try:            conn  = pymysql.connect (**self.db_params)              conn.autocommit (True)              Self.pool.put (Self.connect)  &NBsp;      except pymysql. Error as e:            logutil. Logger (). Error (E)             traceback.print_exc ()             raise pymysql. Error ("Connection database failed  %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 ()   #  close cursor, connection pool is occupied when close is not in time  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 ("Database submission failed  %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 ("Database rollback failed  %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)       &nbSp;     traceback.print_exc ()              raise pymysql. Error ("Database query failed  %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 ("Database execution DML failed  %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 ("Database execution DML failed  %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

How to implement a Python MySQL connection pool and join cache expiration