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()