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 <[email protected]> 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 <
> [email protected]> 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 [email protected].
>> 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 [email protected].
>> 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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/CALW8fqcSnLCCzBGA2vVV85BCZbHe%2BRNzDwR-%2BQHAbF1MMcHchQ%40mail.gmail.com.
