Oracle describes SessionPool at: https://cx-oracle.readthedocs.io/en/latest/user_guide/connection_handling.html#connpool
This pool describes the connection lifecycle as first calling pool.acquire(), and then pool.release(connection), however the good news is that if connection.close() is called normally, that also does a pool release per their documentation, meaning the connection is transparently pooled once acquired. SQLAlchemy allows the means by which a connection is acquired to be customized using the "creator" hook as well as the newer "do_connect" hook: https://docs.sqlalchemy.org/en/13/core/engines.html?highlight=creator#sqlalchemy.create_engine.params.creator https://docs.sqlalchemy.org/en/13/core/events.html#sqlalchemy.events.DialectEvents.do_connect the difference between "creator" and "do_connect" is mostly that the latter hook passes through all the connection arguments whereas the first one does not. "creator" is quicker to demonstrate as below since we are given a complete source of connections to start. Then to disable SQLAlchemy's pooling, following the guidelines at https://docs.sqlalchemy.org/en/13/core/pooling.html#switching-pool-implementations we want to use NullPool. This pool will use the creator() function to get new connections, and when the connection is to be released, it calls connection.close(), just like we want. so we put those together and we get: import cx_Oracle from sqlalchemy import create_engine from sqlalchemy.pool import NullPool pool = cx_Oracle.SessionPool( user="scott", password="tiger", dsn="oracle1120", min=2, max=5, increment=1 ) engine = create_engine("oracle://", creator=pool.acquire, poolclass=NullPool) with engine.connect() as conn: print(conn.scalar("select 1 FROM dual")) that will give you the Engine which you then use with SQLAlchemy's Session normally. Another way this could be done would be to make a SQLAlchemy pool implementation that wraps cx_Oracle's SessionPool. That could be ultimately nicer to use and would be able to call acquire() / release() directly without the need to use NullPool, but would be more work to implement up front. On Wed, Dec 9, 2020, at 7:52 PM, Anupama Goparaju wrote: > Hi. > > I would like to know how to use a cx-oracle SessionPool with SQLAlchemy. > We are leveraging the Session object of SQLAlchemy but we would like to use > the driver level pooling instead of SQLAlchemy pool. > Please advise. > > Thanks, > Anupama > > On Monday, October 3, 2011 at 12:23:58 PM UTC-6 Mike Bayer wrote: >> >> On Oct 3, 2011, at 2:20 PM, Michael Bayer wrote: >> > >> > 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 >> >> in 0.6 you'd use PoolListener, same idea just older API: >> http://www.sqlalchemy.org/docs/06/core/interfaces.html#connection-pool-events >> >> > > -- > 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+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/e9e325d2-ac0e-47da-870f-6985d0e210c4n%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/e9e325d2-ac0e-47da-870f-6985d0e210c4n%40googlegroups.com?utm_medium=email&utm_source=footer>. -- 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+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/e7b8d339-df06-4dcb-949d-6db7fd10dab1%40www.fastmail.com.
