数据库连接池，主要用于多线程，为了防止多线程同时对数据库进行操作出现混乱。创建数据库线程池,维护n个数据库连接供程序使用

    1. import threading
    3. import pymysql
    4. from DBUtils.PooledDB import PooledDB
    7. class SqlHelper(object):
    8.     def __init__(self):
    9.         self.pool = PooledDB(
    10.             # 数据库驱动模块
    11.             creator=pymysql,
    12.             # 线程池最大连接数
    13.             maxconnections=6,
    14.             # 初始化连接池时创建的连接数
    15.             mincached=2,
    16.             # 池中空闲连接的最大数量
    17.             maxcached=5,
    18.             # 池中共享连接的最大数量
    19.             maxshared=3,
    20.             #默认False，即达到最大连接数时，再取新连接将会报错，True，达到最大连接数时，新连接阻塞，等待连接数减少再连接
    21.             blocking=True,
    22.             # 连接的最大使用次数
    23.             maxusage=None,
    24.             # 确定何时使用ping()检查连接。默认1，即当连接被取走，做一次ping操作。0是从不ping，1是默认，2是当该连接创建游标时ping，4是执行sql语句时ping，7是总是ping
    25.             ping=0,
    26.             host='127.0.0.1',
    27.             port=3306,
    28.             user='blogadmin',
    29.             password='blogpwd',
    30.             database='flask_blog',
    31.             charset='utf8',
    32.         )
    34.         self.local = threading.local()
    36.     # 打开连接
    37.     def open(self):
    38.         conn = self.pool.connection()
    39.         cursor = conn.cursor()
    40.         return conn, cursor
    42.     # 关闭连接
    43.     def close(self, cursor, conn):
    44.         cursor.close()
    45.         conn.close()
    48.     # 查询全部
    49.     def fetchall(self, sql, *args):
    50.         conn, cursor = self.open()
    51.         cursor.execute(sql, *args)
    52.         result = cursor.fetchall()
    53.         self.close(cursor, conn)
    54.         return result
    56.     # 查询一条
    57.     def fetchone(self, sql, *args):
    58.         conn, cursor = self.open()
    59.         cursor.execute(sql, *args)
    60.         result = cursor.fetchone()
    61.         self.close(cursor, conn)
    62.         return result
    64.     # 插入一条
    65.     def insert(self, sql, *args):
    66.         conn, cursor = self.open()
    67.         try:
    68.             cursor.execute(sql, *args)
    69.             # 食物提交
    70.             conn.commit()
    71.             self.close(cursor, conn)
    72.             return True
    73.         except:
    74.             # 事务回滚
    75.             conn.rollback()
    76.             return False
    78.     def __enter__(self):
    79.         conn, cursor = self.open()
    80.         rv = getattr(self.local,'stack',None)
    81.         if not rv:
    82.             self.local.stack = [(conn, cursor)]
    83.         else:
    84.             rv.append((conn, cursor))
    85.             self.local.stack = rv
    86.         return cursor
    88.     def __exit__(self):
    89.         rv = getattr(self.local,'stack',None)
    90.         if not rv:
    91.             del self.local.stack
    92.             return
    93.         else:
    94.             conn, cursor = self.local.stack.pop()
    95.             self.close(conn, cursor)
    97.     def __new__(cls, *args, **kwargs):
    98.         if not hasattr(SqlHelper, '_instance'):
    99.             SqlHelper._instance = super().__new__(cls)
    100.         return SqlHelper._instance
    103. db = SqlHelper()
    106. # with db as c1:
    107. #     c1.execute('select sleep(1)')