Using MySQL Databases With Python.

6 min readJan 28, 2021

MySQL is one of the most popular database management systems (DBMSs) on the market today. As most software applications need to interact with data in some form, programming languages like Python provide tools for storing and accessing these data sources.The database is deployed in production at some of the highest trafficked sites such as Uber, Twitter, Facebook and many others major organizations.

Advantages of Database Programming with Python

Platform-independent
Faster and more efficient
Portable
Support for relational database systems
Easy to migrate and port database application interfaces
Support for SQL cursors
It handles open and closed connections

Requirements:

This lab is demonstrating using MacOS. These are the resources I used.

Install Python → https://docs.brew.sh/Homebrew-and-Python

Install MySQL → https://flaviocopes.com/mysql-how-to-install/

Setup MySQL Workbench → https://www.youtube.com/watch?v=WTCrqRBUDT0

Sublime Text Editor (optional)→ https://www.sublimetext.com/

Virtual Environment:
If you’re having trouble with Python, try using a virtual environment where python 3.7 is already installed. It’s easier in my opinion.

Virtual Environment (optional)→ https://packaging.python.org/guides/installing-using-pip-and-virtual-environments/

Make Working Directory:

Create the working directory of mysql in your terminal

$ mkdir mysql
$ cd mysql
$ pwd
$ ls

2. Install mysql-connector which enables enables Python programs to access MySQL databases.

pip install mysql-connector

Connecting to Database:

Create python folder in your mysql directory. → database.py

We will always need this at the top the program to do execute mysql stuff fro python

It has to create an instance of the database. It is actually in the background running but we need to connect to it.
Host is the URL of database. Since it just our own computer we can type in localhost. When you setup MySQL the user name was root and then the password you assigned to it.
Print out the database.

Prove the database works and is setup correctly.

Run python3 database.py. There is better Unicode support in Python 3. In Python 3, text strings are Unicode by default.

python3 database.py

Error Message:

If you get this error message, it means it is not reading mysql-connector, but all good there is another way to connect.

2. This time try to install mysql-connector-python instead and should give you the object of the database.

$ pip install mysql-connector-python

3. Run python3 database.py and you should have the object of the database.

MySQL Workbench and Creating a Database:

Pull up mysql workbench.

this is the screen you should see, and look to the bottom left for your local instance.

Create a cursor.

Create a cursor. Similar to the curser in mysql. The cursor gives us the ability to have multiple separate working environments through the same connection to the database.
Always start out naming it my_cursor and we want it to execute the creation of a database and give it the name create database testdb.
It will now produce a database inside mysql workbench after running database.py.

python3 database.py

4. To print the database into our terminal we’ll create a For Loop to loop through the databases and print them all on screen.

5. Adding the [0] tuple will print it cleaner in the terminal.

6. A new database has now been created in MySQL.

Create a Table

Here we’ll create a table for our content inside the new mysql database we just created called “testdb”.

We have to let our program know that testdb is the only database we want to use. Scroll back to the top of database.py and the database “testdb”.

letting python know we only want to use testdb

Scroll back down and “create table” mysql is very straight forward.

python code for creating mysql table

We are going to create our table type with the name Users.
As the name suggests, varchar means character data that is varying. Also known as Variable Character, it is an indeterminate length string data type. It can hold numbers, letters and special characters.
The 255 is the number of these in that particular column, which happens to be the max amount of characters per column.
Ultimately we want a number unique for each row, that is why we are using User ID, to assign a unique ID.
Last run the database.py command.

$ python3 database.py

In MySQL click schemas, click users, then select rows 1000. You should then see an empty table similar to this with name, email, age, and user id.

Insert Records into the Table

Now we’re going to insert data into our table from python.

Create a variable which will be “sqlstuff” and now we need to insert the commands into the users table.
We then put three placeholders as (%s) which are similar to interpolation in a string.
The record1 is the information which will be in the database, however only the names are quotes and not the numbers, because the numbers will turn into strings.
Anytime we put changes into the database we have to commit them.

$ python3 database.py

Inside MySQL go to Schemas, then Tables, Users, and right click and select rows limit 1000.

Similar process when it comes to multiple records expect now we have to create a “list” in python and each item has to be different.

python code for inserting many records into mysql

There should now be a list of names with ages and user id in your MySql.

It is possible to also change things manually from inside of the MySql workbench,

Format the Results

Let’s make this table readable in the terminal.

python code for making data readable in terminal

We can’t put variable inside of a string so the %s creates a placeholder and then looks for next % sign and whatever is behind that %.
The \t is going to work as a tab button for us.
To help the formatting align we’ll manually edit the emails a bit.
Above the loop we have printed out the headers with email, age, and id.