Connect to Postgresql database from Python app using PyoDBC and docker-compose

Question

I need to connect to a postgres database from a Python app using the PyoDBC driver. I want to have the database and the app inside the same docker-compose network. I installed the necessary dependencies for Debian Stretch described in the documentation of pyodbc, since I think that the python image is based on a debian image, and I considered this stackoverlflow article helpful when writing the Dockerfile for the python app, but unfortunately, I am still getting the error described further down below.

My docker-compose file with services for postgres, pgadmin and the python app looks as follows:

version: '3.7'
services:
    postgres:
        container_name: otda_postgres_1
        restart: always
        image: postgres:12.1
        environment:
            - POSTGRES_USER=myuser
            - POSTGRES_PASSWORD=secret
            - POSTGRES_DB=postgres
        ports:
            - 5431:5432
        volumes:
            - pg_data:/var/lib/postgresql/data
            # Run shell script which executes all below sql files in the psql shell (needed to switch databases in SQL script)
            - ./postgres/startup.sh:/docker-entrypoint-initdb.d/startup.sh
            # SQL scripts to be executed by each command in the startup.sh file and their corresponding data files
            - ./postgres:/postgres
        networks:
            - net

    pgadmin:
        container_name: otda_pgadmin_1
        image: dpage/pgadmin4:4.18
        depends_on:
            - postgres
        restart: always
        environment:
            - PGADMIN_DEFAULT_EMAIL=myemail@gmail.com
            - PGADMIN_DEFAULT_PASSWORD=secret
        volumes:
            - pgadmin_data:/var/lib/pgadmin
            - ./pgadmin4/servers.json:/pgadmin4/servers.json
        ports:
            - 81:80
            - 441:443
        networks:
            - net

    app:
        container_name: otda_app_1
        depends_on:
          - pgadmin
        restart: always
        build:
            context: ./app
            dockerfile: Dockerfile
        # Keep container alive when running docker-compose up
        tty: true
        # Add to the same network to make sure, we can connect to the database
        networks:
            - net

volumes:
    pg_data:
    pgadmin_data:

networks:
  net:
    driver: bridge

The Dockerfile for the python app looks as follows:

FROM python:3.8

# All (container-side) paths below (separated by ":") augment the default search path for custom module files
ENV PYTHONPATH "${PYTHONPATH}:/src"

# Changing to root user so that we can install packages
USER root

# Installing pyodbc dependencies
# - Install driver and dependencies
RUN apt-get update \
    # Probably the below package is not necessary
    && apt-get install g++ -y \
    # Probably the below package is not necessary
    && apt-get install gcc -y \
    # Packages below are most likely necessary
    && apt-get install unixodbc -y \
    && apt-get install unixodbc-dev -y \
    && apt-get install unixodbc-bin -y \
    && apt-get install odbc-postgresql

# - Add odbc config files
COPY ./configs/odbcinst.ini /etc/odbcinst.ini
COPY ./configs/odbc.ini /etc/odbc.ini

# Installing environment
COPY ./requirements.txt /requirements.txt
RUN pip install -r /requirements.txt

# Copying all source code files into container
COPY ./src /src

The requirements.txt file looks as follows:

pyodbc==4.0.30
pandas==1.0.5
folium==0.11.0

./configs/odbcinst.ini looks as follows:

[PostgreSQL Driver]
Driver = /usr/local/lib/psqlodbcw.so

./configs/odbc.ini looks as follows:

[PostgreSQL]
Driver = PostgreSQL Driver
Database = postgres
Servername = otda_postgres_1
UserName = myuser
Password = secret
Port = 5432

And the python script which is supposed to connect to the database looks like this:

import pyodbc
conn_str = pyodbc.connect(
    'Driver={PostgreSQL Driver};'
    'Server=otda_postgres_1;'
    'Port=5432;'
    'Database=postgres;'
    'UID=myuser;'
    'PWD=secret;'
)
conn = pyodbc.connect(conn_str, autocommit=True) # Error occurs here
cursor = conn.cursor()
# Do something with the cursor ...

However, I get the following error message when calling pyodbc.connect:

pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib '/usr/local/lib/psqlodbcw.so' : file not found (0) (SQLDriverConnect)")

which makes total sense, because when I enter into the otda_app_1 container and execute ls /usr/local/lib, I can only see libpython3.8.so libpython3.8.so.1.0 libpython3.so pkgconfig python2.7 python3.7 python3.8 and hence not the psqlodbcw.so file.

I am guessing this is somehow related to the fact that I specified Driver = /usr/local/lib/psqlodbcw.so in odbcinst.ini as described here, but I wouldn't know what else to put there.

Does anyone know how to fix this issue? Many thanks in advance!