基于dbutils的数据库连接池搭建(mysql)

官网

https://webwareforpython.github.io/DBUtils/main.html

安装

pip install dbutils

编辑代码如下

import time
import pymysql
from dbutils.pooled_db import PooledDB

from flask import Flask
import logging

app = Flask(__name__)

MYSQL = {
    "host": "",
    "port": 27546,
    "db": "",
    "username": "",
    "passwd": ""
}


def call_time(func):
    def inner(*args, **kwargs):
        old_time = time.time()
        result = func(*args, **kwargs)
        func_name = str(func).split(' ')[1]
        print('{} use time: {}s'.format(func_name, time.time() - old_time))
        logging.info('{} use time: {}s'.format(func_name, time.time() - old_time))
        return result

    return inner


class MySQLHandler(object):

    def __init__(self):
        """
        :param mincached:连接池中空闲连接的初始数量
        :param maxcached:连接池中空闲连接的最大数量
        :param maxshared:共享连接的最大数量
        :param maxconnections:创建连接池的最大数量
        :param blocking:超过最大连接数量时候的表现，为True等待连接数量下降，为false直接报错处理
        :param maxusage:单个连接的最大重复使用次数
        :param setsession:optional list of SQL commands that may serve to prepare
            the session, e.g. ["set datestyle to ...", "set time zone ..."]
        :param reset:how connections should be reset when returned to the pool
            (False or None to rollback transcations started with begin(),
            True to always issue a rollback for safety's sake)
        :param host:数据库ip地址
        :param port:数据库端口
        :param db:库名
        :param user:用户名
        :param passwd:密码
        :param charset:字符编码
        """
        self.pool = PooledDB(
            creator=pymysql,
            maxconnections=20,
            blocking=True,
            host=MYSQL['host'],
            port=MYSQL['port'],
            user=MYSQL['username'],
            password=MYSQL['passwd'],
            database=MYSQL['db'],
            charset='utf8'
        )

    def get_conn(self):
        conn = self.pool.connection()
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        return conn, cursor

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

    def fetch_one(self, sql, args=None):
        conn, cursor = self.get_conn()
        cursor.execute(sql, args)
        res = cursor.fetchone()
        self.close_conn(conn, cursor)
        return res

    def fetch_all(self, sql, args=None):
        conn, cursor = self.get_conn()
        cursor.execute(sql, args)
        res = cursor.fetchall()
        self.close_conn(conn, cursor)
        return res

    def insert_one(self, sql, args=None):
        conn, cursor = self.get_conn()
        res = cursor.execute(sql, args)
        conn.commit()
        self.close_conn(conn, cursor)
        return res

    def insert_batch(self, sql, args=None):
        conn, cursor = self.get_conn()
        res = cursor.executemany(sql, args)
        conn.commit()
        self.close_conn(conn, cursor)
        return res

    def update(self, sql, args=None):
        conn, cursor = self.get_conn()
        res = cursor.execute(sql, args)
        conn.commit()
        self.close_conn(conn, cursor)
        return res

    def delete(self, sql, args=None):
        conn, cursor = self.get_conn()
        res = cursor.execute(sql, args)
        conn.commit()
        self.close_conn(conn, cursor)
        return res

    # 存储函数调用(无返回值 ,增删改)
    # 参数格式 args=(1, 2, 3, 0)
    def mfunctionVo(self, name, args=None):
        conn, cursor = self.get_conn()
        cursor.callproc(name, args)
        conn.commit()
        self.close_conn(conn, cursor)

    # 存储函数调用(有返回值,查)
    # 参数格式 args=(1, 2, 3, 0)
    def mfunctionRe(self, name, args=None):
        conn, cursor = self.get_conn()
        cursor.callproc(name, args)
        conn.commit()
        res = cursor.fetchall()
        self.close_conn(conn, cursor)
        return res


mh = MySQLHandler()


@app.route("/", methods=["GET"])
@call_time
def index():
    sql_str = f"SELECT * FROM speaker WHERE phone='19037138680'"
    ret = mh.fetch_one(sql_str)
    time.sleep(1)
    print(ret)
    logging.info(ret)
    return ret


if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5000, debug=False)

操作示例

mysql_handler = MySQLHandler()
table_name = 'table_name'
sql_str = f'insert into {table_name} (email, password, update_time) values (%s, %s,now())'
args = (email, password)
mysql_handler.insert_one(sql_str, args)