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()` <https://python-oracledb.readthedocs.io/en/latest/api_manual/connection_pool.html#ConnectionPool.release> or `Connection.close()` <https://python-oracledb.readthedocs.io/en/latest/api_manual/connection.html#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. > > 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/CALW8fqdZxO6h0bsAFGvvwWotNQwMZNZJUyOwL%2B-vfBkiGG0%3DrA%40mail.gmail.com > > <https://groups.google.com/d/msgid/sqlalchemy/CALW8fqdZxO6h0bsAFGvvwWotNQwMZNZJUyOwL%2B-vfBkiGG0%3DrA%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/05d33f86-db38-45b7-b2c4-6ee19a0250d2%40app.fastmail.com.
