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?
2
Answers
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:
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: