最近在测试业务的时候，出现长时间并发线程跑sql的时候建立链接耗时的问题，于是搜罗了一下python下是否有链接池的功能，于是乎找到dbutils这个模块，官方路径是在这里 https://webwareforpython.github.io/DBUtils/ 基础使用相对不麻烦，使用pymysql作为连接器进行演示验证
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
#coding: utf-8 import pymysql from dbutils.pooled_db import PooledDB import threading from concurrent.futures import ThreadPoolExecutor pool = PooledDB( creator=pymysql, maxconnections=16, # 可同时创建的最大连接数，如果并发很快这里可能会出现超过最大链接数的报错 mincached=4, maxcached=12, maxshared=128, blocking=False, maxusage=None, setsession=["set autocommit=0"], #用于设置session的前导语句 ping=1, reset=True, # mysql登陆参数 host="192.30.2.19", port=3306, user="test1234", passwd="test1234", database="mysql", charset="utf8mb4", cursorclass=pymysql.cursors.DictCursor, # 这里设置返回cursor类型, DictCursor会返回字典类型，带有字段名 ) def run_test(num): tid = threading.currentThread().ident conn = pool.connection() cursor = conn.cursor() cursor.execute("begin") cursor.execute(f"insert into db1.t1(id,tid) values({num}, '{tid}')") cursor.execute(f"select {num} as num, '{tid}' as tid from dual") print(cursor.fetchall()) cursor.execute("commit") conn.close() for i in range(30): # 每次最多创建16个线程 threadpool = ThreadPoolExecutor(16) for j in range(16): threadpool.submit(run_test, *(i*16 + j,)) threadpool.shutdown(wait=True) |
  […]