Python implements MySQL database connection pool

Python connects to MySQL database:

In Python programming, you can use MYSQLDB for database connections and operations such as query/insert/update, but each time you connect a MySQL database request, it is independent to request access, a waste of resources, and the number of visits to a certain number, the performance of MySQL will have a greater impact. Therefore, in actual use, the database connection pooling technology is usually used to access the database to achieve the purpose of resource reuse.

Python database Connection Pool package Dbutils:

Dbutils is a set of Python database connection pool packages and allows thread-safe wrapping of non-thread-safe database interfaces. Dbutils from Webware for Python.

The Dbutils offers two external interfaces:

• * PERSISTENTDB: Provides a thread-specific database connection and automatically manages the connection.
• * POOLEDDB: Provides a database connection that can be shared between threads and automatically manages connections.

:dbutils Download and unzip, install using the Python setup.py install command

use MySQLdb and dbutils to build your own MySQL database connection Pooling Toolkit

Under the project catalog, the new package is named: Dbconnecttion, and the new module is named Mysqlconn, the following is mysqlconn.py, which creates a MySQL connection pool object and creates a common operation method such as query/INSERT. This part of the code is implemented as follows:

#-*-Coding:utf-8-*-"" "Created on May 7, 2016 @author:baocheng1, when executing SQL with parameters, first specify the list of conditions to be entered with the SQL statement, and then use tuple/list for conditional batching 2 , in format SQL does not need to use quotation marks to specify the data type, the system will automatically identify according to the input parameters 3, in the input value does not need to use the Mysqldbfrom function, the system will automatically process "" "Import mysqldb.cursors Import Dictcursorfrom DBUTILS.POOLEDDB Import pooleddb#from pooleddb import pooleddbimport Config "" "Config is a configuration file for some databases" " Class MySQL (object): "" "MySQL database object, responsible for generating a database connection, the connection in this class takes connection pool implementation to get the Connection object: conn = Mysql.getconn () releases the connection object; conn.cl OSE () or del conn "" "#连接池对象 __pool = None def __init__ (self): #数据库构造函数, remove the connection from the connection pool and generate an action cursor Self._con n = mysql.__getconn () Self._cursor = Self._conn.cursor () @staticmethod def __getconn (): "" "@s Ummary: Static method, remove connection from connection pool @return mysqldb.connection "" "If Mysql.__pool is None: __pool = Po OLEDDB (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: Executes the query and extracts all result sets @param sql: Query sql, if there is a query        condition, specify only the condition list and pass the condition value using the parameter [param] in @param param: Optional parameter, condition list value (tuple/list) @return: Result list (Dictionary object)/boolean query to results set "" "if param is None:count = self._cursor.execute (sql) Else:count = self._cur Sor.execute (sql,param) If Count>0:result = Self._cursor.fetchall () Else:result = False return result def getone (self,sql,param=none): "" "@summary: Executes the query and takes out the first @param sql: Query SQL, if you have a query condition, specify only the condition list and pass the condition value using the parameter [param] in @param param: Optional parameter, condition list value (tuple/list) @return: result List/boolean query to Result set "" "if param is None:count = self._cursor.execute (sql) Else:count = self . _cursor.execute (Sql,parAM) If Count>0:result = Self._cursor.fetchone () Else:result = False return Result def getMany (self,sql,num,param=none): "" "@summary: Executes the query and takes out the NUM bar result @param sql: Query sql, if there is a query condition, specify only the list of conditions and pass the condition value using the parameter [param] in @param num: Number of result bars obtained @param param: Optional parameter, condition list value (tuple/list) @return: Result L            Ist/boolean query to the result set "" if param is None:count = self._cursor.execute (sql) Else: Count = Self._cursor.execute (sql,param) If Count>0:result = Self._cursor.fetchmany (num) el Se:result = False return result def insertone (self,sql,value): "" "@summary: Inserts a bar into the data table Log @param sql: The SQL format to insert @param value: The record data to insert Tuple/list @return: Insertid The number of rows affected "" "Se Lf._cursor.execute (Sql,value) return Self.__getinsertid () def insertmany (self,sql,values): "" "@s      Ummary: Inserting multiple records into a data table  @param sql: The SQL format to insert @param values: The record data to insert tuple (tuple)/list[list] @return: Count affected Rows "" " Count = Self._cursor.executemany (sql,values) return Count def __getinsertid (self): "" Gets the current connection last The ID that is generated by the secondary insert operation, or 0 "" "Self._cursor.execute (" SELECT @ @IDENTITY as id ") result = Self._cursor.fetchall () return result[0][' id '] def __query (self,sql,param=none): if param is None:count = self._c Ursor.execute (sql) Else:count = Self._cursor.execute (Sql,param) return Count def update (self , Sql,param=none): "" "@summary: Update data table records @param sql:sql format and conditions, using (%s,%s) @param param: value to update t Uple/list @return: Count affected Rows "" "Return Self.__query (sql,param) def delete (Self,sql,param=none        ): "" "@summary: Delete data table records @param sql:sql format and conditions, using (%s,%s) @param param: The condition value to delete tuple/list @return: Number of rows affected by count        "" "Return Self.__query (Sql,param) def begin (self):" "" @summary: Open 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 the Connection pool resource "" "If Isend==1:self.end (' Commit ') else:self.        End (' rollback '); Self._cursor.close () Self._conn.close ()

Configuration file module Cnofig, including database connection information/user name password, etc.:

#coding: Utf-8 "Created on May 7, 2016 @author:baocheng" dbhost = "localhost" dbport = 33606DBUSER = "ZBC" dbpwd = "123456" D bname = "Test" Dbchar = "UTF8"

To create a test module, testing MySQL access using a connection pool:

#coding: Utf-8 ' @author: Baocheng ' from mysqlconn import mysqlfrom _sqlite3 import row# request Resources MySQL = MySQL () Sqlall = "Sele CT Tb.uid as UID, Group_concat (tb.goodsname) as Goodsname from (SELECT goods.uid as UID, IF (ISNULL (goodsrelation.goodsn AME), Goods.goodsid, Goodsrelation.goodsname) as goodsname from goods left joins goodsrelation on goods.goodsid = Goodsrel        ATION.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 FRO M (SELECT goods.uid as UID, IF (ISNULL (goodsrelation.goodsname), Goods.goodsid, Goodsrelation.goodsname) as Goodsname F ROM goods left JOIN goodsrelation in goods.goodsid = Goodsrelation.goodsid) TB GROUP by tb.uid "result = Mysql.getmany (sql                        all,2) If Result:print "get many" 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"]) #释放资源mysql. Dispose () 

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

• DBAPI: Database Interface
• mincached: Number of empty connections opened at startup
• Maxcached: Connection pool maximum number of available connections
• Maxshared: Connection pooling maximum number of shareable connections
• MaxConnections: Maximum allowable number of connections
• Blocking: block If maximum quantity is reached
• Maxusage: Maximum number of multiplexing times for a single connection

According to their own needs, reasonable allocation of the above-mentioned resource parameters to meet their actual needs.
At this point, the MySQL connection pool in Python is finished, the next time directly to use it.
Blog Address: Data Mining Club

Python implements MySQL database connection pool