Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

Programmatically modify QSqlTableModel field



  • Hello Qt community!

    I haven't found a way to bind controls to particular fields of QSqlTableModel, in fact according to some rare posts this is not possible without implementing some custom developed binding libraries.
    So the easiest way would be just to update it using signals. I didn't really found a post with this use case with an answer, so after reading docs I'm trying to implement it using QSqlRecord.setValue:

    QSqlRecord::setValue(const QString &name, const QVariant &val)
    Sets the value of the field called name to val. If the field does not exist, nothing happens.

    mdl2 = self.parent().window().model     # get model
    mdl2.record(self.rowIdx.row() + 1).setValue("CName", self.txtName.text())
    mdl2.submitAll()
    

    But it does not change the field in database. When I add print statements in between the commands, it outputs ("Alpha" is the original content, I added " CCCC"):

    New Value = Alpha CCCC     - new value via self.txtName.text()
    CName = Alpha       - before change - prints old string as expected
    CName = Alpha       - after change - prints old string 
    CName = Alpha       - after submitAll() - prints old string 
    

    SInce the print uses the same reference, it rules out an error in the row index and the name of the field (role).

    Side note: The model is used to display a tree, when a tree is clicked other properties of the node (QSqlRecord fields) are loaded into a side panel and now I try to submit changes made in the side panel to the particular record. Editing the nodes in the tree works fine.

    I assume this is a wrong concept and would like to be pointed out to a correct solution.



  • @Oak77 said in Programmatically modify QSqlTableModel field:

    I haven't found a way to bind controls to particular fields of QSqlTableModel, in fact according to some rare posts this is not possible without implementing some custom developed binding libraries.

    ? What about an editable QTableView for row+columns , or a QDataWidgetMapper for one row detail at a time?

    mdl2.record(self.rowIdx.row() + 1).setValue("CName", self.txtName.text())

    Use QSqlTableModel::setRecord() or QSqlTableModel::setData(). But not just QSqlTableModel::record()::setData() on its own, IIRC.



  • @JonB said in Programmatically modify QSqlTableModel field:

    What about an editable QTableView for row+columns , or a QDataWidgetMapper for one row detail at a time?

    I asume you meant to map a row to a QTableView, where columns would be rows. I don't think it would be easy to handle (with different type of widgets), but the main reason to avoid it in this particular case is, that I have hand-crafted panel with collapsible sections, etc.

    QDataWidgetMapper - that's really interesting stuff, it seems it's designed for exactly this purpose (citing documentation):

    QDataWidgetMapper can be used to create data-aware widgets by mapping them to sections of an item model. A section is a column of a model if the orientation is horizontal (the default), otherwise a row.

    Every time the current index changes, each widget is updated with data from the model via the property specified when its mapping was made. If the user edits the contents of a widget, the changes are read using the same property and written back to the model.

    It's a pity it's a secret :-). I found few posts with my use case, but lacking such answer.

    Use QSqlTableModel::setRecord() or QSqlTableModel::setData(). But not just QSqlTableModel::record()::setData() on its own, IIRC.

    OK. I'm still learning and it will take me some studying as for why to do it this way, but I'll test it, even if I finally use binding via QDataWidgetMapper, which seems to be correct and preferable way.

    Thank you very much for your advises! I will mark this thread resolved as soon as I test proposed methods.



  • @Oak77 said in Programmatically modify QSqlTableModel field:

    I asume you meant to map a row to a QTableView, where columns would be rows.

    I don't know what you mean here. A QTableView just displays your database rows+columns in a grid, and you can edit any column in any row. You have to add your own row Add/Delete buttons if you want those facilities. Your edit widgets will be in-table, so basically they will need to fit (to some extent), you probably don't have any room to do much fancy.

    A QDataWidgetMapper, OTOH, maps any one row at a time to a set of individual widgets you supply. There is no table to edit inside. You can be as free & easy with your widgets/layout as you wish. Sounds like that would be more suited to your "hand-crafted panel with collapsible sections, etc.".

    Some people use both together! Often known as "a master-detail layout". The QTableView is at the top, used only to select which row to edit, no editing in itself. Selecting a row populates the QDataWidgetMapper at the bottom with the data from the columns in that row only. User edits there.



  • @JonB said in Programmatically modify QSqlTableModel field:

    A QTableView just displays your database rows+columns in a grid, and you can edit any column in any row. ...

    OK, I use the QTableView in this basic way in other cases, but it's not suitable in this one (I'm porting my solution from WinForms, where I had it this way in a DataGridView and it wasn't very straight-forward and user-friendly).
    What I thought you meant is to do transposition of the columns into rows with header-value pairs. That sounded complex and the outcome might not be very clean.

    A QDataWidgetMapper, OTOH, maps any one row at a time to a set of individual widgets you supply. There is no table to edit inside. You can be as free & easy with your widgets/layout as you wish. Sounds like that would be more suited to your "hand-crafted panel with collapsible sections, etc.".

    Yes, exactly, sounds perfect.

    Some people use both together! Often known as "a master-detail layout". The QTableView is at the top, used only to select which row to edit, no editing in itself. Selecting a row populates the QDataWidgetMapper at the bottom with the data from the columns in that row only. User edits there.

    Yes, that is what is happening here basically. Except that instead of QTableView I'm using QTreeView and displaying only item's name and the structure in the tree. The tree also allows drag&drop modification of the structure. The details for each item are then loaded into the separate panel with dedicated sections and widgets.

    First I'll test suggested setRecord() and setData() methods, just to practise PyQt. Then I'll replace my signal and method for loading data into the panel with mapping my panel widgets to the model using QDataWidgetMapper.



  • @Oak77
    IIRC, QSqlTableModel::record(row) just copies out of the model's data() into a structure, it's not a "live" connection. So you can modify the content of that structure, like you did, but that does not in itself copy it back into the underlying data(). Hence nor committed back to the database. You have to call QSqlTableModel::setRecord(row) to copy back from a QSqlRecord to the data(), or use setData() directly.



  • @JonB said in Programmatically modify QSqlTableModel field:

    @Oak77
    IIRC, QSqlTableModel::record(row) just copies out of the model's data() into a structure, it's not a "live" connection. So you can modify the content of that structure, like you did, but that does not in itself copy it back into the underlying data(). Hence nor committed back to the database. You have to call QSqlTableModel::setRecord(row) to copy back from a QSqlRecord to the data(), or use setData() directly.

    Oh that was a super important point! Thank you very much!

    Now I understand, why it wasn't committed to database and it was easy to do a fix:

        rec = mdl2.record(self.rowIdx.row() + 1)
        rec.setValue("CName", self.txtName.text())
        mdl2.setRecord(self.rowIdx.row() + 1, rec)
    

    ...and it works as a charm! That would do the job decently. However, of course I'll commence with implementing QDataWidgetMapper to learn and understand it.



  • So far all my attempts to employ QDataWidgetMapper failed. The documentation seems to be pretty straight-forward and resulted in a code like this:

            mapper = QDataWidgetMapper(self)
            mapper.setModel(self.model)
            mapper.addMapping(self.propsbws.txtName,1)
    

    ...to bind 1 QLineEdit called txtName. It doesn't throw any errors, but it doesn't work either. After making sure my references are correct I'm thinking now (apart from that I have no further ideas how to debug and test it), that the reason it is not working is, that the self.model is not directly bound to the QTreeView and thus selecting a row does not let the model know a row is selected and thus the mapper is not activated. How actually selecting works with the model is not covered (AFAIK, pardon me if I missed anything) in the documentation of the QSqlTableModel. There's no direct method of setting selected row.
    My wild guess is that I should setup my own QItemSelectionModel as per (docs)?

    A QItemSelectionModel keeps track of the selected items in a view, or in several views onto the same model. It also keeps track of the currently selected item in a view.

    The reason why the QTreeView is unbound lies in combination of table data behind (whereas only one field is displayed), setting an icon and tree position from that table data, all the drag&drop functions to allow manipulation with the tree and perhaps my limited knowledge how to make all this directly bound. This is a different story, so I'm not elaborating on that (unless requested).

    EDIT:
    I tried to set:

            self.selectionmodel = QItemSelectionModel()  
            self.selectionmodel.setModel = self.model  
    

    self.model is my class TreeModel, which in turn is subclassed QStandardItemModel. And then on change in QTreeView I set:

            self.selectionmodel.setCurrentIndex(Idx, QItemSelectionModel.SelectCurrent)
    

    ...but when setCurrentIndex is invoked, I get a warning printed out into console:

    QItemSelectionModel: Setting the current index when no model has been set will result in a no-op.
    


  • @Oak77 said in Programmatically modify QSqlTableModel field:

    self.selectionmodel.setModel = self.model

    Wrong syntax:

    self.selectionmodel.setModel(self.model)
    

    Now there are no errors but it QDataWidgetMapper is not working either.



  • @Oak77
    Don't know what your problem is. I have used QDataWidgetMapper and found it obvious/easy, though I didn't use it in combination with a QTreeView. But the principles should be pretty clear. Start from a cut-down example, get it working, move up to your full situation. FWIW, there is an old example at https://doc.qt.io/archives/qt-5.7/qtwidgets-itemviews-simplewidgetmapper-example.html, don't know why they seemed to have removed it (or at least I can't find it now), you could check you are doing the right things.



  • @JonB Thank you for advises, I started to work on a dedicated simplified (minimal) code yesterday. I can also try a combination with QTableView. I also followed the example you referenced; in all I found 3 for 3 different cases (I'm listing them for possible followers of the thread):

    I'll do the MUC and study widget mapping and post a solution, if I will be able to find it.



  • Heureka!

    Got it working with this single line put inside a selection change function (connected to selectionChanged signal of the QTreeView:

    self.mapper.setCurrentIndex(self.DetailTree.selectionModel().currentIndex().row())
    

    It connects sets the current index to the mapper, taking the index from the tree view.


Log in to reply