连接池介绍
官方网站介绍:https://cx-oracle.readthedocs.io/en/latest/user_guide/connection_handling.html
官网上的介绍比较详细,也比较清晰。使用起来非常方便。
连接池代码样例
from contextlib import contextmanager
import cx_Oracle
import os
oracle_config = {
'username': '***',
'password': '***',
'hosts': '***.***.***.***:1521/sid'
}
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'
class ReallyOracleConnectionPool(cx_Oracle.SessionPool):
""" 使用信号量来控制连接池数,是因为测试中使用cx_Oracle.SessionPool出现了部分线程丢失连接的情况 """
def __init__(self, *args, **kwargs):
pool_size = kwargs.get('max', 10)
self._semaphore = Semaphore(pool_size)
super().__init__(*args, **kwargs)
def get_connection(self, *args, **kwargs):
self._semaphore.acquire()
return super().acquire(*args, **kwargs)
def put_connection(self, *args, **kwargs):
super().release(*args, **kwargs)
self._semaphore.release()
# cnxpool = cx_Oracle.SessionPool(oracle_config['username'], oracle_config['password'], oracle_config['hosts'], min=10,
# max=10, increment=0, threaded=True)
cnxpool = ReallyOracleConnectionPool(oracle_config['username'], oracle_config['password'], oracle_config['hosts'],
min=10, max=10, increment=0, threaded=True)
@contextmanager
def get_cursor():
try:
# con = cnxpool.acquire()
con = cnxpool.get_connection()
cursor = con.cursor()
yield cursor
finally:
cursor.close()
# cnxpool.release(con)
cnxpool.put_connection(con)
class PyOracle(object):
"""创建python操作oracle类"""
@staticmethod
def get_all(sql):
with get_cursor() as cursor:
cursor.execute(sql)
return cursor.fetchall()
if __name__ == '__main__':
import time
from concurrent.futures import ThreadPoolExecutor
def t(n):
r = PyOracle.get_all("select * from TABLE")
print(str(n) + str(r))
s = time.time()
with ThreadPoolExecutor(max_workers=15) as pool:
for i in range(5):
pool.submit(t, (i))
# for i in range(20):
# t(i)
print(time.time() - s)
对连接池的一般建议是使用固定大小的池。min和max的值应相同(且增量increment等于零)。不应使空闲会话期满。这会降低吞吐量避免连接风暴。请参阅《防止连接风暴的指南:使用静态池》,其中包含有关池大小调整的详细信息。