Re: [sqlalchemy] connection pooling

[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. 


> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> (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 
> 
> 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 

Re: [sqlalchemy] connection pooling

[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.





> 
> Thanks
> Suraj
> 
> On Thu, 8 Jun 2023 at 18:34, Mike Bayer  
> 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 

Re: [sqlalchemy] connection pooling

[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 

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

Re: [sqlalchemy] connection pooling

[Suraj Shaw]

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 
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
> 
> .
>
>
> --
> 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
> 
> .
>

-- 
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.

Re: [sqlalchemy] connection pooling

[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. 

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
>  
> .

-- 
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.

[sqlalchemy] 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

-- 
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.

Re: [sqlalchemy] Connection pooling uses connections rather than sessions

[Anupama Goparaju]

Thanks a lot for the inputs!

On Wednesday, December 9, 2020 at 9:08:51 PM UTC-7 Mike Bayer wrote:

> Oracle describes SessionPool at:
>
>
> https://cx-oracle.readthedocs.io/en/latest/user_guide/connection_handling.html#connpool
>
> This pool describes the connection lifecycle as first calling 
> pool.acquire(), and then pool.release(connection), however the good news is 
> that if connection.close() is called normally, that also does a pool 
> release per their documentation, meaning the connection is transparently 
> pooled once acquired.
>
> SQLAlchemy allows the means by which a connection is acquired to be 
> customized using the "creator" hook as well as the newer "do_connect" hook:
>
>
> https://docs.sqlalchemy.org/en/13/core/engines.html?highlight=creator#sqlalchemy.create_engine.params.creator
>
>
> https://docs.sqlalchemy.org/en/13/core/events.html#sqlalchemy.events.DialectEvents.do_connect
>
> the difference between "creator" and "do_connect" is mostly that the 
> latter hook passes through all the connection arguments whereas the first 
> one does not."creator" is quicker to demonstrate as below since we are 
> given a complete source of connections to start.   
>
> Then to disable SQLAlchemy's pooling, following the guidelines at 
> https://docs.sqlalchemy.org/en/13/core/pooling.html#switching-pool-implementations
>  
> we want to use NullPool.   This pool will use the creator() function to get 
> new connections, and when the connection is to be released, it calls 
> connection.close(), just like we want.
>
> so we put those together and we get:
>
> import cx_Oracle
> from sqlalchemy import create_engine
> from sqlalchemy.pool import NullPool
>
> pool = cx_Oracle.SessionPool(
> user="scott", password="tiger", dsn="oracle1120", min=2, max=5, 
> increment=1
> )
>
> engine = create_engine("oracle://", creator=pool.acquire, 
> poolclass=NullPool)
>
> with engine.connect() as conn:
> print(conn.scalar("select 1 FROM dual"))
>
>
> that will give you the Engine which you then use with SQLAlchemy's Session 
> normally.
>
> Another way this could be done would be to make a SQLAlchemy pool 
> implementation that wraps cx_Oracle's SessionPool.   That could be 
> ultimately nicer to use and would be able to call acquire() / release() 
> directly without the need to use NullPool, but would be more work to 
> implement up front.
>
>
>
> On Wed, Dec 9, 2020, at 7:52 PM, Anupama Goparaju wrote:
>
> Hi.
>
> I would like to know how to use a cx-oracle SessionPool with SQLAlchemy.
> We are leveraging the Session object of SQLAlchemy but we would like to 
> use the driver level pooling instead of SQLAlchemy pool.
> Please advise.
>
> Thanks,
> Anupama
>
> On Monday, October 3, 2011 at 12:23:58 PM UTC-6 Mike Bayer wrote:
>
>
> On Oct 3, 2011, at 2:20 PM, Michael Bayer wrote:
>
> > 
> > 3. Use pool events to emit commands when connections are checked out or 
> checked in. If you need to emit some SQL or cx_oracle commands on the DBAPI 
> connection upon checkout or checkin, the Engine provides pool events which 
> accomplish this. They work with NullPool as well as the regular QueuePool.
> > 
> > http://www.sqlalchemy.org/docs/core/events.html#connection-pool-events
>
>
> in 0.6 you'd use PoolListener, same idea just older API:
>
>
> http://www.sqlalchemy.org/docs/06/core/interfaces.html#connection-pool-events
>
>
>
>
> -- 
> 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/e9e325d2-ac0e-47da-870f-6985d0e210c4n%40googlegroups.com
>  
> 
> .
>
>
>

-- 
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/1ab0ebc5-1d1a-45bd-9059-63eddf2e5a48n%40googlegroups.com.

Re: [sqlalchemy] Connection pooling uses connections rather than sessions

[Mike Bayer]

Oracle describes SessionPool at:

https://cx-oracle.readthedocs.io/en/latest/user_guide/connection_handling.html#connpool

This pool describes the connection lifecycle as first calling pool.acquire(), 
and then pool.release(connection), however the good news is that if 
connection.close() is called normally, that also does a pool release per their 
documentation, meaning the connection is transparently pooled once acquired.

SQLAlchemy allows the means by which a connection is acquired to be customized 
using the "creator" hook as well as the newer "do_connect" hook:

https://docs.sqlalchemy.org/en/13/core/engines.html?highlight=creator#sqlalchemy.create_engine.params.creator

https://docs.sqlalchemy.org/en/13/core/events.html#sqlalchemy.events.DialectEvents.do_connect

the difference between "creator" and "do_connect" is mostly that the latter 
hook passes through all the connection arguments whereas the first one does 
not."creator" is quicker to demonstrate as below since we are given a 
complete source of connections to start.   

Then to disable SQLAlchemy's pooling, following the guidelines at 
https://docs.sqlalchemy.org/en/13/core/pooling.html#switching-pool-implementations
 we want to use NullPool.   This pool will use the creator() function to get 
new connections, and when the connection is to be released, it calls 
connection.close(), just like we want.

so we put those together and we get:

import cx_Oracle
from sqlalchemy import create_engine
from sqlalchemy.pool import NullPool

pool = cx_Oracle.SessionPool(
user="scott", password="tiger", dsn="oracle1120", min=2, max=5, increment=1
)

engine = create_engine("oracle://", creator=pool.acquire, poolclass=NullPool)

with engine.connect() as conn:
print(conn.scalar("select 1 FROM dual"))


that will give you the Engine which you then use with SQLAlchemy's Session 
normally.

Another way this could be done would be to make a SQLAlchemy pool 
implementation that wraps cx_Oracle's SessionPool.   That could be ultimately 
nicer to use and would be able to call acquire() / release() directly without 
the need to use NullPool, but would be more work to implement up front.



On Wed, Dec 9, 2020, at 7:52 PM, Anupama Goparaju wrote:
> Hi.
> 
> I would like to know how to use a cx-oracle SessionPool with SQLAlchemy.
> We are leveraging the Session object of SQLAlchemy but we would like to use 
> the driver level pooling instead of SQLAlchemy pool.
> Please advise.
> 
> Thanks,
> Anupama
> 
> On Monday, October 3, 2011 at 12:23:58 PM UTC-6 Mike Bayer wrote:
>> 
>> On Oct 3, 2011, at 2:20 PM, Michael Bayer wrote:

>> > 
>> > 3. Use pool events to emit commands when connections are checked out or 
>> > checked in. If you need to emit some SQL or cx_oracle commands on the 
>> > DBAPI connection upon checkout or checkin, the Engine provides pool events 
>> > which accomplish this. They work with NullPool as well as the regular 
>> > QueuePool.
>> > 
>> > http://www.sqlalchemy.org/docs/core/events.html#connection-pool-events

>> 

>> in 0.6 you'd use PoolListener, same idea just older API:

>> http://www.sqlalchemy.org/docs/06/core/interfaces.html#connection-pool-events

>> 

>> 

> 

> -- 
> 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/e9e325d2-ac0e-47da-870f-6985d0e210c4n%40googlegroups.com
>  
> .

-- 
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/e7b8d339-df06-4dcb-949d-6db7fd10dab1%40www.fastmail.com.

Re: [sqlalchemy] Connection pooling uses connections rather than sessions

[Anupama Goparaju]

I have tried below approach in SQLAlchemy 1.3.16.

Use a creator function - if you need to use special form when calling 
cx_oracle.connect(), a creator function will allow you to plug into the 
Engine how cx_oracle connections are established.

The creator function, that i associated would internally perform a 
cx_Oracle.SessionPool.acquire(), where SessionPool is created with a fixed 
number of static connections.
How can we ensure that the connections are released and reused from this 
SessionPool when using SQLAlchemy Session object in a flask application 
across API requests?

Thanks,
Anupama
On Wednesday, December 9, 2020 at 5:52:26 PM UTC-7 Anupama Goparaju wrote:

> Hi.
>
> I would like to know how to use a cx-oracle SessionPool with SQLAlchemy.
> We are leveraging the Session object of SQLAlchemy but we would like to 
> use the driver level pooling instead of SQLAlchemy pool.
> Please advise.
>
> Thanks,
> Anupama
>
> On Monday, October 3, 2011 at 12:23:58 PM UTC-6 Mike Bayer wrote:
>
>>
>> On Oct 3, 2011, at 2:20 PM, Michael Bayer wrote:
>>
>> > 
>> > 3. Use pool events to emit commands when connections are checked out or 
>> checked in. If you need to emit some SQL or cx_oracle commands on the DBAPI 
>> connection upon checkout or checkin, the Engine provides pool events which 
>> accomplish this. They work with NullPool as well as the regular QueuePool.
>> > 
>> > http://www.sqlalchemy.org/docs/core/events.html#connection-pool-events
>>
>> in 0.6 you'd use PoolListener, same idea just older API:
>>
>>
>> http://www.sqlalchemy.org/docs/06/core/interfaces.html#connection-pool-events
>>
>>

-- 
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/34851672-66ae-424e-b0d4-3cbdaeb369fan%40googlegroups.com.

Re: [sqlalchemy] Connection pooling uses connections rather than sessions

[Anupama Goparaju]

Hi.

I would like to know how to use a cx-oracle SessionPool with SQLAlchemy.
We are leveraging the Session object of SQLAlchemy but we would like to use 
the driver level pooling instead of SQLAlchemy pool.
Please advise.

Thanks,
Anupama

On Monday, October 3, 2011 at 12:23:58 PM UTC-6 Mike Bayer wrote:

>
> On Oct 3, 2011, at 2:20 PM, Michael Bayer wrote:
>
> > 
> > 3. Use pool events to emit commands when connections are checked out or 
> checked in. If you need to emit some SQL or cx_oracle commands on the DBAPI 
> connection upon checkout or checkin, the Engine provides pool events which 
> accomplish this. They work with NullPool as well as the regular QueuePool.
> > 
> > http://www.sqlalchemy.org/docs/core/events.html#connection-pool-events
>
> in 0.6 you'd use PoolListener, same idea just older API:
>
>
> http://www.sqlalchemy.org/docs/06/core/interfaces.html#connection-pool-events
>
>

-- 
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/e9e325d2-ac0e-47da-870f-6985d0e210c4n%40googlegroups.com.

Re: [sqlalchemy] Connection pooling strategy for a small/fixed number of db users

[Russ]

 well the engine is essentially a holder for a connection pool. If you use 
 a 
 pool like NullPool, it makes a new connection on every use, but in that 
 case 
 there is still not an official way to send in different connection 
 parameters. There’s no advantage to trying to make Engine work in a 
 different way, using two engines is just shorthand for using two different 
 sets of credentials. 


Ok.  That makes sense.  Thanks for clarifying.

Definitely don’t need two sessionmakers, the engine can be passed both to 
 the constructor of Session directly as well as to the sessionmaker 
 function 
 at the moment the new Session is created. But it shouldn’t be any less 
 convenient to have two sessionmakers vs. passing two different engines in, 
 vs. passing connection credentials in somewhere when you need to connect. 
 There’s “two of something” going on no matter how you try to organize 
 that. 


So I only need to select the relevant engine and pas to my )scoped) 
sessionmaker, then.  Great.
 

 I think what’s odd here is that if this is a web app, why is it needing to 
 maintain two sets of credentials internally in a single process for what 
 is 
 apparently the same database. 


Some of the functions in the API allow arbitrary SQL strings to be passed 
for execution... but restricted access/views for reporting only.  The API 
path is used to control server access, rather than exposing the database 
(postgres) to direct external connections.  Postgres only listens on 
localhost.  The implementation for these APIs then uses separate 
credentials to ensure read-only access in their implementation, whereas the 
vast majority of APIs have full access.

Still odd? :)

Thanks for the help, guys!

Russ

-- 
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Connection pooling uses connections rather than sessions

[Andrew]

Good afternoon,

We're using SQLAlchemy 0.6.6 and cx_Oracle 5.0.1, and our DBAs have
raised a concern about the number of sessions being kept open (where a
session is an Oracle concept, and a connection is on the socket).
We'd like to be able to keep the connection persistent, but we'd like
to be able to create a _new_session_ when a session is checked out
from the pool, rather than keep the same exact session open.  That is,
I'd like the pool to be made up of connections, not of sessions.  This
would enable the DBAs to perform statistics gathering and debugging on
sessions, which is something that they use fairly extensively.

Is there a way to do this with configuration?  Is this even possible
using SQLAlchemy, or is this a limitation of cx_Oracle?

Thanks for your help.  You guys are always extremely helpful!

Andrew

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Connection pooling uses connections rather than sessions

[Michael Bayer]

This is a problem that would need to be solved mostly on the cx_oracle side, 
then using standard SQLAlchemy APIs to implement.

Some googling didn't turn up a definitive answer if a single OCI connection can 
persist, while its underlying session is killed.  I found 
http://www.dbametrix.com/kill-session-oracle-11g.html but this doesn't seem 
like something you can call regularly.

As for cx_oracle itself, it offers the opposite behavior, that you can pool 
sessions using a SessionPool:

http://cx-oracle.sourceforge.net/html/module.html#cx_Oracle.SessionPool

and also the usage of Sessions can be customized when a new connection is 
created:

http://cx-oracle.sourceforge.net/html/module.html#database-resident-connection-pooling

Once you decide how you'd like connections to cx_oracle to occur, the venues 
you have in SQLAlchemy for this are:

1. Disable SQLAlchemy connection pooling using NullPool.  If you can get 
cx_oracle's connect()  method to do what you want as far as pooling, you can 
turn of the pool on the SQLAlchemy side using NullPool.

http://www.sqlalchemy.org/docs/core/pooling.html#switching-pool-implementations

2. Use a creator function - if you need to use special form when calling 
cx_oracle.connect(), a creator function will allow you to plug into the Engine 
how cx_oracle connections are established.

http://www.sqlalchemy.org/docs/core/pooling.html#using-a-custom-connection-function

3. Use pool events to emit commands when connections are checked out or checked 
in.   If you need to emit some SQL or cx_oracle commands on the DBAPI 
connection upon checkout or checkin, the Engine provides pool events which 
accomplish this.  They work with NullPool as well as the regular QueuePool.

http://www.sqlalchemy.org/docs/core/events.html#connection-pool-events

You should first get some help for cx_oracle specifically, their list is at 
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users .There, just 
ask them about how to do what you're looking for at the cx_oracle level 
directly.   If you mention SQLAlchemy it will scare them away :).

Then come back over here and we can connect up their recommendations with the 
SQLAlchemy API.




On Oct 3, 2011, at 1:42 PM, Andrew wrote:

 Good afternoon,
 
 We're using SQLAlchemy 0.6.6 and cx_Oracle 5.0.1, and our DBAs have
 raised a concern about the number of sessions being kept open (where a
 session is an Oracle concept, and a connection is on the socket).
 We'd like to be able to keep the connection persistent, but we'd like
 to be able to create a _new_session_ when a session is checked out
 from the pool, rather than keep the same exact session open.  That is,
 I'd like the pool to be made up of connections, not of sessions.  This
 would enable the DBAs to perform statistics gathering and debugging on
 sessions, which is something that they use fairly extensively.
 
 Is there a way to do this with configuration?  Is this even possible
 using SQLAlchemy, or is this a limitation of cx_Oracle?
 
 Thanks for your help.  You guys are always extremely helpful!
 
 Andrew
 
 -- 
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To post to this group, send email to sqlalchemy@googlegroups.com.
 To unsubscribe from this group, send email to 
 sqlalchemy+unsubscr...@googlegroups.com.
 For more options, visit this group at 
 http://groups.google.com/group/sqlalchemy?hl=en.
 

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Connection pooling uses connections rather than sessions

[Michael Bayer]

On Oct 3, 2011, at 2:20 PM, Michael Bayer wrote:

 
 3. Use pool events to emit commands when connections are checked out or 
 checked in.   If you need to emit some SQL or cx_oracle commands on the DBAPI 
 connection upon checkout or checkin, the Engine provides pool events which 
 accomplish this.  They work with NullPool as well as the regular QueuePool.
 
 http://www.sqlalchemy.org/docs/core/events.html#connection-pool-events

in 0.6 you'd use PoolListener, same idea just older API:

http://www.sqlalchemy.org/docs/06/core/interfaces.html#connection-pool-events

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] connection pooling question, is it possible

[Krishnakant Mane]

hello all,
I am working on a free software for accounting and rural banking in India.
We use Pylons for web application.
Now, my question is in reference to the recent threads on this mailing 
list regarding sqlalchemy connections.
I heard that after a certain amount of connections, the library does 
have some problems managing them.
I have looked at some emails but I would like if some one demystifies my 
understanding or misunderstanding.
My application is based on MVC Architecture and the core logic is coded 
as XMLRPC based server side APIs.
We create a connection for every new client which connects to the rpc 
server and maintain all the connections in a list.
now I forsee a situation where more than 500 connections might be alive 
at one time in the list.
I understand that sqlalchemy has some limitations on the number of 
connections (engines ) and their respective session objects which can be 
kept alive at the same time?

if this is true, can i create some kind of a connection pool for the server?
This way connections can be recycled and used for a lot of clients and 
new connections will only be created when needed.
I want to avoide this situation, so I really want to know if there is 
some kind of upper limit on the number of engines that can be active at 
one time.


Happy hacking.
Krishnakant.



--
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] connection pooling question, is it possible

[Michael Bayer]

Krishnakant Mane wrote:
 hello all,
 I am working on a free software for accounting and rural banking in India.
 We use Pylons for web application.
 Now, my question is in reference to the recent threads on this mailing
 list regarding sqlalchemy connections.
 I heard that after a certain amount of connections, the library does
 have some problems managing them.
 I have looked at some emails but I would like if some one demystifies my
 understanding or misunderstanding.
 My application is based on MVC Architecture and the core logic is coded
 as XMLRPC based server side APIs.
 We create a connection for every new client which connects to the rpc
 server and maintain all the connections in a list.
 now I forsee a situation where more than 500 connections might be alive
 at one time in the list.
 I understand that sqlalchemy has some limitations on the number of
 connections (engines ) and their respective session objects which can be
 kept alive at the same time?
 if this is true, can i create some kind of a connection pool for the
 server?
 This way connections can be recycled and used for a lot of clients and
 new connections will only be created when needed.
 I want to avoide this situation, so I really want to know if there is
 some kind of upper limit on the number of engines that can be active at
 one time.

The connection pool can be configured to allow any number of connections
you'd like using the options described at
http://www.sqlalchemy.org/docs/reference/sqlalchemy/connections.html#creating-engines
, or if you are using a Pool directly see the docs at
http://www.sqlalchemy.org/docs/reference/sqlalchemy/pooling.html#sqlalchemy.pool.QueuePool
.  Keep in mind that 500 connections will require a very large amount of
memory on the client machine.   Hope this helps.




 Happy hacking.
 Krishnakant.



 --
 You received this message because you are subscribed to the Google Groups
 sqlalchemy group.
 To post to this group, send email to sqlalch...@googlegroups.com.
 To unsubscribe from this group, send email to
 sqlalchemy+unsubscr...@googlegroups.com.
 For more options, visit this group at
 http://groups.google.com/group/sqlalchemy?hl=en.



-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Re: sqlalchemy connection pooling and mysql last_insert_id()

[Henk]

As far as I know SqlAlchemy does not use LAST_INSERT_ID() (at least
not for single row inserts),
At the mysql protocol level, the id created for the auto inc column
will
be returned for each insert statement (as the result code for that
command).
The python MySQLdb dbapi driver will make this available trough the
property 'lastrowid' on the cursor object.
I guess this is wat SA uses. Because the id is returned on the same
statement
the connection pool will not be a problem.

On Jul 11, 2:18 am, lilo [EMAIL PROTECTED] wrote:
 According my mysql, LAST_INSERT_ID() is connection specific, so there
 is no problem from race conditions.  If I insert a record into a
 autoincremented table and do last_insert_id() on it, would there be a
 possibility where another insert happen just before selecting
 last_insert_id().  This won't be a problem with mysql if there isn't
 any connection pooling.  Since sqlalchemy has support for connection
 pooling, would there be a chance where connection is shared with
 another insert just before selecting last_insert_id()?

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] sqlalchemy connection pooling and mysql last_insert_id()

[lilo]

According my mysql, LAST_INSERT_ID() is connection specific, so there
is no problem from race conditions.  If I insert a record into a
autoincremented table and do last_insert_id() on it, would there be a
possibility where another insert happen just before selecting
last_insert_id().  This won't be a problem with mysql if there isn't
any connection pooling.  Since sqlalchemy has support for connection
pooling, would there be a chance where connection is shared with
another insert just before selecting last_insert_id()?
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---