Connection error while using Base.prepare from SQLAlchemy's automap_base + docker compose

Question

I've been stuck on this error for a while. Just started using Docker and Docker compose (and also Python XD).

My problem is that, when the code reaches the Base.prepare line, I get the following error:

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) could not connect to server: Connection refused
Is the server running on host "postgresdb" (172.20.0.2) and accepting
TCP/IP connections on port 5432?

I tried looking around for a solution for a while but couldn't find anything. This seems to be a pretty specific scenario.

I assumed that the problem was caused by Base.prepare because, when I commented only this out the error would disappear. Please check out the code below for more context.

My docker-compose.yml looks like this:

version: '3.9'

services:
  postgresdb:
    image: postgres:14-alpine3.15
    restart: always
    environment:
        POSTGRES_USER: postgres
        POSTGRES_PASSWORD: postgres
        PGDATA: /var/lib/postgresql/data/pgdata
    volumes:
        - ./pgdata:/var/lib/postgresql/data
        - ./scripts/sql:/docker-entrypoint-initdb.d
    ports:
        - '5002:5432'
    networks:
      - api

  myapi:
    build:
      context: .
      dockerfile: ./Dockerfile.api
    environment:
      POSTGRES_HOST: postgresdb
      POSTGRES_PORT: 5432
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: my_db
    ports:
      - '5001:5001'
    volumes:
      - $PWD/src:/myapi/src
    depends_on:
          - postgresdb
    command: pipenv run python src/main.py
    networks:
      - api

networks:
  api:
    driver: bridge
    name: api

I have a few .sql files in the ./scripts/sql that run to populate the database when I run docker compose up for the first time.

And I have this bit of Python that is supposed to extract metadata from the database with automap_base:

import datetime
import pandas as pd
from sqlalchemy import create_engine, MetaData, Table
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.automap import automap_base
import os

HOST_ENV_KEY = 'POSTGRES_HOST'
PORT_ENV_KEY = 'POSTGRES_PORT'
DATABASE_ENV_KEY = 'POSTGRES_DB'
USERNAME_ENV_KEY = 'POSTGRES_USER'
PASSWORD_ENV_KEY = 'POSTGRES_PASSWORD'

HOST = os.environ.get(HOST_ENV_KEY) 
PORT = os.environ.get(PORT_ENV_KEY) 
DATABASE = os.environ.get(DATABASE_ENV_KEY) 
USERNAME = os.environ.get(USERNAME_ENV_KEY) 
PASSWORD = os.environ.get(PASSWORD_ENV_KEY) 

DB_CONNECTION_STRING = f'postgresql+psycopg2://{USERNAME}:{PASSWORD}@{HOST}:{PORT}/{DATABASE}'

Base = automap_base()

engine= create_engine(DB_CONNECTION_STRING)
Base.prepare(engine, reflect=True, schema='dbo')

SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

def get_db_session():
    db_session = SessionLocal()
    
    try:
        yield db_session
    finally:
        db_session.close()