Using Database Table Views

Probably the most natural and convenient way to present database data is to show database tables and views in GUI tables. This allows users to see many records at once, and it is particularly convenient for showing master-detail relationships.

In this section, we will examine the Asset Manager application. The code is in chap15/assetmanager.pyw. This application has four tables, created by the following SQL statements:

query = QSqlQuery()

query.execj CREATE TABLE actions (

id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, name VARCHAR(20) NOT NULL, description VARCHAR(40) NOT NULL) )

query.exec_( CREATE TABLE categories (

id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, name VARCHAR(20) NOT NULL, description VARCHAR(40) NOT NULL) )

query.execj CREATE TABLE assets (

id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, name VARCHAR(40) NOT NULL, categoryid INTEGER NOT NULL, room VARCHAR(4) NOT NULL,

FOREIGN KEY (categoryid) REFERENCES categories) )

query.execj CREATE TABLE logs (

id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, assetid INTEGER NOT NULL, date DATE NOT NULL, actionid INTEGER NOT NULL,

FOREIGN KEY (assetid) REFERENCES assets,

FOREIGN KEY (actionid) REFERENCES actions) )

The actions and categories tables are typical reference data tables, with an ID, a short description (name), and a long description (description). The main table is assets; this holds the name, category, and location of each asset in a building. The logs table is used to keep track of what happens to an asset over its lifetime. Figure 15.4 shows the tables schematically.

Figure 15.4 The Asset Manager database design

The Asset Manager application has a dialog-style main window with two QTableViews in a master-detail relationship. It is shown in Figure 15.5. The top table shows the assets table and the bottom one shows the records from the logs table that correspond to the current asset record. The user can add and delete assets and log records, and edit both tables in-place. Users can also add, delete, and edit the categories and actions reference tables by popping up a suitable dialog. This dialog also uses a QTableView, although it could easily have used a QDataWidgetMapper instead.

We will begin by looking at the creation and connection to the database, then the main form, and then we will look at the dialog that is used with the reference data. Just as with the Phone Log application, the Asset Manager generates a set of fake records the first time it is run. As noted in the preceding

Asset Manager

Assets

Name

Category

Room

1

21" CRT Monitor

Computer Equipment

0111

2

Swivel Chair

Furniture

0111

3

Computer (32-bit/100GB/1GB)

Computer Equipment

0150

4

Secretary Chair

Furniture

0150

5

Desk (Basic, 3 drawer)

Furniture

Logs

Logs

Date

Action

1

2007-03-20

Acquired

2

2007-04-17

Repaired

3

2007-04-20

Broken

Add Action

Delete Action

Edit Actions...

Edit Categories..

Quit

Figure 15.5 The Asset Manager application section, this is fast with SQLite on Linux and very slow on some Windows machines.

app = QApplication(sys.argv)

db = QSqlDatabase.addDatabase("QSQLITE")

db.setDatabaseName(filename)

QMessageBox.warning(None, "Asset Manager",

QString("Database Error: %1").arg(db.lastError().text())) sys.exit(1) form = MainForm() form.show() app.exec_()

We begin as usual by creating a QApplication object. Next we create the connection; if the database file doesn't exist, SQLite will create an empty one. Then we create the main form, call show() on it to schedule a paint event, and start off the event loop.

What we haven't shown is the code that we have used to generate the fake data the first time the application is run, and to pop up the splash screen. This code is, of course, in the source file, chap15/assetmanager.pyw.

As we did in the preceding section, we will skip the form's layout and concentrate instead on the creation of the widgets and the models. We will also skip the code for creating the buttons, although we will show the first couple of signal-slot connections.

class MainForm(QDialog):

super(MainForm, self)._init_()

self.assetModel = QSqlRelationalTableModel(self)

self.assetModel.setTable("assets")

self.assetModel.setRelation(CATEGORYID,

QSqlRelation("categories", "id", "name")) self.assetModel.setSort(ROOM, Qt.AscendingOrder) self.assetModel.setHeaderData(ID, Qt.Horizontal,

QVariant("ID")) self.assetModel.setHeaderData(NAME, Qt.Horizontal,

QVariant("Name")) self.assetModel.setHeaderData(CATEGORYID, Qt.Horizontal,

QVariant("Category")) self.assetModel.setHeaderData(ROOM, Qt.Horizontal,

QVariant("Room")) self.assetModel.select()

The model is created in much the same way as we saw in the preceding section. The ID, NAME, and others are integer column indexes set up earlier in the assetmanager.pyw file. What's different from using a QDataWidgetMapper is that we have set the header data to give the columns titles; if we don't do this, the QTableView that presents the model will use the database field names for the column titles. Since the categoryid field is a foreign key, we have used a QSql-RelationalTableModel and called setRelation() appropriately.

self.assetView = QTableView()

self.assetView.setModel(self.assetModel)

self.assetView.setItemDelegate(AssetDelegate(self))

self.assetView.setSelectionMode(QTableView.SingleSelection)

self.assetView.setSelectionBehavior(QTableView.SelectRows)

self.assetView.setColumnHidden(ID, True)

self.assetView.resizeColumnsToContents()

The view is a standard QTableView, but instead of setting a QSqlRelational-Delegate, we have set a custom delegate. We will detour to look at this in a moment. The selection mode is set so that users can navigate to individual fields; the selection behavior is that the row that has the focus is highlighted. We don't want to show the ID field since it isn't meaningful to the user, so we hide it.

We have not used a standard QSqlRelationalDelegate because we want to take control of the editing of the room numbers, since they are not straightforward to validate. We will now take a brief detour to look at the AssetDelegate class.

class AssetDelegate(QSqlRelationalDelegate): def_init_(self, parent=None):

super(AssetDelegate, self).__init__(parent)

The initializer is typical of most delegate subclasses, simply calling the base class.

def paint(self, painter, option, index): myoption = QStyleOptionViewItem(option) if index.column() == ROOM:

myoption.displayAlignment |= Qt.AlignRight|Qt.AlignVCenter QSqlRelationalDelegate.paint(self, painter, myoption, index)

We have reimplemented the paint() method only to right-align the room numbers. We do this by changing the QStyleOptionViewItem, and we leave the painting itself to be done by the base class.

def createEditor(self, parent, option, index): if index.column() == ROOM: editor = QLineEdit(parent) regex = QRegExp(r"(?:0[1-9]|1[0124-9]|2[0-7])"

rM(?:0[1-9]|[1-5][0-9]|6[012])") validator = QRegExpValidator(regex, parent) editor.setValidator(validator) editor.setInputMask("9999")

editor.setAlignment(Qt.AlignRight|Qt.AlignVCenter) return editor else:

return QSqlRelationalDelegate.createEditor(self, parent, option, index)

The heart of the createEditor() method is the code that sets up the QLineEdit for entering room numbers. Room numbers are four digits long, made up of a floor number, in the range 01-27 (but excluding 13), and a room number on the floor in the range 01-62. For example, 0231 is floor 2, room 31, but 0364 is invalid. The regular expression is sufficient for specifying valid room numbers, but it cannot set a minimum number of digits, since one, two, or three digits may be a valid prefix for a valid four digit room number. We have solved this by using an input mask that requires exactly four digits to be entered. For the other fields, we pass the work on to the base class.

def setEditorData(self, editor, index): if index.column() == ROOM:

text = index.model().data(index, Qt.DisplayRole).toString() editor.setText(text) else:

QSqlRelationalDelegate.setEditorData(self, editor, index)

Once the editor has been created, the view will call setEditorData() so that it can be populated with the current value. In this case, we care only about the room column, passing on the work for the other fields to the base class.

def setModelData(self, editor, model, index): if index.column() == ROOM:

model.setData(index, QVariant(editor.text())) else:

QSqlRelationalDelegate.setModelData(self, editor, model, index)

We have taken a similar approach to the previous method, handling the room field and leaving the others to be handled by the base class. As a matter of fact, we could have omitted reimplementing this method, and PyQt would have been smart enough to retrieve the value from our QLineEdit. However, it is a better practice to take full responsibility for our own customizations.

We have now finished the detour and can return to the MainForm._init_()

method, beginning with the bottom table that shows the log records that are applicable to the current asset.

self.logModel = QSqlRelationalTableModel(self)

self.logModel.setTable("logs")

self.logModel.setRelation(ACTIONID,

QSqlRelation("actions", "id", "name")) self.logModel.setSort(DATE, Qt.AscendingOrder) self.logModel.setHeaderData(DATE, Qt.Horizontal,

QVariant("Date")) self.logModel.setHeaderData(ACTIONID, Qt.Horizontal,

QVariant("Action")) self.logModel.select()

The code for creating the log model is almost the same as the code we used for the asset model. We use a QSqlRelationalTableModel because we have a foreign key field, and we provide our own column titles.

self.logView = QTableView()

self.logView.setModel(self.logModel)

self.logView.setItemDelegate(LogDelegate(self))

self.logView.setSelectionMode(QTableView.SingleSelection)

self.logView.setSelectionBehavior(QTableView.SelectRows)

self.logView.setColumnHidden(ID, True)

self.logView.setColumnHidden(ASSETID, True)

self.logView.resizeColumnsToContents()

self.logView.horizontalHeader().setStretchLastSection(True)

This code is also similar to what we did for the assets table, but with three differences. Here we have used a custom LogDelegate class—we won't review it because it is structurally very similar to the AssetDelegate. It provides custom editing of the date field. We also hide both the log record's ID field and the assetid foreign key—there's no need to show which asset the log records are for because we are using master-detail, so the only log records that are visible are those that apply to the current asset. (We will see how the master-detail relationship is coded shortly.) The last difference is that we have set the last column to stretch to fill all the available space. The QTableView.horizontalHeader() method returns a QHeaderView, and this is what controls some aspects of the table view's columns, including their widths.

self.connect(self.assetView.selectionModel(),

SIGNAL("currentRowChanged(QModelIndex,QModelIndex)"), self.assetChanged) self.connect(addAssetButton, SIGNAL("clicked()"), self.addAsset)

If the user navigates to a different row we must update the log view to show the log records for the right asset. This is achieved by the first connection in conjunction with the assetChanged() method that we will review in a moment.

Every view has at least one selection model that is used to keep track of which items in the view's model (if any) are selected. We connect the view's selection model's currentRowChanged() signal so that we can update the log view depending on the current asset.

All the other connections are button-clicked connections like the second one shown here. We will cover all the methods the buttons connect to as we progress through this section.

self.assetChanged(self.assetView.currentIndex()) self.setMinimumWidth(650) self.setWindowTitle("Asset Manager")

The initializer ends by calling the assetChanged() method with the asset view's current model index—this will result in the log view showing the relevant asset's records.

def assetChanged(self, index): if index.isValid():

record = self.assetModel.record(index.row()) id = record.value("id").toInt()[0] self.logModel.setFilter(QString("assetid = %1").arg(id)) else:

self.logModel.setFilter("assetid = -1") self.logModel.select() self.logView.horizontalHeader().setVisible( self.logModel.rowCount() > 0)

This method is called once by the form's initializer and then whenever the user navigates to a different asset, that is, to a different row in the assets table view.

If the model index of the new position in the view is valid, we retrieve the row's entire record from the model and set a filter on the log model that selects only those log records which have an assetid corresponding to the asset ID of the current row. (This is the equivalent of doing SELECT * FROM logs WHERE assetid = id.) Then we call select() to refresh the log view with the selected log records. If the model index is invalid, we set the ID to be one that we know does not exist, thereby guaranteeing that no rows will be retrieved and the log view will be empty. Finally, we hide the log view's column titles if there are no log records to display.

The record() method is one of the extensions that the QSqlTableModel and QSqlRelationalTableModel classes provide in addition to the methods from their QAbstractItemModel base class, to make them easier to use with databases. Other extensions include setQuery(), which allows us to write our own SELECT statement using SQL syntax, and insertRecord(), for adding records.

The connection to the assetChanged() method, and the implementation of the method, are all we have to do to establish a master-detail relationship between two models (and therefore, between their views).

def done(self, result=1): query = QSqlQuery()

query.exec_("DELETE FROM logs WHERE logs.assetid NOT IN"

"(SELECT id FROM assets)") QDialog.done(self, 1)

When the application terminates we execute one final query to delete any log records that are present for nonexistent (deleted) assets. In theory, this should never be needed, and therefore should do nothing. This is because, for databases that support transactions, we use transactions to ensure that if an asset is deleted, so are its log records.

def addAction(self):

index = self.assetView.currentIndex() if not index.isValid(): return

QSqlDatabase.database().transaction() record = self.assetModel.record(index.row()) assetid = record.value(ID).toInt()[0]

row = self.logModel.rowCount() self.logModel.insertRow(row)

self.logModel.setData(self.logModel.index(row, ASSETID),

QVariant(assetid)) self.logModel.setData(self.logModel.index(row, DATE),

QVariant(QDate.currentDate())) QSqlDatabase.database().commit() index = self.logModel.index(row, ACTIONID) self.logView.setCurrentIndex(index) self.logView.edit(index)

If the user asks to add an action (a new log record), this method is called. We retrieve the assetid for the current asset, and then insert a new log record as the last record in the logs table. We then set the record's assetid foreign key to the one we have retrieved and provide an initial default date. Finally, we retrieve a model index to the new log record's action combobox, and initiate editing ready for the user to choose a suitable action.

Before we retrieve the assetid, we begin a transaction. This is to prevent the theoretical possibility that having retrieved the assetid, the asset is deleted just before the new log record is created. If this occurred, the log record would refer to a nonexistent asset, something that might cause crashes or subtler problems later on. Once we call commit(), we know that the asset and the new log record exist. If someone now tries to delete the asset, they can do so—but the asset's log records, including this one, will correctly be deleted along with it.

For a really defensive approach we might structure our transaction code like this:

class DatabaseError(Exception): pass rollback = False try:

if not QSqlDatabase.database().transaction():

raise DatabaseError rollback = True

# execute commands that affect the database if not QSqlDatabase.database().commit()

raise DatabaseError rollback = False finally:

if rollback:

if not QSqlDatabase.database().rollback(): raise DatabaseError

This tries to ensure that if some problem occurs that prevents the commit from being reached, or from being able to execute successfully if it is called, we roll back to the previous position and therefore preserve the database's relational integrity. All bets are off if the rollback fails, though. The error text can be retrieved from QSqlDatabase.database().lastError().text(), which returns a QString.

The scope of a transaction goes from when transaction() is called until the transaction is either committed or rolled back. It does not matter whether the database has been accessed through QSqlDatabase or through a model. The context of the transaction applies to all SQL statements, including those executed by independent queries and those executed by different models, as long as they apply to the same database within the same transaction's context.

Using a Context Manager for Unlocking sidebar » 549

Note that transactions are set on the database, accessed through the static QSqlDatabase.database() method. The database can also be accessed by calling the database() method on a model. Each database connection can handle one transaction at a time, so if we want more than one transaction at the same time, we must establish an extra connection for each extra transaction that we want to use.

def deleteAction(self):

index = self.logView.currentIndex() if not index.isValid(): return record = self.logModel.record(index.row()) action = record.value(ACTIONID).toString() if action == "Acquired":

QMessageBox.information(self, "Delete Log",

"The 'Acquired' log record cannot be deleted.<br>" "You could delete the entire asset instead.")

return when = unicode(record.value(DATE).toString()) if QMessageBox.question(self, "Delete Log",

"Delete log<br>%s %s?" % (when, action), QMessageBox.Yes|QMessageBox.No) == QMessageBox.No: return self.logModel.removeRow(index.row()) self.logModel.submitAll()

For deleting actions, the logic that we have implemented is that users cannot delete the "Acquired" log record, that is, the first log record. (But they can delete an asset, and with that all its log records, as we will see shortly.) If the log record is one that the user is allowed to delete and they confirm the deletion,

If we are using Python 2.6, or use from_future_import with_statement in

Python 2.5, we could simplify the code shown earlier by creating and using a context manager.

The transaction-oriented approach tries to arrange things so that problems cannot occur. An alternative approach is to assume that everything will work, and rely on the database to preserve foreign key relationships and other aspects of data integrity. This won't work with SQLite 3, since it does not enforce relational integrity, but it does work with some other databases. With this approach, we can often code without using transactions. Most of the time things will work fine, and for those few occasions when a problem occurs, we rely on the database to refuse to do any action that would break its rules, and to provide us with an error message that we can report to the user.

we simply call removeRow() on the log model and then submitAll() to update the underlying database.

def editActions(self):

form = ReferenceDataDlg("actions", "Action", self) form.exec_()

def editCategories(self):

form = ReferenceDataDlg("categories", "Category", self) form.exec_()

Since both the actions and the categories reference tables have identical structures, we can use the same smart dialog for when we want to drill down to add, edit, and delete their records. We give the dialog the name of the table in the database, and the name of the reference data to be shown in the user interface (in the dialog's title bar, for example).

Asset Manager - Edit Action Reference Data □ X

Name

Description

1

Acquired

When Installed

2

Broken

When failed and unusable

B

Repaired

When back in service

<

Routine maintenance

When tested, refilled, etc.

Add

Delete

OK

Figure 15.6 The Asset Manager Reference Data form

Figure 15.6 The Asset Manager Reference Data form

We won't review the code for the ReferenceDataDlg shown in Figure 15.6, because it does not have anything new to teach us. It uses a QTableView with a QSqlTableModel set to the table that is passed in to its constructor. Editing is in-place and handled automatically by the table view and table model. Adding a record is simply a matter of inserting a new row into the model and setting the view to it.

For reference data deletions, we execute a query to see whether the particular reference data record is being used by one of the other tables, that is, an action is used by any records in the logs table, or if a category is used by any records in the assets table. If the record is in use, we pop up an informative error message and do not permit the deletion to take place. Otherwise, we call removeRow() on the model for the relevant row and then submitAll() to commit the change to the database, just as we did when deleting an action.

Unlike reference data, adding and deleting assets is handled by the main form's methods.

def addAsset(self):

row = self.assetView.currentIndex().row() \

if self.assetView.currentIndex().isValid() else 0

QSqlDatabase.database().transaction()

self.assetModel.insertRow(row)

index = self.assetModel.index(row, NAME)

self.assetView.setCurrentIndex(index)

assetid = 1 query = QSqlQuery()

query.exec_("SELECT MAX(id) FROM assets") if query.next():

assetid = query.value(0).toInt()[0] query.prepare("INSERT INTO logs (assetid, date, actionid) "

"VALUES (:assetid, :date, :actionid)") query.bindValue(":assetid", QVariant(assetid + 1)) query.bindValue(":date", QVariant(QDate.currentDate())) query.bindValue(":actionid", QVariant(ACQUIRED)) query.exec_()

QSqlDatabase.database().commit() self.assetView.edit(index)

When the user adds a new asset we want to create a new log record for the asset with its action set to "Acquired". Naturally, we want either both of these records created, or, if something goes wrong, neither, and to do this we must use a transaction.

We begin by initiating a transaction. Then we insert a new row and make it the current one in the asset view. If this is the very first asset, its ID will be 1, but if there are other assets, its ID will be one more than the highest asset ID. We execute a query to find the current highest asset ID and then we use a prepared query (so that we don't have to worry about quoting), to insert a new record into the logs table. Once the new record has gone into the logs table, we commit the transaction. Now we will have one log record for the new asset with an action of "Acquired", and a new blank asset record. Finally, we initiate editing on the new asset's name field.

We will finish reviewing the main form by looking at the deleteAsset() method. The method is slightly involved, so we will look at it in three parts.

def deleteAsset(self):

index = self.assetView.currentIndex() if not index.isValid(): return

QSqlDatabase.database().transaction() record = self.assetModel.record(index.row()) assetid = record.value(ID).toInt()[0] logrecords = 1

query = QSqlQuery(QString("SELECT COUNT(*) FROM logs "

logrecords = query.value(0).toInt()[0]

We begin by starting a transaction. This is because if an asset is to be deleted, all its log records must also be deleted, and either both of these things must happen or neither, to maintain the database's relational integrity.

We know that there must be at least one log record, the "Acquired" record, but we perform a query to see what the total number of log records is.

msg = QString("<font color=red>Delete</font><br><b>%1</b>" "<br>from room %2") \ .arg(record.value(NAME).toString()) \ .arg(record.value(ROOM).toString()) if logrecords > 1:

msg += QString(", along with %1 log records") \ .arg(logrecords)

if QMessageBox.question(self, "Delete Asset", msg,

QMessageBox.Yes|QMessageBox.No) == QMessageBox.No: QSqlDatabase.database().rollback() return

Here we give the user the opportunity to confirm their deletion or to cancel it. If they cancel, we rollback the transaction and return.

query.exec_(QString("DELETE FROM logs WHERE assetid = %1") \

.arg(assetid)) self.assetModel.removeRow(index.row()) self.assetModel.submitAll() QSqlDatabase.database().commit() self.assetChanged(self.assetView.currentIndex())

We have deleted the log records using a SQL query, and the asset record using the model API. After the deletion we commit the transaction and call assetChanged() to make sure that the master-detail view is showing the correct log records.

We could have used the model API for both deletions. For example:

self.logModel.setFilter(QString("assetid = %1").arg(assetid))

self.logModel.select()

if self.logModel.rowCount() > 0:

self.logModel.removeRows(0, self.logModel.rowCount()) self.logModel.submitAll()

This completes our review of the Asset Manager application. Creating master-detail relationships between tables is quite straightforward, and the same thing can be done between tables and forms using a data widget mapper.

The SQL table models are very easy to use and "just work" with QTableViews. Also, we can create custom delegates to exercise complete control over the appearance and editing of fields, and where necessary we can use delegates to provide record level validation.

One issue that we have not had to concern ourselves with is that of creating unique keys for new records. We have solved the problem by using auto-incrementing ID fields in our tables. But sometimes auto-incrementing is not appropriate—for example, when a key is more complicated than a simple integer. We can handle such cases by connecting to the QSqlTableModel.before-Insert() signal. This signal gives the method it is connected to a reference to the record that is about to be inserted (after the user has finished editing it), so we can populate or change any fields we like just before the data actually gets inserted into the database.

There are also some additional SQL-specific signals that we can connect to—for example, beforeDelete() and beforeUpdate(); these might be useful if we wanted to record deletions or changes in a separate table. Finally, there is the primeInsert() signal—this is emitted when a new record is created, but before the user has had the chance to edit it. This is where we might populate the record with helpful default values. However, in all the examples in this chapter, we have put in default values when the user clicked an Add button. Also note that since QSqlRelationalTableModel is a subclass of QSqlTableModel, it too has these signals.

0 -3

Responses

  • Semhar Massawa
    How to create database connection with pyqt?
    3 years ago
  • YVONNE
    How to delete record QSqlQuery?
    3 years ago
  • Haylom
    How to delete tha selected row in qsqldatabase in pyqt5?
    2 years ago

Post a comment