Airflow PostgresHook retrieval in dag

Question

I'm using python to test the Airflow DAG. I have environmental variable: AIRFLOW_CONN_DB="postgresql://postgres:password@127.0.0.1:6099/db"

and I use the following command to get the hook to get the hook:

hook = PostgresHook(postgres_conn_id="db")

Then I'm trying to read the table as dataframe:

sql='select * from "schema_name".user;'    
df = hook.get_pandas_df(sql)

but the output is the following:

psycopg2.OperationalError: connection to server at "localhost" (::1), port 5432 failed: Connection refused (0x0000274D/10061) Is the server running on that host and accepting TCP/IP connections? connection to server at "localhost" (127.0.0.1), port 5432 failed: Connection refused (0x0000274D/10061) Is the server running on that host and accepting TCP/IP connections?

how to set connection as variable in a way such that I can use it as a hook then?

Answer 1

I reread the documentation. The problem was in typing postgresql (instead of postgres) and quotes inserted in url. The correct env is AIRFLOW_CONN_DB=postgres://postgres:password@127.0.0.1:6099/db