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&nencoding=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&nencoding=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
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&nencoding=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&nencoding=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 Ex
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&nencoding=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&nencoding=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 https://groups.google.com/d/msgid/sqlalchemy/CAFHwexcS1oydycUH6VXuapQSGV