Thanks a lot for the inputs! On Wednesday, December 9, 2020 at 9:08:51 PM UTC-7 Mike Bayer wrote:
> 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+...@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/1ab0ebc5-1d1a-45bd-9059-63eddf2e5a48n%40googlegroups.com.