connection pooling

[Suraj Shaw]

Hi Team,

I am using connection pooling in sqlalchemy using oracle own pool.My script looks like this.

```

from sqlalchemy import create_engine,text

from sqlalchemy.pool import NullPool

import oracledb

pool = oracledb.create_pool(user='XXX', password='XXX',dsn='XXX:1521/XXX', min=1, max=5, increment=1)

def creator():

    return pool.acquire(cclass="MYAPP",purity=oracledb.PURITY_SELF)

engine = create_engine("oracle+oracledb://", creator=creator, poolclass=NullPool)

conn = engine.connect()

result = (conn.execute(text("select current_timestamp from dual"))) 

for row in result:    #first query

    print(row)

conn.close()

conn = engine.connect()

result = (conn.execute(text("select current_timestamp from dual")))

for row in result:     #second query

    print(row)

conn.close()

```

Here the first query in running perfectly when the second time when i am doing it then it is rasing the folowing error

sqlalchemy.exc.DatabaseError: (oracledb.exceptions.DatabaseError) DPY-4011: the database or network closed the connection

Why is this issue coming.

Is it because of conn.close(). Here the conn.close() is not releasing connection back to the pool.

Thanks

[Mike Bayer]

unknown.  I've run your program exactly as written with SQLAlchemy 2.0.15 and it succeeds on both queries.  I would advise reaching out to https://github.com/oracle/python-oracledb/discussions for debugging help.   feel free to show them our recipe from https://docs.sqlalchemy.org/en/20/dialects/oracle.html#using-oracle-database-resident-connection-pooling-drcp for reference.

--

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/10098e90-038e-478e-8a7e-db5b8e2f7e07n%40googlegroups.com.

[Suraj Shaw]

Hi Mike,

Is it like first conn.close() is closing the pool itself instead of releasing connection back to pool.

Thanks
Suraj

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/4049c5f7-6369-4251-8de4-b116441fb782%40app.fastmail.com.

[Suraj Shaw]

Hi Mike,

One more update.
I am using DRCP functionality of oracle so i have to add :pooled at the end of connect string.
If you run without using :pooled it is running correctly with output as 

(datetime.datetime(2023, 6, 8, 22, 13, 56, 762291),)

(datetime.datetime(2023, 6, 8, 22, 13, 57, 376310),)



But when i am using :pooled i am getting

(datetime.datetime(2023, 6, 8, 22, 16, 58, 683404),)

Traceback (most recent call last):

  File "/Users/apple/.virtualenvs/bofa/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1968, in _exec_single_context

    self.dialect.do_execute(

  File "/Users/apple/.virtualenvs/bofa/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 920, in do_execute

    cursor.execute(statement, parameters)

  File "/Users/apple/.virtualenvs/bofa/lib/python3.11/site-packages/oracledb/cursor.py", line 378, in execute

    impl.execute(self)

  File "src/oracledb/impl/thin/cursor.pyx", line 138, in oracledb.thin_impl.ThinCursorImpl.execute

  File "src/oracledb/impl/thin/protocol.pyx", line 382, in oracledb.thin_impl.Protocol._process_single_message

  File "src/oracledb/impl/thin/protocol.pyx", line 383, in oracledb.thin_impl.Protocol._process_single_message

  File "src/oracledb/impl/thin/protocol.pyx", line 343, in oracledb.thin_impl.Protocol._process_message

  File "src/oracledb/impl/thin/protocol.pyx", line 321, in oracledb.thin_impl.Protocol._process_message

  File "src/oracledb/impl/thin/protocol.pyx", line 389, in oracledb.thin_impl.Protocol._receive_packet

  File "src/oracledb/impl/thin/packet.pyx", line 559, in oracledb.thin_impl.ReadBuffer.receive_packet

  File "src/oracledb/impl/thin/packet.pyx", line 358, in oracledb.thin_impl.ReadBuffer._receive_packet_helper

  File "src/oracledb/impl/thin/packet.pyx", line 205, in oracledb.thin_impl.ReadBuffer._get_data_from_socket

  File "/Users/apple/.virtualenvs/bofa/lib/python3.11/site-packages/oracledb/errors.py", line 118, in _raise_err

    raise exc_type(_Error(message)) from cause

oracledb.exceptions.DatabaseError: DPY-4011: the database or network closed the connection

The above exception was the direct cause of the following exception:

Traceback (most recent call last):

  File "/Users/apple/bofa/bofa_drcp1.py", line 23, in <module>

    result = (conn.execute(text("select current_timestamp from dual")))

              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

  File "/Users/apple/.virtualenvs/bofa/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1413, in execute

    return meth(

           ^^^^^

  File "/Users/apple/.virtualenvs/bofa/lib/python3.11/site-packages/sqlalchemy/sql/elements.py", line 483, in _execute_on_connection

    return connection._execute_clauseelement(

           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

  File "/Users/apple/.virtualenvs/bofa/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1637, in _execute_clauseelement

    ret = self._execute_context(

          ^^^^^^^^^^^^^^^^^^^^^^

  File "/Users/apple/.virtualenvs/bofa/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1846, in _execute_context

    return self._exec_single_context(

           ^^^^^^^^^^^^^^^^^^^^^^^^^^

  File "/Users/apple/.virtualenvs/bofa/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1987, in _exec_single_context

    self._handle_dbapi_exception(

  File "/Users/apple/.virtualenvs/bofa/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2344, in _handle_dbapi_exception

    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e

  File "/Users/apple/.virtualenvs/bofa/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1968, in _exec_single_context

    self.dialect.do_execute(

  File "/Users/apple/.virtualenvs/bofa/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 920, in do_execute

    cursor.execute(statement, parameters)

  File "/Users/apple/.virtualenvs/bofa/lib/python3.11/site-packages/oracledb/cursor.py", line 378, in execute

    impl.execute(self)

  File "src/oracledb/impl/thin/cursor.pyx", line 138, in oracledb.thin_impl.ThinCursorImpl.execute

  File "src/oracledb/impl/thin/protocol.pyx", line 382, in oracledb.thin_impl.Protocol._process_single_message

  File "src/oracledb/impl/thin/protocol.pyx", line 383, in oracledb.thin_impl.Protocol._process_single_message

  File "src/oracledb/impl/thin/protocol.pyx", line 343, in oracledb.thin_impl.Protocol._process_message

  File "src/oracledb/impl/thin/protocol.pyx", line 321, in oracledb.thin_impl.Protocol._process_message

  File "src/oracledb/impl/thin/protocol.pyx", line 389, in oracledb.thin_impl.Protocol._receive_packet

  File "src/oracledb/impl/thin/packet.pyx", line 559, in oracledb.thin_impl.ReadBuffer.receive_packet

  File "src/oracledb/impl/thin/packet.pyx", line 358, in oracledb.thin_impl.ReadBuffer._receive_packet_helper

  File "src/oracledb/impl/thin/packet.pyx", line 205, in oracledb.thin_impl.ReadBuffer._get_data_from_socket

  File "/Users/apple/.virtualenvs/bofa/lib/python3.11/site-packages/oracledb/errors.py", line 118, in _raise_err

    raise exc_type(_Error(message)) from cause

sqlalchemy.exc.DatabaseError: (oracledb.exceptions.DatabaseError) DPY-4011: the database or network closed the connection

[SQL: select current_timestamp from dual]

(Background on this error at: https://sqlalche.me/e/20/4xp6)

[Mike Bayer]

On Thu, Jun 8, 2023, at 12:38 PM, Suraj Shaw wrote:

Hi Mike,

Is it like first conn.close() is closing the pool itself instead of releasing connection back to pool.

SQLAlchemy calls the .close() method on the connection itself.  Per oracledb docs at https://python-oracledb.readthedocs.io/en/latest/user_guide/connection_handling.html#creating-a-connection-pool

"When your application has finished performing all required database operations, the pooled connection should be released to make it available for other users of the pool. You can do this with ConnectionPool.release() or Connection.close()." 

The error you are showing is also not the error that's raised if the pool is closed.  here is a demonstration script:

import oracledb

from sqlalchemy import create_engine, text

from sqlalchemy.pool import NullPool

pool = oracledb.create_pool(

    user="scott", password="tiger", dsn="oracle18c/xe",

    min=2, max=5, increment=1, threaded=True,

    encoding="UTF-8", nencoding="UTF-8"

)

def creator():

    return pool.acquire(cclass="MYCLASS", purity=oracledb.PURITY_SELF)

engine = create_engine("oracle+oracledb://", creator=creator, poolclass=NullPool)

conn = engine.connect()

print(conn.scalar(text("select 1 from dual")))

conn.close()

conn = engine.connect()

print(conn.scalar(text("select 1 from dual")))

conn.close()

input("press enter to see a closed pool try to connect")

pool.close()

conn = engine.connect()

the first two statements succeed.  then key input is requested.  subsequent to that, the error message is explicit: "DPY-1002: connection pool is not open".  this is not the "DPY-4011: the database or network closed the connection" message you refer towards:

$ python test3.py 

1

1

press enter to see a closed pool try to connect

Traceback (most recent call last):

  ...

    raise exc_type(_Error(message)) from cause

oracledb.exceptions.InterfaceError: DPY-1002: connection pool is not open

I will reiterate my previous post that there's no action for SQLAlchemy to take here as of yet and you should post a discussion on the oracledb discussion tracker for assistance on what the error message you have actually means, since I dont know.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CALW8fqdZxO6h0bsAFGvvwWotNQwMZNZJUyOwL%2B-vfBkiGG0%3DrA%40mail.gmail.com.

[Mike Bayer]

On Thu, Jun 8, 2023, at 12:47 PM, Suraj Shaw wrote:

Hi Mike,

One more update.

I am using DRCP functionality of oracle so i have to add :pooled at the end of connect string.

If you run without using :pooled it is running correctly with output as 

(datetime.datetime(2023, 6, 8, 22, 13, 56, 762291),)

(datetime.datetime(2023, 6, 8, 22, 13, 57, 376310),)



But when i am using :pooled i am getting

OK, then please test the Oracle connection pool directly to debug this case, I'm not familiar with ":pooled" nor can I test here locally.  

SQLAlchemy does nothing whatsoever with the Oracle connection pool, it only emits statements on the connection and calls .close() on the connection (not the pool, which it has no access to, or knowledge of) at the end.    

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CALW8fqcSnLCCzBGA2vVV85BCZbHe%2BRNzDwR-%2BQHAbF1MMcHchQ%40mail.gmail.com.