SQLAlchemy pool connection closed exception

Question

Anyone has any idea why whenever a connection in a SQLAlchemy pool is closed (conn.close()), I get this exception:

ERROR:Exception during reset or similar       
Traceback (most recent call last):
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 631, in _finalize_fairy
    fairy._reset(pool)
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 771, in _reset
    pool._dialect.do_rollback(self)
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 83, in do_rollback
    dbapi_connection.rollback()
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 850, in __getattr__
    return getattr(self.connection, key)
AttributeError: 'Connection' object has no attribute 'rollback'

I am using SQLAlchemy==1.0.6 connecting to mysql, This is the part of my code:

self.__engine = create_engine("mysql+mysqldb://%s:%s@%s/%s?charset=utf8&use_unicode=0" %(config['db_user'], config['db_passwd'], config['db_host'], config['db']), pool_recycle=3600)
self.__mypool = pool.QueuePool(self.__getConn, pool_size=config['db_pool_size'])
conn = self.__mypool.connect()
results = conn.execute(statement, values)
conn.close()

See [this](http://stackoverflow.com/questions/20401392/read-frame-with-sqlalchemy-mysql-and-pandas) answer. — doru, Jul 06 '15 at 08:42
The problem is different to the answer you provided but thanks anyway. I have figured out the problem. I may answer my question for other's as reference. — Ken, Jul 07 '15 at 03:00

score 2 · Answer 1 · answered Jul 07 '15 at 03:09

It appears that in the current version of SQLAlchemy (was working in some versions back), the queue pool is already integrated into the create_engine(). It doesn't like the QueuePool() to be created separately. The following changes fixed the problem:

self.__mypool = create_engine("mysql+mysqldb://%s:%s@%s/%s?charset=utf8&use_unicode=0" %(config['db_user'], config['db_passwd'], config['db_host'], config['db']), pool_size=config['db_pool_size'], pool_recycle=3600)
conn = self.__mypool.connect()
results = conn.execute(statement, values)
conn.close()

SQLAlchemy pool connection closed exception

1 Answers1