Python/Docker - Unknown server host 'db' when migrating

Question

I am trying to migrate a program using python manage.py db migrate in my Visual Studio terminal but when I run it I am getting an error named (2005, "Unknown server host 'db' (11001)"). I am also using the SQLAlchemy library as well to use it alongside flask to create my app. I have found some solutions on StackOverflow and GitHub like trying to recreate my network https://github.com/docker-library/mysql/issues/644 and trying to import MySQLdb and using MySQLdb.connect() "Unknown MySQL server host" when using Flask in Python, but when I try both of these solutions on my program using Visual Studio they don't work.

Here is the error that I am getting in it's full form:

Traceback (most recent call last):
  File "C:\Users\trevo\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\engine\base.py", line 2336, in _wrap_pool_connect
    return fn()
  File "C:\Users\trevo\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\pool\base.py", line 304, in unique_connection
    return _ConnectionFairy._checkout(self)
  File "C:\Users\trevo\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\pool\base.py", line 778, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "C:\Users\trevo\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\pool\base.py", line 495, in checkout
    rec = pool._do_get()
  File "C:\Users\trevo\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\pool\impl.py", line 241, in _do_get
    return self._create_connection()
  File "C:\Users\trevo\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\pool\base.py", line 309, in _create_connection
    return _ConnectionRecord(self)
  File "C:\Users\trevo\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\pool\base.py", line 440, in __init__
    self.__connect(first_connect_check=True)
  File "C:\Users\trevo\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\pool\base.py", line 661, in __connect
    pool.logger.debug("Error on connect(): %s", e)
  File "C:\Users\trevo\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\util\langhelpers.py", line 68, in __exit__
    compat.raise_(
  File "C:\Users\trevo\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\util\compat.py", line 182, in raise_
    raise exception
  File "C:\Users\trevo\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\pool\base.py", line 656, in __connect
    connection = pool._invoke_creator(self)
  File "C:\Users\trevo\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\engine\strategies.py", line 114, in connect
    return dialect.connect(*cargs, **cparams)
  File "C:\Users\trevo\AppData\Local\Programs\Python\Python39\lib\site-packages\sqlalchemy\engine\default.py", line 493, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "C:\Users\trevo\AppData\Local\Programs\Python\Python39\lib\site-packages\MySQLdb\__init__.py", line 123, in Connect
    return Connection(*args, **kwargs)
  File "C:\Users\trevo\AppData\Local\Programs\Python\Python39\lib\site-packages\MySQLdb\connections.py", line 185, in __init__
    super().__init__(*args, **kwargs2)
MySQLdb._exceptions.OperationalError: (2005, "Unknown server host 'db' (11001)")

Here are my required files respectivly:

Dockerfile:

FROM python:3.9
ENV PYTHONUNBUFFERED 1
WORKDIR /app
COPY requirements.txt /app/requirements.txt
RUN pip install -r requirements.txt
COPY . /app
CMD python main.py

requirements.txt:

Flask==1.1.2
Flask-SQLAlchemy==2.4.4
SQLAlchemy==1.3.20
Flask-Migrate==2.6.0
Flask-Script==2.0.6
Flask-Cors==3.0.9
requests==2.25.0
werkzeug==1.0.1
mysqlclient==2.1.0
pika==1.1.0
itsdangerous==2.0.1
wolframalpha==5.0.0

docker-compose.yml:

version: '3.8'
services:
  backend:
    build:
      context: .
      dockerfile: Dockerfile
    command: 'python main.py'
    ports:
      - 8001:5000
    volumes:
      - .:/app
    depends_on:
      - db

  queue:
    build:
      context: .
      dockerfile: Dockerfile
    command: 'python -u consumer.py'
    depends_on:
      - db

  db:
    image: mysql:5.7.22
    restart: always
    environment:
      MYSQL_DATABASE: veganetworkscript
      MYSQL_USER: root
      MYSQL_PASSWORD: root
      MYSQL_ROOT_PASSWORD: root
    volumes:
      - .dbdata:/var/lib/mysql
    ports:
      - 33068:3306

And finally my manage.py file:

from main import app, db
from flask_migrate import Migrate, MigrateCommand
from flask_script import Manager

migrate = Migrate(app, db)

manager = Manager(app)
manager.add_command('db', MigrateCommand)

if __name__ == '__main__':
    manager.run()

main.py:

from flask_cors import CORS
from sqlalchemy import UniqueConstraint
from vegaaimain.vegamain import main
from flask import Flask, jsonify
from flask_sqlalchemy import SQLAlchemy
from dataclasses import dataclass
app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = 'mysql://root:root@db/veganetworkscript'
CORS(app)

db = SQLAlchemy(app)
 

class Product(db.Model):
    id: int
    title: str
    image: str

    id = db.Column(db.Integer, primary_key=True, autoincrement=False)
    title = db.Column(db.String(200))
    image = db.Column(db.String(200))


class VegaScriptRun(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=False)
    title = db.Column(db.String(200))
    description = db.Column(db.String(400))
    image = db.Column(db.String(200))
    main()

class ProductUser(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer)
    product_id = db.Column(db.Integer)

    UniqueConstraint('product_id', 'user_id', name='user_product_unique')
@app.route('/api/products')
def index():
    return jsonify(Product.query.all())

if __name__ == '__main__':
    app.run(debug=True, host='0.0.0.0')

What exactly am I missing or potentially doing wrong here, thank you!

Answer 1

Make changes as follow and it should work:

app.config["SQLALCHEMY_DATABASE_URI"] = 'mysql://root:root@127.0.0.1:33068/veganetworkscript'

But this will only work for migration when your docker container is not running. You can reach the host machine within the container on ip address 172.17.0.1. So your database URI should be

app.config["SQLALCHEMY_DATABASE_URI"] = 'mysql://root:root@172.17.0.1:33068/veganetworkscript'

Also checkout this link https://www.digitalocean.com/community/tutorials/how-to-use-flask-sqlalchemy-to-interact-with-databases-in-a-flask-application