Unable to connect to mysql using docker-compose

I am trying to connect to MySql DB using a python script ingested via docker. I have the following compose file:

version: '3.9'

services:
  mysql_db:
    image: mysql:latest
    restart: unless-stopped
    environment:
      MYSQL_DATABASE: ${MY_SQL_DATABASE}
      MYSQL_USER: ${MY_SQL_USER}
      MYSQL_PASSWORD: ${MY_SQL_PASSWORD}
      MYSQL_ROOT_PASSWORD: ${MY_SQL_ROOT_PASSWORD}
    ports:
      - '3306:3306'
    volumes:
      - ./mysql-data:/var/lib/mysql

  adminer:
    image: adminer:latest
    restart: unless-stopped
    ports:
      - 8080:8080
  
  ingestion-python:
    build:
      context: . 
      dockerfile: ingestion.dockerfile
    depends_on:
      - mysql_db

Adminer connects to MySql with success. Then I created the following ingestion script to automate a criação de uma tabela. My ingestion script is:

from dotenv import load_dotenv
import os
import pandas as pd
from sqlalchemy import create_engine


def main():
   
    load_dotenv('.env')
    
    user = os.environ.get('MY_SQL_USER')
    password = os.environ.get('MY_SQL_PASSWORD')
    host = os.environ.get('MY_SQL_HOST')
    port = os.environ.get('MY_SQL_PORT')
    db = os.environ.get('MY_SQL_DATABASE')
    table_name = os.environ.get('MY_SQL_TABLE_NAME')


    print(f'mysql+pymysql://{user}:{password}@{host}:{port}/{db}')

    engine = create_engine(f'mysql+pymysql://{user}:{password}@{host}:{port}/{db}')

    df = pd.read_csv('./data/data.parquet', encoding='ISO-8859-1', on_bad_lines='skip', engine='python')
    df.to_sql(name=table_name, con=engine, if_exists='append')


if __name__ == '__main__':
    
    main()

When I run my docker compose (docker-compose up -d) file I get:

2023-02-14 08:58:59 sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2003, "Can't connect to MySQL server on 'mysql_db' ([Errno 111] Connection refused)")
2023-02-14 08:58:59 (Background on this error at: https://sqlalche.me/e/20/e3q8)

The credentials and connections are retrieved from my .env file:

#MYSQL CONFIG
MY_SQL_DATABASE = test_db
MY_SQL_USER = data
MY_SQL_PASSWORD = random
MY_SQL_ROOT_PASSWORD = root

#PYTHON INGESTION
MY_SQL_HOST = mysql_db
MY_SQL_PORT = 3306
MY_SQL_TABLE_NAME = test_table

Why I can't connect to MySql DB using my python script?

This is most likely a timing problem - your ingestion container is starting before the database in the mysql container is ready. The depends_on only waits for the start of the mysql container, not on the database actually being ready to accept connections.

You might want to check the log outputs from the containers to see when the database is actually ready to accept connections, and include some delay into the ingestion container. Another option would be to try opening the connection in a loop with enough retries and some timeout between retries so that you can start as soon as the database is ready.

You should set the hostname in your docker compose file:

mysql_db:
  hostname: "mysql_db"
  image: mysql:latest
  restart: unless-stopped
  environment:
    MYSQL_DATABASE: ${MY_SQL_DATABASE}
    MYSQL_USER: ${MY_SQL_USER}
  MYSQL_PASSWORD: ${MY_SQL_PASSWORD}
  MYSQL_ROOT_PASSWORD: ${MY_SQL_ROOT_PASSWORD}
  ports:
    - '3306:3306'
  volumes:
    - ./mysql-data:/var/lib/mysql

But as fallback you can also try the default hostname:port exposed in docker as connection string since you don't have a network set up:

MY_SQL_HOST = host.docker.internal
MY_SQL_PORT = 3306
MY_SQL_TABLE_NAME = test_table