Re: [sqlalchemy] do_connect listener called couple of times

[Srinu Chp]

Hello Simon,

I tried your suggestion as POC:

def setup_event_handlers(engine):
@event.listens_for(engine, 'do_connect')
def receive_do_connect(dialect, conn_rec, cargs, cparams):
print("inside do_connect")
print('password %s' % cparams['password'])
try:
print("inside try")
return cx_Oracle.connect(*cargs, **cparams)
except Exception as e:
print("inside catch")
cparams['password'] = "NewPassword"
return cx_Oracle.connect(*cargs, **cparams)

Every time except block is triggered even I set correct password in Except 
block. As per document once cparams password set it should pass new 
password for new request. Can you please suggest if I miss anything here?

Regards,
Pydi

On Tuesday, March 22, 2022 at 9:33:57 AM UTC-7 Srinu Chp wrote:

> Hello Simon,
>
> Thank you very much for detail information.
>
> Regards,
> Pydi
>
> On Tuesday, March 22, 2022 at 3:11:34 AM UTC-7 Simon King wrote:
>
>> I don't know anything about Airflow. Are you sure that each of these
>> tasks is running inside the same Python interpreter/process? I see
>> Airflow can distribute tasks among workers:
>>
>> https://airflow.apache.org/docs/apache-airflow/stable/executor/index.html
>>
>> This sounds like a problem that is going to be very specific to your
>> deployment environment. If you have multiple worker processes, you're
>> going to need some way to distribute the new password to each of the
>> workers (eg. a shared cache)
>>
>> But regardless of that, you're still not following the pattern from
>> the documentation. I don't understand why you are creating a new
>> engine inside your do_connect handler. You should be creating a
>> cx-Oracle connection and returning it. The parameters passed to the
>> do_connect handler have already been parsed out of the connection
>> string. So for example, if your connection string includes a
>> "password=some_password" parameter, then cparams will have a
>> "password" key with the value "some_password". The same cparams
>> dictionary will be passed to the do_connect handler each time, so if
>> you mutate the dictionary (eg. by updating the "password" key), the
>> next call to the handler will contain the new value.
>>
>> If each task invocation is creating a new engine using a connection
>> string that is out of date, then none of that will help you, but that
>> would be an Airflow problem, not an SQLAlchemy problem.
>>
>> Simon
>>
>> On Mon, Mar 21, 2022 at 8:27 PM Srinu Chp  wrote:
>> >
>> > Hello Simon,
>> >
>> > I tried POC approach in my project where we are using Airflow using 
>> sqlalchemy to connect to db. Here is the event code:
>> >
>> > @event.listens_for(engine, "do_connect")
>> > def receive_do_connect(dialect, conn_rec, cargs, cparams):
>> > global SQL_ALCHEMY_CONN
>> > log.info("receive_do_connect called for user AIRFLOW.")
>> > log.info("user details DB: {}".format(SQL_ALCHEMY_CONN))
>> > # creating new engine to valide using cx_oracle driver
>> > engine_new = 
>> create_engine(f'{SQL_ALCHEMY_CONN}/?encoding=UTF-8=UTF-8', 
>> max_identifier_length=128)
>> > try:
>> > with engine_new.connect() as conn:
>> > log.info(conn.scalar("select sysdate from dual"))
>> > SQL_ALCHEMY_CONN = "testNew_try"
>> > except Exception as e:
>> > # check for invalid user/pwd error
>> > if search('ORA-01017', str(e)):
>> > log.info("receive_do_connect exception occurred during engine 
>> connection e: {}".format(e))
>> > ...
>> > //update with new password
>> > SQL_ALCHEMY_CONN = "testNew_except"
>> > # this log print new value with updated password
>> > log.info("user details DB after update in except block: 
>> {}".format(SQL_ALCHEMY_CONN))
>> > cparams['New password']
>> >
>> > global SQL_ALCHEMY_CONN value is set during initialization. Once 
>> password is rotated I am trying to update the SQL_ALCHEMY_CONN so that next 
>> request will not go in except block. Every time logs print old 
>> SQL_ALCHEMY_CONN value even value is update in except block.
>> >
>> > second approach:
>> > I tried to set env variable in except block:
>> > os.environ['AIRFLOW__CORE__SQL_ALCHEMY_CONN']
>> > env variable also refer to old value even after updating in except 
>> block.
>> >
>> > Can you please suggestion?
>> > Regards,
>> > Pydi
>> > On Monday, March 21, 2022 at 10:55:09 AM UTC-7 Srinu Chp wrote:
>> >>
>> >> Hello Simon,
>> >>
>> >> Perfect, working as expected in standalone POC. Thank you quick help
>> >>
>> >> Regards,
>> >> Pydi
>> >>
>> >> On Monday, March 21, 2022 at 9:52:04 AM UTC-7 Simon King wrote:
>> >>>
>> >>> 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')
>> >>> 

[sqlalchemy] Conditional Insertion of Single Row with Bind Parameters

[mkmo...@gmail.com]

I would like to do a conditional insert of a a single row. This is often 
useful in a CRUD app for checking permissions and inserting in a single 
database call:

INSERT INTO project (name, user_id)
SELECT :name, :user_id
WHERE EXISTS (
SELECT 1
FROM users
WHERE id = :user_id
and role = :admin_role
)

In SQLAlchemy I use the following which isn't the most beautiful:

ins = insert(Project).from_select(
['name', 'user_id'], 

select(
bindparam('name', project_name),
bindparam('user_id', user_id),
).where(
exists(
select(1).select_from(
User
).where(
User.c.id == 1,
User.c.role == "ADMIN",
)
)
) 
)

I find my use of `bindparam` in the select statement to be rather noisy. 

Does anyone know of a way to make it a bit more easier on the eyes?

Would you be open to making a SQLAlchemy conditional insert API?

Project.insert(name=project_name, user_id=user_id).where(
exists(
# ...
)
)

In other words, create a method "where" on Insert that will not use VALUES 
and instead use a SELECT. 

Thanks and best regards,

Matthew

-- 
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/3ef987dd-cd2b-4326-bdf7-b75045265114n%40googlegroups.com.

Re: [sqlalchemy] Multiple Database Connection

[Mustafa Demiraslan]

Yes all of these are a database not schema.
Thank you for response.

Mike Bayer , 22 Mar 2022 Sal, 18:27 tarihinde
şunu yazdı:

> a single Engine only points to one database URL at a time.   so to that
> extent, as an Engine assumes there's a "default database" when it connects,
> any concept of multiple "databases" beneath that is not something the
> Engine is aware of.
>
> PostgreSQL itself has no "USE" command, so if you have a connection to a
> PostgreSQL database, that's the one you're talking to, so given this
> architecture you would definitely need to create an individual Engine per
> database.
>
> OTOH if these are actually PostgreSQL **schemas** and not databases, that
> would be something entirely different, but you haven't specified this.
>
>
>
> On Tue, Mar 22, 2022, at 10:26 AM, Mustafa Demiraslan wrote:
>
> Current structure is like this:
>
> [image: image.png]
>
> I can't manage pooling for green colored databases.
>
>
> Mike Bayer , 22 Mar 2022 Sal, 17:12 tarihinde
> şunu yazdı:
>
>
> for one engine with 18 databases, what is "switching the bindings"  ?  do
> you mean you call "USE " on the connection ?
>
> creating 18 separate engines would not be that big a deal if that's what
> works best for the code, no issue doing that.
>
> On Tue, Mar 22, 2022, at 9:41 AM, Mustafa Demiraslan wrote:
>
> Hi team,
> I am using 3 database engines at the same time, 1(include 1 database) +
> 1(include 1 database) + 1(include 18 database). I use the database
> containing 18 databases by switching the bindings. I connect to database
> using sessions with session_maker for each engine. During the load test, I
> am stuck on the max_connection limit for the part containing 18 databases.
> Do you think the right approach is to create a separate engine for each
> database(1+1+18 engine) or is it a single engine multiple binding? I would
> be very happy if you express your thoughts.
>
> Regards,
> Mustafa
>
>
> --
> 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/f196963c-9a0b-4359-b972-27010dfdcaden%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/97a1ad3f-2752-4fc4-9997-fcefff3b4589%40www.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/CAM%2BjLs8-po%3DoFJyv1vFKionj8nuMpUU2DQm7AvkHTOEYUbi1QQ%40mail.gmail.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/bddd27ef-3c59-4b24-b3f3-e42b0876886c%40www.fastmail.com
> 

Re: [sqlalchemy] Multiple Database Connection

[Mike Bayer]

a single Engine only points to one database URL at a time.   so to that extent, 
as an Engine assumes there's a "default database" when it connects, any concept 
of multiple "databases" beneath that is not something the Engine is aware of.   

PostgreSQL itself has no "USE" command, so if you have a connection to a 
PostgreSQL database, that's the one you're talking to, so given this 
architecture you would definitely need to create an individual Engine per 
database.

OTOH if these are actually PostgreSQL **schemas** and not databases, that would 
be something entirely different, but you haven't specified this.



On Tue, Mar 22, 2022, at 10:26 AM, Mustafa Demiraslan wrote:
> Current structure is like this:
> 
> image.png
> 
> I can't manage pooling for green colored databases. 
> 
> 
> Mike Bayer , 22 Mar 2022 Sal, 17:12 tarihinde şunu 
> yazdı:
>> __
>> for one engine with 18 databases, what is "switching the bindings"  ?  do 
>> you mean you call "USE " on the connection ?   
>> 
>> creating 18 separate engines would not be that big a deal if that's what 
>> works best for the code, no issue doing that.
>> 
>> On Tue, Mar 22, 2022, at 9:41 AM, Mustafa Demiraslan wrote:
>>> Hi team,
>>> I am using 3 database engines at the same time, 1(include 1 database) + 
>>> 1(include 1 database) + 1(include 18 database). I use the database 
>>> containing 18 databases by switching the bindings. I connect to database 
>>> using sessions with session_maker for each engine. During the load test, I 
>>> am stuck on the max_connection limit for the part containing 18 databases. 
>>> Do you think the right approach is to create a separate engine for each 
>>> database(1+1+18 engine) or is it a single engine multiple binding? I would 
>>> be very happy if you express your thoughts.
>>> 
>>> Regards,
>>> Mustafa
>>> 
>>> 
>>> -- 
>>> 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/f196963c-9a0b-4359-b972-27010dfdcaden%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/97a1ad3f-2752-4fc4-9997-fcefff3b4589%40www.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/CAM%2BjLs8-po%3DoFJyv1vFKionj8nuMpUU2DQm7AvkHTOEYUbi1QQ%40mail.gmail.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/bddd27ef-3c59-4b24-b3f3-e42b0876886c%40www.fastmail.com.

Re: [sqlalchemy] Multiple Database Connection

[Mustafa Demiraslan]

Current structure is like this:

[image: image.png]

I can't manage pooling for green colored databases.


Mike Bayer , 22 Mar 2022 Sal, 17:12 tarihinde
şunu yazdı:

> for one engine with 18 databases, what is "switching the bindings"  ?  do
> you mean you call "USE " on the connection ?
>
> creating 18 separate engines would not be that big a deal if that's what
> works best for the code, no issue doing that.
>
> On Tue, Mar 22, 2022, at 9:41 AM, Mustafa Demiraslan wrote:
>
> Hi team,
> I am using 3 database engines at the same time, 1(include 1 database) +
> 1(include 1 database) + 1(include 18 database). I use the database
> containing 18 databases by switching the bindings. I connect to database
> using sessions with session_maker for each engine. During the load test, I
> am stuck on the max_connection limit for the part containing 18 databases.
> Do you think the right approach is to create a separate engine for each
> database(1+1+18 engine) or is it a single engine multiple binding? I would
> be very happy if you express your thoughts.
>
> Regards,
> Mustafa
>
>
> --
> 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/f196963c-9a0b-4359-b972-27010dfdcaden%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/97a1ad3f-2752-4fc4-9997-fcefff3b4589%40www.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/CAM%2BjLs8-po%3DoFJyv1vFKionj8nuMpUU2DQm7AvkHTOEYUbi1QQ%40mail.gmail.com.

Re: [sqlalchemy] Multiple Database Connection

[Mike Bayer]

for one engine with 18 databases, what is "switching the bindings"  ?  do you 
mean you call "USE " on the connection ?   

creating 18 separate engines would not be that big a deal if that's what works 
best for the code, no issue doing that.

On Tue, Mar 22, 2022, at 9:41 AM, Mustafa Demiraslan wrote:
> Hi team,
> I am using 3 database engines at the same time, 1(include 1 database) + 
> 1(include 1 database) + 1(include 18 database). I use the database containing 
> 18 databases by switching the bindings. I connect to database using sessions 
> with session_maker for each engine. During the load test, I am stuck on the 
> max_connection limit for the part containing 18 databases. Do you think the 
> right approach is to create a separate engine for each database(1+1+18 
> engine) or is it a single engine multiple binding? I would be very happy if 
> you express your thoughts.
> 
> Regards,
> Mustafa
> 
> 
> -- 
> 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/f196963c-9a0b-4359-b972-27010dfdcaden%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/97a1ad3f-2752-4fc4-9997-fcefff3b4589%40www.fastmail.com.

[sqlalchemy] Multiple Database Connection

[Mustafa Demiraslan]

Hi team,
I am using 3 database engines at the same time, 1(include 1 database) + 
1(include 1 database) + 1(include 18 database). I use the database 
containing 18 databases by switching the bindings. I connect to database 
using sessions with session_maker for each engine. During the load test, I 
am stuck on the max_connection limit for the part containing 18 databases. 
Do you think the right approach is to create a separate engine for each 
database(1+1+18 engine) or is it a single engine multiple binding? I would 
be very happy if you express your thoughts.

Regards,
Mustafa

-- 
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/f196963c-9a0b-4359-b972-27010dfdcaden%40googlegroups.com.

Re: [sqlalchemy] do_connect listener called couple of times

[Simon King]

I don't know anything about Airflow. Are you sure that each of these
tasks is running inside the same Python interpreter/process? I see
Airflow can distribute tasks among workers:

https://airflow.apache.org/docs/apache-airflow/stable/executor/index.html

This sounds like a problem that is going to be very specific to your
deployment environment. If you have multiple worker processes, you're
going to need some way to distribute the new password to each of the
workers (eg. a shared cache)

But regardless of that, you're still not following the pattern from
the documentation. I don't understand why you are creating a new
engine inside your do_connect handler. You should be creating a
cx-Oracle connection and returning it. The parameters passed to the
do_connect handler have already been parsed out of the connection
string. So for example, if your connection string includes a
"password=some_password" parameter, then cparams will have a
"password" key with the value "some_password". The same cparams
dictionary will be passed to the do_connect handler each time, so if
you mutate the dictionary (eg. by updating the "password" key), the
next call to the handler will contain the new value.

If each task invocation is creating a new engine using a connection
string that is out of date, then none of that will help you, but that
would be an Airflow problem, not an SQLAlchemy problem.

Simon

On Mon, Mar 21, 2022 at 8:27 PM Srinu Chp  wrote:
>
> Hello Simon,
>
> I tried POC approach in my project where we are using Airflow using 
> sqlalchemy to connect to db. Here is the event code:
>
> @event.listens_for(engine, "do_connect")
> def receive_do_connect(dialect, conn_rec, cargs, cparams):
> global SQL_ALCHEMY_CONN
> log.info("receive_do_connect called for user AIRFLOW.")
> log.info("user details DB: {}".format(SQL_ALCHEMY_CONN))
> # creating new engine to valide using cx_oracle driver
> engine_new = 
> create_engine(f'{SQL_ALCHEMY_CONN}/?encoding=UTF-8=UTF-8', 
> max_identifier_length=128)
> try:
> with engine_new.connect() as conn:
> log.info(conn.scalar("select sysdate from dual"))
> SQL_ALCHEMY_CONN = "testNew_try"
> except Exception as e:
> # check for invalid user/pwd error
> if search('ORA-01017', str(e)):
> log.info("receive_do_connect exception occurred during engine connection e: 
> {}".format(e))
> ...
> //update with new password
> SQL_ALCHEMY_CONN = "testNew_except"
> # this log print new value with updated password
> log.info("user details DB after update in except block: 
> {}".format(SQL_ALCHEMY_CONN))
> cparams['New password']
>
> global SQL_ALCHEMY_CONN value is set during initialization. Once password is 
> rotated I am trying to update the SQL_ALCHEMY_CONN so that next request will 
> not go in except block. Every time logs print old SQL_ALCHEMY_CONN value even 
> value is update in except block.
>
> second approach:
> I tried to set env variable in except block:
> os.environ['AIRFLOW__CORE__SQL_ALCHEMY_CONN']
> env variable also refer to old value even after updating in except block.
>
> Can you please suggestion?
> Regards,
> Pydi
> On Monday, March 21, 2022 at 10:55:09 AM UTC-7 Srinu Chp wrote:
>>
>> Hello Simon,
>>
>> Perfect, working as expected in standalone POC. Thank you quick help
>>
>> Regards,
>> Pydi
>>
>> On Monday, March 21, 2022 at 9:52:04 AM UTC-7 Simon King wrote:
>>>
>>> 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: