python pymysql 连接池

采用连接池的方式来操作DB

#-*- coding:utf-8 -*-
#!/usr/bin/python3

import pymysql
import configUtil
from DBUtils.PooledDB import PooledDB


class MysqlUtil(object):

    # 连接池对象
    __pool = None

    def __init__(self, config):
        # 数据库构造函数，从连接池中取出连接，并生成操作游标
        self.pool = MysqlUtil.__get_conn(config)

    @staticmethod
    def __get_conn(config):
        """
        @summary: 静态方法，从连接池中取出连接
        @return MySQLdb.connection
        """
        host = configUtil.read_config(config, "datasource_url", "mysqlConfig")
        username = configUtil.read_config(config, "datasource_username", "mysqlConfig")
        db_pwd = configUtil.read_config(config, "datasource_password", "mysqlConfig")
        db_database = configUtil.read_config(config, "datasource_database", "mysqlConfig")
        if MysqlUtil.__pool is None:
            __pool = PooledDB(pymysql, mincached=1, maxcached=10, maxconnections=10,
                              host=host, port=3306, user=username, passwd=db_pwd,
                              db=db_database, use_unicode=False, blocking=False, charset="utf8")
        return __pool

    def get_all(self, sql):
        """
        @summary: 执行查询，并取出所有结果集
        @param sql:查询ＳＱＬ，如果有查询条件，请只指定条件列表，并将条件值使用参数[param]传递进来
        @param param: 可选参数，条件列表值（元组/列表）
        @return: result list(字典对象)/boolean 查询到的结果集
        """
        try:
            con = self.pool.connection()
            cur = con.cursor()
            count = cur.execute(sql)
            if count > 0:
                result = cur.fetchall()
            else:
                result = False
            return result
        except Exception as e:
            print('SQL执行有误,原因:', e)
        finally:
            cur.close()
            con.close()

    def update(self, sql):
        try:
            con = self.pool.connection()
            cur = con.cursor()
            cur.execute(sql)
            con.commit()
        except Exception as e:
            con.rollback()  # 事务回滚
            print('SQL执行有误,原因:', e)
        finally:
            cur.close()
            con.close()