How to use database connector (MySQL) and GUI development (Tkinter) to create an interactive application with Python
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!
Run the application
The first screen after “python -m bookrecords bookmanagement.py” -
Click one of the modes of usage.
Screen after you click on the “Book entry” button.
Enter details to save.
Click on the “Submit” button.
Click on the “Back” button to go back to the modes.
Screen after you click on the “Book monitor” button.
Enter criteria to search for records in the database.
Results are shown in a tabular manner.
Screen after you click on the “Show all (500)” button.
Close the window any time to exit the application.
Happy coding!!
Download
GitHub — https://github.com/SaurabhGhosh/bookrecords_db_ui.git
Conclusion
In this blog, I hope you got some ideas about below -
- 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.
In my next blog, I’ll explore another program and learn more concepts.
If you have any questions related to this program, please feel free to post your comments.
Please like, comment and follow me! Keep Learning!