How to Simulate and Connect to a MySQL database with Python and Docker

In this post, I am going to show you how to simulate a MySQL database on your local computer using Docker, then connect to it using pyodbc

One of the most frustrating aspects of working with relational databases is the installation process. Usually, with other technologies like AI every model and tool can be accessed with a single line of code. However, if we want to simulate databases for either experimentation of development purposes, we have no other choice but to perform a manual installation.

The problem with this approach is that is:

• Heavy
  Up to 4GB in space depending on the database you are installing
• Complex
  Too many installation steps and likely the need to search for more than a single installation file
• Time-consuming
  Installation time can range from 15 minutes to 2 hours, in case of unexpected errors

In this post, I am going to teach you how you can quickly simulate a MySQL database in a few seconds with just two lines of code, and then connect to it using a python library called pyodbc. In this way, you will be able to run SQL queries on this database using python directly. Although python is not the best language for interacting with a relational database, it is still something you will need for development. For example, if you are developing the backend of an application using python, a data science project that needs to fetch data from an relational database, or a simple API system to interact with SQL, in this way you can connect the projects that you are working on directly with your database, simulating the storage of millions of data points and running complex queries.

This approach will save you hours of time in installation.

Prerequisites

To execute this process, you will need the following items installed and running on your computer:

• Docker installed
• Docker Hub account
• Python
• ODBC drivers

If you are thinking why should you bother to learn about ODBC drivers and Docker just to simulate a database while you can just install it with an .exe, know that soon or late in your developer journey you will need to have these tools sorted out, installed and running on your pc. Docker is the industry standard for running containers, so better start with it sooner than later.

Docker installation

If you have not installed Docker yet, you can download it from this link. The easiest way to simulate a database is to download a container image of it (do not worry if you do not know what it is) and activate it with just two lines of code.

Dockerhub registration

The other requirement for this tutorial is a Docker Hub account (free subscription will be fine). The reason why you need this account is that you will need to download an image that has been developed by the community, and to make any pull from docker hub they will ask you for you own account.

ODBC

To connect to any database from you computer (even if you install MySQL with an .exe file), you will need to install the right ODBC drivers. ODBC is an API system to connect to the database from your pc.

You can download the MySQL ODBC 8.0 ANSI Driver from this link, installation will only take a few seconds. To check whether the installation has been successful, you can access the ODBC interface from Programs:

Here, you will see a list of Drivers, MySQL ODBC 8.0 ANSI Driver should be among them. You do not need to do anything (like adding it), just make sure it is there.

Simulating MySQL 

Once you have all the prerequisites sorted out, we can start simulating MySQL. Make sure you have Docker desktop open, docker containers cannot run if Docker desktop is not running. Open your terminal (if you are running windows is called Command Prompt, and you can access it from Programs) and run the following two lines of code:

docker pull mysql:8.0.30
docker run --name some-db -e MYSQL_DATABASE=some-db -e HOSTNAME=127.0.0.1 -e MYSQL_ROOT_PASSWORD=1234 -e MYSQL_PASSWORD=1234 -e MYSQL_USER=user1 -d -p 3306:3306 mysql:8.0.30

With the first line of code, Docker will download the container image that contains the MySQL database. With the second line of code, we are going to run the database with our custom credentials. Note that there are several things that can go wrong by using a soft password (ex. helloworld), so I strongly suggest to note change these credentials, at least at your first try.

This container will use the Docker port 3306, and we are going to redirect it to our localhost port 3306 by using: 3306:3306.

If everything worked like it should, you should see the screen above: the container some-db is running on port 3306.

Connecting with Python

Now that our database is up and running, we are ready to connect with it using python. We will be using a library called pyodb, if you haven’t installed it yet, you can do it using the pip command on your terminal:

!pip install pyodbc==4.0.32

To achieve the connection, you will need to run the following lines of code.

server = 'localhost:3306' 
database = 'some-db' 
username = 'ciao' 
password = '1234' 
conn = pyodbc.connect(
    'DRIVER={MySQL ODBC 8.0 ANSI Driver};SERVER='\
    +server+';DATABASE='\
    +database+';UID='\
    +username+';PWD='\
    +password)
cursor = conn.cursor()

Run some SQL queries

If the code does not throw any error, it means the connection has been executed correctly. We can run these simple SQL queries to test it: some simple data will be added to the database and then we will retrieve it with the last command:

cursor.execute('DROP TABLE IF EXISTS customers')
cursor.execute('CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))')
cursor.execute('INSERT INTO customers (name, address) VALUES (1, 2)')
cursor.execute('SELECT * FROM customers')
cursor.fetchall()
\
[('1', '2')]

As we can see, we are able to fetch the data we just put into our database and print it on our screen.