使用sqlalchemy包的时候需要考虑连接池的问题。可以参见文章:https://blog.youkuaiyun.com/Yaokai_AssultMaster/article/details/80958052
该博文描述了《SQLAlchemy与数据库连接的QueuePool问题详解》相关问题,本博文主要讨论关于连接池中的各中状态、连接池的释放等相关细节。
注:本文不讨论关于连接池中的事务问题。
1、相关函数:create_engine:
In [130]: oracle_source=('username','passwd','network_name','GB18030')
In [131]: source_engin=create_engine('oracle+cx_oracle://{0[0]}:{0[1]}@{0[2]}'.format(oracle_source),connect_args={'enc
...: oding':oracle_source[3]},echo=True)
...:
该函数使用默认的参数,默认参数中:pool_size=5 表示计划创建大小为5的一个连接池,max_overflow=10 表示可以在连接池中最多额外创建10个连接,pool_timeout=30 表示连接超时时间为30秒 也就是说一个连接池中最多可以创建15个连接
2、显式指定连接并释放:
In [132]: source_engin.pool.status()
Out[132]: 'Pool size: 5 Connections in pool: 0 Current Overflow: -5 Current Checked out connections: 0'
In [133]: b=source_engin.execute('select 1 from dual')
2019-01-11 15:25:57,330 INFO sqlalchemy.engine.base.Engine SELECT USER FROM DUAL
INFO:sqlalchemy.engine.base.Engine:SELECT USER FROM DUAL
2019-01-11 15:25:57,330 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2019-01-11 15:25:57,330 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60 CHAR)) AS anon_1 FROM DUAL
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS VARCHAR(60 CHAR)) AS anon_1 FROM DUAL
2019-01-11 15:25:57,330 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2019-01-11 15:25:57,330 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS NVARCHAR2(60)) AS anon_1 FROM DUAL
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS NVARCHAR2(60)) AS anon_1 FROM DUAL
2019-01-11 15:25:57,330 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2019-01-11 15:25:57,330 INFO sqlalchemy.engine.base.Engine select value from nls_session_parameters where parameter = 'NLS_NUMERIC_CHARACTERS'
INFO:sqlalchemy.engine.base.Engine:select value from nls_session_parameters where parameter = 'NLS_NUMERIC_CHARACTERS'
2019-01-11 15:25:57,330 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2019-01-11 15:25:57,330 INFO sqlalchemy.engine.base.Engine select 1 from dual
INFO:sqlalchemy.engine.base.Engine:select 1 from dual
2019-01-11 15:25:57,330 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
In [134]: type(b)
Out[134]: sqlalchemy.engine.result.ResultProxy
In [135]: source_engin.pool.status()
Out[135]: 'Pool size: 5 Connections in pool: 0 Current Overflow: -4 Current Checked out connections: 1'
In [136]: b.close()
In [137]: source_engin.pool.status()
Out[137]: 'Pool size: 5 Connections in pool: 1 Current Overflow: -4 Current Checked out connections: 0'
如上图所示:
创建engine的时候,设置了连接池的初始大小为5;
当前overflow=-5,当overflow=15的时候将不在进行创建连接;
Connections in pool: 0 表示连接池中已经连接的数量为0,此时在数据库中看不到相关的连接,也就是说数据库中看不到连接信息:
Checked out connections: 0 表示从连接池中取出了连接,但是没有释放的数量;因此在执行了显式地释放连接后,这个值等于0
当调用execute后,数据库中可以查到了连接信息,而且Connections in pool变成了1,Current Overflow变成了-4:以后的连接操作就可以使用Connections in pool中的连接了。
3、利用上下文来隐式地释放连接:
In [138]: with source_engin.connect() as f:
...: print('yes')
...: time.sleep(3)
...:
yes
In [139]: source_engin.pool.status()
Out[139]: 'Pool size: 5 Connections in pool: 1 Current Overflow: -4 Current Checked out connections: 0'
如图可以看出,使用上下文管理后, Checked out connections中的数量并没有增加,说明已经关闭了该查询。
4、大量使用连接不释放的情形:
In [140]: [source_engin.execute('select * from hs_user.sysarg') for y in range(17)]
2019-01-11 15:30:00,651 INFO sqlalchemy.engine.base.Engine select * from hs_user.sysarg
INFO:sqlalchemy.engine.base.Engine:select * from hs_user.sysarg
2019-01-11 15:30:00,651 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
2019-01-11 15:30:00,760 INFO sqlalchemy.engine.base.Engine select * from hs_user.sysarg
INFO:sqlalchemy.engine.base.Engine:select * from hs_user.sysarg
2019-01-11 15:30:00,760 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
...部分省略
d:\python36\lib\site-packages\sqlalchemy\pool.py in _do_get(self)
1187 "QueuePool limit of size %d overflow %d reached, "
1188 "connection timed out, timeout %d" %
-> 1189 (self.size(), self.overflow(), self._timeout), code="3o7r")
1190
1191 if self._inc_overflow():
TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30 (Background on this error at: http://sqlalche.me/e/3o7r)
当大量的连接在使用并且没有释放的时候会触发异常。此时数据库的连接数为15,如下图所示:
综合来说:
1、create_engine的默认参数中的连接数在一般项目中是足够使用的,可以适当调整超时时间。
2、使用sqlalchemy的时候,可以通过显式释放或者上下文管理将操作使用的连接归还给连接池。
3、大量的、长时间的连接操作同时并发处理的时候,才需要调整连接数。
4、需要考虑数据库本身会自动清理长时间连接不活动的问题。