How to Show Qsqlquerymodel in Qml

Can't display data from QSqlQueryModel in a QML TableView

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]
        }
    }

}

How to assign SQL query output model from Qt to QML's TableView?

You have to subclass QSqlQueryModel and reimplement roleNames and data methods

MySqlModel.h

class MySqlModel: public QSqlQueryModel
{
    Q_OBJECT
public:
    MySqlModel(QObject *parent = 0) : QSqlQueryModel(parent) {}

    enum Roles {
        BinId = Qt::UserRole + 1,
        PartitionId,
        UnitId,
        ItemCount
    };
    QHash<int, QByteArray> roleNames() const {
        QHash<int, QByteArray> roles;
        roles[BinId] = "binIdRole";
        roles[PartitionId] = "partitionIdRole";
        roles[UnitId] = "unitIdRole";
        roles[ItemCount] = "itemCountRole";
        return roles;
    }
    QVariant data(const QModelIndex &index, int role) const {
        if (!index.isValid())
            return QVariant();

        QString fieldName;
        switch (role) {
            case BinId: fieldName = QStringLiteral("t1.BinId"); break;
            case PartitionId: fieldName = QStringLiteral("t1.PartitionId"); break;
            case UnitId: fieldName = QStringLiteral("t2.UnitId"); break;
            case ItemCount: fieldName = QStringLiteral("t2.ItemCount"); break;
        }
        if (!this->record().isGenerated(fieldName))
            return QVariant();
        else {
            QModelIndex item = indexInQuery(index);
            if ( !this->query().seek(item.row()) )
                return QVariant();
            return this->query().value(fieldName);
        }
        return QVariant();
    }
};

main.qml

Window  {
    visible: true
    width: 640
    height: 480
    TableView {
        anchors.fill: parent
        model: SQQL
        TableViewColumn{ role: "binIdRole" ; title: "BinId" ; visible: true}
        TableViewColumn{ role: "partitionIdRole" ; title: "PartitionId" }
        TableViewColumn{ role: "unitIdRole" ; title: "UnitId" }
        TableViewColumn{ role: "itemCountRole" ; title: "ItemCount" }
    }
}

I get undefined data from QSqlQueryModel in my ListView QML (SQLite database)

The problem was in the QML part of the program. After binding the ModelOne and ModelTwo to the property model of each of the ListViews while trying to get access to the fields in tables there was no need to to do this this way:

Text {
    text: modelOne.Time
}

instead it should have been:

Text {
    text: Time
}

Output data from mysql table using QSqlQueryModel

Since you are using a QSqlQueryModel that is based on a QAbstractTableModel then you can access each item using the role "modelData" or "display".

Text {
    text: model.modelData // or model.display
    anchors.fill: parent
    anchors.margins: 10
    color: 'black'
    font.pixelSize: 15
    verticalAlignment: Text.AlignVCenter
}

QSqlQueryModel reference error in roleName for ListView QML

How to observe the fields of the table are not roles, so they can not be accessed from QML, so to be accessed, the name of the fields must be added as a role, for this the class must be overwritten:

class SqlQueryModel: public QSqlQueryModel{
public:
    using QSqlQueryModel::QSqlQueryModel;
    QVariant data(const QModelIndex &index, int role) const
    {
        QVariant value;
        if (index.isValid()) {
            if (role < Qt::UserRole) {
                value = QSqlQueryModel::data(index, role);
            } else {
                int columnIdx = role - Qt::UserRole - 1;
                QModelIndex modelIndex = this->index(index.row(), columnIdx);
                value = QSqlQueryModel::data(modelIndex, Qt::DisplayRole);
            }
        }
        return value;
    }
    QHash<int, QByteArray> roleNames() const
    {
        QHash<int, QByteArray> roles = QSqlQueryModel::roleNames();
        for (int i = 0; i < this->record().count(); i ++) {
            roles.insert(Qt::UserRole + i + 1, record().fieldName(i).toUtf8());
        }
        return roles;
    }
};

Example:

#include <QGuiApplication>
#include <QQmlApplicationEngine>
#include <QQmlContext>

#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlQueryModel>
#include <QSqlRecord>
#include <QDebug>

static bool createConnection()
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(":memory:");
    if (!db.open()) {
        qDebug()<<"Unable to establish a database connection.\n"
                  "This example needs SQLite support. Please read "
                  "the Qt SQL driver documentation for information how "
                  "to build it.\n\n"
                  "Click Cancel to exit.";
        return false;
    }

    QSqlQuery query;
    query.exec("create table usuarios (ID INTEGER PRIMARY KEY AUTOINCREMENT, "
               "nombre VARCHAR(20), apellido VARCHAR(20))");
    query.exec("insert into usuarios values(1, 'Danny', 'Young')");
    query.exec("insert into usuarios values(2, 'Christine', 'Holand')");
    query.exec("insert into usuarios values(3, 'Lars', 'Gordon')");
    query.exec("insert into usuarios values(4, 'Roberto', 'Robitaille')");
    query.exec("insert into usuarios values(5, 'Maria', 'Papadopoulos')");
    return true;
}

class SqlQueryModel: public QSqlQueryModel{
public:
    using QSqlQueryModel::QSqlQueryModel;
    QVariant data(const QModelIndex &index, int role) const
    {
        QVariant value;
        if (index.isValid()) {
            if (role < Qt::UserRole) {
                value = QSqlQueryModel::data(index, role);
            } else {
                int columnIdx = role - Qt::UserRole - 1;
                QModelIndex modelIndex = this->index(index.row(), columnIdx);
                value = QSqlQueryModel::data(modelIndex, Qt::DisplayRole);
            }
        }
        return value;
    }
    QHash<int, QByteArray> roleNames() const
    {
        QHash<int, QByteArray> roles = QSqlQueryModel::roleNames();
        for (int i = 0; i < this->record().count(); i ++) {
            roles.insert(Qt::UserRole + i + 1, record().fieldName(i).toUtf8());
        }
        return roles;
    }
};

int main(int argc, char *argv[])
{
    QCoreApplication::setAttribute(Qt::AA_EnableHighDpiScaling);

    QGuiApplication app(argc, argv);
    if(!createConnection())
        return -1;

    SqlQueryModel sqlModel;
    sqlModel.setQuery("SELECT usuarios.nombre FROM usuarios");
    qDebug() << sqlModel.roleNames();

    QQmlApplicationEngine engine;
    engine.rootContext()->setContextProperty("sqlModel", &sqlModel);
    engine.load(QUrl(QStringLiteral("qrc:/main.qml")));
    if (engine.rootObjects().isEmpty())
        return -1;

    return app.exec();
}

Output:

QSqlQueryModel - override function data not being called

The problem is caused because you are using the name of the item: TableModel, the solution is to change the name of the context-property:

engine.rootContext()->setContextProperty("tableModel", &tableModel);

model: tableModel

On the other hand, I recommend using the model that is implemented in another post where I generalize the logic.

QSqlQueryModel TableView custom delegate

A possible solution is to use a Loader:

// ...
delegate: Row{
    Loader{
        active: model.column === 0
        sourceComponent: Image {
            id: statusImg
            height: 18
            width: 18
            source: "../../../Images/icons/tick.png"
        }
    }
    Text {
        text: model.display
    }
}
// ...

QSqlQueryModel in a QML view causes items being shown twice

Okay, finally I found the error.

According to this bug report :

https://bugreports.qt-project.org/browse/QTBUG-30205

You should not call exec() if you put the query in the constructor of QSqlQuery.

---

Related Topics