Binding a connection to a SQLAlchemy session object

[Anupama Goparaju]

Hi,

I see in the documentation that we can bind a connection to a SQLAlchemy session object:

# at the module level, the global sessionmaker, # bound to a specific Engine 

Session = sessionmaker(bind=engine) 

 

# later, some unit of code wants to create a # Session that is bound to a specific Connection conn = engine.connect() 

session = Session(bind=conn)

How do we release the connection to an external connection pool safely? For instance, if we are using a cx_Oracle SessionPool to acquire and release connections? 

session.close() is not doing that seamlessly in this case.

if we do, session = Session(bind=engine) - it usually releases connection to the pool seamlessly on session.close().

Thanks,

Anupama

[Mike Bayer]

if you acquire a connection using engine.connect(), you must release it to the connection pool using connection.close().  this is independent of the Session being bound to this Connection while it is acquired from the pool.   Session.close() will not achieve this when you are using Session(bind=connection) as the Session understands the scope of this Connection is external to that Session.

This is then a separate issue from the use of cx_Oracle's SessionPool, for which we would assume you are using the recipe at https://docs.sqlalchemy.org/en/14/dialects/oracle.html#using-cx-oracle-sessionpool .

Thanks,

Anupama

--

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+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/66a43491-e94b-4b9c-a2ff-6e4ec8ee2cccn%40googlegroups.com.

[Anupama Goparaju]

Thanks for the quick reply. Correct, using the same recipe of cx_Oracle SessionPool + SQLAlchemy NullPool. 

If we handle the connection close explicitly to release to the pool, will that skip any exception handling on connections that Session object handles internally for db connections and also any rollbacks on errors?

[Mike Bayer]

On Mon, May 17, 2021, at 6:22 PM, Anupama Goparaju wrote:

Thanks for the quick reply. Correct, using the same recipe of cx_Oracle SessionPool + SQLAlchemy NullPool. 

If we handle the connection close explicitly to release to the pool, will that skip any exception handling on connections that Session object handles internally for db connections and also any rollbacks on errors?

I dont see any error handling that would be skipped, connection-level error handling is all within the connection itself.     a rollback() is separate from the scope of the connection and the Session will begin/commit/rollback on that connection.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/a47048cd-178b-4191-bc42-23abeede0d69n%40googlegroups.com.

[Anupama Goparaju]

ok great! thanks again!