Python Database connection Pool dbutils

Dbutils is a Python module that implements database connection pooling

Two modes:

1. Create a link for each thread, even though the thread will not close even if the close () method is called, just put the thread in the connection pool for its own use, and the thread connection shuts down automatically when the connection is closed.

1  fromDbutils.persistentdbImportPersistentdb2 ImportPymysql3PooL =Persistentdb (4Creator = Pymysql,#modules that use linked databases5Maxusage = None,#the maximum number of times a link is used, none means unlimited6Setsession = [],#commands to execute before starting a session7ping = 0,#Ping the MySQL server to check if the service is available8Closeable = False,#Conn.close () is actually ignored for the next use until the thread shuts down, automatically closes the link, and equals True when Conn.close () is really closed9Threadlocal = None,#The object for which this thread has exclusive values for saving linked objectsTenHost ='127.0.0.1', OnePort = 3306, Auser ='Root', -Password ='123', -Database ='Ok1', theCharSet ='UTF8' - ) - deffunc (): -conn =pool.connection () +cursor =conn.cursor () -Cursor.execute ('SELECT * from book') +result =Cursor.fetchall () A     Print(Result) at cursor.close () - conn.close () - ImportThreading -  forIinchRange (5): -t = Threading. Thread (target=func) -T.start ()

Mode One

2. Create a batch connection to the connection pool for all threads to share with

Note: Because the threadsafety value in Pymysql,mysqldb is 1, all threads share the connection

1 Import Time2 ImportPymysql3 ImportThreading4  fromDbutils.pooleddbImportpooleddb,shareddbconnection5 6POOL =Pooleddb (7Creator = Pymysql,#modules that use linked databases8MaxConnections = 6,#Maximum number of connections allowed for connection pool, 0 and none means no limit9mincached = 2,#when initializing, the connection pool creates at least idle connections, 0 means not creatingTenmaxcached = 5,#Connection Pool Idle maximum number of connections, 0 and none means no limit Onemaxshared = 3,#The maximum number of connections shared in the connection pool, 0 and none means all shared, PS: In fact, there is no use, because Pymsql and mysqldb modules such as threadsafety are 1, all values regardless of the number of settings, _maxcahed is always 0, So forever is all link sharing Ablocking = True,#If there are no available shared connections in the link pool, if the wait is blocked, true indicates wait, false means no wait and then an error -Setsession = [],#List of commands to execute before starting the session -ping = 0,#Ping the Mysql server to check if the service is available theHost ='127.0.0.1', -Port = 3306, -user ='Root', -Password ='123', +Database ='Ok1', -CharSet ='UTF8' + ) A deffunc (): at     #detects if the number of currently running connections is less than the maximum number of connections, waits for a connection or throws a raise toomanyconnections exception if not less than -     #Otherwise, get the connection in the connection created at initialization time Steadydbconnection -     #The Steadydbconnection object is then encapsulated into the pooleddedicateddbconnection and returned -     #If the connection you initially created does not have a link, go to create the Steadydbconnection object and encapsulate it in pooleddedicateddbconnection and return -     #once the link is closed, the connection is returned to the connection pool for subsequent threads to continue to use -conn =pool.connection () incursor =conn.cursor () -Cursor.execute ('SELECT * from book') toresult =Cursor.fetchall () +     Print(Result) - conn.close () theFunc ()

Mode two

PS: About the Pymysql module, if there is no single-threaded case, the connection to the MySQL database is not a problem, but if the multi-threaded, you need to lock, once locked, the back of the thread will have to wait, it is bound to reduce the use of efficiency.

1 ImportPymysql2 ImportThreading3  fromThreadingImportRlock4LOCK =Rlock ()5CONN = Pymysql.connect (host='127.0.0.1',6Port = 3306,7user ='Root',8Password ='123',9Database ='Ok1',TenCharSet ='UTF8' One                        ) A defTask (ARG): - With LOCK: -cursor =conn.cursor () theCursor.execute ('SELECT * from book') -result =Cursor.fetchall () - cursor.close () -         Print(Result) +  forIinchRange (10): -t = Threading. Thread (target=task,args=(i,)) +T.start ()

Locking

1 ImportPymysql2 ImportThreading3CONN = Pymysql.connect (host='127.0.0.1',4port=3306,5User='Root',6password='123',7Database='Ok1',8charset='UTF8')9 Ten defTask (ARG): Onecursor =conn.cursor () ACursor.execute ('SELECT * from book') -result =Cursor.fetchall () - cursor.close () the     Print(Result) -  forIinchRange (10):#1 o'clock no error . -t = Threading. Thread (Target=task, args=(i,)) -T.start ()

No lock

Python Database connection Pool dbutils