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.
>
>
>
>
>
>
>
>
>
>
>
>
> (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
>
> <https://groups.google.com/d/msgid/sqlalchemy/CALW8fqcSnLCCzBGA2vVV85BCZbHe%2BRNzDwR-%2BQHAbF1MMcHchQ%40mail.gmail.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/1728f52e-4a65-4407-a4b2-ad6bf1dff23d%40app.fastmail.com.
