Set up MySQL in Docker

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:

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

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

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.

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:

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)

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

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:

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.

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

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