Can you count the active/open sessions?

[dndc...@gmail.com]

I'd like to run some tests against my app to make sure I'm properly closing all of the sessions that I open. Is there a way to get the number of open database sessions with SQLAlchemy? Is this a strange request that hints I may be taking the wrong approach?

Currently I'm using scoped sessions with SQLAlchemy 0.8 like so:

    self.db = create_engine(connection_string)
    self.Session = scoped_session(sessionmaker(bind=self.db))

    # then a bunch of calls like
    session = self.Session()
    # ...execute queries
    self.Session.remove()


I tried inspecting the engine and engine.pool objects, but neither of them seem to have anything that stores or returns a session count. I also read about event listening[1], but it doesn't look like the SessionMaker class has events to hook into for opening or closing sessions.

I realize that scoped sessions return a thread-local object, so just incrementing and decrementing a counter for each session call won't work. But we could track the session objects in a dictionary to avoid double-counting.

Here's what I came up with:

    open_sessions = {}

    def open_session(self):
        session = self.Session()
        self.open_sessions[session] = True
        return session

    def close_session(self):
        session = self.Session()
        self.Session.remove()
        if session in self.open_sessions:
            del self.open_sessions[session]


Is this the right approach?


---
[1] http://docs.sqlalchemy.org/en/rel_0_8/core/event.html

[Michael Bayer]

well ideally your app would be constructed such that the Session lifecycle is controlled by a block that uses something like try:/finally: so that there's no doubt about things.  but if things aren't organized that way, i think what you're really concerned about is connections, not as much sessions, though you can use events to track both.   I'd track checked out connections using pool events: http://docs.sqlalchemy.org/en/rel_0_8/core/events.html#sqlalchemy.events.PoolEvents.checkout, http://docs.sqlalchemy.org/en/rel_0_8/core/events.html#sqlalchemy.events.PoolEvents.checkin - just have a counter, increment it on checkout, decrement it on checkin.    For sessions, you can track the start/end of a session's transaction scope: http://docs.sqlalchemy.org/en/rel_0_8/orm/events.html#sqlalchemy.orm.events.SessionEvents.after_transaction_create http://docs.sqlalchemy.org/en/rel_0_8/orm/events.html#sqlalchemy.orm.events.SessionEvents.after_transaction_end - there will be some nesting here per session, but again if you increment on create, decrement on end, a count of zero will indicate "everything is closed".   

---
[1] http://docs.sqlalchemy.org/en/rel_0_8/core/event.html

--
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+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.

[Jonathan Vanasco]

in general , It's worth reading these 2 sections of the docs if you haven't already :

http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#session-frequently-asked-questions

http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#using-thread-local-scope-with-web-applications

[culix]

Hi Michael, thanks for the reply and the links. You're right - counting connections makes much more sense.

In my tests with and without connection tracking my app seems to perform at the same speed. This is good! Is there any significant performance overheard to event listening? Or perhaps it depends on the event.

From the API it looks like there is no un-listen command or equivalent. I guess this means you never have to unregister your listener command?

[Michael Bayer]

On Sep 2, 2013, at 12:46 PM, culix <dndc...@gmail.com> wrote:

> Hi Michael, thanks for the reply and the links. You're right - counting connections makes much more sense.
>
> In my tests with and without connection tracking my app seems to perform at the same speed. This is good! Is there any significant performance overheard to event listening? Or perhaps it depends on the event.

event listening is generally very low overhead.

>
> From the API it looks like there is no un-listen command or equivalent.

event.remove() is implemented for 0.9.

> I guess this means you never have to unregister your listener command?

I don't think it's a common use case as event listeners are generally part of how an application configures itself to listen to fixed things like engines, connection pools and mappers.