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)

score 2 · Accepted Answer · answered Sep 19 '17 at 18:57

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()

Thank you so much for this detailed explanation and example. I'll adjust my code and report back if it works. — davideps, Sep 21 '17 at 06:10
your code works beautifully. When I adjust my code based on your suggestions, the new combobox entry is added to the database with a NULL id and I get this error: "handleComboEdit Qt.EditRole, ID, 1, Qt.MatchExactly)[0] IndexError: list index out of range". However, the result of "ID = model.query().lastInsertId()" is an integer. So, it is the model.match that is likely not working yet. I'll report back shortly... — davideps, Sep 22 '17 at 06:20
Actually, the problem seems to be that I was defining the sqlite table incorrectly. "id INT PRIMARY KEY" results in a table that looks fine but does not work correctly. "id INTEGER PRIMARY KEY" works correctly. Thank you! — davideps, Sep 22 '17 at 06:38
your technique for getting the database ID from the combobox just saved me a lot of trial-and-error too! — davideps, Sep 22 '17 at 14:57
I'm having difficulty understanding *why* your code works. I see model.submitAll() but you don't explicitly grab the content with self.combo.currentText() and save it with model.insertRecord(). Is there a way to just insert the user entry to the database using model.insertRecord(-1, userentry) and then update the combo box? — davideps, Oct 07 '17 at 19:14
When the user presses return after entering something in the combo-box, a new item is added, and the underlying model is updated. This emits a signal, which is handled by the `handleComboEdit` slot. At that point, the model data may be out of sync with the database. So calling `submitAll()` rectifies that. (Strictly speaking, I should have set the [edit stategy](https://doc.qt.io/qt-5/qsqltablemodel.html#editStrategy) to `OnManualSubmit` for this to make complete sense, because the default is `OnRowChange` - but that doesn't really effect the overall logic of the example). — ekhumoro, Oct 07 '17 at 19:43
If you want to control everything yourself, you should set the [insert policy](https://doc.qt.io/qt-5/qcombobox.html#insertPolicy-prop) of the combo-box to `NoInsert`, so that it doesn't automatically add a new item. It would then be up to you to update the model (and perhaps the database, depending on the edit stategy). The combo-box will automatically reflect whatever changes you make to the model. — ekhumoro, Oct 07 '17 at 19:53
Thank you. After manually inserting into the database, I've found this refreshes the widget correctly: `self.paramModel.select()` and then `self.paramCombo.setCurrentIndex(self.paramCombo.findText(newentry))` — davideps, Oct 08 '17 at 05:50

How to link an editable QComboBox to a database

1 Answers1