Re: [sqlalchemy] do_connect listener called couple of times
As suggested here:
https://docs.sqlalchemy.org/en/14/core/engines.html#fully-replacing-the-dbapi-connect-function
In your do_connect handler, rather than calling engine.connect(), you
need to call cx_Oracle.connect(), and return the result. You can wrap
this in an exception handler that detects the "incorrect password"
error to fetch new credentials. Something like this perhaps:
@event.listens_for(engine, 'do_connect')
def receive_do_connect(dialect, conn_rec, cargs, cparams):
try:
return cx_Oracle.connect(*cargs, **cparams)
except :
cparams["password"] = get_new_password()
return cx_Oracle.connect(*args, **cparams)
Hope that helps,
Simon
On Mon, Mar 21, 2022 at 4:26 PM Srinu Chp wrote:
>
> Hello Simon,
>
> Thank you for prompt response. I really appreciate your help. I am trying to
> achieve password rotation and we are using secret client to fetch new
> password. I tried do_connect event and fetch new password from secret client,
> working as expected but we are facing performance issue as we are every time
> connecting to secret client(3~5sec for each request). Instead I am trying to
> achieve if connect fails then fetch from secret client.
>
> I tried with handle_error event, when i get error check for invalid user/pwd
> and update session with latest engine. This approach also did not help
>
> Any insights are highly appreciated. Please suggest best approach.
>
> Regards,
> Pydi
> On Monday, March 21, 2022 at 2:22:04 AM UTC-7 Simon King wrote:
>>
>> I don't really understand what's going on in your code, but you seem
>> to be calling engine.connect() inside your "do_connect" event handler.
>> I would expect that to trigger another "do_connect" event, which in
>> turn will call engine.connect() again, which will trigger another
>> "do_connect" event, and so on. I'm surprised the application gets as
>> far as it does. Maybe the exception handler inside receive_do_connect
>> is allowing it to stumble on.
>>
>> Simon
>>
>> On Mon, Mar 21, 2022 at 4:51 AM Srinu Chp wrote:
>> >
>> > Hello Team,
>> >
>> > I tried to create a standalone application POC for sqlalchemy to db
>> > connection. When I registered do_connect event, I see event is triggered
>> > couple of times for one call:
>> > sqlalchemy_connection.py
>> >
>> > import os
>> > import cx_Oracle
>> > from sqlalchemy import create_engine
>> > from sqlalchemy import event
>> >
>> > cx_Oracle.init_oracle_client(lib_dir=os.environ.get("HOME") +
>> > "/Downloads/instantclient_19_8")
>> > SQLALCHEMY_CONN = "test"
>> > count = 0
>> > engine = None
>> > def connect_db(pwd):
>> > global count
>> > global engine
>> > print(SQLALCHEMY_CONN)
>> > username = "ADMIN"
>> > password = pwd
>> > dsn = "pydidb_high"
>> > engine = create_engine(
>> > f'oracle://{username}:{password}@{dsn}/?encoding=UTF-8=UTF-8',
>> > max_identifier_length=128)
>> > setup_event_handlers(engine)
>> >
>> > def setup_event_handlers(engine):
>> > @event.listens_for(engine, 'do_connect')
>> > def receive_do_connect(dialect, conn_rec, cargs, cparams):
>> > print("inside do_connect")
>> > global count
>> > try:
>> > with engine.connect() as conn:
>> > print("inside do_connect try block")
>> > print(conn.scalar("select sysdate from dual"))
>> > count += 2
>> > except Exception as e:
>> > print("inside do_connect except block")
>> > count += 1
>> >
>> > def db_connect_test():
>> > print(engine)
>> > with engine.connect() as conn:
>> > print(conn.scalar("select sysdate from dual"))
>> >
>> > gevent_sync.py
>> >
>> > import gevent
>> > import random
>> > import sqlalchemy_connection
>> >
>> > def task(pid):
>> > gevent.sleep(random.randint(0,2)*0.001)
>> > print('Task %s done' % pid)
>> > sqlalchemy_connection.connect_db(**)
>> > sqlalchemy_connection.db_connect_test()
>> >
>> > def synchronous():
>> > for i in range(1,2):
>> > task(i)
>> > # sqlalchemy_connection.connect_db(**)
>> >
>> > def asynchronous():
>> > threads = [gevent.spawn(task, i) for i in range(2)]
>> > gevent.joinall(threads)
>> >
>> > print('Synchronous:')
>> > synchronous()
>> > print('count %s ' % sqlalchemy_connection.count)
>> >
>> > # print('Asynchronous:')
>> > # asynchronous()
>> > # print('count %s' % sqlalchemy_connection.count)
>> >
>> > Output:
>> >
>> > Synchronous:
>> > Task 1 done
>> > test
>> > Engine(oracle://ADMIN:***@pydidb_high/?encoding=UTF-8=UTF-8)
>> > inside do_connect
>> > inside do_connect
>> > inside do_connect
>> > inside do_connect
>> > inside do_connect
>> > inside do_connect
>> > inside do_connect
>> > inside do_connect
>> > inside do_connect
>> > inside do_connect
>> > inside do_connect
>> > inside do_connect
>> > inside do_connect
>> > inside do_connect
>> > inside do_connect
>> > inside do_connect except block
>> > inside do_connect try block
>> > 2022-03-21 04:39:47
>> > inside do_connect try block
>> > 2022-03-21 04:39:49
>> > inside do_connect try block
>> > 2022-03-21 04:39:51
>> > inside do_connect try block
>> > 2022-03-21
Re: [sqlalchemy] do_connect listener called couple of times
Hello Simon,
Thank you for prompt response. I really appreciate your help. I am trying
to achieve password rotation and we are using secret client to fetch new
password. I tried do_connect event and fetch new password from secret
client, working as expected but we are facing performance issue as we are
every time connecting to secret client(3~5sec for each request). Instead I
am trying to achieve if connect fails then fetch from secret client.
I tried with handle_error event, when i get error check for invalid
user/pwd and update session with latest engine. This approach also did not
help
Any insights are highly appreciated. Please suggest best approach.
Regards,
Pydi
On Monday, March 21, 2022 at 2:22:04 AM UTC-7 Simon King wrote:
> I don't really understand what's going on in your code, but you seem
> to be calling engine.connect() inside your "do_connect" event handler.
> I would expect that to trigger another "do_connect" event, which in
> turn will call engine.connect() again, which will trigger another
> "do_connect" event, and so on. I'm surprised the application gets as
> far as it does. Maybe the exception handler inside receive_do_connect
> is allowing it to stumble on.
>
> Simon
>
> On Mon, Mar 21, 2022 at 4:51 AM Srinu Chp wrote:
> >
> > Hello Team,
> >
> > I tried to create a standalone application POC for sqlalchemy to db
> connection. When I registered do_connect event, I see event is triggered
> couple of times for one call:
> > sqlalchemy_connection.py
> >
> > import os
> > import cx_Oracle
> > from sqlalchemy import create_engine
> > from sqlalchemy import event
> >
> > cx_Oracle.init_oracle_client(lib_dir=os.environ.get("HOME") +
> "/Downloads/instantclient_19_8")
> > SQLALCHEMY_CONN = "test"
> > count = 0
> > engine = None
> > def connect_db(pwd):
> > global count
> > global engine
> > print(SQLALCHEMY_CONN)
> > username = "ADMIN"
> > password = pwd
> > dsn = "pydidb_high"
> > engine = create_engine(
> > f'oracle://{username}:{password}@{dsn}/?encoding=UTF-8=UTF-8',
> max_identifier_length=128)
> > setup_event_handlers(engine)
> >
> > def setup_event_handlers(engine):
> > @event.listens_for(engine, 'do_connect')
> > def receive_do_connect(dialect, conn_rec, cargs, cparams):
> > print("inside do_connect")
> > global count
> > try:
> > with engine.connect() as conn:
> > print("inside do_connect try block")
> > print(conn.scalar("select sysdate from dual"))
> > count += 2
> > except Exception as e:
> > print("inside do_connect except block")
> > count += 1
> >
> > def db_connect_test():
> > print(engine)
> > with engine.connect() as conn:
> > print(conn.scalar("select sysdate from dual"))
> >
> > gevent_sync.py
> >
> > import gevent
> > import random
> > import sqlalchemy_connection
> >
> > def task(pid):
> > gevent.sleep(random.randint(0,2)*0.001)
> > print('Task %s done' % pid)
> > sqlalchemy_connection.connect_db(**)
> > sqlalchemy_connection.db_connect_test()
> >
> > def synchronous():
> > for i in range(1,2):
> > task(i)
> > # sqlalchemy_connection.connect_db(**)
> >
> > def asynchronous():
> > threads = [gevent.spawn(task, i) for i in range(2)]
> > gevent.joinall(threads)
> >
> > print('Synchronous:')
> > synchronous()
> > print('count %s ' % sqlalchemy_connection.count)
> >
> > # print('Asynchronous:')
> > # asynchronous()
> > # print('count %s' % sqlalchemy_connection.count)
> >
> > Output:
> >
> > Synchronous:
> > Task 1 done
> > test
> > Engine(oracle://ADMIN:***@pydidb_high/?encoding=UTF-8=UTF-8)
> > inside do_connect
> > inside do_connect
> > inside do_connect
> > inside do_connect
> > inside do_connect
> > inside do_connect
> > inside do_connect
> > inside do_connect
> > inside do_connect
> > inside do_connect
> > inside do_connect
> > inside do_connect
> > inside do_connect
> > inside do_connect
> > inside do_connect
> > inside do_connect except block
> > inside do_connect try block
> > 2022-03-21 04:39:47
> > inside do_connect try block
> > 2022-03-21 04:39:49
> > inside do_connect try block
> > 2022-03-21 04:39:51
> > inside do_connect try block
> > 2022-03-21 04:39:54
> > inside do_connect try block
> > 2022-03-21 04:39:56
> > inside do_connect try block
> > 2022-03-21 04:39:59
> > inside do_connect try block
> > 2022-03-21 04:40:01
> > inside do_connect try block
> > 2022-03-21 04:40:04
> > inside do_connect try block
> > 2022-03-21 04:40:09
> > inside do_connect try block
> > 2022-03-21 04:40:15
> > inside do_connect try block
> > 2022-03-21 04:40:17
> > inside do_connect try block
> > 2022-03-21 04:40:19
> > inside do_connect try block
> > 2022-03-21 04:40:21
> > inside do_connect try block
> > 2022-03-21 04:40:24
> > 2022-03-21 04:40:26
> > count 29
> >
> > highly appreciate any inputs.
> > Regards,
> > Pydi
> >
> > --
> > 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
Re: [sqlalchemy] do_connect listener called couple of times
I don't really understand what's going on in your code, but you seem
to be calling engine.connect() inside your "do_connect" event handler.
I would expect that to trigger another "do_connect" event, which in
turn will call engine.connect() again, which will trigger another
"do_connect" event, and so on. I'm surprised the application gets as
far as it does. Maybe the exception handler inside receive_do_connect
is allowing it to stumble on.
Simon
On Mon, Mar 21, 2022 at 4:51 AM Srinu Chp wrote:
>
> Hello Team,
>
> I tried to create a standalone application POC for sqlalchemy to db
> connection. When I registered do_connect event, I see event is triggered
> couple of times for one call:
> sqlalchemy_connection.py
>
> import os
> import cx_Oracle
> from sqlalchemy import create_engine
> from sqlalchemy import event
>
> cx_Oracle.init_oracle_client(lib_dir=os.environ.get("HOME") +
> "/Downloads/instantclient_19_8")
> SQLALCHEMY_CONN = "test"
> count = 0
> engine = None
> def connect_db(pwd):
> global count
> global engine
> print(SQLALCHEMY_CONN)
> username = "ADMIN"
> password = pwd
> dsn = "pydidb_high"
> engine = create_engine(
> f'oracle://{username}:{password}@{dsn}/?encoding=UTF-8=UTF-8',
> max_identifier_length=128)
> setup_event_handlers(engine)
>
> def setup_event_handlers(engine):
> @event.listens_for(engine, 'do_connect')
> def receive_do_connect(dialect, conn_rec, cargs, cparams):
> print("inside do_connect")
> global count
> try:
> with engine.connect() as conn:
> print("inside do_connect try block")
> print(conn.scalar("select sysdate from dual"))
> count += 2
> except Exception as e:
> print("inside do_connect except block")
> count += 1
>
> def db_connect_test():
> print(engine)
> with engine.connect() as conn:
> print(conn.scalar("select sysdate from dual"))
>
> gevent_sync.py
>
> import gevent
> import random
> import sqlalchemy_connection
>
> def task(pid):
> gevent.sleep(random.randint(0,2)*0.001)
> print('Task %s done' % pid)
> sqlalchemy_connection.connect_db(**)
> sqlalchemy_connection.db_connect_test()
>
> def synchronous():
> for i in range(1,2):
> task(i)
> # sqlalchemy_connection.connect_db(**)
>
> def asynchronous():
> threads = [gevent.spawn(task, i) for i in range(2)]
> gevent.joinall(threads)
>
> print('Synchronous:')
> synchronous()
> print('count %s ' % sqlalchemy_connection.count)
>
> # print('Asynchronous:')
> # asynchronous()
> # print('count %s' % sqlalchemy_connection.count)
>
> Output:
>
> Synchronous:
> Task 1 done
> test
> Engine(oracle://ADMIN:***@pydidb_high/?encoding=UTF-8=UTF-8)
> inside do_connect
> inside do_connect
> inside do_connect
> inside do_connect
> inside do_connect
> inside do_connect
> inside do_connect
> inside do_connect
> inside do_connect
> inside do_connect
> inside do_connect
> inside do_connect
> inside do_connect
> inside do_connect
> inside do_connect
> inside do_connect except block
> inside do_connect try block
> 2022-03-21 04:39:47
> inside do_connect try block
> 2022-03-21 04:39:49
> inside do_connect try block
> 2022-03-21 04:39:51
> inside do_connect try block
> 2022-03-21 04:39:54
> inside do_connect try block
> 2022-03-21 04:39:56
> inside do_connect try block
> 2022-03-21 04:39:59
> inside do_connect try block
> 2022-03-21 04:40:01
> inside do_connect try block
> 2022-03-21 04:40:04
> inside do_connect try block
> 2022-03-21 04:40:09
> inside do_connect try block
> 2022-03-21 04:40:15
> inside do_connect try block
> 2022-03-21 04:40:17
> inside do_connect try block
> 2022-03-21 04:40:19
> inside do_connect try block
> 2022-03-21 04:40:21
> inside do_connect try block
> 2022-03-21 04:40:24
> 2022-03-21 04:40:26
> count 29
>
> highly appreciate any inputs.
> Regards,
> Pydi
>
> --
> 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/08096636-06c4-478f-a54d-0bc8f71db414n%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
