This is a problem that would need to be solved mostly on the cx_oracle side, then using standard SQLAlchemy APIs to implement.
Some googling didn't turn up a definitive answer if a single OCI connection can persist, while its underlying "session" is killed. I found http://www.dbametrix.com/kill-session-oracle-11g.html but this doesn't seem like something you can call regularly. As for cx_oracle itself, it offers the opposite behavior, that you can "pool" sessions using a SessionPool: http://cx-oracle.sourceforge.net/html/module.html#cx_Oracle.SessionPool and also the usage of Sessions can be customized when a new connection is created: http://cx-oracle.sourceforge.net/html/module.html#database-resident-connection-pooling Once you decide how you'd like connections to cx_oracle to occur, the venues you have in SQLAlchemy for this are: 1. Disable SQLAlchemy connection pooling using NullPool. If you can get cx_oracle's connect() method to do what you want as far as pooling, you can turn of the pool on the SQLAlchemy side using NullPool. http://www.sqlalchemy.org/docs/core/pooling.html#switching-pool-implementations 2. Use a creator function - if you need to use special form when calling cx_oracle.connect(), a creator function will allow you to plug into the Engine how cx_oracle connections are established. http://www.sqlalchemy.org/docs/core/pooling.html#using-a-custom-connection-function 3. Use pool events to emit commands when connections are checked out or checked in. If you need to emit some SQL or cx_oracle commands on the DBAPI connection upon checkout or checkin, the Engine provides pool events which accomplish this. They work with NullPool as well as the regular QueuePool. http://www.sqlalchemy.org/docs/core/events.html#connection-pool-events You should first get some help for cx_oracle specifically, their list is at https://lists.sourceforge.net/lists/listinfo/cx-oracle-users . There, just ask them about how to do what you're looking for at the cx_oracle level directly. If you mention SQLAlchemy it will scare them away :). Then come back over here and we can connect up their recommendations with the SQLAlchemy API. On Oct 3, 2011, at 1:42 PM, Andrew wrote: > Good afternoon, > > We're using SQLAlchemy 0.6.6 and cx_Oracle 5.0.1, and our DBAs have > raised a concern about the number of sessions being kept open (where a > session is an Oracle concept, and a connection is on the socket). > We'd like to be able to keep the connection persistent, but we'd like > to be able to create a _new_session_ when a session is checked out > from the pool, rather than keep the same exact session open. That is, > I'd like the pool to be made up of connections, not of sessions. This > would enable the DBAs to perform statistics gathering and debugging on > sessions, which is something that they use fairly extensively. > > Is there a way to do this with configuration? Is this even possible > using SQLAlchemy, or is this a limitation of cx_Oracle? > > Thanks for your help. You guys are always extremely helpful! > > Andrew > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalchemy@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
