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) On Thu, 8 Jun 2023 at 22:08, Suraj Shaw <shawsuraj351...@gmail.com> wrote: > Hi Mike, > > Is it like first conn.close() is closing the pool itself instead of > releasing connection back to pool. > > Thanks > Suraj > > On Thu, 8 Jun 2023 at 18:34, Mike Bayer < > mike_not_on_goo...@zzzcomputing.com> wrote: > >> 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. >> >> On Thu, Jun 8, 2023, at 7:23 AM, Suraj Shaw wrote: >> >> 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 >> >> >> -- >> 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/10098e90-038e-478e-8a7e-db5b8e2f7e07n%40googlegroups.com >> <https://groups.google.com/d/msgid/sqlalchemy/10098e90-038e-478e-8a7e-db5b8e2f7e07n%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/4049c5f7-6369-4251-8de4-b116441fb782%40app.fastmail.com >> <https://groups.google.com/d/msgid/sqlalchemy/4049c5f7-6369-4251-8de4-b116441fb782%40app.fastmail.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/CALW8fqcSnLCCzBGA2vVV85BCZbHe%2BRNzDwR-%2BQHAbF1MMcHchQ%40mail.gmail.com.