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 <appropriate-cx-oracle-error>:
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 <chpsr...@gmail.com> 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 <chps...@gmail.com> 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+...@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/a0d30530-fc23-4e70-8965-a7b10c17a65cn%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/CAFHwexd0zf7D7shMrEXTaCJMHFYyarrWN--UGKKbGhfvrUgx1Q%40mail.gmail.com.
