Data is fundamental to the ways that modern business, communications, science, and even our personal lives are changing. The information we create from our online shopping, social media posts, search-engine queries, and location data is collected, managed, and analyzed and can be used for a number of reasons, including to track consumer patterns, to train artificial intelligence algorithms, or even to study the geographic distribution of particular events such as diseases.
Data analysis is an important process, and this chapter will have a look at working with structured data for GUI development. Data can be stored in many different formats, including textual, visual, and multimedia.
In order to analyze data, we need to organize it into structures that we can store and then access electronically through a computer system. Sometimes you may only be working with a small dataset consisting of one or two files. Other times, you may need to access certain portions of an entire database filled with private information. A database is an organized collection of multiple datasets.
We generally view the data from files and databases in tables. The rows and columns of a table typically work best for handling the style of data in data files. If we had a dataset of employees in a company, each row might represent an individual employee in the company, while each column depicts the different types of attributes for each employee, such as their age, salary, and employee ID number.
This chapter will focus only on using PyQt’s table classes for displaying and manipulating data. We will see how to use tables for creating the foundation for a spreadsheet editor, for working with CSV files, and for working with the SQL database management language. Of course, there are also other formats for viewing data, namely, lists and trees, should they better fit your application’s requirements.
Take a look at PyQt’s convenience class for making tables, QTableWidget
Find out how to add context menus to GUI applications
Learn about Qt’s model/view architecture for working with data using the QTableView class
See an example of how to work with CSV files in PyQt
Introduce the QtSql module for working with SQL and databases
The QTableWidget Class
The QTableWidget class provides a means to display and organize data in tabular form, presenting the information in rows and columns. Using tables breaks down data into a more quickly readable layout. An example of PyQt’s tables can be seen in Figure 10-1.
QTableWidget provides you with the standard tools that you will need to create tables, including the ability to edit cells, set the number of rows and columns, and add vertical or horizontal header labels.
Setting either horizontal or vertical header labels is done with setHorizontalHeaderItem() or setHorizontalHeaderLabels(). Change Horizontal to Vertical for the vertical header.
Example code that uses the QTableWidget class and some of its functions
Explanation
When we import classes in the beginning of the program, we need to make sure to include QTableWidget and QTableWidgetItem, which is used to create items for the table widget. A table is composed of a group of cells, and the items are the bits of textual information in each one. QTableWidget has a number of signals for checking to see if cells or items have been clicked, double-clicked, or even altered.
Adding rows above or below the currently selected row using insertRow()
Adding columns before or after the currently selected column using insertColumn()
Deleting the current row or column using removeRow() or removeColumn()
Clearing the entire table, including items and headers with clear()
A new row is then inserted in the current row’s location, causing all other rows to move down. For methods that manipulate columns, use the currentColumn() method .
From there, we get the column for the current header and show a QInputDialog to get the header label from the user. Finally, the item for the horizontal header is set using setHorizontalHeaderItem().
Creating Context Menus
This application also introduces how to create a context menu, sometimes called a pop-up menu, that appears in the window due to a user’s interaction, such as when the right mouse button is clicked. A context menu displays a list of commands, such as Back Page or Reload Page, that make interacting with the GUI even more convenient. Context menus can also be set for managing specific widgets.
A context menu is typically created using QMenu(). You can either use existing actions that are created in the menubar or the toolbar, or you can create new ones. In the preceding example, two actions are created specifically for the context menu, copy_act and paste_act. If a cell in the table is not empty, we “copy” the text to item_text. In the pasteItem() slot, the current row and column of the selected cell is checked. We then “paste” the item using setItem(). The copy and paste actions could also be implemented using the QClipboard.
The QTableWidget is actually a convenience class, providing simplified access to other classes, namely, QTableView and QAbstractModel. Before learning about accessing databases with PyQt, you should take a moment to get familiar with the model/view architecture used by Qt.
Introduction to Model/View Programming
Qt, and therefore PyQt, needs a system to access, display, and manage data that can be presented to the user. An older technique used for managing the relationship between data and its visual representation for user interfaces is the model-view-controller (MVC) software design pattern. MVC divides a program’s logic into three interlinked components – a model, a view, and a controller.
PyQt utilizes a similar design pattern that is based on MVC – the model/view architecture.
The Components of the Model/View Architecture
Model – The class that communicates with the data source, accessing the data, and provides a point of connection between the data and the view and delegate.
View – The class that is responsible for displaying the data to the user, either in list, table, or tree formats, and for retrieving items of data from the model using model indexes. The view also has similar functionality to the controller in the MVC pattern, which handles the input from a user’s interaction with items displayed in the view.
Delegate – The class that is in charge of painting items and providing editors in the view. The delegate also communicates back to the model if an item has been edited.
PyQt’s Model/View Classes
QTableWidget is one of a few convenience classes that PyQt provides for working with data. QTableWidget creates a table of items, QListWidget displays a list of items, and QTreeWidget provides a hierarchal treelike structure. An example of QListWidget can be seen in Chapter 8. These widgets provide all the tools necessary to work with data, and the view, model, and delegate classes all grouped into one class. However, these classes are more focused on item-based interfaces and are less flexible than working with the model/view structure. Each of these widgets inherits behavior from an abstract class, QAbstractItemView, creating the behavior for selecting items and managing headers.
Models – All models are based on the QAbstractItemModel class, defining the interface used by both views and delegates to access data, and can be used to handle lists, tables, or trees. Data can take on a number of forms, including Python data structures, separate classes, files, or databases. Some other model classes are QStandardItemModel, QFileSystemModel, and SQL-related models.
Views – All views are based on QAbstractItemView and are used to display data items from a data source, including QListView, QTableView, and QTreeView.
Delegates – The base class is QAbstractItemDelegate, responsible for drawing items from the model and providing an editor widget for modifying items. For example, while editing a cell in a table, the editor widget, such as QLineEdit, is placed directly on top of the item.
The following example in Listing 10-2 demonstrates how to use the model/view classes for displaying data using tables. Chapter 12 contains an extra example that shows how to use QFileSystemModel and QTreeView to display the contents of directories on your computer.
Communication between the models, views, and delegates is handled by signals and slots. The model uses signals to notify the view about changes to the data. The view generates signals that provide information about how a user interacts with items. Signals from the delegate are emitted while editing an item to inform the model and view about the state of the editor.
Code demonstrating how to design a GUI using model/view architecture
Explanation
The preceding example displays the contents of a CSV file in a table view and demonstrates how simple it is to use the model/view paradigm. Tables can be used to organize and display various types of data, such as employee or inventory information.
We begin by importing classes, including QTableView from the QtWidgets module, and the QStandardItemModel and QStandardItem classes from QtGui. QStandardItemModel will supply the item-based model we need to work with the data; QStandardItem provides the items that are used in the model.
Next, we call loadCSVFile() to read the contents of the data file and add the items to the model to be displayed in the view. The table_view widget is added to the QVBoxLayout.
Working with SQL Databases in PyQt
Now that we have looked at PyQt’s model/view architecture and the QTableView class, let’s move on and begin taking a look at how to use SQL for handling structured data.
What Is SQL?
The Structured Query Language (SQL) is a programming language designed for communication with databases. The data stored in databases is organized into a set of tables. The rows of the tables are referred to as records, and the columns are referred to as fields. Each column can only store a specific kind of information, such as names, dates, or numbers.
With SQL, we can query the data stored in relational databases – a collection of data items that have predefined relationships across multiple tables, marked by a unique identifier known as a foreign key. In a relational database, multiple tables comprise a schema, more than one schema makes up a database, and those databases are stored on a server. Relational databases allow for multiple users to handle the data at the same time. For this reason, accessing a database often requires a user to log in with a username and password in order to connect to the database.
This section will focus solely on using SQL along with classes from PyQt’s QtSql module for creating a very basic database management system interface.
Working with Database Management Systems
The QtSql module provides drivers for a number of relational database management systems (RDBMS) , including MySQL, Oracle, Microsoft SQL Server, PostgreSQL, and SQLite versions 2 and 3. An RDBMS is the software that allows users to interact with relational databases using SQL.
For the following examples, we will be using SQLite 3 since the library already comes shipped with Python and is included with Qt. SQLite is not a client-server database engine, so we do not need a database server. SQLite operates on a single file and is mainly used for small desktop applications.
Getting Familiar with SQL Commands
A list of common SQLite keywords and functions that can be found in this chapter1
SQLite Keywords | Description |
---|---|
AUTOINCREMENT | Generates a unique number automatically when a new record is inserted into the table. |
CREATE TABLE | Creates a new table in the database. |
DELETE | Deletes a row from the table. |
DROP TABLE | Deletes a table that already exists in the database. |
FOREIGN KEY | Constraint that links two tables together. |
FROM | Specifies the table to interact with when selecting or deleting data. |
INTEGER | Signed integer data type. |
INSERT INTO | Inserts new rows into the table. |
MAX() | Function that finds the maximum value of a specified column. |
NOT NULL | Constraint that ensures a column will not accept NULL values. |
PRIMARY KEY | Constraint that uniquely identifies a record in the table. |
REFERENCES | Used with FOREIGN KEY to specify another table which has relation with the first table. |
SELECT | Selects data from a database. |
SET | Identifies which columns and values should be updated. |
UNIQUE | Constraint that ensures all values in a column are unique. |
UPDATE | Updates existing values in a row. |
VALUES | Defines the values of an INSERT INTO statement. |
VARCHAR | Variable character data type for strings. |
WHERE | Filters the results of a query to include only records that satisfy specific conditions. |
In the following sections, we will see how to create a user interface that can be used to view a database’s information in a table view.
Project 10.1 – Account Management GUI
For this project, we are going to take a different approach to designing the account management GUI. This section builds up to the final project by working through a number of smaller example programs. There is a good deal of information to unpack, and if this is your first time working with SQL, especially to build an interface in PyQt, then the process for working with databases can become a little unclear.
Imagine you have a business and you want to create a database to keep track of your employees’ information. You want to include information such as their first and last names, employee IDs, e-mail addresses, departments, and the countries where they work. (This could be extended to include more information such as salaries, phone numbers, and dates of hire.) In the beginning, a small database is okay. However, as your workforce builds, so will the information. Some employees may have the same first or last name, or work in the same country. You need a way to manage all of those employees so that fields in the database are populated with the correct information and data types.
Using a relational database, we can avoid issues with the data’s integrity. We could set up multiple tables, one for the different employees’ accounts and one for the countries. For this example, we only use repeating country names to demonstrate how to use PyQt’s classes for working with relational databases. Figure 10-6 displays the account management GUI.
- 1.
Introduce how to use QSqlDatabase to connect to databases and QSqlQuery for creating queries
- 2.
A few examples of how to use QSqlQuery for working with databases
- 3.
Introduce QSqlTableModel for working with databases with no foreign keys
- 4.
Show how use to QSqlRelationalTableModel to create tables with foreign key support
- 5.
Create the account management GUI
Working with QtSql
Code showing examples of how to create queries with QSqlQuery
To see an example of what the data this program created looks like in a table view, refer back to Figure 10-6 .
Explanation
This program does not create a GUI, so we only need to import the QSqlDatabase and QSqlQuery classes from QtSql. We will use QSqlDatabase to create the connection that allows access to a database; QSqlQuery can be used to perform SQL statements in PyQt.
A connection is referenced by its name, not by the name of the database. If you want to give your database a name, pass it as an argument after the driver in the addDatabase() method. If no name is specified, then that connection becomes the default connection.
If the accounts.db file does not already exist, then it will be created. Once the parameters are set, you must call open() to activate the connection to the database. A connection cannot be used until it is opened.
Each field, such as employee_id or first_name, is associated with one of the placeholders. Since we used AUTOINCREMENT for id, we do not have to include the field or a placeholder in the query.
The prepare() method gets the query ready for execution. If the query is prepared successfully, then values can be binded to the fields using the addBindValue() method .
Next, we create the values for the first_name, last_name, and other fields using Python lists and dictionaries. A for loop is then used where we bind the values to the placeholders. exec_() is called at the end of each iteration to insert the values into the accounts table. The countries table is prepared in a similar manner.
Once the tables are populated, we call sys.exit(0) to exit the program.
Example Queries Using QSqlQuery
Demonstrating how to insert, update, and delete records using SQL and PyQt
This code will modify the database created in Listing 10-3. To view the changes, run this code and then run the code in one of the following examples to see how the tables have been manipulated.
Explanation
This example also has no GUI window. If you run this program after running the program in Listing 10-3, you will notice how the queries here modify the database.
We start by creating a connection to the SQLite 3 driver and add the database created in the previous program, accounts.db. Next, we complete the connection using open().
With that query, we could use the values from first_name and last_name to update or delete records. To cycle through the results of the query, we use next(). Other methods that could be used to navigate the results include next(), previous(), first(), and last().
To insert a single record, we can use the INSERT SQL command. You could also add multiple records into the database. Refer back to Listing 10-3 to see how. In this query, we insert specific values for each field. To update records, use UPDATE. We update the department value for the employee that was just inserted. Finally, to delete a record, use DELETE.
Working with QSqlTableModel
We are finally going to create a GUI for visualizing the database’s contents. In this table, we are only going to visualize the accounts table to demonstrate the QSqlTableModel class , an interface that is useful for reading and writing database records when you only need to use a single table with no links to other tables. The following program will demonstrate how to use model/view programming to view the contents of a SQL database.
Code to view SQL database using QSqlTableModel
Explanation
Get started by importing the PyQt classes, including QSqlTableModel. Next, create the TableDisplay class for displaying the contents of the database.
In the createConnection() method , we connect to the database and activate the connection with open(). This time, let’s check to make sure that the tables we want to use are in the database. If they cannot be found, then a dialog box will be displayed to inform the user and the program will close.
This line also handles stretching the table when the window resizes.
Finally, populate the model with data using select(). If you have made changes to the table but have not submitted them, then select() will cause the edited items to return back to their previous states .
Working with QSqlRelationalTableModel
Code to view SQL database using QSqlRelationalTableModel
Explanation
This time we need to import QSqlRelationalModel since we are working with relational databases and foreign keys. Also, QSqlRelation stores the information about SQL foreign keys.
We connect to the database like before, except this time we are checking for both tables, accounts and countries. Next we create instances of the QSqlRelationalModel and QTableView classes. The setTable() method is used to cause the model to fetch the accounts table’s information.
The rest of the program is the same as Listing 10-5 .
Account Management GUI Solution
The account management GUI uses the QSqlRelationalModel for managing the accounts and countries tables. We use the concepts we learned in the previous sections and design a GUI with features for managing the database directly rather than programmatically.
Code for the account management GUI
Your GUI should look similar to the one displayed in Figure 10-6 .
Explanation
The QTableView object, table_view, is created in the setupWidgets() method , along with the GUI’s labels, push buttons, and combo box. For table_view, we set the model and a few parameters. The table’s vertical and horizontal headers will stretch to fit the window. QAbstractItemView.SingleSelection only allows the user to select one item at a time. QAbstractItemView.SelectRows only allows rows to be selected in the table.
The two push buttons, add_record_button and del_record_button, emit signals that add and delete rows in the table. For addRecord(), we check how many rows are in the table with rowCount() and use insertRow() to insert an empty row at the end of table view. We query the database to find out the largest id value. If a user does not enter a value for id into the row, then the new record’s id is equal to the highest id value plus one. For deleteRecord(), we get the currently selected row’s index and delete the row with removeRow(). Then we update the model using select().
For the QComboBox, when the selection has changed, the widget emits a currentTextChanged() signal. We use the text to determine how to set the view’s order for displaying records.
An example of the combo box used by the delegate can be seen in the bottom-right corner of Figure 10-6. The widget appears whenever the user needs to select a country from the countries table that will be displayed in the view.
Summary
PyQt provides convenience classes for lists, tables, and trees. QListWidget, QTableWidget, and QTreeWidget are useful when you need to view data for general situations. While they are practical for creating quick interfaces for editing data, if you need to have more than one widget for displaying a dataset in an application, you must also create a process for keeping the datasets and the widgets in agreement. A better option is to use PyQt’s model/view architecture.
With the model/view paradigm, you are able to have multiple views in a single application that work in unison to view and update the database. You also have more control over the look of the editing widgets and the items in the view with the delegate.
There are different formats available for storing and managing data. One example is the CSV format which is convenient for reading, parsing, and storing smaller datasets. However, for large databases that contain multiple tables with relational characteristics, a relational database management system that uses SQL is a more preferable option for managing the data. SQL allows users to select desired information that might be shared between tables, as well as insert, update, and delete existing records easily.
The model/view architecture is very useful for working with SQL databases, providing the tools necessary for connecting to a database and viewing its content. PyQt provides three models for working with SQL databases. For an editable data model without foreign key support, use QSqlTableModel. If you have tables with relational properties, use QSqlRelationalTableModel. Finally, the QSqlQueryModel is beneficial when you only need to read the results of a query without editing them.
Over the course of this book, we took a look at a few applications that could have benefited greatly by being able to connect to databases using SQL. The login GUI in Chapter 3 could connect to a database to retrieve usernames and passwords. The to-do list GUI in Chapter 4 could be completely redesigned to include a QCalendarWidget (covered in Chapter 12) that keeps track of events by using a database. There is also the pizza ordering GUI from Chapter 6. You could implement a database for storing customers’ information, using a relational database for adding new customers, updating existing ones, and preventing data from being duplicated.
In Chapter 11, we will take a brief look at multithreading in PyQt.