python sqlalchemy 连接池_深入研究sqlalchemy连接池

简介：

相对于最新的MySQL5.6，MariaDB在性能、功能、管理、NoSQL扩展方面包含了更丰富的特性。比如微秒的支持、线程池、子查询优化、组提交、进度报告等。

本文就主要探索MariaDB当中连接池的一些特性，配置。来配合我们的sqlalchemy。

一：起因

本来是不会写这个东西的，但是，写好了python--flask程序，使用sqlalchemy+mariadb，部署以后总是出问题，500错误之类的。

使用默认连接参数

engine = create_engine('mysql+mysqlconnector://plan:plan@mysql/plan',)

错误提示是:

sqlalchemy.exc.OperationalError: (mysql.connector.errors.OperationalError) MySQL Connection not available. [SQL: 'SELECT public.id AS public_id, public.public_name AS public_public_name, public.public_email AS public_public_email \nFROM public \nWHERE public.public_name = %(public_name_1)s \n LIMIT %(param_1)s'] [parameters: [{}]] (Background on this error at: http://sqlalche.me/e/e3q8)

http://sqlalche.me/e/e3q8:

OperationalError:

Exception raised for errors that are related to the database’s operation andnot necessarily under the control of the programmer, e.g. an unexpecteddisconnect occurs, the data source name is not found, a transaction could notbe processed, a memory allocation error occurred during processing, etc.

This error is aDBAPI Errorand originates fromthe database driver (DBAPI), not SQLAlchemy itself.

TheOperationalErroris the most common (but not the only) error class usedby drivers in the context of the database connection being dropped, or notbeing able to connect to the database. For tips on how to deal with this, seethe sectionDealing with Disconnects.

意思是没有正确断开和数据库的连接。

二:处理断开

http://docs.sqlalchemy.org/en/latest/core/pooling.html#pool-disconnects

官方给了三种方案来解决这个问题：

1.悲观处理

engine = create_engine("mysql+pymysql://user:pw@host/db", pool_pre_ping=True)

pool_pre_ping=True

表示每次连接从池中检查，如果有错误，监测为断开的状态，连接将被立即回收。

2.自定义悲观的ping

from sqlalchemy importexcfrom sqlalchemy importeventfrom sqlalchemy importselect

some_engine=create_engine(...)

@event.listens_for(some_engine,"engine_connect")defping_connection(connection, branch):ifbranch:#"branch" refers to a sub-connection of a connection,

#we don't want to bother pinging on these.

return

#turn off "close with result". This flag is only used with

#"connectionless" execution, otherwise will be False in any case

save_should_close_with_result =connection.should_close_with_result

connection.should_close_with_result=Falsetry:#run a SELECT 1. use a core select() so that

#the SELECT of a scalar value without a table is

#appropriately formatted for the backend

connection.scalar(select([1]))exceptexc.DBAPIError as err:#catch SQLAlchemy&#