The Components of the Model/View Architecture

Model/View programming, similar to MVC, also separates the logic between three components but combines the view and the controller objects and introduces a new element – a delegate. A diagram of the architecture can be seen in Figure 14-1.

Using the model/view structure has quite a few benefits, specifically being ideal for developing large-scale applications, giving more flexibility and control over the appearance and editing of data items, simplifying the framework for displaying data, and offering the ability to display multiple views of a model at the same time.

PyQt’s Model/View Classes

What these widgets provide are all the tools necessary to work with data and already include the view, model, and delegate classes grouped together into single classes. However, these classes are more focused on item-based interfaces and are less flexible than working with the Model/View structure. It is also worth noting that each of these widgets inherits behavior from the abstract item view class, QAbstractItemView, creating the behavior for selecting items and managing headers.

An abstract class provides the points of connection, referred to as an interface, between other components, providing the class methods, functionality, and default implementation of features. They are the basis used for creating other classes. Qt’s abstract data classes can also be used to create custom models, views, or delegates.

In this section, we are going to create a GUI that demonstrates how to use the Model/View classes for displaying data in tables. For the GUI in Figure 14-2, data that is contained in a CSV file will be loaded and displayed in the table. In this example, we will also take a look at using the QStandardItemModel class , which provides a general model for storing data. This example will also demonstrate how we can connect a model for managing data to a view that will display the data.

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. For a simple GUI, you may not need to interact with a delegate, but it is important to know that signals from the delegate are emitted while editing an item in the view. This, in turn, informs the model and view about the state of the editor widget.

Explanation for Introduction to Model/View

Tables are great for organizing and displaying various types of textual (and sometimes graphical) 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 creates the items that are used in the model.

Working with CSV Files

In initializeUI() from Listing 14-1, the next step is to call loadCSVFile() and read the contents of the data file. The items are then added to the model to be displayed in the view. The contents of the file are displayed in Figure 14-3.

In the loadCSVFile() method in Listing 14-3, we can see how to read headers and data from a CSV file. Comma-Separated Values (CSV) is a very common format used for storing the data of spreadsheets and datasets.

# model_view_ex.py

    def loadCSVFile(self):

        """Load header and rows from CSV file."""

        file_name = "files/parts.csv"

        with open(file_name, "r") as csv_f:

            reader = csv.reader(csv_f)

            header_labels = next(reader)

            self.model.setHorizontalHeaderLabels(

                header_labels)

            for i, row in enumerate(csv.reader(csv_f)):

                items = [QStandardItem(item) for item in row]

                self.model.insertRow(i, items)

Listing 14-3

Code for the loadCSVFile() method in the introductory Model/View example

We’ll open the file, set up the reader object to read the sequences in the file, get the header labels, and skip to the next line using next(). For this example, we'll assume that the CSV file will have header labels. The horizontal labels for model are set using the list of items from the first row in the file, which are stored in header_labels.

For the remaining rows, we use a list comprehension to read the items for each row into a list. Items created for QStandardItemModel need to be instances of QStandardItem. The insertRow() method is used to insert the list of items into the ith row.

With a fundamental understanding of how to create a model and a view, we can move onto creating GUIs that handle larger datasets. While the rest of this chapter will focus on SQL-based classes and models, you do not have to use SQL. All you need is some form of structured data in order to use the Model/View classes.

What Is SQL?

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.

Explanation for Working with the QtSql Module

In this first example, we are going to see how to use QSqlQuery to create a small database that we will be able to view in the account management GUI. The database has two tables, accounts and countries. The two tables are linked together through the country_id field in accounts and the id field in countries.

Building a Dataset with QSqlQuery

Now that the connections are established in CreateEmployeeData, we can begin querying our database. You typically might start with databases that already have data in them, but in this example, we are going to see how we can create a database using SQL commands. To query a database using PyQt, we first need to create an instance of QSqlQuery. This is handled in Listing 14-5.

# create_database.py

    query = QSqlQuery()

    # Erase database contents

    query.exec("DROP TABLE accounts")

    query.exec("DROP TABLE countries")

    query.exec("""CREATE TABLE accounts (

        id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,

        employee_id INTEGER NOT NULL,

        first_name VARCHAR(30) NOT NULL,

        last_name VARCHAR(30) NOT NULL,

        email VARCHAR(40) NOT NULL,

        department VARCHAR(20) NOT NULL,

        country_id VARCHAR(20) REFERENCES countries(id))""")

    # Positional binding to insert records into the database

    query.prepare("""INSERT INTO accounts (

        employee_id, first_name, last_name,

        email, department, country_id)

        VALUES (?, ?, ?, ?, ?, ?)""")

Listing 14-5

Building a dataset with QSqlQuery in the CreateEmployeeData class

The exec() method is used to execute the SQL queries in PyQt. In the following lines, we want to create a query object and delete the table accounts:

    query = QSqlQuery()

    query.exec("DROP TABLE accounts")

Let’s next create a new accounts table using exec() and the SQL command CREATE TABLE accounts. Each table entry will have its own unique id by using AUTOINCREMENT. The accounts table will include information for an employee’s id, first name, last name, email, department, and the country where they are located. We also create a countries table that holds the names of the employee’s countries and is linked to the accounts table using the following line:

    country_id VARCHAR(20) REFERENCES countries(id))

The country_id references the countries table’s id. Figure 14-5 shows the connection between the two tables.

The next task is to insert records into the tables. We could continue to use exec() to execute queries, but this would become tedious if we have a large database. To insert multiple records at the same time, we separate the query from the actual values being inserted using placeholders and the prepare() method. The placeholder will act as a temporary variable, allowing users to supply different data using the same SQL query. In the following code, the positional placeholders are the ?. PyQt supports two placeholder syntaxes: the ODBC style, which uses ?, and the Oracle style, which uses :field_name.

    query.prepare("""INSERT INTO accounts (

                  employee_id, first_name, last_name,

                  email, department, country_id)

                  VALUES (?, ?, ?, ?, ?, ?)""")

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 bound to the fields using the addBindValue() method. For information about executing SQL statements in Qt, have a look at https://doc.qt.io/qt-6/sql-sqlstatements.html. Different approaches for binding values are found at https://doc.qt.io/qt-6/sql-sqlstatements.html.

Next, we’ll create the values for the first_name, last_name, and other fields in the SQL tables using Python lists and dictionaries in Listings 14-6 and 14-7.

# create_database.py

    first_names = ["Emma", "Olivia", "Ava", "Isabella",

        "Sophia", "Mia", "Charlotte", "Amelia", "Evelyn",

        "Abigail", "Valorie", "Teesha", "Jazzmin", "Liam",

        "Noah", "William", "James", "Logan", "Benjamin",

        "Mason", "Elijah", "Oliver", "Jason", "Lucas",

        "Michael"]

    last_names = ["Smith", "Johnson", "Williams", "Brown",

        "Jones", "Garcia", "Miller", "Davis", "Rodriguez",

        "Martinez", "Hernandez", "Lopez", "Gonzalez",

        "Wilson", “Anderson", "Thomas", "Taylor", "Moore",

        "Jackson", "Martin", "Lee", "Perez", "Thompson",

        "White", "Harris"]

    # Create data for the first table, account

    employee_ids = random.sample(

        range(1000, 2500), len(first_names))

    countries = {"USA": 1, "India": 2, "China": 3,

        "France": 4, "Germany": 5}

    country_names = list(countries.keys())

    country_codes = list(countries.values())

    departments = ["Production", "R&D", "Marketing", "HR",

                   "Finance", "Engineering", "Managerial"]

    for f_name in first_names:

        l_name = last_names.pop()

        email = (l_name + f_name[0]).lower() + "@job.com"

        country_id = random.choice(country_codes)

        dept = random.choice(departments)

        employee_id = employee_ids.pop()

        query.addBindValue(employee_id)

        query.addBindValue(f_name)

        query.addBindValue(l_name)

        query.addBindValue(email)

        query.addBindValue(dept)

        query.addBindValue(country_id)

        query.exec()

Listing 14-6

Creating the values for the example dataset in the CreateEmployeeData class, part 1

A for loop is then used where we bind the values to the placeholders. The exec() method 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 in Listing 14-7.

# create_database.py

    # Create data for the second table, countries

    country_query = QSqlQuery()

    country_query.exec("""CREATE TABLE countries (

        id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,

        country VARCHAR(20) NOT NULL)""")

    country_query.prepare(

        "INSERT INTO countries (country) VALUES (?)")

    for name in country_names:

        country_query.addBindValue(name)

        country_query.exec()

    print("[INFO] Database successfully created.")

if __name__ == "__main__":

    CreateEmployeeData()

    sys.exit(0)

Listing 14-7

Creating the values for the example dataset in the CreateEmployeeData class, part 2

Once the tables are populated, we call sys.exit(0) to exit the program. There is no QApplication instance since there is no GUI.

Explanation for Querying a Database with QSqlQuery

The program created from Listings 14-8 to 14-10 is not necessary for the accounting manager GUI, but it does give a few more examples for understanding how to input, update, and delete records with SQL in a PyQt application. The purpose of the section is to demonstrate how to open an existing database and modify its contents. We’ll do this for the database created in the “Explanation for Working with the QtSql Module” section.

We start by adding a database using the SQLite 3 driver and a default connection since no connection name is passed to addDatabase(). Next, set the database created in the previous program, accounts.db. Next, we’ll complete the connection using open().

We create a new QSqlQuery instance to search for the first and last names of the employees whose employee ids are greater than 2000.

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 the QSqlQuery method 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. In this query, we insert specific values for each field. You could also add multiple records into the database. Refer back to the “Explanation for Working with the QtSql Module” section to see how.

To update records, use UPDATE. We update the department value for the employee that was just inserted. Finally, to delete a record, use DELETE.

This example also has no GUI window. To see the changes, you could run this program after running the program in the “Explanation for Working with the QtSql Module” section and then use the GUI in the next section to visualize the results in a table.

Working with the QSqlTableModel Class

We are finally going to create a GUI for visualizing the database’s contents. In the table in Figure 14-6, 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.

We could use QSqlQuery to do all of the database work, but combining the class with the Model/View paradigm allows us to design GUIs that make the data management process simpler.

Explanation for Working with QSqlTableModel

Get started by using the basic_window.py script from Chapter 1 and then import the PyQt classes we need, including QSqlTableModel, in Listing 14-11. QHeaderView is the class that provides both horizontal and vertical headers for item view classes.

Next, create the MainWindow class for displaying the contents of the database.

# table_model.py

# Import necessary modules

import sys

from PyQt6.QtWidgets import (QApplication, QWidget,

    QTableView, QHeaderView, QMessageBox, QVBoxLayout)

from PyQt6.QtSql import QSqlDatabase, QSqlTableModel

class MainWindow(QWidget):

    def __init__(self):

        super().__init__()

        self.initializeUI()

    def initializeUI(self):

        """Set up the application's GUI."""

        self.setMinimumSize(1000, 500)

        self.setWindowTitle("SQL Table Model")

        self.createConnection()

        self.setUpMainWindow()

        self.show()

if __name__ == "__main__":

    app = QApplication(sys.argv)

    window = MainWindow()

    sys.exit(app.exec())

Listing 14-11

Code for the MainWindow class using QSqlTableModel

We’ll call createConnection() before setUpMainWindow() since the model and view objects in the main window rely on the data from the database. In the createConnection() method in Listing 14-12, 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 like the one in Figure 14-7 will be displayed to inform the user and the program will close.

# table_model.py

    def createConnection(self):

        """Set up the connection to the database.

        Check for the tables needed."""

        database = QSqlDatabase.addDatabase("QSQLITE")

        database.setDatabaseName("files/accounts.db")

        if not database.open():

            print("Unable to open data source file.")

            sys.exit(1) # Error code 1 - signifies error

        # Check if the tables we need exist in the database

        tables_needed = {"accounts"}

        tables_not_found = tables_needed -

            set(database.tables())

        if tables_not_found:

            QMessageBox.critical(None, "Error",

                f"""<p>The following tables are missing

                from the database: {tables_not_found}</p>""")

            sys.exit(1) # Error code 1 - signifies error

Listing 14-12

Code for the createConnection() method in the QSqlTableModel example

The instances of the QSqlTableModel and the QTableView are created in the setUpMainWindow() method in Listing 14-13. For QSqlTableModel, we need to set the database table we want to use with setTable(). Here, we’ll use the accounts table.

# table_model.py

    def setUpMainWindow(self):

        """Create and arrange widgets in the main window."""

        # Create the model

        model = QSqlTableModel()

        model.setTable("accounts")

        table_view = QTableView()

        table_view.setModel(model)

        table_view.horizontalHeader().setSectionResizeMode(

            QHeaderView.ResizeMode.Stretch)

        # Populate the model with data

        model.select()

        main_v_box = QVBoxLayout()

        main_v_box.addWidget(table_view)

        self.setLayout(main_v_box)

Listing 14-13

Code for the setUpMainWindow() method in the QSqlTableModel example

Next, create a QTableView object and set its model using setModel(). To make the table stretch to fit into the view horizontally, we use the following line:

        table_view.horizontalHeader().setSectionResizeMode(

            QHeaderView.Stretch)

This line also handles stretching the table when the window resizes.

Finally, the model is populated 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.

Figure 14-6 displays the contents of the database in a table view. Notice how the header labels display the field names used when the database was created. We will see how to set header labels when we actually create the account management GUI. Also, the country_id column currently only displays numbers associated with the different names in the countries table. If you only want to display specific columns, the following code lets you select which ones you want to display:

        model.setQuery(QSqlQuery("SELECT id, employee_id,

            first_name, last_name FROM accounts"))

In the next section, you’ll find out how to create and display relations set by foreign keys in the table view.

Working with the QSqlRelationalTableModel Class

Next, we are going to see how to use the QSqlRelationalTableModel class for working with relational databases. The QSqlRelationalTableModel class provides a model for managing and editing data in a SQL table, with additional support for using foreign keys. A foreign key is a SQL constraint used to link tables together.

The application in Figure 14-8 builds upon the GUI in the “Working with the QSqlTableModel Class” section.

Explanation for Working with QSqlRelationalTableModel

Start with basic_window.py script from Chapter 1. This time we need to import QSqlRelationalTableModel since we are working with relational databases and foreign keys. Also, QSqlRelation is included because we’ll need to use the class to store the information about SQL foreign keys. The QSqlRelationalDelegate is also needed because we’ll need to display editor widgets in the columns that pertain to foreign keys.

Listing 14-14 handles all of this as well as sets up the MainWindow class.

# relational_model.py

# Import necessary modules

import sys

from PyQt6.QtWidgets import (QApplication, QWidget,

    QTableView, QMessageBox, QHeaderView, QVBoxLayout)

from PyQt6.QtSql import (QSqlDatabase, QSqlRelation,

    QSqlRelationalTableModel, QSqlRelationalDelegate)

class MainWindow(QWidget):

    def __init__(self):

        super().__init__()

        self.initializeUI()

    def initializeUI(self):

        """Set up the application's GUI."""

        self.setMinimumSize(1000, 500)

        self.setWindowTitle("Relational Table Model")

        self.createConnection()

        self.setUpMainWindow()

        self.show()

if __name__ == "__main__":

    app = QApplication(sys.argv)

    window = MainWindow()

    sys.exit(app.exec())

Listing 14-14

Code for the MainWindow class using QSqlRelationalTableModel

We connect to the database in Listing 14-15 just like we did in the QSqlTableModel example, except this time we are checking for both tables, accounts and countries.

# relational_model.py

    def createConnection(self):

        """Set up the connection to the database.

        Check for the tables needed."""

        database = QSqlDatabase.addDatabase("QSQLITE")

        database.setDatabaseName("files/accounts.db")

        if not database.open():

            print("Unable to open data source file.")

            sys.exit(1) # Error code 1 - signifies error

        # Check if the tables we need exist in the database

        tables_needed = {"accounts", "countries"}

        tables_not_found = tables_needed -

            set(database.tables())

        if tables_not_found:

            QMessageBox.critical(None, "Error",

                f"""<p>The following tables are missing

                from the database: {tables_not_found}</p>""")

            sys.exit(1) # Error code 1 - signifies error

Listing 14-15

Code for the createConnection() method in the QSqlRelationalTableModel example

For setUpMainWindow() in Listing 14-16, create instances of the QSqlRelationalTableModel and QTableView classes. The setTable() method causes model to fetch the accounts table’s information.

# relational_model.py

   def setUpMainWindow(self):

        """Create and arrange widgets in the main window."""

        # Create the model

        model = QSqlRelationalTableModel()

        model.setTable("accounts")

        # Set up relationship for foreign keys

        model.setRelation(model.fieldIndex("country_id"),

            QSqlRelation("countries", "id", "country"))

        table_view = QTableView()

        table_view.setModel(model)

        table_view.horizontalHeader().setSectionResizeMode(

            QHeaderView.ResizeMode.Stretch)

        # Populate the model with data

        model.select()

Listing 14-16

Code for the setUpMainWindow() method in the QSqlRelationalTableModel example

The country_id field in accounts is mapped to countries table’s field, id. For the QSqlRelationalTableModel method setRelation(), we’ll need to pass the index of the column that contains a foreign key (done with fieldIndex()) and a QSqlRelation object that defines the relationship. For QSqlRelation, the field id of table countries maps to country_id in the accounts table. The final argument, country, specifies which field should be displayed in the accounts table.

If you compare Figure 14-8 to Figure 14-6, you’ll notice that data in the last column, country, has been updated to display the names of the countries and that the header has also been changed to country.

Adding Delegates to Edit Relational Data

The purpose of delegates when using Model/View classes becomes more obvious when you either start creating your own custom classes or when you need to use relational classes to select values for fields with foreign keys. With a delegate, an editor widget such as QLineEdit or QComboBox will appear when a user is editing data. You may not have even realized that every time you edit values in the cells of QTableView, you have been using a delegate all along. That’s because of how Qt seamlessly blends the view and delegates.

For SQL relational databases, QSqlRelationalDelegate data from a QSqlRelationalTableModel can be viewed and edited.

For the final portion of setUpMainWindow(), let’s create a QSqlRelationalDelegate instance and add it to table_view using setItemDelegate() in Listing 14-17.

# relational_model.py

        # Instantiate the delegate

        delegate = QSqlRelationalDelegate()

        table_view.setItemDelegate(delegate)

        main_v_box = QVBoxLayout()

        main_v_box.addWidget(table_view)

        self.setLayout(main_v_box)

Listing 14-17

Adding delegates in the QSqlRelationalTableModel example

Now if you double-click in the country column, you will see a QComboBox appear containing the list of countries. An example of this is shown in Figure 14-9.

The last step is to create and set the layout for the main window.

By this point, you should have a fundamental understanding of how to use model and view classes, use QtSql classes to perform queries, and display foreign key relationships in a table using relational classes. We are now ready to build the account management GUI.

Explanation for the Account Management GUI

The account management GUI uses the QSqlRelationalTableModel for managing the accounts and countries tables. We'll use the concepts we learned in the previous sections to design a GUI with features for managing the database directly rather than programmatically. Refer back to Figure 14-4 to see the interface.

The application lets a user add, delete, and sort the contents of the table. Rows added or deleted will also update the database.

Also, be sure to download the icons folder from GitHub for this project.

For this GUI, a separate method, createModel(), will create the QSqlRelationalTableModel that is used by the QTableView object in setUpMainWindow(). This is done to help organize the code.

The add_product_button and del_product_button objects are used to add and delete items from the table and the model. Each button is connected to a slot using the clicked signal. The slots are created in Listing 14-23.

Items in a view can also be sorted. The sort_combo provides various ways to organize the table’s data based on the column names. For the QComboBox, when the selection has changed, the widget can emit a currentTextChanged signal. The signal also passes text that we can use to determine how to set the view’s order for displaying records in the setSortingOrder() slot. This is handled in Listing 14-24. The widgets are then arranged in buttons_h_box and added to edit_container.

In Model/View programming, the delegate provides the default tools for painting item data in the view and for providing editor widgets for item models. The appearance and editor widgets of the item delegate can be customized. For the account management GUI, the delegate used is the QSqlRelationalDelegate. This class provides a combo box for editing data in fields that are foreign keys for other tables.

An example of the combo box used by the delegate can be seen in the bottom-right corner of Figure 14-4. The widget appears whenever the user needs to select a country from the countries table that will be displayed in the view.

For deleteItem(), we get the currently selected row’s index and delete the row with removeRow(). Then we update the model using select().

Finally, select() is called to update the model and view with the data.

At this point, you should run the application and test it out. If you want to tinker around with the code, first have a look at the different selection modes and selection behaviors. From there, you could go back to the SQL database and try implementing additional fields or create new foreign keys to test out the relational classes.