How to stop a session object from grabbing another database connection?

[Derek Litz]

A session when closed, commited, or rolled back, releases the database connection it acquired from the pool.  The session can grab a new connection later on as needed.  Also, many objects a session can create have a reference to this session, and the session could be used elsewhere.  This leads to the potential of doing database access where one would have thought no database access was going on, even if I'm clearly not re-using the session object anywhere else directly.

ie.  I call session.close() before passing my data to a template engine, but one of the objects was a query object (by mistake), which when iterated over will query the database while rendering the template.

Is there a standard way I could stop the session object from grabbing another connection to the database and throw an exception instead?   If not I'd be open to non-standard ways as well :)

Derek

[Michael Bayer]

You can reset the ".bind" on the Session to None, but this assumes that you aren't using "bound metadata".

As far as non-standard, I've never tried this but if you manually "deactivate" the transaction, should work:

session.transaction._deactivate()

This can certainly be a public API at some point if it works for you.

[Derek Litz]

Do you have a reference I could like at for "bound metadata", not quite sure what is meant by that.

Anyways, I tried out both methods:

session.bind = None, doesn't stop the session from being used at all (it does break get_bind() though, humorously).

session.transaction._deactivate() does "work" in a sense (by itself: see below).  I can't query with the session until I rollback() or close().  The MAJOR drawback of this is the connection is held on to by the session.  The session is normally very good at releasing the connection back to the pool as soon as possible and I'd like to keep this behavior as normal.

Oh, nice, I'll leave above as reference, but I just tried closing the session PRIOR to calling session.transaction._deactivate() and it manages create a session in the state that I want.

for s in sessions:
    s.query(User).first()
    s.close()
    s.transaction._deactivate()

Yes, it'd certainly be nice to have a public API for something like this (I do feel the "close" communicates it works in this fashion, simply because of how close works with a connection ie. it's closed and you can't use it anymore, but changing THAT API may be too much to ask.)

Adding a ".deactivate" method to the session that makes it unusable from that point on sounds like a good idea.

Output from interpretor below:

+------------------+

session.query(User).first()
Out[100]: <ntb.common.orm.user.User at 0x2a11d10>

session.bind = None

session.query(User).first()
Out[102]: <ntb.common.orm.user.User at 0x2a11d10>

session.get_bind()
ERROR: An unexpected error occurred while tokenizing input
The following traceback may be corrupted or invalid
The error message is: ('EOF in multi-line statement', (15, 0))

---------------------------------------------------------------------------
UnboundExecutionError                     Traceback (most recent call last)
/home/dlitz/Dev/ntb/<ipython-input-103-7acbb6e607dc> in <module>()
----> 1 session.get_bind()

/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.pyc in get_bind(self, mapper, clause)
    937             else:
    938                 raise sa_exc.UnboundExecutionError(
--> 939                     "This session is not bound to a single Engine or "
    940                     "Connection, and no context was provided to locate "
    941                     "a binding.")

UnboundExecutionError: This session is not bound to a single Engine or Connection, and no context was provided to locate a binding.

+------------------+

And using session.transaction._deactivate() (by itself)

+------------------+

len(sessions)
Out[9]: 15

for s in sessions:
    s.query(User).first()
    s.transaction._deactivate()

session = session_maker()

session.query(User).first()

---------------------------------------------------------------------------
TimeoutError                              Traceback (most recent call last)
/home/dlitz/Dev/ntb/<ipython-input-13-fbb31c44fc53> in <module>()
----> 1 session.query(User).first()

/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in first(self)
   2105             ret = list(self)[0:1]
   2106         else:
-> 2107             ret = list(self[0:1])
   2108         if len(ret) > 0:
   2109             return ret[0]

/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in __getitem__(self, item)
   1999                 return list(res)[None:None:item.step]
   2000             else:
-> 2001                 return list(res)
   2002         else:
   2003             if item == -1:

/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in __iter__(self)
   2174         if self._autoflush and not self._populate_existing:
   2175             self.session._autoflush()
-> 2176         return self._execute_and_instances(context)
   2177
   2178     def _connection_from_session(self, **kw):

/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in _execute_and_instances(self, querycontext)
   2187                         mapper = self._mapper_zero_or_none(),
   2188                         clause = querycontext.statement,
-> 2189                         close_with_result=True)
   2190
   2191         result = conn.execute(querycontext.statement, self._params)

/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in _connection_from_session(self, **kw)
   2178     def _connection_from_session(self, **kw):
   2179         conn = self.session.connection(
-> 2180                         **kw)
   2181         if self._execution_options:
   2182             conn = conn.execution_options(**self._execution_options)

/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.pyc in connection(self, mapper, clause, bind, close_with_result, **kw)
    727
    728         return self._connection_for_bind(bind,
--> 729                                         close_with_result=close_with_result)
    730
    731     def _connection_for_bind(self, engine, **kwargs):

/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.pyc in _connection_for_bind(self, engine, **kwargs)
    731     def _connection_for_bind(self, engine, **kwargs):
    732         if self.transaction is not None:
--> 733             return self.transaction._connection_for_bind(engine)
    734         else:
    735             return engine.contextual_connect(**kwargs)

/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.pyc in _connection_for_bind(self, bind)
    264                         "given Connection's Engine")
    265             else:
--> 266                 conn = bind.contextual_connect()
    267
    268         if self.session.twophase and self._parent is None:

/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in contextual_connect(self, close_with_result, **kwargs)
   2474
   2475         return self._connection_cls(self,
-> 2476                                     self.pool.connect(),
   2477                                     close_with_result=close_with_result,
   2478                                     **kwargs)

/usr/local/lib/python2.7/site-packages/sqlalchemy/pool.pyc in connect(self)
    222         """
    223         if not self._use_threadlocal:
--> 224             return _ConnectionFairy(self).checkout()
    225
    226         try:

/usr/local/lib/python2.7/site-packages/sqlalchemy/pool.pyc in __init__(self, pool)
    385         self._echo = _echo = pool._should_log_debug()
    386         try:
--> 387             rec = self._connection_record = pool._do_get()
    388             conn = self.connection = self._connection_record.get_connection()
    389             rec.fairy = weakref.ref(

/usr/local/lib/python2.7/site-packages/sqlalchemy/pool.pyc in _do_get(self)
    725                             "QueuePool limit of size %d overflow %d reached, "
    726                             "connection timed out, timeout %d" %
--> 727                             (self.size(), self.overflow(), self._timeout))
    728
    729             if self._overflow_lock is not None:

TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30

+---------------+

And using close() before transaction._deactivate()

+----------------+

for s in sessions:
    s.query(User).first()
    s.close()
    s.transaction._deactivate()
   
len(sessions_2)
Out[49]: 15

for s in sessions_2:
    s.query(User).first()

"YAY! :)"

Out[51]: 'YAY! :)'

+---------------+

Thanks again for the prompt response.

Derek

[Michael Bayer]

On Sep 19, 2012, at 11:09 AM, Derek Litz wrote:

Do you have a reference I could like at for "bound metadata", not quite sure what is meant by that.

its this:

engine = create_engine(...)

metadata.bind = engine

Anyways, I tried out both methods:

session.bind = None, doesn't stop the session from being used at all (it does break get_bind() though, humorously).

session.transaction._deactivate() does "work" in a sense (by itself: see below).  I can't query with the session until I rollback() or close().  The MAJOR drawback of this is the connection is held on to by the session.  The session is normally very good at releasing the connection back to the pool as soon as possible and I'd like to keep this behavior as normal.

Oh, nice, I'll leave above as reference, but I just tried closing the session PRIOR to calling session.transaction._deactivate() and it manages create a session in the state that I want.

right, you'd need to close() the session first.   the new "transaction" is then dormant, then you deactivate, which prevents it from allowing anything else.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/ALrv8F32NH8J.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.