Python example of using QSqlTableModel to link a database table to a QComboBox control.

Qt provides some useful mappers for easily linking relational database tables with table/grid widgets. This works well for quickly producing simple CRUD applications. But sometimes we might want to escape the limitations of table and grid controls, and do something fancier — something to provide users with a more customised, fine-grained interface, which usually ends up being something based on forms/dialogs. So we design a form, populate it with controls, and we are then faced with having to populate individual controls from database tables.

I did this recently, and was then faced by what I assume would be a fairly common situation: I had a simple database table for storing status values, and wanted to populate a combo box on a dialog with data from that table (and retrieve the matching ID the user has selected at some point).

Surprisingly I couldn’t find a simple example of this online or in the Summerfield book, so I thought I’d write about the approach I used. (Let me know in the comments if there’s a better way!)

For example, in a hypothetical contact management application, we might have an SQLite table for storing the “category” of phone numbers:

CREATE TABLE phone_number_type (
     id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
     label TEXT NOT NULL
);

So in this case we essentially have a glorified enum (but perhaps one which the user can add/modify, so it can’t just be hard-coded into the application). At a minimum, there would be an integer primary key field, along with a corresponding string for the category name. (There could possibly be other fields as well, but we can happily ignore them for the task at hand.)

We can populate the table with some data like so:

INSERT INTO "phone_number_type" VALUES(1,'Home');
INSERT INTO "phone_number_type" VALUES(2,'Work');
INSERT INTO "phone_number_type" VALUES(3,'Mobile');
INSERT INTO "phone_number_type" VALUES(4,'Mistress');
INSERT INTO "phone_number_type" VALUES(5,'Meth Dealer');

Since in such cases we’re usually dealing with a small amount of data, a valid approach might be to just load all the data from the table into a data structure in memory, and populate the combo box from that as appropriate. However, this approach is less than ideal when the table in question contains data that the user can modify, and therefore might change frequently. It also means writing repetitive boilerplate code.

In this case we can use the Qt Framework’s QSqlTableModel class to help us out. The following example creates a simple dialog box for editing phone numbers. The dialog box will contain a combo box widget populated with data from the phone_number_type table, as defined above.

#!/usr/bin/env python
import os
import sys
from PyQt4.QtCore import *
from PyQt4.QtGui import *
from PyQt4.QtSql import *

CONFIG_DATABASE_PATH = "./"
CONFIG_DATABASE_NAME = "comboboxexample.db"

class EditItemDlg(QDialog):
    def __init__(self, parent=None):
        super(EditItemDlg, self).__init__(parent)
        self.setWindowTitle("Edit Phone Number")

        # Create model
        self.model = QSqlTableModel(self)
        self.model.setTable("phone_number_type")
        self.model.select()

        buttonBox = QDialogButtonBox(QDialogButtonBox.Ok |
                                     QDialogButtonBox.Cancel)
        buttonBox.button(QDialogButtonBox.Ok).setDefault(True)

        self.phoneEdit = QLineEdit()
        phoneLabel = QLabel("&Phone Number:")
        phoneLabel.setBuddy(self.phoneEdit)

        # Create combo and set its model
        self.typeComboBox = QComboBox()
        self.typeComboBox.setModel(self.model)
        self.typeComboBox.setModelColumn(
                          self.model.fieldIndex("label"))
        typeLabel = QLabel("&Type:")
        typeLabel.setBuddy(self.typeComboBox)

        controlLayout = QGridLayout()
        controlLayout.addWidget(phoneLabel, 0, 0)
        controlLayout.addWidget(self.phoneEdit, 0, 1)
        controlLayout.addWidget(typeLabel, 0, 2)
        controlLayout.addWidget(self.typeComboBox, 0, 3)
        controlLayout.addWidget(buttonBox, 1, 0, 1, 4,
                                Qt.AlignRight)

        self.setLayout(controlLayout)
        self.resize(500, 125)

        self.connect(buttonBox, SIGNAL("accepted()"),
                     self, SLOT("accept()"))
        self.connect(buttonBox, SIGNAL("rejected()"),
                     self, SLOT("reject()"))

def main():
    app = QApplication(sys.argv)

    filename = os.path.join(CONFIG_DATABASE_PATH,
                            CONFIG_DATABASE_NAME)
    db = QSqlDatabase.addDatabase("QSQLITE")
    db.setDatabaseName(filename)
    if not db.open():
        QMessageBox.warning(None, "Combo Box Example",
            QString("Database Error: %1").arg(db.lastError().text()))
        sys.exit(1)

    # Would normally be invoked as modal dialog.
    # But for simplicity we use it as the main form here.
    form = EditItemDlg()
    form.show()
    app.exec_()
    del form
    del db


if __name__ == '__main__':
    main()

The main() function is mostly just Qt boilerplate — and code to initialise the database and main dialog window. The EditItemDlg class demonstrates a simple use of QSqlTableModel to populate a combo box.

In the __init__ method of EditItemDlg, we create an QSqlTableModel for the phone_number_type table. We then create QComboBox control, and replace its default model with our QSqlTableModel by calling the setModel() method.

We then call setModelColumn() to tell the combo box which database field it should use as option text to populate the choices in the drop down of the combo box — in this case, the label field of the phone_number_type table.

The result is a combo box widget conveniently populated with choices based on data currently in the phone_number_type table.