[Python] How to connect from Python to Postgres DB deployed on Azure Container Apps?

I am facing an issue connecting from python to postgres on Azure Container Apps (ACA).

What do I need to modify to fix the connection issue?

ACA deployment yaml definition:

properties:
workloadProfileName: D16
configuration:
activeRevisionsMode: Single
ingress:
external: true
targetPort: 5432
template:
containers:
- image: docker.io/postgres:latest
name: postgres-db-container
resources:
cpu: 1
memory: 2Gi
env:
- name: POSTGRES_USER
value: postgres
- name: POSTGRES_PASSWORD
value: postgres
- name: PGDATA
value: /var/lib/postgresql/data/pgdata
volumeMounts:
- volumeName: pgdata-volume
mountPath: /var/lib/postgresql/data
scale:
minReplicas: 1
maxReplicas: 1
volumes:
- name: pgdata-volume
storageType: AzureFile
storageName: datamount
mountOptions: 'dir_mode=0700,file_mode=0700,uid=999,gid=999,nobrl'


When I ssh into the container I can see that the service is running and I can also run psql commands, the problem arises when I try to connect from python as follows (host adress modified to hide confidential information):

pip install psycopg2-binary


import psycopg2

db_host = "https://my-app.funnyname-1234567.germanywestcentral.azurecontainerapps.io"
conn_string = f"host='{db_host}' dbname='postgres' user='postgres' password='postgres'"
conn = psycopg2.connect(conn_string)


Traceback

---------------------------------------------------------------------------
OperationalError Traceback (most recent call last)
Cell In[7], line 8
5 conn_string = f"host='{db_host}' dbname='postgres' user='postgres' password='postgres'"
7 # get a connection, if a connect cannot be made an exception will be raised here
----> 8 conn = psycopg2.connect(conn_string)

File d:\VSCodeProjects\chat-with-docs-app\venv\lib\site-packages\psycopg2\__init__.py:122, in connect(dsn, connection_factory, cursor_factory, **kwargs)
119 kwasync['async_'] = kwargs.pop('async_')
121 dsn = _ext.make_dsn(dsn, **kwargs)
--> 122 conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
123 if cursor_factory is not None:
124 conn.cursor_factory = cursor_factory

OperationalError: could not translate host name "https://my-app.funnyname-1234567.germanywestcentral.azurecontainerapps.io" to address: Der angegebene Host ist unbekannt.

Continue reading...