Implement Mysql database connection pool using Python

Python connects to the Mysql database: in python programming, MySQLdb can be used to connect to the database and perform operations such as query, insert, and update operations. However, each request to connect to the mysql database is independent of requests for access, it is a waste of resources, and when the number of accesses reaches a certain level, it will have a great impact on mysql performance. Therefore, in actual use,

Python connects to the Mysql database: in python programming, you can use MySQLdb to connect to the database and perform operations such as query, insert, and update. However, each request to connect to the mysql database is independent of requests for access, it is a waste of resources, and when the number of accesses reaches a certain level, it will have a great impact on mysql performance. Therefore, in actual use,

Connect python to the Mysql database:

In python programming, MySQLdb can be used to connect databases and perform operations such as query, insert, and update. However, each request to connect to the mysql database is independent of requests for access, which is a waste of resources, when the number of accesses reaches a certain level, the mysql performance will be greatly affected. Therefore, in actual use, the connection pool technology of the database is usually used to access the database for resource reuse.

Python database connection pool package DBUtils:

DBUtils is a Python database connection pool package that allows thread-Safe Packaging of non-thread-safe database interfaces. DBUtils is from Webware for Python.

DBUtils provides two external interfaces:

• * PersistentDB: Provides thread-specific database connections and automatically manages connections.
• * PooledDB: Provides database connections that can be shared among threads and automatically manages connections.

: After DBUtils is downloaded and decompressed, run the python setup. py install command to install it.

The following uses MySQLdb and DBUtils to create your own mysql database connection pool Toolkit

In the project directory, create a package named dbConnecttion and the module named MySqlConn. below is MySqlConn. this module creates the Mysql connection pool object, and creates common operation methods such as query/insert. The code is implemented as follows:

#-*-Coding: UTF-8-*-"" Created on May 7, 2016 @ author: baocheng1, when executing a SQL statement with parameters, please first use the SQL statement to specify the list of conditions to be entered, 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 PooledDB # from PooledDB import PooledDBimport Config "Config is the configuration file of 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 = 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 (Dictionary object)/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 ()

The configuration file module Cnofig contains the database connection information/user name and password:

# Coding: UTF-8 ''' Created on July 15, May 7, 2016 @ author: baocheng ''' DBHOST = "localhost" DBPORT = 33606 DBUSER = "zbc" DBPWD = "123456" DBNAME = "test" DBCHAR = "utf8"

Create the test module and test mysql access using the connection pool:

# Coding: UTF-8 ''' @ author: baocheng ''' from MySqlConn import Mysqlfrom _ sqlite3 import Row # apply for resource mysql = Mysql () sqlAll = "SELECT tb. uid as uid, group_concat (tb. goodsname) as goodsname FROM (SELECT goods. uid AS uid, IF (ISNULL (goodsrelation. goodsname), goods. goodsID, goodsrelation. goodsname) AS goodsname FROM goods left join goodsrelation ON goods. goodsID = goodsrelation. goodsId) tb group by tb. uid "result = mysql. getAll (sqlAll) if result: print "get all" for row in result: print "% s \ t % s" % (row ["uid"], row ["goodsname"]) sqlAll = "SELECT tb. uid as uid, group_concat (tb. goodsname) as goodsname FROM (SELECT goods. uid AS uid, IF (ISNULL (goodsrelation. goodsname), goods. goodsID, goodsrelation. goodsname) AS goodsname FROM goods left join goodsrelation ON goods. goodsID = goodsrelation. goodsId) tb group by tb. uid "result = mysql. getMany (sqlAll, 2) if result: print "get failed" for row in result: print "% s \ t % s" % (row ["uid"], row ["goodsname"]) result = mysql. getOne (sqlAll) print "get one" print "% s \ t % s" % (result ["uid"], result ["goodsname"]) # Release the mysql resource. dispose ()

Of course, there are many other parameters that can be configured:

• Dbapi: Database Interface
• Mincached: number of empty connections enabled at startup
• Maxcached: Maximum number of available connections in the connection pool
• Maxshared: Maximum number of accessible connections in the connection pool
• Maxconnections: Maximum allowed connections
• Blocking: whether to block when the maximum number is reached
• Maxusage: Maximum number of times a single connection is reused.

Configure the preceding resource parameters as needed to meet your actual needs.
So far, the mysql connection pool in python has been implemented and can be used directly next time.
Blog address: data mining club