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.

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.

Since context menus are caused by events, we can reimplement the contextMenuEvent().

def contextMenuEvent(self, event):

    context_menu = QMenu(self)

    context_menu.addAction(self.add_row_above_act)

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 context menu is displayed using exec_(). We pass self.mapToGlobal() as an argument to get the coordinates of the mouse relative to the screen. An example of the context menu can be seen in Figure 10-2.

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.

The Components of the Model/View Architecture

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

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.

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.

The simple GUI created using model/view programming can be seen in Figure 10-4.

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.

For the remaining rows, we use a list comprehension to read the items for each row into a list and use insertRow() to insert the list of items into the ith row. Figure 10-5 shows the contents of the parts.csv file.

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 QtSql

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.

We begin by creating a connection to the database in the CreateEmployeeData class. The addDatabase() function allows you to specify the SQL driver that you want to use. The examples in this chapter use SQLite 3 so we pass QSQLITE as the argument. Once the database object is created, we can set the other connection parameters, including which database we are going to use, the username, password, host name, and the connection port. For SQLite 3 we only need to specify the name of the database with setDatabaseName(). You can also create multiple connections to a database.

database = QSqlDatabase.addDatabase("QSQLITE")

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

Note

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.

Now that the connections are established, 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. Then, we call the exec_() method to execute the SQL statement in query. In the following lines, we want to delete the table accounts :

query = QSqlQuery()

query.exec_("DROP TABLE accounts")

Next, let’s create a new accounts table using exec_() and 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, e-mail, department, and the country where they are located. We also create a countries table which 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 10-7 illustrates the connection between the two tables.

The next thing to do is to insert records into our 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 – 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 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

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.

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.

Figure 10-8 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 later. 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"))

Working with QSqlRelationalTableModel

If you compare Figure 10-9 to Figure 10-8, you will notice that data in the last column has been updated to display the names of the countries and that the header has been changed to country.

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.