python sqlHelper.py

import pymysql
import threading
from dbutils.pooled_db import PooledDB


class SqlHelper(object):

    def __init__(self):
        self.pool = PooledDB(
            creator=pymysql,  # 使用链接数据库的模块
            maxconnections=5,  # 连接池允许的最大连接数，0和None表示不限制连接数
            mincached=1,  # 初始化时，链接池中至少创建的链接，0表示不创建
            blocking=True,  # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
            ping=0,
            # ping MySQL服务端，检查是否服务可用。# 如：0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always
            # host=current_app.config.get("MYSQL_HOST"),
            # port=current_app.config.get("MYSQL_PORT"),
            # user=current_app.config.get("MYSQL_USERNAME"),
            # password=current_app.config.get("MYSQL_PASSWORD"),
            # database=current_app.config.get("MYSQL_DATABASE"),
            # charset='utf8'

            host='你的host',
            port=3306,
            user='用户名',
            password='你的密码',
            database='库名',
            charset='utf8'

        )
        self.local = threading.local()

    def open(self):
        '''
        执行sql语句，返回的数据都是元组。可随意使用添加  cursor=pymysql.cursors.DictCursor 方法将查询结果对象返回未字典
        '''
        conn = self.pool.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        return conn, cursor

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

    def querysql(self, sql):
        """ 获取所有数据 """
        conn, cursor = self.open()
        cursor.execute(sql)
        result = cursor.fetchall()
        self.close(conn, cursor)
        return result

    def fetchall(self, sql, *args):
        """ 获取所有数据 """
        conn, cursor = self.open()
        cursor.execute(sql, args)
        result = cursor.fetchall()
        self.close(conn, cursor)
        return result

    def fetchone(self, sql, *args):
        """ 获取单条数据 """
        conn, cursor = self.open()
        cursor.execute(sql, args)
        result = cursor.fetchone()
        self.close(conn, cursor)
        return result

    def update(self, table, fdata=None, where=None, param=None):
        """
        @summary: 更新数据表记录
        举例 update("表名", {'字段':值,'字段':值},'字段=%s and 字段=%s',(条件值，条件值))
        @param table: 表名
        @param fdata: 更改的字段:值
        @param where: 条件语句 ＳＱＬ格式及条件，使用(%s,%s)
        @param param: where对应的值: 要更新的  值 tuple/list
        @return: count 受影响的行数
        """
        conn, cursor = self.open()
        item = []
        for i in fdata:
            item.append(f"{i}='{fdata[i]}'")
        setData = ", ".join(item)
        sql = f"update {table} set {setData} where {where}"
        try:
            count = cursor.execute(sql, param)
            conn.commit()
            return count
        except Exception as e:
            print(e)
            conn.rollback()
            raise
        finally:
            self.close(conn, cursor)


    def insert(self, table, param=None):
        """
        @summary: 插入数据表记录
        举例 insert("表名", {"字段":值, '字段':值})
        @param table: 表名 值string
        @param param: 要更新的  值 tuple/list
        @return: lastrowid 受影响的行数id
        """
        conn, cursor = self.open()
        fields = []
        values = []
        for i in param:
            fields.append(i)
            values.append(param[i])
        keys = ", ".join(fields)
        qmark = ", ".join(["%s"] * len(values))
        sql = f"insert into {table}(%s) values (%s)" % (keys, qmark)
        try:
            cursor.execute(sql, list(values))
            conn.commit()
            lastrowid = cursor.lastrowid
            return lastrowid
        except Exception as e:
            print(e)
            conn.rollback()
            raise
        finally:
            self.close(conn, cursor)

    def insertMany(self, table, fields=None, fdata=None):
        """
        @summary: 批量插入数据表记录
        举例 insertMany("表名", ("字段", '字段'),([值1,值2],[值1,值2]))
        @param table: 表名 值string
        @param fields: 要插入的记录字段tuple/list
        @param fdata: 要插入的记录数据tuple(tuple)/list[list]
        @return: count 受影响的行数
        """
        conn, cursor = self.open()
        keys = ", ".join(fields)
        qmark = ", ".join(["%s"] * len(fdata))
        sql = f"insert into {table}(%s) values (%s)" % (keys, qmark)
        try:
            count = cursor.executemany(sql, fdata)
            conn.commit()
            return count
        except Exception as e:
            print(e)
            conn.rollback()
            raise
        finally:
            self.close(conn, cursor)


    def delete(self, table, where=None, param=None):
        """
        @summary: 删除数据表记录
        举例 delete("表名", '字段1=%s and 字段2=%s',(值1,值2))
        @param table: 表名
        @param where: 条件语句 ＳＱＬ格式及条件，使用(%s,%s)
        @param param: where对应的值: 要更新的  值 tuple/list
        @return: count 受影响的行数
        """
        conn, cursor = self.open()
        sql = f"delete from {table} where {where}"
        try:
            count = cursor.execute(sql, param)
            conn.commit()
            return count
        except Exception as e:
            print(e)
            conn.rollback()
            raise
        finally:
            self.close(conn, cursor)

    def __enter__(self):
        conn, cursor = self.open()
        rv = getattr(self.local, 'stack', None)
        if not rv:
            self.local.stack = [(conn, cursor), ]
        else:
            rv.append((conn, cursor))
            self.local.stack = rv
        return cursor

    def __exit__(self, exc_type, exc_val, exc_tb):
        rv = getattr(self.local, 'stack', None)
        if not rv:
            # del self.local.stack
            return
        conn, cursor = self.local.stack.pop()
        cursor.close()
        conn.close()


db = SqlHelper()