python多线程使用MySQL导致中断连接重写mysql操作类

最近有一个需求，有一张表里面记录视频上传状态,因为视频上传这个时间会非常长,所以这个时间就空闲下来了，这时候考虑到python多线程来解决，一开始通过pymysql直接连接导致了很多的报错，数据库中断连接，最后采用dbutils mysql线程池解决了这个问题
话不多说直接上代码

import pymysql
from dbutils.pooled_db import PooledDB
from dbutils.persistent_db import PersistentDB
class MysqlHelper(object):
    conn = None
    def __init__(self, host, username, password, db=None,charset='utf8',port=3306):
        self.__pool = PooledDB(creator=pymysql,
                               mincached=10,
                               maxcached=0,
                               maxshared=20,
                               maxconnections=200,
                               maxusage=20,
                               blocking=True,
                               user=username,
                               passwd=password,
                               db=db,
                               host=host,
                               port=port)

    def connect(self):
        conn = self.__pool.connection()
        cursor = conn.cursor()
        # print("连接成功")
        return conn, cursor

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

    def get_one(self, sql, params=()):
        result = None
        title = []
        try:
            conn,cursor=self.connect()
            cursor.execute(sql, params)
            result = cursor.fetchone()
            des = cursor.description
            title = [item[0] for item in des]
            self.close()
        except Exception as e:
            print(e)
        return list(title),result

    def get_all(self, sql, params=()):
        list_data = ()
        title=[]
        try:
            conn,cursor=self.connect()
            cursor.execute(sql, params)
            list_data = cursor.fetchall()
            des = cursor.description
            title = [item[0] for item in des]
            self.close()
        except Exception as e:
            print(e)
        return title,list_data

    def insert(self, sql, params=()):
        return self.__edit(sql, params)

    def update(self, sql, params=()):
        return self.__edit(sql, params)

    def delete(self, sql, params=()):
        return self.__edit(sql, params)

    def __edit(self, sql, params):
        count = 0
        try:
            conn,cursor=self.connect()
            count = cursor.execute(sql, params)
            conn.commit()
            self.close()
        except Exception as e:
            print(e)
        return count
if __name__=='__main__':
    mysql=MysqlHelper(host='192.168.56.104',db='dbatest',username='dbadmin',password='dbadmin',port=3306)
    mysql.connect()
    sql='select * from users limit 10 '
    title,data=mysql.get_all(sql)
    print(title,data)
    l1=[]
    for j in data:
        l1.append([j[0],j])
    print(l1)

参数解释：
creator: either an arbitrary function returning new DB-API 2 connection objects or a DB-API 2 compliant database module 一般python3里面使用pymysql

mincached : 连接池里面初始的空闲链接（默认为0，代表开始时没有空闲连接）

maxcached: 连接池里面最大的空闲连接数量（默认为0/None，代表无限制）

maxshared: 最大的允许共享的连接数（默认为0/None，代表所有连接都不可共享）

maximum number of shared connections allowed (the default value of 0 or None means all connections are dedicated)

共享连接出现于：连接数达到最大连接数，如果允许共享，则使用连接共享

When this maximum number is reached, connections are shared if they have been requested as shareable.

maxconnections: 最大的允许的连接数（默认为0/None，代表多少连接都行）

blocking: 确定当超过最大连接数后的行为，如果为true，则阻塞等待连接减少，默认为报告错误（error）

maxusage: 单个连接最大可以被复用的次数（默认为0/None，代表不限制复用）

当达到最大的被复用次数后，连接自动重置（关闭/重新打开）

setsession: an optional list of SQL commands that may serve to prepare the session, e.g. [“set datestyle to german”, …]

reset: 当连接放回连接池时，是否每次都调用 rollback 以保证事务终止，为 False 或 None 则不额外调用 rollback 方法

how connections should be reset when returned to the pool (False or None to rollback transcations started with begin(), the default value True always issues a rollback for safety’s sake)

failures: an optional exception class or a tuple of exception classes for which the connection failover mechanism shall be applied, if the default (OperationalError, InternalError) is not adequate

ping: an optional flag controlling when connections are checked with the ping() method if such a method is available (0 = None = never, 1 = default = whenever fetched from the pool, 2 = when a cursor is created, 4 = when a query is executed, 7 = always, and all other bit combinations of these values)

The creator function or the connect function of the DB-API 2 compliant database module specified as the creator will receive any additional parameters such as the host, database, user, password etc. You may choose some or all of these parameters in your own creator function, allowing for sophisticated failover and load-balancing mechanisms.
常见错误使用方法1：
def getConn(self):
self.conn = self.__pool.connection()
self.cursor = self.conn.cursor()
此处不应该共享链接，和cursor，会导致报错：
AttributeError: ‘NoneType’ object has no attribute ‘read’
或者：
AttributeError: ‘NoneType’ object has no attribute ‘settimeout‘
完整项目请下载：
https://mp.csdn.net/mp_download/manage/download/UpDetailed

• 技术无止境

•