I inherited some code that uses sqlalchemy with psycopg2, which needs to run on AWS. RDS Postgres supports iam-based authentication, but the way it does it is rather kludgy: you request a temporary password using the AWS RDS API, which is good for about 15 minutes, and you pass that as the password.

The code I have effectively does e = create_engine(make_sqlalchemy_string()), where make_sqlalchemy_string() makes an aws api call, gets the temporary password, and everything is good for a while. Except something, somewhere in the sqlalchemy code closes the connection, and then the password is no longer good. Is there already a way to make sqlalchemy request a new connection string every time it needs to reconnect?

I can think of a number of workarounds, but none of them are pleasant. Is there a standard way of achieving this?

Thanks

One approach would be to use the creator argument of create_engine():

creator – a callable which returns a DBAPI connection. This creation function will be passed to the underlying connection pool and will be used to create all new database connections. Usage of this function causes connection parameters specified in the URL argument to be bypassed.

Just modify your make_sqlalchemy_string() to produce the dsn argument for psycopg2.connect(), which might mean not having to modify it at all due to accepted connection string formats, and pass the creator:

create_engine('postgresql://', creator=lambda: psycopg2.connect(make_dsn_string()))