python3使用DBUtils以连接池方式管理第三方数据库操作模块（clickhouse连接池）

就我本人目前使用到的数据库，支持DBUtils管理连接池的有

• DBUtils+pymysql
• DBUtils+clickhouse_driver

DBUtils就不用过多介绍了，套用官方文档的说法，是一套 Python 模块，允许以安全有效的方式在线程化Python应用程序和数据库之间进行连接。
以前一直用DBUtils+pymysql来管理mysql连接池，直到后来因为用到clickhouse，通过“clickhouse 连接池”关键字在网上找不到实现的方法，于是尝试研究DBUtils连接池实现方法来写一个clickhouse连接池管理模块。无意中在DBUtils官方文档发现DBUtils支持所有符合DB-API 2 的数据库适配器，刚好clickhouse_driver的官方文档也提到了DB-API 2。经过了解，原来DB-API 2是python数据库API规范，也就是说，只要符合DB-API 2，都能用DBUtils管理连接池。

DBUtils+clickhouse 连接池验证,和DBUtils+pymysql的使用基本一样，至于使用PersistentDB还是PooledDB就根据需求来定了。

# -*- coding: utf-8 -*-
import os
import time
import random
from threading import Thread
from clickhouse_driver import dbapi
from dbutils.persistent_db import PersistentDB

clickhouse_config = {
    "host": "192.168.136.145",
    "port": 9000,
    "user": "default",
    "password": "",
    "database": "xxxxx",
}
pool = PersistentDB(creator=dbapi, **clickhouse_config)


def example(pool):
    # 获取连接和游标
    conn = pool.connection()
    cur = conn.cursor()
    while True:
        try:
            # 执行sql语句
            sql = f"show databases"
            cur.execute(sql)
            conn.commit()
            result = cur.fetchall()
            print(result)
            os.system("netstat -anlpt | grep 9000 | grep ESTABLISHED")
            # 关闭游标和连接,关闭了的话要重新获取,不关闭的话，DBUtils对于失活的conn会自动重连
            # cur.close()
            # conn.close()
        except Exception as e:
            print(e)
        time.sleep(random.randint(1, 2))


tasks = []

for i in range(3):
    tasks.append(Thread(target=example, args=(pool,)))

for task in tasks:
    task.start()

for task in tasks:
    task.join()

运行中重启了clickhouse，可以看到报错之后的自动重连

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/opt/myproject/test_utils/env/lib/python3.6/site-packages/clickhouse_driver/connection.py", line 260, in connect
    return self._init_connection(host, port)
  File "/opt/myproject/test_utils/env/lib/python3.6/site-packages/clickhouse_driver/connection.py", line 226, in _init_connection
    self.socket = self._create_socket(host, port)
  File "/opt/myproject/test_utils/env/lib/python3.6/site-packages/clickhouse_driver/connection.py", line 221, in _create_socket
    raise err
  File "/opt/myproject/test_utils/env/lib/python3.6/site-packages/clickhouse_driver/connection.py", line 212, in _create_socket
    sock.connect(sa)
ConnectionRefusedError: [Errno 111] Connection refused
Code: 210. Connection refused (192.168.136.145:9000)
[('_temporary_and_external_tables',), ('default',), ('xxxxx',), ('system',)]
tcp        0      0 192.168.136.145:53778   192.168.136.145:9000    ESTABLISHED 10409/python3.6     
[('_temporary_and_external_tables',), ('default',), ('xxxxx',), ('system',)]
tcp        0      0 192.168.136.145:53778   192.168.136.145:9000    ESTABLISHED 10409/python3.6     
tcp        0      0 192.168.136.145:53780   192.168.136.145:9000    ESTABLISHED 10409/python3.6     
[('_temporary_and_external_tables',), ('default',), ('xxxxx',), ('system',)]
tcp        0      0 192.168.136.145:53778   192.168.136.145:9000    ESTABLISHED 10409/python3.6     
tcp        0      0 192.168.136.145:53780   192.168.136.145:9000    ESTABLISHED 10409/python3.6     
tcp        0      0 192.168.136.145:53782   192.168.136.145:9000    ESTABLISHED 10409/python3.6     
[('_temporary_and_external_tables',), ('default',), ('xxxxx',), ('system',)]
tcp        0      0 192.168.136.145:53778   192.168.136.145:9000    ESTABLISHED 10409/python3.6     
tcp        0      0 192.168.136.145:53780   192.168.136.145:9000    ESTABLISHED 10409/python3.6     
tcp        0      0 192.168.136.145:53782   192.168.136.145:9000    ESTABLISHED 10409/python3.6 

[参考]
[DBUtils 文档] https://webwareforpython.github.io/DBUtils/main.html
[DB-API 2 文档] https://www.python.org/dev/peps/pep-0249/
[clickhouse-driver 文档] https://clickhouse-driver.readthedocs.io/en/latest/quickstart.html