Using Database Form Views

One of the easiest user interfaces we can provide for database data is a form that displays the fields from a single record at a time. In this section we will develop an application that uses such a form, initially a simplified version of the phone log database introduced in the preceding section, and then the full version which includes the foreign key field.

The examples presented in this section depend on the QDataWidgetMapper class introduced with Qt 4.2. The next section's example uses SQL table models and QTableView, and can be used with Qt 4.1 or later.

The simplified application is shown in Figure 15.2, and its source code is in chap15/phonelog.pyw; the full version is in chap15/phonelog-fk.pyw. When these applications are run for the very first time they create a database of fake records which they then use on subsequent runs. Generating these records using Qt's built-in SQLite is fast on Linux but very slow on some Windows machines. (A splash screen is used to disguise the slowness.)

The simplified application has a single table, calls, and no foreign key field. The form is represented by the PhoneLogDlg class. The initializer is quite long,

Figure 15.2 The simplified Phone Log application

so we will look at it in parts, skipping the layout since our focus in this chapter is on database programming.

class PhoneLogDlg(QDialog):

def_init_(self, parent=None):

super(PhoneLogDlg, self).__init__(parent)

callerLabel = QLabel("&Caller:")

self.callerEdit = QLineEdit()


today = QDate.currentDate()

startLabel = QLabel("&Start:")

self.startDateTime = QDateTimeEdit()


self.startDateTime.setDateRange(today, today)


endLabel = QLabel("&End:")

self.endDateTime = QDateTimeEdit()


self.endDateTime.setDateRange(today, today)


topicLabel = QLabel("&Topic:")

topicEdit = QLineEdit()


firstButton = QPushButton()


We create a label and a suitable editing widget for each field. We also create all the form's buttons, although we show the creation of only the first one. We pass a string to the Add, Delete, and Quit buttons' constructors to give them their captions, in addition to giving them icons.

self.model = QSqlTableModel(self) self.model.setTable("calls") self.model.setSort(STARTTIME, Qt.AscendingOrder)

With the widgets in place, we create a QSqlTableModel. Since we did not specify a particular database connection, it uses the default one. We tell the model which table it is to work on and call select() to make it populate itself with data. We also choose to apply a sort order to the table.

Now that we have suitable widgets and a model, we must somehow link them together. This is achieved by using a QDataWidgetMapper.

self.mapper = QDataWidgetMapper(self)

self.mapper.setSubmitPolicy(QDataWidgetMapper.ManualSubmit) self.mapper.setModel(self.model) self.mapper.addMapping(self.callerEdit, CALLER) self.mapper.addMapping(self.startDateTime, STARTTIME) self.mapper.addMapping(self.endDateTime, ENDTIME) self.mapper.addMapping(topicEdit, TOPIC) self.mapper.toFirst()

To make a data widget mapper work, we must give it a model and a set of mappings between the widgets in the form and the corresponding columns in the model. (The variables, ID, CALLER, STARTTIME, and so on, are set to 0, 1, 2, and so on at the start of the file.) The mapper can be set to submit changes automatically, or only when told. We prefer the latter approach because it gives us finer control and means that when the user navigates to a different record we can make sure that any unsaved changes are saved. Once we have set up the mapping, we need to make the mapper populate the widgets with a record; we have done this by calling toFirst(), which means that at startup, the first record is shown.

self.connect(firstButton, SIGNAL("clicked()"), lambda: self.saveRecord(PhoneLogDlg.FIRST)) self.connect(prevButton, SIGNAL("clicked()"), lambda: self.saveRecord(PhoneLogDlg.PREV)) self.connect(nextButton, SIGNAL("clicked()"), lambda: self.saveRecord(PhoneLogDlg.NEXT)) self.connect(lastButton, SIGNAL("clicked()"), lambda: self.saveRecord(PhoneLogDlg.LAST)) self.connect(addButton, SIGNAL("clicked()"), self.addRecord) self.connect(deleteButton, SIGNAL("clicked()"), self.deleteRecord) self.connect(quitButton, SIGNAL("clicked()"), self.accept)

self.setWindowTitle("Phone Log")

Lambda callbacks

The first four connections provide navigation. In each case, we call save-Record(), which saves any unsaved changes, and then navigates in accordance with the argument that has been wrapped in the lambda statement. This means that we need only a single method, saveRecord(), instead of one for each navigation button. However, the connections will work only with PyQt 4.1.1 or later. For earlier versions we must keep an instance variable (for example, a list) that contains references to the lambda functions to prevent them from being garbage-collected.

def accept(self):

self.mapper.submit() QDialog.accept(self)

If the user clicks Quit we call QDataWidgetMapper.submit(), which writes the current record back to the underlying model, and then we close the window.

def saveRecord(self, where):

row = self.mapper.currentIndex() self.mapper.submit() if where == PhoneLogDlg.FIRST: row = 0

elif where == PhoneLogDlg.PREV:

row = 0 if row <= 1 else row - 1 elif where == PhoneLogDlg.NEXT: row += 1

if row >= self.model.rowCount(): row = self.model.rowCount() - 1 elif where == PhoneLogDlg.LAST:

row = self.model.rowCount() - 1 self.mapper.setCurrentIndex(row)

If the user navigates, we must remember the current row, since it is forgotten after calling submit(). Then, after saving the current record, we set the row to be the one appropriate for the navigation the user requested (but kept within bounds), and then use setCurrentIndex() to move to the appropriate record.

def addRecord(self):

row = self.model.rowCount()




now = QDateTime.currentDateTime()




We have chosen to always add new records at the end. To do this we find the row after the last one, save the current record, and then insert a new record at the last row in the model. Then we set the mapper's current index to the new row, initialize a couple of fields, and give the caller field the focus, ready for the user to start typing.

def deleteRecord(self):

caller = self.callerEdit.text()

starttime = self.startDateTime.dateTime().toString(


if QMessageBox.question(self, QString("Delete"),

QString("Delete call made by<br>%1 on %2?") \ .arg(caller).arg(starttime), QMessageBox.Yes|QMessageBox.No) == QMessageBox.No: return row = self.mapper.currentIndex() self.model.removeRow(row) self.model.submitAll() if row + 1 >= self.model.rowCount(): row = self.model.rowCount() - 1 self.mapper.setCurrentIndex(row)

If the user clicks Delete we pick out some information from the current record and use it when we ask the user to confirm the deletion. If they confirm, we retrieve the current row, remove the row from the model, and call submitAll() to force the model to write back the change to the underlying data source (in this case the database). Then we finish up by navigating to the next record.

We have used submitAll() because we have performed the deletion on the model, not the mapper, and for databases we must confirm changes to the model by calling this method unless the view (or data widget mapper) has been set to automatically submit. The data widget mapper's API does not allow us to add or delete records, only edit existing ones, and for this reason, we must add or delete records using the underlying model.

The techniques we have used so far can be applied to any database table or editable database view to provide users with a means of navigating, adding, updating, and deleting records. However, in most cases, there are foreign keys to consider, an issue we will now address as we review the phone log application, shown in Figure 15.3.

Figure 15.3 The Phone Log application

The calls table (shown on page 447) has a foreign key outcomeid field. We want this field to appear as a combobox in the form, showing the outcomes table's name field for each corresponding ID. To do this we create a combobox in the usual way, but we do not populate it.

Since we are now using a table that has a foreign key we must use a QSql-RelationalTableModel rather than a QSqlTableModel.

self.model = QSqlRelationalTableModel(self)



QSqlRelation("outcomes", "id", "name")) self.model.setSort(STARTTIME, Qt.AscendingOrder)

The QSqlRelationalTableModel is very similar to a QSqlTableModel, except that it provides a few extra methods for handling relations. The setRelation() method takes a field index in the model, and a QSqlRelation object. The relation object is created with the name of the foreign key's table, the field to actually store, and the field to display.

The data widget mapper code must also be changed. In particular, we must use a QSqlRelationalDelegate rather than the standard built-in delegate, and we must set up the combobox that is used for the foreign key.

self.mapper = QDataWidgetMapper(self)

self.mapper.setSubmitPolicy(QDataWidgetMapper.ManualSubmit) self.mapper.setModel(self.model)

self.mapper.setItemDelegate(QSqlRelationalDelegate(self)) self.mapper.addMapping(self.callerEdit, CALLER) self.mapper.addMapping(self.startDateTime, STARTTIME) self.mapper.addMapping(self.endDateTime, ENDTIME) self.mapper.addMapping(topicEdit, TOPIC) relationModel = self.model.relationModel(OUTCOMEID) self.outcomeComboBox.setModel(relationModel) self.outcomeComboBox.setModelColumn(

relationModel.fieldIndex("name")) self.mapper.addMapping(self.outcomeComboBox, OUTCOMEID) self.mapper.toFirst()

The code is similar to what we had before. Setting the relational delegate is easy, but setting up the combobox is slightly subtle. First, we must retrieve the relation model (outcomes table) used by the model (calls table) to handle the foreign key. A QComboBox is actually a convenience view widget with a built-in model, just like a QListWidget; but it is possible to substitute our own model, and that's what we have done here. However, a combobox shows a single column, and our relation model has two columns (id, name), so we must specify which one to display. We cannot be certain about the column indexes used by the relation model (since it was created for us, not by us), so we use the fieldIndex() method with a field name to specify the correct column index. Once the combobox is set up, we can add it to the mapper like any other widget.

That completes the changes for handling foreign keys. In addition, we have taken the opportunity to make a couple of other small changes to the application.

In the simplified version, we connected the Quit button to a custom accept() method and, rather unintuitively, called accept() from the reject() method. This was to ensure that the application always saved the current record's changes before terminating. In the foreign key version, we have taken a different approach, and connected the Quit button to the done() method.

def done(self, result=None): self.mapper.submit() QDialog.done(self, True)

This method is called as a result of the Quit button connection, or if the user closes the window by clicking the X close button or presses Esc. We save the current record and call the base class's done() method. The second argument is mandatory, but it doesn't matter what value it holds in this case: A True value signifies accept() anda False value signifies reject(), but either way, the window will close.

We have made one other tiny change, adding two lines to the addRecord() method:



This ensures that when the user clicks Add to add a new record, the outcome combobox will have a sensible default, in addition to the date/time defaults we already set.

Forms are very useful for tables with lots of fields, especially if a lot of validation needs to be done on the basis of interfield dependencies. But for tables with fewer fields, or where users want to see multiple records, we need to use tabular views. This is the subject of the next section.

Was this article helpful?

0 -2


The main focus of this report is to show how to get involved in video marketing on the run, how to rank quickly on YouTube and Google using FREE semi-automatic tools and services. QUICKLY AND FREE. I will show methods and techniques I use to rank my videos, as well as free resources and tools to make video clips, to get backlinks and free traffic.

Get My Free Ebook

Post a comment