Connecting MySQL with Python to Make a Database

MySQL is a database management systems commonly used for deploying cloud-native applications. Programming languages such as Python can be used to access and affect the database. Python is popular for this programming databases because it is platform-independent, fast, portable, and supportive of relational databases, SQL cursors, and opened or closed connections.

Today I will use a Python-Connector to connect Python & MySQL, and then I’ll make a test MySQL database. I gained inspiration to try this exercise from a former coach of mine, and you can find his original article here. Also W3 Schools was very helpful — especially this tutorial.

Setting Up

First I needed to establish Python, MySQL, MySQL Workbench, a working directory, MySQL Connector, and a project in an IDE:

• Python. Image 1 shows me confirming that I had an up-to-date version of Python3 on my computer.

Image 1: Confirming up-to-date Python v3.9.5

• MySQL. Image 2 shows me confirming that I had an up-to-date version of MySQL, and Image 3 shows that MySQL was started in my System Preferences.

Image 2: Confirming MySQL on my machine and for the correct MacOS version

Image 3: MySQL Running in System Preferences

• MySQL Workbench, which is the official GUI for MySQL. You can find relevant details for doing so on MacOS here. Image 4 shows me using the Database tab to make a connection, and Image 5 shows the interface after making a Standard (TCP/IP) connection.

Image 4: Going to MySQLWorkbench > Database > Connect to Database

Image 5: Python-MySQL-project-connection

• Working Directory: I then needed to make a working directory for our activity (Image 6).

Image 6: Making directory and confirming I’m in it

• MySQL-Connector: To ensure Python could connect to MySQL I install MySQL-Connector with the command pip install mysql-connector (Image 7). Note this step requires pip being installed as well.

Image 7: Successful install of MySQL-Connector

• Project in IDE: You will probably also want to use an IDE (I’m using PyCharm). As seen in Image 8, make sure when making the project in your IDE that the database.py file that we will use is in the mysql directory created earlier.

Image 8: Making the project in PyCharm

That concludes the prerequisites & set up.

Connecting to Database

We will start by making our database.py — you can find a relevant repo with a suitable database.py template here.

In our first 7 lines, we want to ensure we are executing MySQL from Python (Image 9). Note that you will need to populate your unique password for MySQL.

Image 9: Enables execution of MySQL from Python

I went to confirm that I was connected by going to mysql directory and executing the command python3 database.py. I received the output shown in Image 10, which shows that at this point we were not connected.

Image 10: Output showing lacking connection

In order to connect, I instead installed “MySQL Connector Python with the following command as seen in Image 11: pip install mysql-connector-python

Image 11: Installing MySQL Connector Python

Making the Database

Creating the DatabaseI ran python3 database.py and I was successfully connected (Image 12). Also as written in database.py, we printed information_schema, mysql, performance_schema, sys, and testdb.

Image 12: Running python3 database.py

Also as per the file in the repo, testdb was created which I confirmed in MySQL Workbench (Image 13).

Image 13: Confirming testdb created in MySQL Workbench

With that, we were successfully connected to MySQL & our test database was made.

More Capabilities

Next, if you wanted to create a table inside of the testdb database, you can clarify in the top of database.py that the only database with which you will be working is testdb (Image 14).

Image 14: Adding Line 7

From this point, you can build out your database with characteristics including but not limited to:

• Creating tables
• Inserting a record into a table
• Inserting multiple records into a table
• Pulling data from the table

Note that you can build all of this data out and have it printed in your Terminal by running python3 database.py — so the use of MySQL Workbench in the exercise is actually optional.

I’ll leave that work for another article, but if you want to see those tasks accomplished, I will again reference the article written by a former coach of mine here, and also the W3 School resources.

Thanks for reading.

Credits: I was pointed in the direction of this and many more exercises by the great people over at Level Up In Tech.