Python

I wanted to start my Flask app because I had already started working with Django and found it quite interesting that I could have a site up and running within 30 minutes of coding.

Table of Contents
1. Docker MySQL
2. Connecting with Python
3. Database Setup
4. Building the App (coming soon).

Docker MySQL

To get started with Docker and MySQL, I went ahead and ran the following. This will download MySQL 5.7 if it’s not already installed, opening port 3306, creating a database called flaskapi and setting the root password to secret. If you’re running MySQL in a production environment, you’ll want to make the password more secure.

Run docker ps -a to verify that the container is running.
The exec portion will allow me to run commands within MySQL-cli. You can exit the shell by hitting Ctrl+D on most operating systems. If Ctrl+D doesn’t do the job, use Ctrl+C.

🎷 ➜ docker run -d --name flaskapp-db -p 13306:3306 -v ./flaskapi/db/ -e MYSQL_ROOT_PASSWORD=secret -e MYSQL_DATABASE=flaskapp mysql:5.7
c37629dde9aeb6dafc1ea06d1da7485d7055e1f2d2e4ffb91391ce3c1dfb6546

🎷 ➜ docker ps -a
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS                    PORTS                                NAMES
c37629dde9ae        mysql:5.7           "docker-entrypoint.s…"   3 seconds ago       Up 2 seconds              33060/tcp, 0.0.0.0:13306->3306/tcp   flaskapp-db

🎷 ➜ docker exec -it c37 mysql -uroot -psecret

Now that we have verified our MySQL container is running, let’s connect to it using Python.

Using Python

If you don’t specify an IP address when creating your container, it’s likely you are going to use localhost or 127.0.0.1. Double check your container settings by running the following, careful to replace 2d with your container ID.

🎷 ➜ docker inspect -f '{{ (index (index .NetworkSettings.Ports "3306/tcp") 0).HostIp }}' c37
0.0.0.0

Or in Python

import docker

client = docker.DockerClient()
container = client.containers.get('2d')
host_ip = container.attrs['NetworkSettings']['Ports']['3306/tcp'][0]['HostIp']

Now that we have the IP address, we can continue connecting to our database. To test our connection to the database, we’re going to do a manual connection using Python. In the below example, replace the localhost, username, etc. with your database credentials and database name.

import mysql.connector

mydb = mysql.connector.connect(
      host="localhost",
      user="username",
      passwd="password",
      database="database_name"
)

Samples to execute MySQL Commands (in your python edior):

mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))")    
mycursor.execute("SHOW TABLES")

mycursor.execute("INSERT INTO customers (name, address) VALUES ('John', 'Highway 21')")    
mydb.commit() # Use this command after insert or update

Database Setup

Now that we’ve verified connection to the service, we need to login and create the necessary articles table. Do to this, login to the container using the following code

docker exec -it c37 mysql -uroot -psecret flaskapp
...
mysql> 

Now that you’re logged into MySQL CLI, run the following query to create the necessary articles table.

• id is an integer, 11 characters in length; auto increment becaues it’s the primary key (unique identifier)
• title is a variable character, 255 characters in length
• author is also a variable character, 255 characters in length
• body is TEXT, which has far greater character limits than VARCHAR
• create_date is a timestamp of the current time

CREATE TABLE articles (id INT(11) AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255), author VARCHAR(100), body TEXT, create_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP)

Now we have the table setup, stay tuned for part two where we build the app.