Connection pooling uses connections rather than sessions

[Andrew]

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

[Michael Bayer]

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.

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>

[Michael Bayer]

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

[Anupama Goparaju]

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

[Anupama Goparaju]

I have tried below approach in SQLAlchemy 1.3.16.

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.

The creator function, that i associated would internally perform a cx_Oracle.SessionPool.acquire(), where SessionPool is created with a fixed number of static connections.

How can we ensure that the connections are released and reused from this SessionPool when using SQLAlchemy Session object in a flask application across API requests?

Thanks,

Anupama

[Mike Bayer]

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.

--

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.

[Anupama Goparaju]

Thanks a lot for the inputs!