Virtual column in QTableView?

Question

I'm started to learning Qt4 Model/View Programming and I have beginner question.

I have simple application which show sqlite table in QTableView:

class Model(QtSql.QSqlTableModel):
    def __init__(self, parent=None):
        super(Model, self).__init__(parent)
        self.setEditStrategy(QtSql.QSqlTableModel.OnFieldChange)

        self.setTable("test")
        self.select()

class App(QtGui.QMainWindow):
    def __init__(self, model):
        QtGui.QMainWindow.__init__(self)

        self.ui = Ui_MainWindow()
        self.ui.setupUi(self)

        self.ui.tableView.setModel(model)

if __name__ == "__main__":
    myDb = QtSql.QSqlDatabase.addDatabase("QSQLITE")
    myDb.setDatabaseName("test.db")

    if not myDb.open():
        print 'FIXME'

    model = Model()

    app = QtGui.QApplication(sys.argv)
    window = App(model)
    window.show()
    sys.exit(app.exec_())

Here how database looks like:

sqlite> create table test (a INTEGER, b INTEGER, c STRING);
sqlite> insert into test VALUES(1, 2, "xxx");
sqlite> insert into test VALUES(6, 7, "yyy");

So I'm getting something like:

+---+---+-----+
| a | b |  c  |
+---+---+-----+
| 1 | 2 | xxx |
+---+---+-----+
| 6 | 7 | yyy |
+---+---+-----+

Is it possible to modify Model to have in QTableView something like virtual column? For example something like:

+---+---+-----+-----+
| a | b | sum |  c  |
+---+---+-----+-----+
| 1 | 2 |  3  | xxx |
+---+---+-----+-----+
| 6 | 7 | 13  | yyy |
+---+---+-----+-----+

Or maybe I should do it in some other way?

Answer 1

Yes, you can do that. Although @BrtH's answer is relevant, models are tricky and it's easy to get lost. So I thought a more case in point example would be better.

Personally, I'd use a proxy model derived from QAbstractProxyModel. But, in your case reimplementing QSqlTableModel is also feasible. Below is an implementation for your goal. Note that, it's essential for you to know basics of Model/View methodology so that you understand what each method does.

class Model(QtSql.QSqlTableModel):
    def __init__(self, parent=None):
        super(Model, self).__init__(parent)
        self.setEditStrategy(QtSql.QSqlTableModel.OnFieldChange)

        self.setTable("test")
        self.select()


    def columnCount(self, parent=QtCore.QModelIndex()):
        # this is probably obvious
        # since we are adding a virtual column, we need one more column
        return super(Model, self).columnCount()+1


    def data(self, index, role=QtCore.Qt.DisplayRole):
        if role == QtCore.Qt.DisplayRole and index.column()==2:
            # 2nd column is our virtual column.
            # if we are there, we need to calculate and return the value
            # we take the first two columns, get the data, turn it to integer and sum them
            # [0] at the end is necessary because pyqt returns value and a bool
            # http://www.riverbankcomputing.co.uk/static/Docs/PyQt4/html/qvariant.html#toInt
            return sum(self.data(self.index(index.row(), i)).toInt()[0] for i in range(2))
        if index.column() > 2:
            # if we are past 2nd column, we need to shift it to left by one
            # to get the real value
            index = self.index(index.row(), index.column()-1)
        # get the value from base implementation
        return super(Model, self).data(index, role)


    def headerData(self, section, orientation, role=QtCore.Qt.DisplayRole):
        # this is similar to `data`
        if section==2 and orientation==QtCore.Qt.Horizontal and role==QtCore.Qt.DisplayRole:
            return 'Sum'
        if section > 2 and orientation==QtCore.Qt.Horizontal:
            section -= 1
        return super(Model, self).headerData(section, orientation, role)


    def flags(self, index):
        # since 2nd column is virtual, it doesn't make sense for it to be Editable
        # other columns can be Editable (default for QSqlTableModel)
        if index.column()==2:
            return QtCore.Qt.ItemIsSelectable | QtCore.Qt.ItemIsEnabled
        return QtCore.Qt.ItemIsSelectable | QtCore.Qt.ItemIsEnabled | QtCore.Qt.ItemIsEditable


    def setData(self, index, data, role):
        # similar to data.
        # we need to be careful when setting data (after edit)
        # if column is after 2, it is actually the column before that
        if index.column() > 2:
            index = self.index(index.row(), index.column()-1)
        return super(Model, self).setData(index, data, role)

Answer 2

It is most certainly possible. Below is an example of some of my own code, modified for your data.

import sqlite3

conn = sqlite3.connect('test.db')


class MyTreeModel(QAbstractItemModel):
    def __init__(self, parent=None):
        super(MyTreeModel, self).__init__(parent)

        c = conn.cursor()
        c.execute("SELECT a, b, c FROM 'test'")
        self.items = c.fetchall()

        self.columns = ['a', 'b', 'sum', 'c']

    def columnCount(self, index):
        return len(self.columns)

    def rowCount(self, parent):
        return len(self.items)

    def data(self, index, role=Qt.DisplayRole):
        if index.isValid():
            col= index.column()
            spot = self.items[index.row()]
            if role == Qt.DisplayRole:
                if col == 0 or col == 1:
                    return self.items[index.row()][col]
                elif col == 2:
                    return self.items[index.row()][0] + self.items[index.row()][1]
                elif col == 3:
                    return self.items[index.row()][2]

    def headerData(self, section, orientation, role=Qt.DisplayRole):
        if orientation == Qt.Horizontal and role == Qt.DisplayRole:
            return self.columns[section]

    def index(self, row, col, index):
        if not index.isValid():
            return self.createIndex(row, col)
        else:
            return QModelIndex()

    def parent(self, child):
        return QModelIndex()

This is a model for a QTreeView, but I think you can easily adapt it.
For more information about these methods and the model, see http://srinikom.github.com/pyside-docs/PySide/QtCore/QAbstractItemModel.html.

Answer 3

Have you looked at QSqlQueryModel? It allows to show results of any SQL query. Code for your example:

class Model(QtSql.QSqlQueryModel):
    def __init__(self, parent=None):
        super(Model, self).__init__(parent)
        self.setQuery("SELECT a, b, a + b, c FROM test")
        self.setHeaderData(0, QtCore.Qt.Horizontal, "a")
        self.setHeaderData(1, QtCore.Qt.Horizontal, "b")
        self.setHeaderData(2, QtCore.Qt.Horizontal, "sum")
        self.setHeaderData(3, QtCore.Qt.Horizontal, "c")

But take in mind:

The model is read-only by default. To make it read-write, you must subclass it and reimplement setData() and flags().