How to Setup a MySQL Server in Docker for Your Python Project

What will you learn?

Need to setup a SQL server? You don’t need to install it on your system. Use the prebuilt servers coming to Docker. In this video you will learn how to setup it up in Docker, connect to it, and some advantages of using Docker for this.

When you need a MySQL server, most think they need to install one. But luckily you don’t need that if you have Docker running.

Docker enables you to create a MySQL server in a few seconds. Wipe it and start all over, which is often necessary while testing. Also, you can have multiple MYSQL servers running simultaneously.

But how do you do all that. In this video you will learn how

  • Get get Docker
  • Setup a MySQL server in Docker
  • How to prepopulate it with tables
  • How to connect to it with Python – adding rows of data and reading the data.

Step 1: Install Docker Desktop if you don’t have it

Docker enables you to run a lot of services locally on your machine with simple commands. Also, they are only run in Docker so no real installation is done on your machine. This makes it very handy. This is what we will do with the MySQL server in this tutorial.

First of all you need make sure you have Docker installed.

You can install Docker Desktop and it will automatically install all you need to manage all Docker tools need.

Go to Docker official download and get Docker Desktop for your OS (here).

Follow the installation instructions and you are ready to go.

  • Linux Notice. Please notice that if you use Linux there is no Docker Desktop you will need to use command lines when we use the Desktop. For the most part we only use to see the containers and see what is running. This can be done from a command line as well.

You can launch Docker Desktop, which will start the Docker Daemon in the background.

Step 2: Clone the repository with the setup

To build the Docker image and setup the MySQL server, we need a few files.

You can get all the files from the GitHub here.

The Dockerfile contains the recipe of the image we will build.

FROM mysql/mysql-server
ENV MYSQL_DATABASE=DB \
    MYSQL_ROOT_PASSWORD=password \
    MYSQL_ROOT_HOST=%
ADD schema.sql /docker-entrypoint-initdb.d
EXPOSE 3306

This files describes all Docker needs to build an image, which you can run afterwards.

The first line contains the image from mysql/mysql-server which is the official MySQL server image (source).

Then it creates a Database and adds a schema.

Let’s explore the schema.

DROP DATABASE IF EXISTS `DB`;
CREATE DATABASE `DB`;
USE `DB`;
DROP TABLE IF EXISTS `Orders`;
CREATE TABLE `Orders` (
  `OrderTime` datetime,
  `Item` varchar(100) NOT NULL
);

Here you can change it for your needs. This one creates a simple database with one table Orders, with two columns OrderTime and Item.

Step 3: Build the Docker image and run it

You can build the Docker image by the following command.

docker build -t local-mysql .

And run it as follows.

docker run -dp 3306:3306 local-mysql

If you want to learn more about Docker for Python developers also read this guide.

This should start the MySQL server in your Docker Desktop.

Step 4: How to call your MySQL server

The file batch_process.py will insert a lot of rows into the MySQL database (the server).

import csv
from datetime import datetime
import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="password"
)
cur = mydb.cursor()
cur.execute("USE DB")

data_file = 'orders.csv'
with open(data_file, "r") as f:
    csv_reader = csv.DictReader(f)
    records = list(csv_reader)

for record in records:
    print(record)
    order_time = record['order_time']
    date_obj = datetime.strptime(order_time, '%Y-%m-%d %H:%M:%S.%f')
    item = record['item']
    sql_stmt = f"INSERT INTO Orders(OrderTime, Item) VALUES('{date_obj}', '{item}')"
    cur.execute(sql_stmt)
    mydb.commit()
cur.close()
mydb.close()

Please notice, that you need to have mysql-connector-python installed.

pip install mysql-connector-python

The code opens a CSV file orders.csv and inserts all the rows into the database.

To see what is in the database run the view_sql.py.

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="password"
)
cur = mydb.cursor()
cur.execute("USE DB")
sql_stmt = f"SELECT * FROM Orders"
cur.execute(sql_stmt)
response = cur.fetchall()
for row in response:
    print(row[0], row[1])
cur.close()
mydb.close()

This will show all the rows in the MySQL database running in your Docker.

Step 5: Understanding it all and a few notes

You can stop and start the local-mysql container and the content will be kept.

But if you delete it, and re-run the command, all the content in the database will be lost.

Why bother using Docker and not install a MySQL on your computer?

Well, you often need to produce code and be able to reproduce the setup on another machine. When you setup something locally, you forget what you did and some might be system specific. Hence, when you need to setup in production, it often needs to be done differently. This is where Docker is great. It does it all, it keeps the steps, it can be reproduced on all machines with Docker running.

As a professional Python developer, you need to start using Docker for all these kind of things. This will enable you to work as the highly paid developers do.

Want to learn more?

Get my book that will teach you everything a modern Python cloud developer needs to master.

Learn how to create REST API microservices that generate metrics that allow you to monitor the health of the service.

What does all that mean? 

Don’t wait, get my book and master it and become one of the sought after Python developers and get your dream job.

Leave a Reply

%d bloggers like this: