CODEWITHSUNDEEP
pythonqtsqlitepyqtpyqt5
Eric Myers
Eric Myers

Reputation: 183

QDataWidgetMapper-find index for sqlite database row in pyqt

I want to use QDataWidgetMapper to link a widget with data in a sqlite table. The methods I find for specifying the data's row are ones that seem to only work if iterating through the rows sequentially one at a time: setCurrentIndex, toFirst, toLast, toNext, toPrevious. I would like to be able to jump directly to the row containing the data needed, and retrieve a handle to that row to then pass to the mapper.

The primary key of the table is an integer that autoincrements, but since rows can be deleted there is no guarantee that the index could be guessed using the key.

-------------------------
|     item_info         |
|-----------------------|
|item_id  |  item_desc  |
|---------|-------------|
|1        |  item 1     |
|2        |  item 2     |
|7        |  blue item  |
|15       |  pink item  | #ordered index is 3
-------------------------

I briefly hoped to be able to use rowid to return the ordered index of table rows, but it looks like that only returns the primary key and isn't related to a notion of the row's position in the table.

I could use a counter to go through each row until the correct one is found, and then use the resulting counter value as the argument for setCurrentIndex, but this seems like a terrible solution. Here's an example of that, displaying the item_desc from the row with item_id 15:

import sys 
from PyQt5 import Qt,QtWidgets,QtSql

class Form(QtWidgets.QWidget):
    def __init__(self,parent=None):
        QtWidgets.QWidget.__init__(self,parent)
        self.line_edit=QtWidgets.QLineEdit()
        layout=QtWidgets.QVBoxLayout()
        layout.addWidget(self.line_edit)
        self.setLayout(layout)        

        db=QtSql.QSqlDatabase.addDatabase("QSQLITE")
        db.setDatabaseName("populated_db.db")
        db.open()

        self.item_info_model = QtSql.QSqlTableModel(self)
        self.item_info_model.setTable("item_info")
        self.item_info_model.select()

        self.item_info_mapper = Qt.QDataWidgetMapper(self)
        self.item_info_mapper.setSubmitPolicy(Qt.QDataWidgetMapper.ManualSubmit)
        self.item_info_mapper.setModel(self.item_info_model)

        self.item_info_mapper.addMapping(self.line_edit, 1) #establishes mapping between line edit and the second column of data


        self.show_description(15)

    def show_description(self,desired_item_id):
        ###### NOW HERE IS AN UGLY TECHNIQUE TO SEARCH ONE BY ONE UNTIL DESIRED ROW IS DISCOVERED ######
        i=0 #start counter
        success=False

        query=QtSql.QSqlQuery()
        query.exec("SELECT item_id FROM item_info")
        query.first()

        while not success:
            if query.value(0)==desired_item_id:
                success=True
                self.item_info_mapper.setCurrentIndex(i)
            else:
                i+=1
                query.next()

a=QtWidgets.QApplication(sys.argv)
form=Form()
form.show()
a.exec_()

This accomplishes the task, but it seems like there should be a better way that uses the database efficiently, with some direct way to set the mapping rather than a numeric index. I've been unable to find it so far if that is the case though.

Upvotes: 2

Views: 1045

Answers (1)

a_manthey_67
a_manthey_67

Reputation: 4286

you can use the record() method of your model:

def show_description(self,desired_item_id):
    for i in range(self.item_info_model.rowCount()):
        if self.item_info_model.record(i).value(0) == desired_item_id:  # the field can be defined by position or by name 
            self.item_info_mapper.setCurrentIndex(i)
            break

alternativaly you can set a filter to your model:

def show_description(self,desired_item_id):
    self.item_info_model.setFilter('item_id = {}'.format(desired_item_id))
    self.item_info_mapper.toFirst()

your model is already populated (by self.item_info_model.select() in class Form(), so it will be reselected automatically see documentation

Upvotes: 2

Related Questions