Mike, et al.,
I've got some questions about closing connections. I suspect my framework
may be at fault, but there is potentially a sqlalchemy issue here as well.
See attached script with nested transaction and explicit connection.close().
Things are even more complex because versions have handled this differently
in the past:
- on rel_0_9_1 and ealier, the conn.close() *always *actually emitted a
DBAPI ROLLBACK, but on rel_0_9_2+, the previous call to .begin_nested() now
prevents the DBAPI ROLLBACK call, even though the close() is on the
connection itself. I'm not sure if that was an intended change, but it
seems .close() on a connection should always cause ROLLBACK, no?
- rel_1_3_9 and earlier this code
raises sqlalchemy.exc.ResourceClosedError on the last DBSession.close() as
it invokes the registered 'rollback' event with an already-closed
connection, but on current master (1.4.0b1) there is no exception since a
rollback isn't attempted, leaving the db connection in idle transaction.
On all versions since rel_0_9_1, even after both of the script's finally
clauses (close() statements) but before the program terminates, *the
transaction is still left in transaction in the database, though the
connection's been checked back into the pool.*
As far as whether my code here is badly formed, my question is: is it wrong
to mix session closing and connection closing or should that be fine?
(My actual application is obviously more complex, with zope.sqlalchemy &
transaction and frameworks; I boiled it down to this script for demo
purposes and removed those libraries, making this code look weirder.)
Thanks in advance!
Kent
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/a9d73e26-84c6-4dcf-bb6d-82f541fa1f6d%40googlegroups.com.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy import event
from sqlalchemy import __version__
print "\n## sqlalchemy %s\n" % __version__
def do_some_info_reset(connection):
print("## ## do_some_info_reset on %x ## ##" % id(connection))
# access connection:
connection.info
pg_url = 'postgresql://salespylot:salespylot@localhost:5444/salespylottest'
engine = create_engine(pg_url, echo=True)
event.listen(engine, 'rollback', do_some_info_reset)
conn = engine.connect()
maker = sessionmaker(autoflush=True, autocommit=False)
DBSession = scoped_session(maker)
DBSession.configure(bind=conn)
metadata = MetaData(engine)
# map a system postgres table for demo purposes:
table=Table("pg_language", metadata,
Column("lanname", Unicode(255), primary_key=True))
class Something(object):
pass
mapper(Something, table)
# mimic application layers with some try blocks:
try:
try:
DBSession.begin_nested()
DBSession.query(Something).all()
DBSession.close()
DBSession.query(Something).all()
finally:
# should direct conn.close() do rollback as 0.9.1 an earlier?
conn.close()
finally:
DBSession.close()
