数据库连接池

数据库连接池

# @author： zhc
# @Time: 2023/5/5
# @FileName: 数据库连接池
# pip install PyMySQL , pip install DBUtils==1.3
import pymysql
from DBUtils.PooledDB import PooledDB

POOL = PooledDB(
    creator=pymysql,  # 使用链接数据库的模板
    maxconnections=6,
    mincached=2,
    # maxcached=5,
    # maxshared=3,
    blocking=True,
    # maxusage=None,
    # setsession=[],
    # ping=0,
    host='127.0.0.1',
    port=3306,
    user='root',
    password='p@ss0rd',
    # database='pooldb',
    database='cov',
    charset='utf8'
)

# 去连接池中获取一个连接
conn = POOL.connection()

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

cursor.close()

# 讲连接放回到连接池
conn.close()

print(result)

多线程测试

# @author： zhc
# @Time: 2023/5/5
# @FileName: 数据库连接池
# pip3 install PyMySQL , pip install DBUtils==1.3
import pymysql
from threading import Thread
from DBUtils.PooledDB import PooledDB

POOL = PooledDB(
    creator=pymysql,  # 使用链接数据库的模板
    maxconnections=6,
    mincached=2,
    # maxcached=5,
    # maxshared=3,
    blocking=True,
    # maxusage=None,
    # setsession=[],
    # ping=0,
    host='127.0.0.1',
    port=3306,
    user='root',
    password='p@ss0rd',
    # database='pooldb',
    database='cov',
    charset='utf8'
)




def task(num):
    # 去连接池中获取一个连接
    conn = POOL.connection()

    cursor = conn.cursor()
    # cursor.execute('select * from drug')
    cursor.execute('select sleep(3)')
    result = cursor.fetchall()

    cursor.close()

    # 讲连接放回到连接池
    conn.close()
    print(num, "-------------->", result)



for i in range(57):
    t = Thread(target=task, args=(i,))
    t.start()

函数实现sqlhelper

# @author： zhc
# @Time: 2023/5/5
# @FileName: sqlhelper

import pymysql
from DBUtils.PooledDB import PooledDB

POOL = PooledDB(
    creator=pymysql,  # 使用链接数据库的模板
    maxconnections=6,
    mincached=2,
    # maxcached=5,
    # maxshared=3,
    blocking=True,
    # maxusage=None,
    # setsession=[],
    # ping=0,
    host='127.0.0.1',
    port=3306,
    user='root',
    password='p@ss0rd',
    # database='pooldb',
    database='cov',
    charset='utf8'
)

def fetchall(sql, *args):
    """获取所有数据"""
    # 去连接池中获取一个连接
    conn = POOL.connection()
    cursor = conn.cursor()
    cursor.execute(sql, args)
    result = cursor.fetchall()
    cursor.close()
    conn.close()

    return result

def fetchone(sql, *args):
    """获取单条数据"""
    # 去连接池中获取一个连接
    conn = POOL.connection()
    cursor = conn.cursor()
    cursor.execute(sql, args)
    result = cursor.fetchone()
    cursor.close()
    conn.close()

    return result

单例模式

# @author： zhc
# @Time: 2023/5/5
# @FileName: sqlhelper2

import pymysql
from DBUtils.PooledDB import PooledDB

class SqlHelper:
    def __init__(self):
        self.pool = PooledDB(
                    creator=pymysql,  # 使用链接数据库的模板
                    maxconnections=6,
                    mincached=2,
                    blocking=True,
                    host='127.0.0.1',
                    port=3306,
                    user='root',
                    password='p@ss0rd',
                    # database='pooldb',
                    database='cov',
                    charset='utf8'
                )


    def __open(self):
        conn = self.pool.connection()
        cursor = conn.cursor()
        return conn, cursor

    def __close(self, cursor, conn):
        cursor.close()
        conn.close()


    def fetchall(self, sql, *args):
        """获取所有数据"""
        # 去连接池中获取一个连接
        conn, cursor = self.__open()
        cursor.execute(sql, args)
        result = cursor.fetchall()
        self.__close(cursor, conn)

        return result

    def fetchone(self, sql, *args):
        """获取单条数据"""
        # 去连接池中获取一个连接
        conn, cursor = self.__open()
        cursor.execute(sql, args)
        result = cursor.fetchone()
        self.__close(cursor, conn)

        return result


db = SqlHelper()