How to use database connector (MySQL) and GUI development (Tkinter) to create an interactive application with Python

Published in

Predict

7 min readJan 15, 2023

Let’s learn python programming concepts by developing a GUI application to store, search and view data from MySQL database.

What to expect in this blog?

You’ll be exploring two primary areas in this blog -

Database operations — You’ll connect to a database to perform functions like creating a new table, inserting new records and searching records. You’ll use the MySQL database and MySQL connector libraries of Python.
GUI application development — You’ll create a small GUI application with the Tkinter library to perform the database functions with moderate user interaction.

Some key points you’ll be exploring with this code -

Execute script alone or as a module
Python class, methods and variables
Using error handling with try and except
Using MySQL library methods to store and retrieve records
Using the Tkinter library and components
Using Treeview control of Tkinter to print table rows
Using the “showinfo()” function to show an alert message.

Let’s plan for the work

Before starting the design and coding, let’s understand the requirement clearly.

User interaction involved

Users should be able to choose the mode of accessing the tool first — either for entering book details (let’s call it “Book entry”) or for searching and viewing book details (let’s call it “Book monitor”).
During the “Book entry” mode of usage, users should be able to see the fields clearly that need to be filled for creating a new record in the database.
During the “Book monitor” mode of usage, users should have the choice to search with criteria or show all (up to the top 500) records.
During the “Book monitor” mode of usage, users should be able to input criteria values for searching for a book from the database.
During the “Book monitor” mode of usage, users should be shown the results retrieved from the database in a tabular manner.
During the “Book monitor” mode of usage, users should be able to click on one record to see full details of the record on an alert message.
Users should be shown a clear message on the application to convey any instruction, error or information while creating a new record.
Users should be able to go back to the previous page where the mode of usage can be chosen.

Essential processing involved supporting the user interaction

While inserting a new record, create the required table if it does not exist already.
While inserting a new record, check for duplicate records in the database.
While searching for existing records, check whether the required criteria fields are provided.
Catch errors from database operations to show appropriate messages.

High-level design thinking

Now you can plan the required classes, methods and attributes.

SQLHandler class

You need a class to handle the interaction with the database. This is necessary so that you do not need to update your user interface code if there is a change in database connectivity or logic. For example, if you need to interact with a different database in future (maybe Oracle or MongoDB), you’ll only update your SQLHandler class.

This class will have below methods -

__init__() — This will create the connection to the database before any operation.
create_book_table() — This method will create the required table in the database if it does not exist already.
insert_book_to_db() — This method will create a new record in the database table.
search_book_on_db() — This method will take the criteria as input and search for matching records from the database.
get_all_books() — This method will retrieve all the records from the database.
close_connection() — This will close the connection if the connection is still active.

BookDTO class

This class will be used to carry the record fields between methods in the application and between the application and the database handler.

BookEntry class

This class will handle the operations related to creating new records in the database. This class will have below methods -

show_fields() — This method will render the screen elements for capturing the details of a new record. It’ll render the text input fields as well as the buttons to submit or navigate.
validate_entry() — This method will check if the input fields are filled by the user.
save_book_record() — This method will read all the screen input fields and call the validate_entry() method first. If the validation is successful, this method will call the insert_book_to_db() method of SQLHandler class to store the record in the database.

BookMonitor class

This class will handle the operations related to searching records in the database. This class will have below methods -

show_fields() — This method will render the screen elements to input search criteria. This will also render the buttons to choose selective search or show all records.
validate_entry() — This method will check if the search criteria fields are filled by the user.
search_book_record() — This method will read all the screen input fields and call the validate_entry() method first. If the validation is successful, this method will call the search_book_on_db() method of SQLHandler class to search the database for matching records. If the search retrieves records, this method will show the records in tabular representation on the screen.
show_book_records() — This method will call the get_all_books() method of SQLHandler class to retrieve the top 500 records from the database and then show the records in tabular representation on the screen.

Global methods and attributes

Apart from the above classes and methods, the application module will contain below global attributes and methods -

Tk surface — This variable will be the parent surface for the application.
show_fields() — This is the global method to render two buttons so that the user can select either of the modes of usage — “Book entry” or “Book monitor”.

Let’s code

Now that you know the main methods and their purpose, now let’s get started.

You can follow the comments and docstrings within the code snippets.

“SQLHandler” class

Import, class definition and __init__() method

Key points —
— Initializing connection to the database
— Catching and handling exception

create_book_table()

Key points —
— Executing CREATE query to the database

insert_book_to_db()

Key points —
— Executing INSERT query to the database
— Checking for duplicate records through exception handling

search_book_on_db()

Key points —
— Executing SELECT query to the database
— Reading results from the connection cursor

get_all_books()

close_connection()

Key points —
— Closing connection to the database

BookDTO class

class definition and __init__() method

Key points —
— Overriding __str__() method of object

Application module

This module will contain the BookEntry, BookMonitor classes and the global elements for the application.

Application imports

BookEntry class

Class definition and __init__() method

show_fields()

Key points —
— Creating text input field with Tkinter
— Creating a button with Tkinter and assigning a handler method
— Creating StringVar with Tkinter to display a message

validate_entry()

Key points —
— Reading text input field from Tkinter surface

save_book_record()

Key points —
— Resetting a text input field

BookMonitor class

Class definition and __init__() method

show_fields()

validate_entry()

search_book_record()

Key points —
— Creating TreeView widget with Tkinter
— Creating a scrollbar with Tkinter

show_book_records()

Global methods and attributes

Starting/Invoking the application

This is written within the application module (the “bookmanagement.py” file in GitHub), however outside the class.
Notice the check for “if __name__ == ‘__main__’:”. This is to let the module execute only when run directly by the “python -m bookrecords bookmanagement.py” command.

If you want to make a package (e.g. “bookrecords”) for the application and execute by only calling the module by “python -m bookrecords”, the above code will go into the “__main__.py” file in the package like below.

That’s all the coding. Now it’s time to start the application!