Retrieve Full Connection Uri From Airflow Postgres Hook

Is there a neater way to get the complete URI from a Postgres hook? .get_uri() doesn't include the 'extra' params so I am appending them like this: def pg_conn_id_to_uri(postgres_c

Solution 1:

If cleaner doesn't necessarily imply for brevity here, then here's something that might work

fromtypingimportDict,Anyfrompsycopg2importextensionsfromairflow.hooks.postgres_hookimportPostgresHookfromairflow.models.connectionimportConnectiondefpg_conn_id_to_uri(postgres_conn_id:str)->str:# create hook & connhook:PostgresHook=PostgresHook(postgres_conn_id=postgres_conn_id)conn:Connection=hook.get_connection(conn_id=postgres_conn_id)# retrieve conn_args & extrasextras:Dict[str,Any]=conn.extra_dejsonconn_args:Dict[str,Any]=dict(host=conn.host,user=conn.login,password=conn.password,dbname=conn.schema,port=conn.port)conn_args_with_extras:Dict[str,Any]= {**conn_args, **extras}
    # build and return stringconn_string:str=extensions.make_dsn(dsn=None,**conn_args_with_extras)returnconn_string

Note that the snippet is untested

---

Of course we can still trim off some more lines from here (for e.g. by using python's syntactic sugar conn.__dict__.items()), but I prefer clarity over brevity

---

The hints have been taken from Airflow's & pyscopg2's code itself

• PostgresHook
• pyscopg2