Can't display data from QSqlQueryModel in a QML TableView

Question

I'm trying to display data from a MySQL database in a table with the help of TableViewcomponent of QML.

Initially I tried making a QSqlQueryModel object from a QSqlQuery object and pass it to QML context as a property. But I came to know from Qt documentation that I must implement roleNames() to supply column to role mapping to TableView, so I subclassed QSqlQueryModel like so

import sys
from PyQt5.QtCore import QUrl, Qt
from PyQt5.QtGui import QGuiApplication
from PyQt5.QtQuick import QQuickView
from PyQt5.QtSql import QSqlDatabase, QSqlQuery, QSqlQueryModel


class QtTabModel(QSqlQueryModel):
    def __init__(self):
        super(QtTabModel, self).__init__()

    @staticmethod
    def roleNames():
        roles = {
            Qt.UserRole + 1 : "id",
            Qt.UserRole + 2 : "name"
        }
        return roles

app = QGuiApplication(sys.argv)
view = QQuickView()
db = QSqlDatabase.addDatabase("QMYSQL")
db.setHostName("localhost")
db.setDatabaseName("qtdb")
db.setUserName("abc")
db.setPassword("xyz")
qry = QSqlQuery()
if db.open():
    qry = db.exec("SELECT id, name FROM qttab")
tabmodel = QtTabModel()
tabmodel.setQuery(qry)
ctx = view.rootContext()
ctx.setContextProperty("tabmodel", tabmodel)
view.setSource(QUrl.fromLocalFile("sqltabletest.qml"))
view.show()
app.exec()

and my QML is

import QtQuick 2.2
import QtQuick.Controls 1.1

TableView {
    width: 200
    height: 300
    model: tabmodel
}

but it's showing nothing, just a blank window

I can see my QSqlQuery is working as I can print data from database using value(n) method from that query. I've also checked with making rolenames() member function, but the end result is same.

def roleNames(self):
    roles = {
        Qt.UserRole + 1 : "id",
        Qt.UserRole + 2 : "name"
    }
    return roles

Update:

QSqlQueryModel works with widget classes, I've tested it with QTableView widget. But I need to make it work with QML.

import sys

from PyQt5.QtCore import Qt
from PyQt5.QtWidgets import QApplication, QTableView
from PyQt5.QtSql import QSqlDatabase, QSqlQuery, QSqlQueryModel


app = QApplication(sys.argv)
db = QSqlDatabase.addDatabase("QMYSQL")
db.setHostName("localhost")
db.setDatabaseName("qtdb")
db.setUserName("abc")
db.setPassword("xyz")
qry = QSqlQuery()
if db.open():
    qry = db.exec("SELECT id, name FROM qttab")
tabmodel = QSqlQueryModel()
tabmodel.setQuery(qry)
tabmodel.setHeaderData(0, Qt.Horizontal, "ID")
tabmodel.setHeaderData(1, Qt.Horizontal, "Name")
tabview = QTableView()
tabview.setModel(tabmodel)
tabview.show()
db.close()
app.exec()

Can anyone please help me to resolve this issue? Thanks in advance.

Answer 1

Ok, your comment reminded me that you indeed need to reimplement data() for the sake of QML's model. Why? Because QML's model calls data() with the roles given by roleName(). It doesn't call data() with Qt::DisplayRole like in QWidget world. Furthermore, you need to define TableViewColumn with role names, otherwise model will not call data(). Here's an example of how you can reimplement data():

import sys
from PyQt5.QtCore import QUrl, Qt, QVariant
from PyQt5.QtCore import QObject, pyqtSlot
from PyQt5.QtGui import QGuiApplication
from PyQt5.QtQuick import QQuickView
from PyQt5.QtSql import QSqlDatabase, QSqlQuery, QSqlQueryModel

class QtTabModel(QSqlQueryModel):
    def __init__(self):
        super(QtTabModel, self).__init__()

    def roleNames(self):
        roles = {
            Qt.UserRole + 1 : 'id',
            Qt.UserRole + 2 : 'name'
        }
        return roles

    def data(self, index, role):
        if role < Qt.UserRole:
            # caller requests non-UserRole data, just pass to papa
            return super(QtTabModel, self).data(index, role)

        # caller requests UserRole data, convert role to column (role - Qt.UserRole -1) to return correct data
        return super(QtTabModel, self).data(self.index(index.row(), role - Qt.UserRole -1), Qt.DisplayRole)

    @pyqtSlot(result=QVariant)  # don't know how to return a python array/list, so just use QVariant
    def roleNameArray(self):
        # This method is used to return a list that QML understands
        list = []
        # list = self.roleNames().items()
        for key, value in self.roleNames().items():
            list.append(value)

        return QVariant(list)

Add TableViewColumn to TableView. Keep in mind that role are case-sensitive. They must match exactly with what roleNames() returns:

import QtQuick 2.2
import QtQuick.Controls 1.1

TableView {
    width: 200
    height: 300
    model: tabmodel
    TableViewColumn {
        role: "id" // case-sensitive, must match a role returned by roleNames()
    }
    TableViewColumn {
        role: "name"
    }

}

Here's a way to automatically generate TableViewColumn. It calls roleNameArray slot defined in python code above to get the role name list. We don't call roleNames() here since I don't know how to make QML understand the result it returns :), so we have to convert it to a list. Finally we loop through the list and call TableView.addColumn to create columns:

TableView {
    width: 200
    height: 300
    model: tabmodel
    Component.onCompleted: {
        var roles = model.roleNameArray()
        for (var i=0; i<roles.length; i++) {
          var column = addColumn( Qt.createQmlObject(
            "import QtQuick.Controls 1.1; TableViewColumn {}",
            this) )
          column.role = roles[i]
          column.title = roles[i]
        }
    }

}