First, we move the widgets from our Python GUI around between the two tabs we created in the previous recipes in order to organize our Python GUI better to connect to a MySQL database.

We rename several widgets and separate the code that accesses the MySQL data to what used to be named Tab 1, and we will move unrelated widgets to what we called Tab 2 in earlier recipes. We also adjust some internal Python variable names in order to understand our code better.

Our refactored Python GUI now looks like the following screenshot. We have renamed the first tab as MySQL and created two tkinter LabelFrame widgets. We labeled the one on the top Python database and it contains two labels and six tkinter entry widgets plus three buttons, which we aligned in four rows and three columns using the tkinter grid layout manager. We will enter book titles and pages into the entry widgets, and clicking the buttons will result in either inserting, retrieving, or modifying book quotations. The LabelFrame at the bottom has a label of Book Quotation and the ScrolledText widget that is part of this frame will display our books and quotations:

GUI_MySQL.py

We will create two SQL tables to hold our data. The first will hold the data for the book title and book page, and then, we will join with the second table, which will hold the book quotation. We will link the two tables together via primary to foreign key relations.

So, let's create the first database table now. Before we do that, let's verify first that our database does, indeed, have no tables. According to the online MySQL documentation, the command to view the tables that exist in a database is as follows:

SHOW [FULL] TABLES [{FROM | IN} db_name] 
    [LIKE 'pattern' | WHERE expr]

It is important to note that, in the preceding syntax, arguments in square brackets, such as FULL, are optional while arguments in curly braces, such as FROM, are required for the SHOW TABLES command. The pipe symbol between FROM and IN means that the MySQL syntax requires one or the other:

# unpack dictionary credentials  
conn = mysql.connect(**guiConf.dbConfig) 
# create cursor  
cursor = conn.cursor() 
# execute command 
cursor.execute("SHOW TABLES FROM guidb") 
print(cursor.fetchall()) 

# close connection to MySQL 
conn.close()

When we execute the SQL command in Python, we get the expected result, which is an empty tuple showing us that our database currently has no tables:

GUI_MySQL_class.py

We can also first select the database by executing the USE <DB> command and then we don't have to pass it into the SHOW TABLES command because we have already selected the database we want to talk to. The following code creates the same true result as the previous one:

cursor.execute("USE guidb") 
cursor.execute("SHOW TABLES")

Now that we know how to verify that our database has no tables, let's create some. After we have created two tables, we will verify that they have truly made it into our database by using the same commands as before.

We create the first table, named Books, by executing the following code:

# connect by unpacking dictionary credentials 
conn = mysql.connect(**guiConf.dbConfig) 

# create cursor  
cursor = conn.cursor() 

# select DB 
cursor.execute("USE guidb") 

# create Table inside DB 
cursor.execute("CREATE TABLE Books (        
      Book_ID INT NOT NULL AUTO_INCREMENT,  
      Book_Title VARCHAR(25) NOT NULL,      
      Book_Page INT NOT NULL,               
      PRIMARY KEY (Book_ID)                 
    ) ENGINE=InnoDB") 

# close connection to MySQL 
conn.close()

We can verify that the table has been created in our database by executing the following commands:

GUI_MySQL_class.py

Now the result is no longer an empty tuple but a tuple that contains a tuple, showing the books table we just created.

We can use the MySQL command-line client to see the columns in our table. In order to do this, we have to log in as the root user. We also have to append a semicolon to the end of the command.

If you don't have a shortcut on your desktop, you can find the executable at the following path for a typical default installation:

C:Program FilesMySQLMySQL Server 5.7inmysql.exe

Without a shortcut to run the MySQL client, you have to pass it some parameters:

• C:Program FilesMySQLMySQL Server 5.7inmysql.exe
• -u root
• -p

Either double-clicking the shortcut or using the command line with the full path to the executable and passing in the required parameters, will bring up the MySQL command-line client, which prompts you to enter the password for the root user:

If you remember the password you assigned to the root user during the installation, you can then run the SHOW COLUMNS FROM books; command, as shown in the following screenshot. This will display the columns of our books table from our guidb:

Next, we will create the second table, which will store the book and journal quotations. We will create it by executing the following code:

# select DB 
cursor.execute("USE guidb") 

# create second Table inside DB 
cursor.execute("CREATE TABLE Quotations (  
        Quote_ID INT,                      
        Quotation VARCHAR(250),            
        Books_Book_ID INT,                 
        FOREIGN KEY (Books_Book_ID)        
            REFERENCES Books(Book_ID)      
            ON DELETE CASCADE              
    ) ENGINE=InnoDB")

Executing the SHOW TABLES command now shows that our database has two tables:

GUI_MySQL_class.py

We can see the columns by executing the SQL command using Python:

GUI_MySQL_class.py

Using the MySQL client might present the data in a better format. We could also use Python's pretty print (pprint) feature:

GUI_MySQL_class.py

The MySQL client still shows our columns in a clearer format, which can be seen when you run this client.