One 、 Encapsulating the database
1.1 Basic database configuration
1.2 Write a singleton pattern annotation
1.3 Build a connection pool
1.4 encapsulation Python operation MYSQL Code for
Two 、 Connection pool test
Scene one ： The same example , perform 2 Time sql
Scene two ： Create... In turn 2 An example , Respective implementation sql
Scene three ： start-up 2 Threads , But when a thread creates a connection pool instance , There are time intervals
Scene 4 ： start-up 2 Threads , When a thread creates a connection pool instance , No time interval
Preface ：
Thread safety problem ： When 2 When threads use the thread pool at the same time , Will create 2 A thread pool . If there are multiple threads , Stagger the use of thread pools , Only one thread pool will be created , Will share a thread pool . I used the annotation mode of the singleton mode , It feels like a single instance of this annotation , Solved the problem of multithreading , But it doesn't solve the thread safety problem , The singleton pattern needs to be optimized .
Mainly through PooledDB Module implementation .
One 、 Encapsulating the database 1.1 Basic database configurationdb_config.py
# -*- coding: UTF-8 -*-import pymysql# database information DB_TEST_HOST = "127.0.0.1"DB_TEST_PORT = 3308DB_TEST_DBNAME = "bt"DB_TEST_USER = "root"DB_TEST_PASSWORD = "123456"# Database connection code DB_CHARSET = "utf8"# mincached : Number of idle connections opened at startup ( The default value 0 Do not create a connection at the beginning )DB_MIN_CACHED = 5# maxcached : The maximum number of idle connections allowed in the connection pool ( The default value 0 Represents the size of the non idle connection pool )DB_MAX_CACHED = 0# maxshared : Maximum number of shared connections allowed ( The default value 0 Means that all connections are private ) If the maximum quantity is reached , The connection requested to be shared will be shared DB_MAX_SHARED = 5# maxconnecyions : Maximum number of connection pools created ( The default value 0 It means no limit )DB_MAX_CONNECYIONS = 300# blocking : Set the behavior when the maximum number of connection pools is reached ( The default value 0 or False Represents an error returned <toMany......> Others represent blocking until the number of connections decreases , Connection assigned )DB_BLOCKING = True# maxusage : The maximum number of multiplexes allowed for a single connection ( The default value 0 or False Represents unlimited reuse ). When the maximum number is reached , The connection will automatically reconnect ( Close and reopen )DB_MAX_USAGE = 0# setsession : An optional one SQL The command list is used to prepare each session , Such as ["set datestyle to german", ...]DB_SET_SESSION = None# creator : Use modules that connect to the database DB_CREATOR = pymysqlSet the maximum and minimum connection pool to 5 individual . When the connection pool is started , It will establish 5 A connection .
1.2 Write a singleton pattern annotationsingleton.py
# Singleton mode function , To decorate a class def singleton(cls,*args,**kw): instances = {} def _singleton(): if cls not in instances: instances[cls] = cls(*args,**kw) return instances[cls] return _singleton1.3 Build a connection pool db_dbutils_init.py
from dbutils.pooled_db import PooledDBimport db_config as config# import randomfrom singleton import singleton"""@ function ： Create a database connection pool """class MyConnectionPool(object): # Private property # Can be accessed directly through objects , But it can be accessed inside this class ; __pool = None # def __init__(self): # self.conn = self.__getConn() # self.cursor = self.conn.cursor() # Create database connection conn And cursor cursor def __enter__(self): self.conn = self.__getconn() self.cursor = self.conn.cursor() # Create a database connection pool def __getconn(self): if self.__pool is None: # i = random.randint(1, 100) # print(" Number of thread pools created "+str(i)) self.__pool = PooledDB( creator=config.DB_CREATOR, mincached=config.DB_MIN_CACHED, maxcached=config.DB_MAX_CACHED, maxshared=config.DB_MAX_SHARED, maxconnections=config.DB_MAX_CONNECYIONS, blocking=config.DB_BLOCKING, maxusage=config.DB_MAX_USAGE, setsession=config.DB_SET_SESSION, host=config.DB_TEST_HOST, port=config.DB_TEST_PORT, user=config.DB_TEST_USER, passwd=config.DB_TEST_PASSWORD, db=config.DB_TEST_DBNAME, use_unicode=False, charset=config.DB_CHARSET ) return self.__pool.connection() # Release connection pool resources def __exit__(self, exc_type, exc_val, exc_tb): self.cursor.close() self.conn.close() # Close the connection and return it to the link pool # def close(self): # self.cursor.close() # self.conn.close() # Remove a connection from the connection pool def getconn(self): conn = self.__getconn() cursor = conn.cursor() return cursor, conn# Get connection pool , Instantiation @singletondef get_my_connection(): return MyConnectionPool()1.4 encapsulation Python operation MYSQL Code for mysqlhelper.py
import timefrom db_dbutils_init import get_my_connection""" Execute the statement query and return the result. The result is not returned 0; increase / Delete / Change to return the number of changed data , No return 0"""class MySqLHelper(object): def __init__(self): self.db = get_my_connection() # Get connections from the data pool # # def __new__(cls, *args, **kwargs): # if not hasattr(cls, 'inst'): # Single case # cls.inst = super(MySqLHelper, cls).__new__(cls, *args, **kwargs) # return cls.inst # Encapsulate execution commands def execute(self, sql, param=None, autoclose=False): """ 【 It mainly determines whether there are parameters and whether the connection is released after execution 】 :param sql: String type ,sql sentence :param param: sql Statement to replace "select %s from tab where id=%s" Among them %s Is the parameter :param autoclose: Whether to close the connection :return: Return the connection conn And cursor cursor """ cursor, conn = self.db.getconn() # Get connection from connection pool count = 0 try: # count : Is the number of data pieces changed if param: count = cursor.execute(sql, param) else: count = cursor.execute(sql) conn.commit() if autoclose: self.close(cursor, conn) except Exception as e: pass return cursor, conn, count # Release the connection def close(self, cursor, conn): """ Release the connection and return it to the connection pool """ cursor.close() conn.close() # Query all def selectall(self, sql, param=None): cursor = None conn = None count = None try: cursor, conn, count = self.execute(sql, param) res = cursor.fetchall() return res except Exception as e: print(e) self.close(cursor, conn) return count # Check the list def selectone(self, sql, param=None): cursor = None conn = None count = None try: cursor, conn, count = self.execute(sql, param) res = cursor.fetchone() self.close(cursor, conn) return res except Exception as e: print("error_msg:", e.args) self.close(cursor, conn) return count # increase def insertone(self, sql, param): cursor = None conn = None count = None try: cursor, conn, count = self.execute(sql, param) # _id = cursor.lastrowid() # Get the primary key of the currently inserted data id, The id It should be auto generated conn.commit() self.close(cursor, conn) return count except Exception as e: print(e) conn.rollback() self.close(cursor, conn) return count # Add multiple lines def insertmany(self, sql, param): """ :param sql: :param param: Must be a tuple or a list [(),()] or （（）,（）） :return: """ cursor, conn, count = self.db.getconn() try: cursor.executemany(sql, param) conn.commit() return count except Exception as e: print(e) conn.rollback() self.close(cursor, conn) return count # Delete def delete(self, sql, param=None): cursor = None conn = None count = None try: cursor, conn, count = self.execute(sql, param) self.close(cursor, conn) return count except Exception as e: print(e) conn.rollback() self.close(cursor, conn) return count # to update def update(self, sql, param=None): cursor = None conn = None count = None try: cursor, conn, count = self.execute(sql, param) conn.commit() self.close(cursor, conn) return count except Exception as e: print(e) conn.rollback() self.close(cursor, conn) return count# if __name__ == '__main__':# db = MySqLHelper()# sql = "SELECT SLEEP(10)"# db.execute(sql)# time.sleep(20) # TODO Check the list # sql1 = 'select * from userinfo where name=%s' # args = 'python' # ret = db.selectone(sql=sql1, param=args) # print(ret) # (None, b'python', b'123456', b'0') # TODO Add a single # sql2 = 'insert into hotel_urls(cname,hname,cid,hid,url) values(%s,%s,%s,%s,%s)' # ret = db.insertone(sql2, ('1', '2', '1', '2', '2')) # print(ret) # TODO Add more # sql3 = 'insert into userinfo (name,password) VALUES (%s,%s)' # li = li = [ # (' Province by province ', '123'), # (' arrive ','456') # ] # ret = db.insertmany(sql3,li) # print(ret) # TODO Delete # sql4 = 'delete from userinfo WHERE name=%s' # args = 'xxxx' # ret = db.delete(sql4, args) # print(ret) # TODO to update # sql5 = r'update userinfo set password=%s WHERE name LIKE %s' # args = ('993333993', '%old%') # ret = db.update(sql5, args) # print(ret) Two 、 Connection pool test modify db_dbutils_init.py file , When creating a connection pool def __getconn(self): Methods , Add a print random number , In the future, it is convenient for us to determine whether it is a singleton thread pool .
The modified db_dbutils_init.py file ：
from dbutils.pooled_db import PooledDBimport db_config as configimport randomfrom singleton import singleton"""@ function ： Create a database connection pool """class MyConnectionPool(object): # Private property # Can be accessed directly through objects , But it can be accessed inside this class ; __pool = None # def __init__(self): # self.conn = self.__getConn() # self.cursor = self.conn.cursor() # Create database connection conn And cursor cursor def __enter__(self): self.conn = self.__getconn() self.cursor = self.conn.cursor() # Create a database connection pool def __getconn(self): if self.__pool is None: i = random.randint(1, 100) print(" Random number of thread pool "+str(i)) self.__pool = PooledDB( creator=config.DB_CREATOR, mincached=config.DB_MIN_CACHED, maxcached=config.DB_MAX_CACHED, maxshared=config.DB_MAX_SHARED, maxconnections=config.DB_MAX_CONNECYIONS, blocking=config.DB_BLOCKING, maxusage=config.DB_MAX_USAGE, setsession=config.DB_SET_SESSION, host=config.DB_TEST_HOST, port=config.DB_TEST_PORT, user=config.DB_TEST_USER, passwd=config.DB_TEST_PASSWORD, db=config.DB_TEST_DBNAME, use_unicode=False, charset=config.DB_CHARSET ) return self.__pool.connection() # Release connection pool resources def __exit__(self, exc_type, exc_val, exc_tb): self.cursor.close() self.conn.close() # Close the connection and return it to the link pool # def close(self): # self.cursor.close() # self.conn.close() # Remove a connection from the connection pool def getconn(self): conn = self.__getconn() cursor = conn.cursor() return cursor, conn # Get connection pool , Instantiation @singletondef get_my_connection(): return MyConnectionPool()Start testing ：
Scene one ： The same example , perform 2 Time sqlfrom mysqlhelper import MySqLHelperimport timeif __name__ == '__main__': sql = "SELECT SLEEP(10)" sql1 = "SELECT SLEEP(15)" db = MySqLHelper() db.execute(sql) db.execute(sql1) time.sleep(20)In the database , Use show processlist;
show processlist;When executing the first sql when . The database connection shows .
When executing the second sql when . The database connection shows ：
When performing the sql, Program sleep when . The database connection shows ：
Program print results ：
Random number of thread pool 43
It can be concluded from the above ：
After the thread pool starts , Generated 5 A connection . Execute the first sql when , Used 1 A connection . Finish the first sql after , Additional 1 A connection . This is a linear , There are... In the thread pool 5 A connection , But every time , Only one of them was used .
There is a doubt. , If the connection pool does not support concurrency, it is meaningless ？
Above , Although the thread pool is enabled 5 A connection , But every time sql, Only one connection is used . Then why not set the thread pool size to 1 Well ？ What is the point of setting the thread pool size ？（ If in a non concurrent scenario , Whether setting the size is meaningless ？）
Advantages over not using a thread pool ：
If you do not use a thread pool , One at a time sql All have to create 、 disconnect . Use connection pooling like this , Don't create it over and over again 、 disconnect , Just use the ready-made connection directly .
Scene two ： Create... In turn 2 An example , Respective implementation sqlfrom mysqlhelper import MySqLHelperimport timeif __name__ == '__main__': db = MySqLHelper() db1 = MySqLHelper() sql = "SELECT SLEEP(10)" sql1 = "SELECT SLEEP(15)" db.execute(sql) db1.execute(sql1) time.sleep(20)First instance db, perform sql. Thread pool started 5 A connection
Second example db1, perform sql：
When the program sleeps , altogether 5 A thread pool ：
Print the results ：
it turns out to be the case that ：
Although we have created 2 An example , however （1） Create print results of thread pool , Print only 1 Time , And from beginning to end , The thread pool is only started 5 A connection , And connected id No change , That means it's always this 5 A connection .
prove , Although we have created 2 An example , But this 2 An instance is actually an instance .（ Singleton mode is in effect ）
Scene three ： start-up 2 Threads , But when a thread creates a connection pool instance , There are time intervalsimport threadingfrom mysqlhelper import MySqLHelperimport timedef sl1(): time.sleep(2) db = MySqLHelper() sql = "SELECT SLEEP(6)" db.execute(sql)def sl2(): time.sleep(4) db = MySqLHelper() sql = "SELECT SLEEP(15)" db.execute(sql)if __name__ == '__main__': threads = [] t1 = threading.Thread(target=sl1) threads.append(t1) t2 = threading.Thread(target=sl2) threads.append(t2) for t in threads: t.setDaemon(True) t.start() time.sleep(20)2 Threads are spaced 2 second .
Observe the number of connections to the database ：
Print the results ：
In concurrent execution 2 individual sql when , Shared this 5 A connection , And the print result is only printed once , Note although concurrent creation 2 Second instance , But really only one connection pool is created .
Scene 4 ： start-up 2 Threads , When a thread creates a connection pool instance , No time intervalimport threadingfrom mysqlhelper import MySqLHelperimport timeif __name__ == '__main__': db = MySqLHelper() sql = "SELECT SLEEP(6)" sql1 = "SELECT SLEEP(15)" threads = [] t1 = threading.Thread(target=db.execute, args=(sql,)) threads.append(t1) t2 = threading.Thread(target=db.execute, args=(sql1,)) threads.append(t2) for t in threads: t.setDaemon(True) t.start() time.sleep(20)Observe the database connection ：
Print the results ：
It turns out that ：
The terminal prints 2 Time , The database is established 10 A connection , Description created 2 A thread pool . Such a singleton pattern , There are thread safety issues .
This is about Python This is the end of the article on encapsulating database connection pools , More about Python For connection pool content, please search the previous articles of SDN or continue to browse the relevant articles below. I hope you can support SDN more in the future ！
