Can't connect python server and mysql with Docker compose

Question

I am newly learning Docker and compose, I have searched and watched many tutorials but could not understand what is causing this connection error:

_mysql_connector.MySQLInterfaceError: Can't connect to MySQL server on 'localhost:3306' (99)

my file structure: docker-compose.yml .env mysql/ database.sql Dockerfile python/ server.py Dockerfile

docker-compose.yml:

version: '3.8'

services:

  mysql:
    build: 
      context: ./mysql
      dockerfile: Dockerfile
    restart: always
    environment:
      MYSQL_DATABASE: ${MYSQL_DATABASE}
      MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD}
    ports:
      - '3306:3306'

  pythonapp:
    build:
      context: ./python
      dockerfile: Dockerfile
    depends_on:
      - mysql
    ports:
      - '8080:8080'

python/Dockerfile:

FROM python:3.11-slim

RUN pip install mysql-connector-python && \
    pip install python-dotenv

COPY . /app

WORKDIR /app

CMD ["python", "-u", "server.py"]

EXPOSE 8080

mysql/Dockerfile:

FROM mysql:latest

COPY ./database.sql /docker-entrypoint-initdb.d/

python/server.py:

from http.server import HTTPServer, BaseHTTPRequestHandler
from dotenv import load_dotenv
import mysql.connector
import json
import os 

load_dotenv('../.env')

# Establish connection to MySQL
db_connection = mysql.connector.connect(
    user=os.getenv('MYSQL_USER'), 
    password=os.getenv('MYSQL_ROOT_PASSWORD'), 
    host=os.getenv('_HOST'),
    port=3306,
    database=os.getenv('MYSQL_DATABASE'))
print("DB connected")
...

.env:

_HOST=localhost
MYSQL_DATABASE=db
MYSQL_USER=root
MYSQL_ROOT_PASSWORD=password

found this: PyMySQL can't connect to MySQL on localhost and:

I have tried _HOST=127.0.0.1, _HOST=mysql, _HOST=host.docker.internal but the error did not change. Also tried to compose for second time to solve connection maybe because the database was not ready for connection, but getting the same error. So the MySQL database is running but the python server does not connect.

Answer 1

First of all, localhost is always wrong host for the db when using docker compose like this. localhost points to the localhost of the python container, not to the localhost of your host machine. You need to refer to it with the service name mysql.

_HOST=mysql

Then, in addition to the answer by Leo Aso, you might need to wait until the MYSQL service is healthy and can be connected to. The depends_on alone is not enough, and it will try to connect to the DB before it is ready. This works for me

version: '3.8'

services:

  mysql:
    image: mysql:latest
    environment:
      MYSQL_DATABASE: ${MYSQL_DATABASE}
      MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD}
    healthcheck:
        test: ["CMD", "mysqladmin" ,"ping", "-h", "localhost"]
        timeout: 20s
        retries: 10

  pythonapp:
    build:
      context: .
      dockerfile: Dockerfile
    depends_on:
      mysql:
        condition: service_healthy

I added a healthcheck to the mysql service and condition: serivce_healthy to the python service, so that it will not start until mysql is reporting healthy.

Bonus: How to debug

If it's still not working, first verify that you can connect to the db at all even inside the mysql container, by running this command:

docker compose exec -it mysql mysql -ppassword

Answer 2

You are calling load_dotenv('../.env'), but your .env file only exists locally; there is no such file inside the Docker container.

You will have to find a way to get it there somehow. The easiest way would be to move the .env file inside the Python folder, then COPY it in your Dockerfile, and use load_dotenv('./.env') in your code.