Shulou(Shulou.com)05/31 Report--

This article is to share with you about how to achieve MySQL connection pool in Python, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

In python programming, MySQLdb can be used for database connection and operations such as query / insert / update, but each connection to the mysql database request is an independent request for access, which is a waste of resources, and when the number of visits reaches a certain number, it will have a great impact on the performance of mysql. Therefore, in practical use, the connection pool technology of the database is usually used to access the database to achieve the purpose of resource reuse.

Database connection pool

Python database connection pool package DBUtils:

DBUtils is a set of Python database connection pooling packages that allow thread-safe wrapping 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 between threads and automatically manages connections. Download address: after the DBUtils is downloaded and decompressed, use the python setup.py install command to install

Next, use MySQLdb and DBUtils to build your own mysql database connection pool toolkit. Under the project directory, the new package is named dbConnecttion, and the new module is named MySqlConn. Below is MySqlConn.py, this module creates Mysql connection pool objects and creates general operation methods such as query / insert. This part of the code is implemented as follows:

#-*-coding: UTF-8-*-"" 1. 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 allocation 2. There is no need to use quotation marks to specify the data type in the format SQL, the system will automatically identify 3 according to the input parameters, and there is no need to use the escape function in the input values. The system will automatically process "import MySQLdbfrom MySQLdb.cursors import DictCursorfrom DBUtils.PooledDB import PooledDB#from PooledDBimport PooledDBimport Config" Config is the configuration file of some databases "class Mysql (object):" MYSQL database object, which is responsible for generating database connections. The connection in this class uses connection pooling to obtain connection objects: conn = Mysql.getConn () releases connection objects. Conn.close () or del conn "" # connection pool object _ _ pool = None def _ _ init__ (self): # database constructor to take 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 Remove a 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 fetch all result sets @ param sql: query SQL. If there are query conditions, just specify the list of conditions and pass the condition values in @ param param: optional parameter using the parameter [param] Conditional list value (tuple / list) @ return: result list (dictionary object) / result set queried by boolean "" if param is None: count = self._cursor.execute (sql) else: count = self._cursor.execute (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 take out the first @ param sql: query SQL. If there are any query conditions, just specify the list of conditions and pass the condition values in with the parameter [param] @ param param: optional parameter Conditional list value (tuple / list) @ return: result set found by result list/boolean query "" 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: execute the query And take out the num results @ param sql: query SQL. If there are query conditions, just specify the list of conditions, and pass the condition values in @ param num using the parameter [param]: number of results obtained @ param param: optional parameter Conditional list value (tuple / list) @ return: result set queried by result list/boolean "" if param is None: count = self._cursor.execute (sql) else: count = self._cursor.execute (sql,param) if count > 0: result = self._cursor.fetchmany (num) else: result = False return result def insertOne (self,sql Value): "" @ summary: insert a record into the data table @ param sql: SQL format to insert @ param value: record data to insert tuple/list @ return: number of rows affected by insertId "" self._cursor.execute (sql,value) return self.__getInsertId () def insertMany (self,sql Values): "" @ summary: insert multiple records into the data table @ param sql: SQL format to insert @ param values: record data to insert tuple (tuple) / list [list] @ return: number of rows affected by count "" count = self._cursor.executemany (sql,values) return count def _ getInsertId (self): "" get the id generated by the last insert operation of the current connection If not, 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._cursor.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 Use (% param param% s) @ param param: the value to update tuple/list @ return: count affected rows "return self.__query (sql,param) def delete (self,sql,param=None):" @ summary: delete data table record @ param sql: SQL format and condition Use (% param param% s) @ param param: conditional value tuple/list @ return: count affected rows "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 connection pool resources "if isEnd==1: self.end ('commit') else: self.end (' rollback') Self._cursor.close () self._conn.close ()

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

# coding:utf-8DBHOST = "localhost" DBPORT = 3306DBUSER = "hello" DBPWD = "hello" DBNAME = "hello" DBCHAR = "utf8"

Create a test module and test using connection pooling for mysql access:

# coding:utf-8from MySqlConn import Mysqlfrom _ sqlite3 import Row# Application 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 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"]) # release resource mysql.dispose ()

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

Dbapi: database interface

Mincached: the number of empty connections opened at startup

Maxcached: maximum number of connections available for connection pooling

Maxshared: maximum number of connections that can be shared by connection pool

Maxconnections: maximum number of allowed connections

Blocking: whether it is blocked when the maximum number is reached

Maxusage: maximum number of times of reuse for a single connection

The above is how to achieve MySQL connection pool in Python. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.