How to Connect to mysql Docker from Python application on MacOS Mojave

How to Connect to mysql Docker from Python application on MacOS MojavethecruisyBlockedUnblockFollowFollowingMar 23Recently, I had difficulties of connecting to mysql docker hosted locally on my MacBook from local Python application.

And so, I thought to write this guide to help people save their time from trying to figure out how to do it.

In this article, I am going to walk you through the process end-to-end, from installing mysql docker all the way to writing Python script to connect to it.

Installing mysql DockerThis guide assumes you have already installed Docker on your MacBook and all the steps below are to be executed from your Terminal application.

I personally use iTerm2.

1.

Pulling mysql docker imageAs of the time of writing this, the latest version available is mysql 8.

0.

# you don't need to specify the `:latest` tag if you want the newest version available# if you want the previous version, just add `:5.

7` tag or whatever version that you're afterMacBook-Pro:~ username$ docker pull mysql:latestYou actually do not need to follow this step and can skip straight to step 2.

below.

But for me, I like to break it down so you know what is exactly going on.

After completing this step, all that has been done is storing a docker image of mysql on your local MacBook so you can build a docker container out of it.

2.

Running the mysql dockerYou can check if you have pulled the right image by executing docker image ls from your terminal.

If the docker pull was successful, you should see something like this.

REPOSITORY TAG IMAGE ID CREATED SIZEmysql latest 91dadee7afee 2 weeks ago 477MBThen, you can start the docker container by running the following.

(As mentioned in step 1, you can actually jump straight to below command.

It will automatically do docker pull to pull the mysql image)MacBook-Pro:~ username$ docker run –name=user_mysql_1 –env="MYSQL_ROOT_PASSWORD=root_password" -p 3306:3306 -d mysql:latestLet me explain the parameters in the command above:–name: this is optional.

It specifies the name of the running docker container–env: this is required, otherwise you would see the following error.

As from the error message, you can choose to specify any of the MYSQL_ROOT_PASSWORD, MYSQL_ALLOW_EMPTY_PASSWORD and MYSQL_RANDOM_ROOT_PASSWORD.

error: database is uninitialized and password option is not specifiedYou need to specify one of MYSQL_ROOT_PASSWORD, MYSQL_ALLOW_EMPTY_PASSWORD and MYSQL_RANDOM_ROOT_PASSWORD-p: this is to specify that you want to expose port 3306 in the docker container to the outside world on port 3306.

One thing to note is if you already have a running mysql, you would need to either specify a different port for the outside world or terminate the running mysql.

The format of this parameter is as follows hostPort:containerPort.

-d: this is to indicate you want to detach the running docker or in other words, running the docker in the background.

3.

Check if the docker is running properlyAfter the step 2 runs successfully, you can do docker ps to check if the mysql docker is running.

You should see the following.

CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES9dfda8bb31c2 mysql "docker-entrypoint.

s…" 10 seconds ago Up 8 seconds 0.

0.

0.

0:3306->3306/tcp, 33060/tcp user_mysql_1Notice the NAMES is user_mysql_1, this is specified by the –name option in docker run command.

Awesome, now you have a running mysql docker on your MacBook.

Congratulations!.Next, I will show you how to do a basic setup on the mysql inside the container so you can start connecting to it from your Python application.

Set Up mysql Inside DockerThe following steps will still be executed from your Terminal application.

1.

Access mysql from terminalTo access mysql inside your docker container, do the following.

MacBook-Pro:~ username$ docker exec -it user_mysql_1 mysql -uroot -proot_passwordNotice the root’s password is what is specified in the previous step 2 docker run above.

2.

Create a databaseNow, you are inside mysql as root user.

First, we’re going to create a database for you to work with.

Let’s name it test_db.

mysql> CREATE DATABASE test_db;3.

Create a table inside the new databaseHere, we will create a table so we can test our Python application later.

mysql> use test_db;mysql> CREATE TABLE test_table (userId INT NOT NULL AUTO_INCREMENT PRIMARY KEY, firstName VARCHAR(20), lastName VARCHAR(20));4.

Create a new userAfter the database has been created, let’s create a user to interact with that database.

As best practice, we should never use the root user to do our work.

We should only use root user if we really have to, e.

g.

to create a new database, to create a new user.

Let’s create a new user named newuser with password newpassword.

mysql> CREATE USER 'newuser'@'%' IDENTIFIED BY 'newpassword';Next, we will grant a sort of admin access to newuser to manage the database test_db.

mysql> GRANT ALL PRIVILEGES ON test_db.

* to 'newuser'@'%';Instead of specifying an ip address, notice the '%'.

This is to allow remote connection from anywhere via user newuser.

Awesome.

Now, you have already set everything up and we are ready to move on to our Python application.

Write Python Script to Connect to mysql DockerFor this guide, I am going to use sqlalchemy library to connect to mysql inside the docker container.

I also assume that you have set up your virtual environment for this project, so it’s easier for you to control dependencies and packages.

1.

Installing sqlalchemy libraryIf you have not installed this library on your environment, install it using pip from your Terminal as follows.

pip install sqlalchemy2.

Installing dependencyOne dependency for us that we need to install is pymysql library, since we are going to connect to mysql.

Same as above, we are going to use pip to install it on our environment.

pip install pymysql3.

Connect to mysql from PythonHere, I will show you that in just a few lines of code, we are going to connect to our mysql inside the docker container.

import sqlalchemy as db# specify database configurationsconfig = { 'host': 'localhost', 'port': 3306, 'user': 'newuser', 'password': 'newpassword', 'database': 'test_db'}db_user = config.

get('user')db_pwd = config.

get('password')db_host = config.

get('host')db_port = config.

get('port')db_name = config.

get('database')# specify connection stringconnection_str = f'mysql+pymysql://{db_user}:{db_pwd}@{db_host}:{db_port}/{db_name}'# connect to databaseengine = db.

create_engine(connection_str)connection = engine.

connect()# pull metadata of a tablemetadata = db.

MetaData(bind=engine)metadata.

reflect(only=['test_table'])test_table = metadata.

tables['test_table']test_tableThe output of test_table is as follows.

Table('test_table', MetaData(bind=Engine(mysql+pymysql://cruisy:***@localhost:3306/test_db)), Column('userId', INTEGER(display_width=11), table=<test_table>, primary_key=True, nullable=False), Column('firstName', VARCHAR(length=20), table=<test_table>), Column('lastName', VARCHAR(length=20), table=<test_table>), schema=None)That’s it!.You are now able to connect to mysql inside a docker container from your Python application.

I hope this guide helps and saves you some time from trying to figure it out yourselves.

Please give a clap if you find this article useful, leave a comment below if you have any questions or want to provide feedback or tell me what other guide you would like to see.

.. More details

Leave a Reply