About a year ago you helped me ensure my scoped session gets the same connection to the database, which might be important.
I found out using "bind=connection" doesn't guarantee the session_maker uses that connection if something went wrong with the session and ScopedSession.remove() was called. Is there a way to guarantee this? See attached script that fails on version 1.0.12 Is this the intended behavior when sessionmaker has a specific connection as bind? On Mon, Mar 23, 2015 at 12:40 PM, Michael Bayer <mike...@zzzcomputing.com> wrote: > > > Kent <jkentbo...@gmail.com> wrote: > > > In cases where we interact with the database session (a particular > Connection) to, for example, obtain an application lock which is checked > out from database for the lifetime of the database session (not just the > duration of a transaction), it is important that I guarantee future scoped > session instances get the same connection (and, for example, the > pool_recycle or something else has thrown out that connection and grabbed a > new one). > > > > Please advise me where I can best implement this guarantee. A Session > subclass's connection() method seems it might be the appropriate place, but > let me know if there is a better recipe. > > you’d want to create that Session associated with the Connection directly: > > my_session = scoped_session(bind=some_connection) > > then of course make sure you .close() it and .close() the connection at > the end of the use of that session. > > > > > > > The Session.connection() method's docs say: > > "If this Session is configured with autocommit=False, either the > Connection corresponding to the current transaction is returned, or if no > transaction is in progress, a new one is begun and the Connection returned > (note that no transactional state is established with the DBAPI until the > first SQL statement is emitted)." > > > > If the session is one registered in my scoped registry, I'd like to > always return the same connection to guarantee I am using the one with the > database-side checked-out application lock. > > > > What's my best option? > > > > Thanks much! > > > > -- > > 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 post to this group, send email to sqlalchemy@googlegroups.com. > > Visit this group at http://groups.google.com/group/sqlalchemy. > > For more options, visit https://groups.google.com/d/optout. > > -- > You received this message because you are subscribed to a topic in the > Google Groups "sqlalchemy" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/sqlalchemy/WcdRsvBTozk/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. > -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.exc import OperationalError eng = create_engine('postgresql://salespylot:salespylot@localhost:5444/sqla', echo=True) conn=eng.connect() # bind to specific connection Session = scoped_session(sessionmaker(bind=conn)) pid = conn.execute("select pg_backend_pid()").scalar() raw_conn_addr = id(Session.connection().connection.connection) metadata = MetaData(eng) rocks_table = Table("rocks", metadata, Column("id", Integer, primary_key=True), ) class Rock(object): pass mapper(Rock, rocks_table) metadata.create_all() Session.query(Rock).all() # See if normally get same connection Session.remove() Session.query(Rock).all() # all is good: we got original connection again: assert pid == Session.connection().execute("select pg_backend_pid()").scalar() assert raw_conn_addr == id(Session.connection().connection.connection) # something drastic happens to conn aux_conn=eng.connect() aux_conn.execute(text("select pg_terminate_backend(:pid)"), pid=pid) try: Session.query(Rock).all() except OperationalError as e: print e # Error, framework automatically may issue this: Session.remove() Session.query(Rock).all() # New connection has been created, didn't anticipate this... newpid = Session.connection().execute("select pg_backend_pid()").scalar() new_addr = id(Session.connection().connection.connection) print "%d != %d; %d != %d" % (pid, newpid, raw_conn_addr, new_addr) assert pid == newpid or raw_conn_addr == new_addr