DBUtils is a module of Python used to implement database connection pool, and allows thread safe packaging of non thread safe database interface.

Two modes of connection:

1. Create a connection for each thread. Even if the thread calls the close method, it will not close. It just puts the connection back into the connection pool for its own thread to use again. When the thread terminates, the connection automatically closes.

POOL = PersistentDB(
    creator=pymysql,  # Modules using linked databases
    maxusage=None,  # The maximum number of times a link can be reused, None means unlimited
    setsession=[],  # List of commands executed before starting a session. For example: ["set datestyle to...", "set time zone..."]
    ping=0,
    # ping MySQL Server, check whether 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 it is False, conn.close() is actually ignored for the next use, and the link will be automatically closed when the thread is closed again. If it is True, conn.close() will close the link, and an error will be reported when pool.connection is called again, because the connection has been closed (pool.steady_connection() can get a new link)
    threadlocal=None,  # This thread has exclusive value object, which is used to save the linked object. If the linked object is reset
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    database='pooldb',
    charset='utf8'
)

def func():
    conn = POOL.connection(shareable=False)
    cursor = conn.cursor()
    cursor.execute('select * from tb1')
    result = cursor.fetchall()
    cursor.close()
    conn.close()

func()

1. The connection is established in the connection pool, and all threads share the connection (since the threadsafety values of pymysql, MySQL dB, etc. are 1, the threads in the connection pool in this mode will be shared by all threads).

import time
import pymysql
import threading
from DBUtils.PooledDB import PooledDB, SharedDBConnection
POOL = PooledDB(
    creator=pymysql,  # Modules using linked databases
    maxconnections=6,  # The maximum number of connections allowed in the connection pool, 0 and None indicate unlimited connections
    mincached=2,  # At least idle links created in the link pool during initialization, 0 means not to create
    maxcached=5,  # The most idle links in the link pool, 0 and None are unlimited
    maxshared=3,  # The maximum number of links shared in the link pool. 0 and None represent all shares. PS: useless, because the threadsafety of pymysql, MySQL dB and other modules is 1. No matter how many values are set, the "maxcached" is always 0, so all links are always shared.
    blocking=True,  # If there is no connection available in the connection pool, whether to block waiting. True, wait; False, do not wait and report an error
    maxusage=None,  # The maximum number of times a link can be reused, None means unlimited
    setsession=[],  # List of commands executed before starting a session. For example: ["set datestyle to...", "set time zone..."]
    ping=0,
    # ping MySQL Server, check whether 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='123',
    database='pooldb',
    charset='utf8'
)


def func():
    # Check whether the number of currently running connections is less than the maximum number of links. If not, wait or report raise TooManyConnections exception
    # otherwise
    # The priority is to get the SteadyDBConnection from the link created during initialization.
    # The SteadyDBConnection object is then encapsulated in PooledDedicatedDBConnection and returned.
    # If the link created at the beginning has no link, create a SteadyDBConnection object, then encapsulate it in PooledDedicatedDBConnection and return it.
    # Once the link is closed, the connection returns to the connection pool for subsequent threads to use.
    conn = POOL.connection()

    # print(th, 'link removed', conn1. \
    # print(th, 'there are currently', pool. \

    cursor = conn.cursor()
    cursor.execute('select * from tb1')
    result = cursor.fetchall()
    conn.close()


func()

Let's analyze mode 1. Why does each thread have a unique connection? We need to see what we've done in theading.local()!

#To be analyzed!