Combined use of PythonDBUtils data connection pool and mysql _ MySQL

Use PythonDBUtils data connection pool in combination with mysql python

#-*-Coding: UTF-8-*-"" desc: database operation class @ note: 1. when executing an SQL statement with parameters, specify the list of conditions to be entered with the SQL statement, then tuple/list is used for conditional configuration. 2. you do not need to use quotation marks to specify the data type in the format SQL statement, the system automatically identifies the value based on the input parameters. 3. if the conversion function is not required for the input value, the system automatically processes "" import MySQLdbfrom MySQLdb. cursors import DictCursorfrom DBUtils. pooledDB import PooledDBimport Config "Config is the configuration file for some databases" class Mysql (object): "MYSQL database object, responsible for generating database connections, the connection pool is used to obtain the connection object: conn = Mysql. getConn () releases the connection object; conn. close () or del conn "" # connection pool object _ pool = None def _ init _ (self): "database constructor, which extracts the connection from the connection pool, and generate the operation cursor "" # self. _ conn = MySQLdb. connect (host = Config. DBHOST, port = Config. DBPORT, user = Config. DBUSER, passwd = Config. DBPWD, # db = Config. DBNAME, use_unicode = False, charset = Config. DBCHAR, cursorclass = DictCursor) self. _ conn = Mysql. _ getConn () self. _ cursor = self. _ conn. cursor () @ staticmethod def _ getConn (): "@ summary: static method, which extracts the connection from the connection pool @ return MySQLdb. connection "if Mysql. _ pool is None: _ pool = PooledDB (creator = MySQLdb, mincached = 1, maxcached = 20, host = Config. DBHOST, port = Config. DBPORT, user = Config. DBUSER, passwd = Config. DBPWD, db = Config. DBNAME, use_unicode = False, charset = Config. DBCHAR, cursorclass = DictCursor) return _ pool. connection () def getAll (self, SQL, param = None): "@ summary: execute the query and retrieve all result sets @ param SQL: query SQL, if a query condition exists, specify only the condition list and pass the condition value in with the [param] parameter @ param: optional parameter. the condition list value (tuples/Lists) @ return: result list/boolean query result set "if param is None: count = self._cursor.exe cute (SQL) else: count = self._cursor.exe cute (SQL, param) if count> 0: result = self. _ cursor. fetchall () else: result = False return result def getOne (self, SQL, param = None): "" @ summary: execute the query and retrieve the first @ param SQL: query SQL statements. if there are query conditions, specify only the condition list and pass the condition value in with the [param] parameter @ param: optional parameter, the condition list value (tuples/lists) @ return: result list/boolean query result set "if param is None: count = self._cursor.exe cute (SQL) else: count = self._cursor.exe cute (SQL, param) if count> 0: result = self. _ cursor. fetchone () else: result = False return result def getask( self, SQL, num, param = None): "@ summary: execute the query, obtain the num result @ param SQL: queries the SQL statement. if there are query conditions, specify only the condition list and pass the condition value in with the [param] parameter @ param num: number of obtained results @ param: optional parameter, condition list value (tuples/Lists) @ return: result list/boolean query result set "if param is None: count = self._cursor.exe cute (SQL) else: count = self._cursor.exe cute (SQL, param) if count> 0: result = self. _ cursor. fetchextract (num) else: result = False return result def insertOne (self, SQL, value): "" @ summary: insert a record to the data table @ param SQL: SQL format @ param value: the record data to be inserted tuple/list @ return: insertId number of affected rows "self._cursor.exe cute (SQL, value) return self. _ getInsertId () def insertrecords (self, SQL, values): "@ summary: insert multiple records to the data table @ param SQL: the SQL format to insert @ param values: record data to be inserted tuple (tuple)/list [list] @ return: count the number of affected rows "" count = self._cursor.exe cute.pdf (SQL, values) return count def _ getInsertId (self): "gets the id generated by the last insert operation of the current connection, 0 "self._cursor.exe cute (" SELECT @ identity as id ") result = self. _ cursor. fetchall () return result [0] ['id'] def _ query (self, SQL, param = None): if param is None: count = self._cursor.exe cute (SQL) else: count = self._cursor.exe cute (SQL, param) return count def update (self, SQL, param = None): "" @ summary: update data table record @ param SQL: SQL format and conditions, use (% s, % s) @ param: the value to be updated tuple/list @ return: count the number of affected rows "return self. _ query (SQL, param) def delete (self, SQL, param = None): "@ summary: delete data table records @ param SQL: SQL format and conditions, use (% s, % s) @ param: condition value to be deleted tuple/list @ return: count affected rows "return self. _ query (SQL, param) def begin (self): "@ summary: enable transaction" self. _ conn. autocommit (0) def end (self, option = 'commit '): "@ summary: end transaction" if option = 'commit': self. _ conn. commit () else: self. _ conn. rollback () def dispose (self, isEnd = 1): "@ summary: Release connection pool resources" if isEnd = 1: self. end ('commit ') else: self. end ('rollback'); self. _ cursor. close () self. _ conn. close ()