How to link an editable QComboBox to a database

Question

I'm using Pyside to create a combobox that draws from a sqlite database. Users can select one of the existing items or add a new item. Users see the item names (called "param"), but I need access to the item ids from the database. So, there are two steps:

READING ITEMS: I'm able to read from the database, but I can't display the item names while accessing the item ids behind the scenes.

ADDING ITEMS: Do I need to detect a change in the combobox and then use an SQL insert command or does the model handle this for me?

This code reads from the database but does not display correctly:

param_model = QSqlQueryModel()
param_model.setQuery("select id, param from partable order by param")
param_model.setHeaderData(0, Qt.Horizontal,"id")
param_model.setHeaderData(1, Qt.Horizontal,"param")

param_view = QTableView()
param_view.setColumnHidden(0,True)

self.paramfield = QComboBox()
self.paramfield.adjustSize()
self.paramfield.setEditable(True)
self.paramfield.setModel(param_model)
self.paramfield.setView(param_view)

Answer 1

There are several issues with your code. Firstly, you need to use a QSqlTableModel, which is editable, rather than a QSqlQueryModel, which is read only. Secondly, you do not need to set headers or a view on the combo-box. Thirdly, you must set the correct model column on the combo-box in order to display the appropriate values.

On the question of adding items: it is only necessary to submit the changes via the model. However, it is also often desirable to find the id or index of the item that was added (e.g. in order to reset the current index). This can be a little tricky if the model is sorted and/or allows duplicate entries.

The demo script below shows you how to deal with all the issues mentioned above:

import sys
from PySide import QtCore, QtGui, QtSql

class Window(QtGui.QWidget):
    def __init__(self):
        super(Window, self).__init__()
        self.db = QtSql.QSqlDatabase.addDatabase('QSQLITE')
        self.db.setDatabaseName(':memory:')
        self.db.open()
        self.db.transaction()
        self.db.exec_(
            'CREATE TABLE partable'
            '(id INTEGER PRIMARY KEY, param TEXT NOT NULL)'
            )
        self.db.exec_("INSERT INTO partable VALUES(1, 'Red')")
        self.db.exec_("INSERT INTO partable VALUES(2, 'Blue')")
        self.db.exec_("INSERT INTO partable VALUES(3, 'Green')")
        self.db.exec_("INSERT INTO partable VALUES(4, 'Yellow')")
        self.db.commit()
        model = QtSql.QSqlTableModel(self)
        model.setTable('partable')
        column = model.fieldIndex('param')
        model.setSort(column, QtCore.Qt.AscendingOrder)
        model.select()
        self.combo = QtGui.QComboBox(self)
        self.combo.setEditable(True)
        self.combo.setModel(model)
        self.combo.setModelColumn(column)
        self.combo.lineEdit().returnPressed.connect(self.handleComboEdit)
        layout = QtGui.QVBoxLayout(self)
        layout.addWidget(self.combo)

    def handleComboEdit(self):
        if self.combo.lineEdit().isModified():
            model = self.combo.model()
            model.submitAll()
            ID = model.query().lastInsertId()
            if ID is not None:
                index = model.match(
                    model.index(0, model.fieldIndex('id')),
                    QtCore.Qt.EditRole, ID, 1, QtCore.Qt.MatchExactly)[0]
                self.combo.setCurrentIndex(index.row())

if __name__ == '__main__':

    app = QtGui.QApplication(sys.argv)
    window = Window()
    window.setGeometry(800, 50, 200, 50)
    window.show()
    sys.exit(app.exec_())   

PS: here's how to get the id from the combo-box, using its current index:

model = self.combo.model()
index = self.combo.currentIndex()
ID = model.index(index, model.fieldIndex('id')).data()