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.
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.
- 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.
- 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.
- Working Directory: I then needed to make a working directory for our activity (Image 6).
- 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.
- 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.
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.
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.
In order to connect, I instead installed “MySQL Connector Python with the following command as seen in Image 11: pip install 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.
Also as per the file in the repo, testdb was created which I confirmed in MySQL Workbench (Image 13).
With that, we were successfully connected to MySQL & our test database was made.
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).
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.
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.