I'm trying to create a sqlalchemy engine using an existing psycopg2 connection.
My first approach is to try and extract a database URI from the psycopg2 connection, but this is failing due to security:
Calling connection.dsn prints all that's necessary to generate your connection string, but the password is obfuscated:
print(connection.dsn)
user=my_user password=xxx dbname=my_db host=localhost port=5432
similarly, calling connection.get_dsn_parameters() returns a dictionary without a password.
print(connection.get_dsn_parameters())
{'user': 'my_user', 'dbname': 'my_db', 'host': 'localhost', 'port': '5432', 'tty': '', 'options': '', 'sslmode': 'prefer', 'sslcompression': '0', 'krbsrvname': '***', 'target_session_attrs': 'any'}
I also see that if I had a connection pool, I could follow the approach from this SO answer, but I only have the connection.
I thought of faking a creator function, but I'm pretty sure this could backfire if sqlalchemy closes my one connection internally:
# connection exists in the scope
def get_conn():
return connection
create_engine('postgresql+psycopg2://', creator=get_conn)
Any ideas?
