PyMySQL和eventlet实现数据库连接池

eventlet的db_pool文档地址:http://eventlet.net/doc/modules/db_pool.html

需要先安装pymysql 0.9.3模块和eventlet 0.25.1模块。具体代码如下：

import pymysql
from eventlet.db_pool import RawConnectionPool, ConnectionPool, DatabaseConnector

# 一种简单的数据库连接池
pool = RawConnectionPool(pymysql, min_size=0, max_size=4, max_idle=10, max_age=30, connect_timeout=5,
                         host='masaike.mysql.rds.aliyuncs.com',
                         port=3306,
                         user='mysqlsa',
                         password='123456',
                         db='call_center_robot')
is_dev = False
if is_dev:
    con_pool = ConnectionPool(pymysql,
                              host='masaike.mysql.rds.aliyuncs.com',
                              port=3306,
                              user='call_center_sql',
                              password='123456',
                              db='call_center_robot')
else:
    # 直接创建数据库连接池
    con_pool = ConnectionPool(pymysql,
                              host='masaike.mysql.rds.aliyuncs.com',
                              port=3306,
                              user='mysqlsa',
                              password='123456',
                              db='call_center_robot')


def search_sql(total_sql):
    '''
    单库查询
    连接数据库，执行sql查询
    :param total_sql:
    '''
    # 从连接池拿连接对象
    with con_pool.item() as db:
        try:
            with db.cursor(pymysql.cursors.DictCursor) as cursor:
                # 使⽤execute⽅法执⾏SQL语句
                cursor.execute(total_sql)
                # 获取数据
                data = cursor.fetchall()
                db.commit()
        except:
            data = ''
            # 把连接放回连接池
            con_pool.put(db)
    return data


# 多个主机的数据库信息
db_info = {
    # 连接池名称(原文档这里是数据库host)
    'test': {
        'port': 3306,  # 端口
        'user': 'mysqlsa',  # 用户名
        'host': 'masaike.test.mysql.rds.aliyuncs.com',  # 主机名
        'password': '123456',  # 密码
        'db': 'call_center_robot'  # 数据库名
    },
    'prod': {
        'port': 3306,
        'host': 'masaike.prod.mysql.rds.aliyuncs.com',
        'user': 'call_center_sql',
        'password': '123456',
        'db': 'call_center_robot'
    },
}
# 创建数据库连接池池
db_pool = DatabaseConnector(pymysql, db_info)


def search_sql_pool(total_sql, pool_name='deault', db_name=''):
    '''
    多库查询
    :param total_sql: 被执行的sql
    :param pool_name: 连接池名称
    :param db_name: 数据库名
    :return:
    '''
    # 从数据库连接池池中获取连接池
    conn_pool = db_pool.get(pool_name, db_name)
    with conn_pool.item() as db:
        try:
            with db.cursor(pymysql.cursors.DictCursor) as cursor:
                # 使⽤execute⽅法执⾏SQL语句
                cursor.execute(total_sql)
                # 获取数据
                data = cursor.fetchall()
            # 提交
            db.commit()
        except:
            data = ''
            # 把连接放回连接池
            conn_pool.put(db)
    return data


if __name__ == '__main__':
    data = search_sql('select now()')
    print(data)
    data = search_sql_pool('select now()', 'test', 'call_center_robot')
    print(data)

说明：对于上面代码里的多个主机数据库信息字典有一点说明，在原文档使用host作为字典的key，看一下文档。

但是一般数据库主机的host都很长，作为参数传递不方便。 查看了eventlet.db_pool的DatabaseConnector源码，发现源码有一处问题，也可能是作者故意设置的。

class DatabaseConnector(object):
    """
    This is an object which will maintain a collection of database
    connection pools on a per-host basis.
    """

    def __init__(self, module, credentials,
                 conn_pool=None, *args, **kwargs):
        """constructor
        *module*
            Database module to use.
        *credentials*
            Mapping of hostname to connect arguments (e.g. username and password)
        """
        assert(module)
        self._conn_pool_class = conn_pool
        if self._conn_pool_class is None:
            self._conn_pool_class = ConnectionPool
        self._module = module
        self._args = args
        self._kwargs = kwargs
        # this is a map of hostname to username/password
        self._credentials = credentials
        self._databases = {}

    def credentials_for(self, host):
        if host in self._credentials:
            return self._credentials[host]
        else:
            return self._credentials.get('default', None)

    def get(self, host, dbname):
        """Returns a ConnectionPool to the target host and schema.
        """
        key = (host, dbname)
        if key not in self._databases:
            new_kwargs = self._kwargs.copy()
            new_kwargs['db'] = dbname
            new_kwargs['host'] = host
            new_kwargs.update(self.credentials_for(host))
            dbpool = self._conn_pool_class(
                self._module, *self._args, **new_kwargs)
            self._databases[key] = dbpool

        return self._databases[key]

DatabaseConnector的get方法里，如果key对应的连接池不存在，则会创建一个新的连接池，credentials_for方法会根据host返回数据库信息，最后用credentials_for返回的数据库信息更新当前的new_kwargs。所以如果把db_info字典的key改成连接池名称，把host放到连接池名称对应的字典中，真正的host会把当前new_kwargs里的host(连接池名称)更新了，如果这是这个模块作者故意做的，不得不感叹大佬的编程艺术~~