Python Database connection Pool dbutils

Dbutils is a Python module used to implement database connection pooling in two ways

Open a connection for each thread

This is generally not recommended because there are 100 connections to 100 threads. The thread does not close even if it calls the Close method, where close simply puts the connection back into the connection pool for its own thread to use again. When the thread terminates, the connection is automatically closed.

From flask Import Flaskapp = Flask (__name__) from dbutils.persistentdb import persistentdbimport pymysqlimport timepool = P Ersistentdb (Creator=pymysql, # Using a linked database module Maxusage=none, # The number of times a link is reused, None means unrestricted setsession=[], # Execution before the start of the session A list of commands. such as: ["Set Datestyle to ...", "Set time zone ..."] ping=0, # Ping the MySQL server, check if the service is available. # for example: 0 = None = never, 1 = default = Whenever it is requested, 2 = When a cursor is created, 4 = When a query is executed, 7 = Always Closeable=false, # If False, Conn.close () is actually ignored for the next use, and the link is automatically closed when the thread is closed.    If True, Conn.close () closes the link, and then calls Pool.connection again with an error, because the connection is actually closed (pool.steady_connection () can get a new link)     Threadlocal=none, # This thread exclusive worth object, used to save the linked object, if the linked object is reset host= ' 127.0.0.1 ', port=3306, user= ' root ', password= ' 123456 ', database= ' Test ', charset= ' UTF8 ') @app. Route ('/login ') def func (): conn = pool.connection () cursor = Conn.cursor () cursor.execute (' select * from user ') result = Cursor.fetchall () cursor.close () Conn.closE () # is not really closed, but a fake off. conn = Pymysql.connect () conn.close () time.sleep () conn = pool.connection () cursor = conn.cursor () cursor . Execute (' select * from user ') result = Cursor.fetchall () cursor.close () conn.close () return ' login ' if __name_ _ = = ' __main__ ': App.run (debug=true)

The Time.sleep () method is used here, even if the Conn.close () was previously called, and the connection is maintained in Windows through the netstat -ano | findstr 3306 ability to discover

Create a fixed number of connections

From flask Import Flaskapp = Flask (__name__) from DBUTILS.POOLEDDB import pooleddbimport pymysqlimport timepool = POOLEDDB ( Creator=pymysql, # using the link database module maxconnections=6, # The maximum number of connections allowed for connection pooling, 0 and none means no limit to the number of connections mincached=2, # Initialization, at least the idle created in the link pool Links, 0 means not to create maxcached=5, # Links pool up to idle links, 0 and none without limiting maxshared=0, # The number of links in the link pool that are shared most, 0 and none means all sharing.    PS: Useless, because Pymysql and mysqldb modules such as threadsafety are 1, all values regardless of set to how much, _maxcached forever is 0, so forever is all links are shared. Blocking=true, # If there are no available connections in the connection pool, wait is blocked. True, wait, False, do not wait and then error maxusage=none, # The number of times a link is reused, None means unrestricted setsession=[], # The list of commands executed before the session starts. such as: ["Set Datestyle to ...", "Set time zone ..."] ping=0, # Ping the MySQL server, check if the service is available. # for example: 0 = None = never, 1 = default = Whenever it is requested, 2 = When a cursor is created, 4 = When a query is executed, 7 = always host= ' 127.0.0.1 ', port=3306, user= ' root ', password= ' 123456 ', database= ' test ', charset= ' UTF8 ') @app. Route ('/login ') def func (): conn = pool.connection () cursor = Conn.cursor () cursor.exeCute (' select * from user ') result = Cursor.fetchall () cursor.close () Conn.close () # is not really closed, but false. conn = Pymysql.connect () conn.close () time.sleep () conn = pool.connection () cursor = conn.cursor () cursor . Execute (' select * from user ') result = Cursor.fetchall () cursor.close () conn.close () return ' login ' if __name_ _ = = ' __main__ ': App.run (debug=true)

At this point Conn.close () will put the link to the connection pool, the next time the thread will be used to connect to the pool to fetch. Look at the source flow, because maxshared is always 0, so

Python Database connection Pool dbutils