Using QSqlQueryModel instead of QStandardItemModel

[JonB]

I have inherited a mess of an application which uses a variety of QStandardItemModel/QSqlQueryModel/QSqlTableModel/QSqlRelationalTableModel (probably written by different people over different times) for its database queries. Typically the data is presented in a QTreeView (please don't ask why it's not a QTableView).

I come from a rather different background from Qt's table-models and views, so my expectations may be a bit different from what Qt provides.

I feel like doing a rewrite so that at least QStandardItemModel is replaced by one of the QSql... types, since they all access SQL data. I feel that would be more consistent/maintainable. At present I am looking at an area like:

        model = QStandardItemModel(0, LetterColumns.ColumnCount, self)
        model.setHorizontalHeaderLabels(["Letter Code", "Type", "Description"])

        # dataQuerier.getAllLetters() is a utility function
        # which queries the database with a "SELECT" and returns all columns
        # via some "daos" module, I think
        for letter in self.dataQuerier.getAllLetters():
            model.insertRow(0)
            model.setData(model.index(0, LetterColumns.LetterCode), letter.ltCode)
            model.setData(model.index(0, LetterColumns.LtType), letterTypeMap[letter.type])
            ...

I do not like the principle of populating all rows in QStandardItemModel when QSqlQueryModel would do this. So I'd like to change this over to QSqlQueryModel (at minimum, might even go for QSqlTableModel).

I'm confused over where Qt would like me to put some stuff between the model and the view. So....

• A QTreeView attached to a QSqlQueryModel shows all columns from the query. To show just 3, I seem to have to iterate all columns with QTreeView.setColumHidden(col, False) and then QTreeView.setColumHidden(col, True) on just those I want to achieve a generic solution corresponding to the QStandardItemModel code above which imports just the desired columns into the model for viewing. Is that right?

• The above uses QStandardItemModel.setHorizontalHeaderLabels(["Letter Code", "Type", "Description"]) to simply set 3 visible columns on the model (which has many more). For QSqlQueryModel I seem to have found I need to call model.setHeaderData(0, QtCore.Qt.Horizontal, "Letter Code") for each column to achieve same effect. Is that right? Or, should I be trying to do it by some method of the QTreeView view instead of on the model? Or, if I use QSqlQueryModel I guess I could write my own SELECT query to return only the columns I want (but not if I go for QSqlTableModel as I believe that pulls all columns from the specified table)?

• You will see that one column's rows are model.setData(model.index(0, LetterColumns.LtType), letterTypeMap[letter.type]). This is a mapping (via in-memory data) from the database table value to the desired visual value. I'm afraid it cannot be changed to get the mapped value at the SQL query side. I can see this presents a bit of a problem using QSqlQueryModel, which is designed for data read from the database, rather than QStandardItemModel, which is effectively more flexible. How best to achieve? I don't think QSqlQueryModel allows me to append a column with my own values after the SELECT has returned, which might have been a "dirty" way? Or, am I supposed to override QSqlQueryModel.data()/setData() to do the mapping, putting the onus on the model? In my world, this would have been achieved via the view QTreeView having an "output-mapping" function which mapped values for desired columns as it rendered; it feels more like View functionality than Model?

If you have been kind enough to read through the above, do you think it is worth my pursuing this change from QStandardItemModel to a QSql...Model architecture, or am I just making more trouble than it is worth?

Thank you! :)

[SGaist]

Hi,

First thing to keep in mind: QSqlQueryModel is read-only but there are examples to make it writable.

If you want to filter things or modify what is shown by the view, a QSortFilterProxyModel might be of interest.

There are several ways to customise the header views content depending on what view you are using to show your table data.

All in all, no, you're not crazy, but I recommend starting small and modify the models gradually.

[JonB]

@SGaist
OK, thank you.

Then let's take one specific problem:

The columns which come back from the database from a SELECT are insufficient for me. I need an extra column (at least for display), whose value is calculated in Qt code based on one of the columns which is returned. In the code above this model.setData(model.index(0, LetterColumns.LtType), letterTypeMap[letter.type]).

Can I do this by adding my own column to the data/table returned from SQL query? (I don't think so.) Can I do this in QTree/TableView? i.e. have the view have its own extra column beyond what is the model? (Again, perhaps not?) Or else I seem to be stuck....

[VRonin]

There's a proxy model for that: KExtraColumnsProxyModel

[JonB]

@VRonin said in Using QSqlQueryModel instead of QStandardItemModel:

There's a proxy model for that: KExtraColumnsProxyModel

Oohh --- thank you! That's a third-party package I'd need to install and distribute/have end-users install, which I'm not dead keen on.... Does that imply that there is indeed no in-built facility for my extra column in Qt alone?

[VRonin]

There is not but that particular class can be just copy pasted and just adding a few lines to your license (that class comes from KDE which uses LGPL). No need to compile/distribute external binaries

[JonB]

@VRonin Thank you, that is useful, I will indeed investigate.

[JonB]

@VRonin
I have now had a chance to investigate KExtraColumnsProxyModel.

This seems like what I was looking for, and I will mark this post as solved.

However, unfortunately it has made me realise I cannot use any third-party code for Qt. It all tends to be written in C++ (understandably). I am using PyQt (not my choice!). Which leaves me with:

• I don't fancy introducing C++ code which I need to compile for my multi-platform targets.
• Even if I did, I don't think you can auto-generate PyQt/Python bindings.
• And looking at that code I don't fancy rewriting it natively in Python.

Unless there are any PyQt/Python experts here? Which is all rather a shame going forward, as it limits me to native Qt-supplied solutions... :(

[SGaist]

You can use sip to create the bindings you need for that class.

[JonB]

@SGaist
I'd be obliged if you would comment on the following:

I did look at sip, and more specifically https://riverbankcomputing.com/pipermail/pyqt/2016-May/037557.html, and https://steveire.wordpress.com/2016/05/18/generating-python-bindings-with-clang/ which discusses something like clang->sip to generate Python bindings for C++. The gist was that the source had to be annotated in a particular way (via XML, "rules files"??) to allow it to work, and even then there were issues and it needed some manual help. So it did not look straightforward?

Even if it does not apply here, I would like to understand whether generating PyQt/Python bindings from arbitrary C++ source is a totally simple, automated process or whether in practice it's not so simple? Thanks.

[SGaist]

Looks pretty interesting and I'd say worth a shot.

Well, the proposition of Steve makes it automated. Otherwise take a look at the PyMyLabel project and the sip example, the class you need isn't overly complicated so writing the corresponding sip files wouldn't be that complex.