How to: connect to MySQL running in Docker from outside, using SQLAlchemy

For those using Docker to interact with a database, it may be interesting to know that SQLAlchemy has a nice integration with it, and allows to connect to the DB hosted in Docker in a easy way.

Below there is an example that shows how we can connect to a MySQL database, running on Docker, from Python code, using the SQLAlchemy library

This is a bare example on how to setup a connection, that will not go deep in the possibilities that SQLAlchemy offers for interaction.

Let’s go!


What do we need

This guide assumes that you have Docker installed in your OS, if you don’t just follow the simple steps on Docker website.

https://www.docker.com/get-docker

Don’t worry about prices, Docker Community Edition will just do 🙂

We will need Kitematic to interact easily with the containers so will be good to download it as well.

https://kitematic.com

Screen Shot 2018-02-18 at 00.33.03

Once we download Kitematic, we run it to see the clean interface

Set up MySQL in Docker

First we will need to run Kitematic to start adding containers

This is a very nice 1:30 video showing how easy is to run Kitematic

After Kitematic is running, we will proceed to download the MySQL container. We simply type MySQL in the search box and choose the official one:

docker-mysql-box

Once is downloaded, it will appear in the list of containers, so we can just run it from there, clicking on the button Run.

docker-mysql-running

Almost!

By default MySQL needs one of three parameters regarding passwords to be specified on the Docker container:

  • MYSQL_ROOT_PASSWORD
  • MYSQL_ALLOW_EMPTY_PASSWORD
  • MYSQL_RANDOM_ROOT_PASSWORD

Without digging too much on this options, let’s choose MYSQL_ROOT_PASSWORD which will allow us specify a password for the root user.

Now go to the Settings tab of the container, and under General, we will add a new Environment Variable called MYSQL_ROOT_PASSWORD. The value of the password can be set up to any value of our choice

docker-env-variables

Now there is one important step to do. We need to tell Docker we want to allow connections to MySQL from outside. That will allow us to write Python code (or any other!) and query this database from our machine (or anywhere!) and not only using the MySQL terminal in Docker.

Let’s go to the Hostname/Ports tab under Settings, in the container, and let’s configure the published port for Mysql to be localhost:3306. We will use this port to make the connection later from SQLAlchemy.

docker-ports

All is setup now, if we move to the Home tab of MySQL container, everything should be running normally. The last lines displayed in the terminal should look something like this:

docker-mysql-runs-in-terminal

MySQL is correctly Setup.

Now let’s create a user to access this database and make our queries

 

Creating a User in MySQL

This is not a hard task, nevertheless, in order for SQLAlchemy to make queries from the outside, we need to create a user with special privileges in MySQL that will allow the connection to go through.

First of all, let’s click on the EXEC button that appears in the MySQL container, so we have access to the terminal.

To connect to the database, we will type:

mysql -uroot -p

And then proceed to type our password (same as we specified on the previous step)

terminal-mysql

If all goes good, the database will grant us access. Note you can use the root user as well to make the connection.

Now let’s create this user we are talking about. For brevity, let’s grant him all privileges to connect, without worrying too much about security, that will be for a future chapter 🙂

This is the commands we have to run

CREATE USER 'username'@'%' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' WITH GRANT OPTION;

FLUSH PRIVILEGES;

Again, if all goes good, MySQL will swallow this commands and create a very powerful user for us

Screen Shot 2018-02-18 at 01.02.43

 

Create a new project in Python

Let’s create a new project, and set up a virtual environment for it to avoid screwing up the global installation of Python with our dirty SQLAlchemy packages

Once we have this, we pip install our requirements, which in this case is just this two fellas:

pip install SQLAlchemy​

pip install mysql-connector-python

SQLAlchemy acts as an ORM to communicate with the MySQL database.

MySQL connector is an adapter to ease the connection task to MySQL through SQLAlchemy.

Once they are installed, we move to the sexy stuff… Let’s finally write some Python code!

connectdb.py

Start by adding a file called connectdb.py or any other name you find suit. Inside, we are going to write a very bare connection to the database which will do nothing yet:

from sqlalchemy import create_engine

engine = create_engine('mysql+mysqlconnector://newuser:newpassword@localhost:3306')

The important parts here are:

  • First we are calling create_engine which allows us to connect to any database using SQLAlchemy as intermediary
  • Then, we create the connection string which contains:
  • mysql+mysqlconnector Database and Database adapter, respectively
  • newuser:newpassword The username and password we setup in MySQL
  • @localhost:3306: The port we setup in Docker container

So we have beautifully put together Docker, MySQL and Python. Now time to test if this works!

Let’s create a very simple piece of code that will iterate through the existing databases and print their names, this way we will know if connections are allowed in the Docker container from the outside.

with engine.connect() as con:

    statement = ("show databases;")

    result = con.execute(statement)

    for row in result:
        print("db:", row[0])

Now we just need to run the script, and hopefully, we will see a number of database names printing in the console

Screen Shot 2018-02-18 at 01.25.57.png

If you see a list of database names, CONGRATULATIONS!

You managed to configure properly a project with SQLAlchemy to connect to a MySQL database hosted in Docker.

If you receive an error in this last step, something like ‘Access denied for user …’ or similars, you have to review if there is no typo on the connection string, if you created the user correctly on MySQL, or if Docker has the port correctly setup to allow connections from localhost