Database connection pool

pymsql linked database

import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123456', db='s8day127db')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# cursor.execute("select id,name from users where name=%s and pwd=%s",['lqz','123',])
cursor.execute("select id,name from users where name=%(user)s and pwd=%(pwd)s",{'user':'lqz','pwd':'123'})
obj = cursor.fetchone()
conn.commit()
cursor.close()
conn.close()

print(obj)

Database connection pool version

One way

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. Connection automatically closes when thread terminates

from DBUtils.PersistentDB import PersistentDB
import pymysql
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.
    ping=0,
    # Ping the MySQL server to check whether the service is available.
    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='123456',
    database='test',
    charset='utf8'
)

def func():
    conn = POOL.connection(shareable=False)
    cursor = conn.cursor()
    cursor.execute('select * from user')
    result = cursor.fetchall()
    print(result)
    cursor.close()
    conn.close()
if __name__ == '__main__':

    func()

Mode two

Create a batch of connections to the connection pool for all threads to share

setting.py

from datetime import timedelta
from redis import Redis
import pymysql
from DBUtils.PooledDB import PooledDB, SharedDBConnection

class Config(object):
    DEBUG = True
    SECRET_KEY = "umsuldfsdflskjdf"
    PERMANENT_SESSION_LIFETIME = timedelta(minutes=20)
    SESSION_REFRESH_EACH_REQUEST= True
    SESSION_TYPE = "redis"
    PYMYSQL_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='123456',
        database='s8day127db',
        charset='utf8'
    )

class ProductionConfig(Config):
    SESSION_REDIS = Redis(host='192.168.0.94', port='6379')



class DevelopmentConfig(Config):
    SESSION_REDIS = Redis(host='127.0.0.1', port='6379')


class TestingConfig(Config):
    pass

utils/sql.py

import pymysql
from settings import Config
class SQLHelper(object):

    @staticmethod
    def open(cursor):
        POOL = Config.PYMYSQL_POOL
        conn = POOL.connection()
        cursor = conn.cursor(cursor=cursor)
        return conn,cursor

    @staticmethod
    def close(conn,cursor):
        conn.commit()
        cursor.close()
        conn.close()

    @classmethod
    def fetch_one(cls,sql,args,cursor =pymysql.cursors.DictCursor):
        conn,cursor = cls.open(cursor)
        cursor.execute(sql, args)
        obj = cursor.fetchone()
        cls.close(conn,cursor)
        return obj

    @classmethod
    def fetch_all(cls,sql, args,cursor =pymysql.cursors.DictCursor):
        conn, cursor = cls.open(cursor)
        cursor.execute(sql, args)
        obj = cursor.fetchall()
        cls.close(conn, cursor)
        return obj

Use:

obj = SQLHelper.fetch_one("select id,name from users where name=%(user)s and pwd=%(pwd)s", form.data)