在更改 SQLAlchemy Session 从每次请求都创建到共享同一个 Session 之后遇到了如下问题:

StatementError: (sqlalchemy.exc.InvalidRequestError) Can’t reconnect until invalid transaction is rolled back [SQL: ]

或者是

raised unexpected: OperationalError(“(_mysql_exceptions.OperationalError) (2006, ‘MySQL server has gone away’)”,)

错误是 SQLAlchemy 抛出。原因是你从pool拿的connection 没有以 session.commit 或session.rollback 或者session.close 放回pool里。这时connection的transaction 没有完结(rollback or commit)。 而不知什么原因(recyle了,timeout了)你的connection又死掉了,你的sqlalchemy尝试重新连接。由于transaction还没完结,无法重连。

正确用法是确保session 在使用完成后用 session.close, session.commit 或者 session.rollback 把连接还回pool。

SQLAlchemy 数据库连接池使用

sessions 和 connections 不是相同的东西, session 使用连接来操作数据库,一旦任务完成 session 会将 connection 交还给 pool。

在使用create_engine创建引擎时,如果默认不指定连接池设置的话,一般情况下,SQLAlchemy会使用一个QueuePool绑定在新创建的引擎上。并附上合适的连接池参数。

在以默认的方法create_engine时(如下),就会创建一个带连接池的引擎。

engine = create_engine('mysql+mysqldb://root:password@127.0.0.1:3306/dbname')

在这种情况下,当你使用了session后就算显式地调用session.close(),也不能把连接关闭。连接会由QueuePool连接池进行管理并复用。

这种特性在一般情况下并不会有问题,不过当数据库服务器因为一些原因进行了重启的话。最初保持的数据库连接就失效了。随后进行的session.query()等方法就会抛出异常导致程序出错。

如果想禁用SQLAlchemy提供的数据库连接池,只需要在调用create_engine是指定连接池为NullPool,SQLAlchemy就会在执行session.close()后立刻断开数据库连接。当然,如果session对象被析构但是没有被调用session.close(),则数据库连接不会被断开,直到程序终止。

下面的代码就可以避免SQLAlchemy使用连接池:

#!/usr/bin/env python
#-*- coding: utf-8 -*-

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.pool import NullPool

engine = create_engine('mysql+mysqldb://root:password@127.0.0.1:3306/dbname', poolclass=NullPool)
Session = sessionmaker(bind=engine)
session = Session()
usr_obj_list = session.query(UsrObj).all()
print usr_obj_list[0].id
session.close()

create_engine()函数和连接池相关的参数有:

  • -pool_recycle, 默认为-1, 推荐设置为7200, 即如果connection空闲了7200秒, 自动重新获取, 以防止connection被db server关闭.
  • -pool_size=5, 连接数大小,默认为5,正式环境该数值太小,需根据实际情况调大
  • -max_overflow=10, 超出pool_size后可允许的最大连接数,默认为10, 这10个连接在使用过后, 不放在pool中, 而是被真正关闭的.
  • -pool_timeout=30, 获取连接的超时阈值, 默认为30秒

直接只用create_engine 时,就会创建一个带连接池的引擎

engine = create_engine(‘postgresql://postgres@127.0.0.1/dbname’)

当使用session后就显示地调用session.close(),也不能把连接关闭,连接由QueuePool连接池管理并复用

引发问题

当数据库重启,最初保持的连接就会失败,随后进行session.query() 就会失败抛出异常mysql 数据 ,interactive_timeout等参数处理连接的空闲时间超过(配置时间),断开

scoped session

想要线程安全时使用 scoped_session() ,文档解释

the scoped_session() function is provided which produces a thread-managed registry of Session objects. It is commonly used in web applications so that a single global variable can be used to safely represent transactional sessions with sets of objects, localized to a single thread.

using transactional=False is one solution, but a better one is to simply rollback(), commit(), or close() the Session when operations are complete - transactional mode (which is called “autocommit=False” in 0.5) has the advantage that a series of select operations will all
share the same isolated transactional context..this can be more or less important depending on the isolation mode in effect and the kind of application.

DBAPI has no implicit “autocommit” mode so there is always a transaction implicitly in progress when queries are made.

This would be a fairly late answer. This is what happens: While using the session, a sqlalchemy Error is raised (anything which would also throw an error when be used as pure SQL: syntax errors, unique constraints, key collisions etc.).

You would have to find this error, wrap it into a try/except-block and perform a session.rollback().

After this you can reinstate your session.

reference

  • http://stackoverflow.com/questions/21738944/how-to-close-a-sqlalchemy-session
  • https://groups.google.com/forum/#!topic/sqlalchemy/qAMe78TV0M0
  • http://stackoverflow.com/questions/29224472/sqlalchemy-connection-pool-and-sessions
  • http://docs.sqlalchemy.org/en/latest/orm/session_basics.html?highlight=session#basics-of-using-a-session
  • https://mofanim.wordpress.com/2013/01/02/sqlalchemy-mysql-has-gone-away/