Storing Data in Our MySQL Database via Our GUI

In this chapter, we will learn how to install and use a MySQL database and connect it to our GUI.

MySQL is a full-fledged Structured Query Language (SQL) database server and comes with a very nice GUI of its own so that we can view and work with the data. We will create a database, insert data into our database, and then see how we can modify, read, and delete data.

Data storage in a SQL database is essential for software programs written in Python. All of our data currently only exists in memory and we want to make it persistent so that we do not lose our data once we close our running Python program.

Here, you will learn how to increase your programming skills by adding SQL to your programming toolbox.

The first recipe in this chapter will show you how to install the free MySQL Community Edition.

After successfully connecting to...

Installing and connecting to a MySQL server from Python

Before we can connect to a MySQL database, we have to connect to the MySQL server. In order to do this, we need to know the IP address of the MySQL server as well as the port it is listening on.

We also have to be a registered user with a password in order to be authenticated by the MySQL server.

Getting ready

You will need to have access to a running MySQL server instance, as well as have administrator privileges in order to create databases and tables.

How to do it...

Let's look at how to install and connect...

Configuring the MySQL database connection

In the previous recipe, we used the shortest way to connect to a MySQL server, that is, by hardcoding the credentials that are required for authentication in the connect method. While this is a fast approach for early development, we definitely do not want to expose our MySQL server credentials to anyone. Instead, we want to grant permission to specific users so that they can access databases, tables, views, and related database commands.

A much safer way to be authenticated by a MySQL server is by storing the credentials in a configuration file, which is what we will do in this recipe. We will use our configuration file to connect to the MySQL server and then create our own database on the MySQL server.

We will use this database in all of the recipes in this chapter.

...

Designing the Python GUI database

Before we start creating tables and inserting data into them, we have to design the database. Unlike changing local Python variable names, changing a database schema once it has been created and loaded with data is not that easy.

We would have to DROP the table, which means we would lose all the data that was in the table. So, before dropping a table, we would have to extract the data, save the data in a temporary table or other data format, and then DROP the table, recreate it, and finally reimport the original data.

I hope you are getting the picture of how tedious this could be.

Designing our GUI MySQL database means that we need to think about what we want our Python application to do with it and then choose names for our tables that match the intended purpose.

...

Using the SQL INSERT command

This recipe presents the entire Python code that shows you how to create and drop MySQL databases and tables, as well as how to display the existing databases, tables, columns, and data of our MySQL instance.

After creating the database and tables, we will insert data into the two tables we will create in this recipe.

We are using a primary key to foreign key relationship to connect the data of the two tables.

We will go into detail about how this works in the following two recipes, where we will modify and delete the data in our MySQL database.

Getting ready

This recipe builds on the MySQL database we created in the previous recipe, Designing the Python GUI database, and also shows you how to...

Using the SQL UPDATE command

This recipe will use the code from the previous recipe, Using the SQL INSERT command, explain it in more detail, and then extend the code to update the data.

In order to update the data that we previously inserted into our MySQL database tables, we need to use the SQL UPDATE command.

Getting ready

This recipe builds on the previous recipe, Using the SQL INSERT command, so read and study the previous recipe in order to follow the code in this recipe, where we will modify the existing data.

How to do it...

Let's take a look at how we can...

Using the SQL DELETE command

In this recipe, we will use the SQL DELETE command to delete the data we created in the previous recipe, Using the SQL UPDATE command.

While deleting data might sound trivial at first, once we get a rather large database design in production, things might not be that easy any more.

Because we have designed our GUI database by relating two tables via a primary to foreign key relation, when we delete certain data, we do not end up with orphan records because this database design takes care of cascading deletes.

Getting ready

This recipe uses the MySQL database, tables, and the data that was inserted into those tables from the previous recipe, Using the SQL UPDATE command. In order to demonstrate...

Storing and retrieving data from our MySQL database

We will use our Python GUI to insert data into our MySQL database tables. We already refactored the GUI we built in the previous recipes in preparation for connecting and using a database.

We will use two textbox Entry widgets, into which we can type the book or journal title and the page number. We will also use a ScrolledText widget to type our favorite book quotations into, which we will then store in our MySQL database.

Getting ready

This recipe will build on the MySQL database and tables we created in the previous recipes of this chapter.

How to do it...

...

Using MySQL Workbench

MySQL has a very nice GUI that we can download for free. It's called MySQL Workbench.

In this recipe, we will successfully install Workbench and then use it to run SQL queries against the GuiDB we created in the previous recipes.

Getting ready

In order to use this recipe, you will need MySQL database we developed in the previous recipes. You will also need a running MySQL server.

How to do it...

We can download MySQL Workbench from the official MySQL website: https://dev.mysql.com/downloads/workbench/.

Let's look at how we can perform this...