python connects to Mysql database:

Python Programming can be done using MySQLdb data base Connections and operations such as query/insert/update, but each connection MySQL When database requests are accessed independently, it is a waste of resources, and when the number of accesses reaches a certain number, it will have a great impact on the performance of mysql. Therefore, in actual use, database connection pool technology is usually used to access the database to achieve the purpose of resource reuse.

python's database connection pool package DBUtils:

DBUtils is a set of Python database connection pool packages that allow thread-safe wrapping of non-thread-safe database interfaces. DBUtils comes from Webware for Python.

DBUtils provides two external interfaces:

• * PersistentDB: Provides thread-specific database connections and automatically manages connections.
• * PooledDB: Provides shared database connections between threads and automatically manages connections.

Download address: DBUtils After downloading and decompressing, install using the python setup.py install command

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's connection pool object and creates common operation methods such as query/insert. This part of the code is implemented as follows:

1. # -*- coding: UTF-8 -*-  
2. """ 
3. Created on 2016 7 May 2000 
4.  
5. @author: baocheng 
6. 1,Executing parameterizedＳＱＬPlease use it first. sql Statement specifies the list of conditions to be entered, and then uses tuple/list Conditional batch 
7. ２,In FormatＳＱＬNo quotation marks are needed to specify the data type. The system will automatically recognize the input parameters. 
8. ３,There is no need to use the transfer function in the input value, and the system will process it automatically. 
9. """  
10.    
11. import MySQLdb  
12. from MySQLdb.cursors import DictCursor  
13. from DBUtils.PooledDB import PooledDB  
14. #from PooledDB import PooledDB  
15. import Config  
16.   
17. """ 
18. Config Is the configuration file for some databases 
19. """  
20.    
21. class Mysql(object):  
22.     """ 
23.     MYSQL Database objects, responsible for generating database connections , Connections in this class use connection pooling to obtain connection objects: conn = Mysql.getConn() 
24.             Release connection objects;conn.close()or del conn 
25.     """  
26.     #Connection pool object  
27.     __pool = None  
28.     def __init__(self):  
29.         #The database constructor takes the connection out of the connection pool and generates the operation cursor  
30.         self._conn = Mysql.__getConn()  
31.         self._cursor = self._conn.cursor()  
32.   
33.     @staticmethod  
34.     def __getConn():  
35.         """ 
36.         @summary: Static method to remove connections from connection pool 
37.         @return MySQLdb.connection 
38.         """  
39.         if Mysql.__pool is None:  
40.             __pool = PooledDB(creator=MySQLdb, mincached=1 , maxcached=20 ,  
41.                               host=Config.DBHOST , port=Config.DBPORT , user=Config.DBUSER , passwd=Config.DBPWD ,  
42.                               db=Config.DBNAME,use_unicode=False,charset=Config.DBCHAR,cursorclass=DictCursor)  
43.         return __pool.connection()  
44.    
45.     def getAll(self,sql,param=None):  
46.         """ 
47.         @summary: Execute the query and fetch all result sets 
48.         @param sql:queryＳＱＬ，If there are query conditions, specify only the list of conditions and use parameters for the condition values[param]Pass in 
49.         @param param: Optional parameters, conditional list values (tuples)/List) 
50.         @return: result list(Dictionary object)/boolean Queried result sets 
51.         """  
52.         if param is None:  
53.             count = self._cursor.execute(sql)  
54.         else:  
55.             count = self._cursor.execute(sql,param)  
56.         if count>0:  
57.             result = self._cursor.fetchall()  
58.         else:  
59.             result = False  
60.         return result  
61.    
62.     def getOne(self,sql,param=None):  
63.         """ 
64.         @summary: Execute the query and take out Article 1 
65.         @param sql:queryＳＱＬ，If there are query conditions, specify only the list of conditions and use parameters for the condition values[param]Pass in 
66.         @param param: Optional parameters, conditional list values (tuples)/List) 
67.         @return: result list/boolean Queried result sets 
68.         """  
69.         if param is None:  
70.             count = self._cursor.execute(sql)  
71.         else:  
72.             count = self._cursor.execute(sql,param)  
73.         if count>0:  
74.             result = self._cursor.fetchone()  
75.         else:  
76.             result = False  
77.         return result  
78.    
79.     def getMany(self,sql,num,param=None):  
80.         """ 
81.         @summary: Execute the query and take it out num Article result 
82.         @param sql:queryＳＱＬ，If there are query conditions, specify only the list of conditions and use parameters for the condition values[param]Pass in 
83.         @param num:Number of results obtained 
84.         @param param: Optional parameters, conditional list values (tuples)/List) 
85.         @return: result list/boolean Queried result sets 
86.         """  
87.         if param is None:  
88.             count = self._cursor.execute(sql)  
89.         else:  
90.             count = self._cursor.execute(sql,param)  
91.         if count>0:  
92.             result = self._cursor.fetchmany(num)  
93.         else:  
94.             result = False  
95.         return result  
96.    
97.     def insertOne(self,sql,value):  
98.         """ 
99.         @summary: Insert a record into the data table 
100.         @param sql:To insertＳＱＬformat 
101.         @param value:Record data to be inserted tuple/list 
102.         @return: insertId Number of rows affected 
103.         """  
104.         self._cursor.execute(sql,value)  
105.         return self.__getInsertId()  
106.    
107.     def insertMany(self,sql,values):  
108.         """ 
109.         @summary: Insert multiple records into the data table 
110.         @param sql:To insertＳＱＬformat 
111.         @param values:Record data to be inserted tuple(tuple)/list[list] 
112.         @return: count Number of rows affected 
113.         """  
114.         count = self._cursor.executemany(sql,values)  
115.         return count  
116.    
117.     def __getInsertId(self):  
118.         """ 
119.         Gets the last insert operation generated by the current connection id,If not, 0 
120.         """  
121.         self._cursor.execute("SELECT @@IDENTITY AS id")  
122.         result = self._cursor.fetchall()  
123.         return result[0]['id']  
124.    
125.     def __query(self,sql,param=None):  
126.         if param is None:  
127.             count = self._cursor.execute(sql)  
128.         else:  
129.             count = self._cursor.execute(sql,param)  
130.         return count  
131.    
132.     def update(self,sql,param=None):  
133.         """ 
134.         @summary: Update data table records 
135.         @param sql: ＳＱＬFormat and conditions, use(%s,%s) 
136.         @param param: To be updated  value tuple/list 
137.         @return: count Number of rows affected 
138.         """  
139.         return self.__query(sql,param)  
140.    
141.     def delete(self,sql,param=None):  
142.         """ 
143.         @summary: Delete data table records 
144.         @param sql: ＳＱＬFormat and conditions, use(%s,%s) 
145.         @param param: Conditions to be deleted value tuple/list 
146.         @return: count Number of rows affected 
147.         """  
148.         return self.__query(sql,param)  
149.    
150.     def begin(self):  
151.         """ 
152.         @summary: Open a transaction 
153.         """  
154.         self._conn.autocommit(0)  
155.    
156.     def end(self,option='commit'):  
157.         """ 
158.         @summary: Closing the transaction 
159.         """  
160.         if option=='commit':  
161.             self._conn.commit()  
162.         else:  
163.             self._conn.rollback()  
164.    
165.     def dispose(self,isEnd=1):  
166.         """ 
167.         @summary: Release connection pool resources 
168.         """  
169.         if isEnd==1:  
170.             self.end('commit')  
171.         else:  
172.             self.end('rollback');  
173.         self._cursor.close()  
174.         self._conn.close()  

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

1. #coding:utf-8  
2. ''''' 
3. Created on 2016 7 May 2000 
4.  
5. @author: baocheng 
6. '''  
7. DBHOST = "localhost"  
8. DBPORT = 33606  
9. DBUSER = "zbc"  
10. DBPWD = "123456"  
11. DBNAME = "test"  
12. DBCHAR = "utf8"  

Create a test module to test mysql access using connection pools:

1. #coding:utf-8  
2. ''''' 
3.  
4. @author: baocheng 
5. '''  
6. from MySqlConn import Mysql  
7. from _sqlite3 import Row  
8.   
9. #Application resources  
10. mysql = Mysql()  
11.   
12. 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"  
13. result = mysql.getAll(sqlAll)  
14. if result :  
15.     print "get all"  
16.     for row in result :  
17.         print "%s\t%s"%(row["uid"],row["goodsname"])  
18. 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"  
19. result = mysql.getMany(sqlAll,2)  
20. if result :  
21.     print "get many"  
22.     for row in result :  
23.         print "%s\t%s"%(row["uid"],row["goodsname"])          
24.           
25.           
26. result = mysql.getOne(sqlAll)  
27. print "get one"  
28. print "%s\t%s"%(result["uid"],result["goodsname"])  
29.   
30. #Releasing resources  
31. mysql.dispose()  

Of course, there are many other parameters to configure:

• dbapi: database interface
• mincached: Number of empty connections opened at startup
• maxcached: Maximum number of connections available in connection pools
• maxshared: Maximum number of shared connections in connection pool
• maxconnections: maximum number of connections allowed
• blocking: blocking when the maximum number is reached
• maxusage: Maximum number of multiplexing times for a single connection

According to their own needs, the above resource parameters should be rationally allocated to meet their actual needs.
So far, the mysql connection pool in python has been implemented, and it will be better to use it directly next time.
Blog address: data mining club