Using QSqlQueryModel instead of QStandardItemModel
I have inherited a mess of an application which uses a variety of
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
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
QStandardItemModelis 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
QSqlQueryModelwould do this. So I'd like to change this over to
QSqlQueryModel(at minimum, might even go for
I'm confused over where Qt would like me to put some stuff between the model and the view. So....
QTreeViewattached to a
QSqlQueryModelshows 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
QStandardItemModelcode 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
QSqlQueryModelI 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
QTreeViewview instead of on the model? Or, if I use
QSqlQueryModelI guess I could write my own
SELECTquery to return only the columns I want (but not if I go for
QSqlTableModelas 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
QSqlQueryModelallows me to append a column with my own values after the
SELECThas 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
QTreeViewhaving 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
QSql...Modelarchitecture, or am I just making more trouble than it is worth?
Thank you! :)
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.
OK, thank you.
Then let's take one specific problem:
The columns which come back from the database from a
SELECTare 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....
There's a proxy model for that: KExtraColumnsProxyModel
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?
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
@VRonin Thank you, that is useful, I will indeed investigate.
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... :(
You can use sip to create the bindings you need for that class.
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
sipto 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.
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.