Virtual column in QTableView?

Question

I started to learn Qt4 Model / View Programming and I have a beginner question.

I have a simple application that shows a sqlite table in a 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's what the 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 get something like:

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

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

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

Or maybe I should do it another way?

Answer 1

Yes you can do it. Although @BrtH's answer matters, the models are complex and easy to get lost. So I thought a more suitable example would be better.

Personally, I would use a proxy model derived from QAbstractProxyModel . But in your case, it is also possible to re-execute QSqlTableModel . Below is an implementation for your purpose. Please note that it is important for you to know the basics of the 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 certainly possible. Below is an example 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 QTreeView, but I think you can easily adapt it.
For more information about these methods and models, see http://srinikom.github.com/pyside-docs/PySide/QtCore/QAbstractItemModel.html .

Answer 3

Have you looked at QSqlQueryModel ? It allows you to display the 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 consider:

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