Need help with QSqlModel - PySide6

[SGaist]

@LS-KS I fail to see the relation with setQuery.

You need to translate SqlQueryModel from the Wiki in Python. This one is a generic class that you can then use in place of QSqlQueryModel as it will provide the extra roles in an automated fashion.

[LS-KS]

Hi @SGaist,
I had some problems to adapt the wiki article to PySide6.
I endet up with following model implementation:

class SqlQueryModel(QSqlQueryModel):

    def __init__(self, parent=None):
        super().__init__(parent)
        self._roleNames = {}


    def setQuery(self, query: str, db: QSqlDatabase):
        super().setQuery(query, db)
        self.generateRoleNames()

    def generateRoleNames(self):
        self._roleNames = {}
        for i in range(super().record().count()):
            self._roleNames[QtCore.Qt.UserRole + i + 1] = super().record().fieldName(i)
        print(f"generateRoleNames produced: {self._roleNames =}")

    def data(self, index:QModelIndex, role: int = ...):
        print(f"data called with {index = }, {role = }")
        if role < QtCore.Qt.UserRole:
            print("if here")
            if not self.query().exec():
                print("Error while executing", self.query().lastError().text())
            i: int =0
            while self.query().next():
                data = self.query().value(index.column())
                if i == index.row(): break
                i +=1
        else:
            print("else here")
            columnIdx = role - QtCore.Qt.UserRole - 1
            modelIndex = self.index(index.row(), columnIdx)
            data = super().data(modelIndex, QtCore.Qt.DisplayRole)
        print(f"fetched data: {data =} ")
        return data

I have to say, that I still see no data in my ListView. But since the data function is not called when my StackLayout loads the view the issue might not necessary related to QSqlQueryModel.

My connect method from controller:

    def connect(self, db_name, user, password):
        if any([db_name == "", user == "", password == ""]):
            self.loginSuccess.emit(False)
            return
        db_file = Path(__file__).resolve().parent.parent / 'data' / f"{db_name}.sqlite"
        connection: QSqlDatabase = QSqlDatabase.addDatabase("QSQLITE", db_name)
        connection.setDatabaseName(str(db_file))
        if not connection.open(user, password):
            print("Error:", connection.lastError().text())
            self.loginSuccess.emit(False)
            return
        if connection.isOpen():
            self.db_name = db_name
            self._topicmodel.setQuery(query="SELECT * FROM topics", db=connection)
            self._topicmodel.query().prepare("SELECT * FROM topics")
            print("0; 0: ", self._topicmodel.data(self._topicmodel.index(0,0), QtCore.Qt.DisplayRole))
            print("0; 0: ", self._topicmodel.data(self._topicmodel.index(0,1), QtCore.Qt.DisplayRole))
            self._topicmodel.setHeaderData(0, QtCore.Qt.Horizontal, "id")
            self._topicmodel.setHeaderData(1, QtCore.Qt.Horizontal, "topic")
            self.loginSuccess.emit(True)
        else:
            self.loginSuccess.emit(False)

If I don't call prepare() the return value of data is always None and exec(), isValid(), next(), result() are returning False.
I found this by trying each method of QSqlQuery().

I find this odd and would like you to ask how this could be refactored....

[SGaist]

Why are you calling query.exec() in your data method ?

Revert it to its original implementation and verify it's working properly with a QTableView.

Once that done, show your QML code.

[LS-KS]

Happy Easter, @SGaist !
Thank you so much so far.
@SGaist said in Need help with QSqlModel - PySide6:

Why are you calling query.exec() in your data method ?

it didn't work and i went through the reference. I made a gui-less example that showed i can fetch data after exec() was called.

The followeing code indeed shows the data correctly in a QTableView:

from PySide6.QtWidgets import QTableView, QApplication
from PySide6.QtSql import QSqlDatabase
from viewmodel.models import SqlQueryModel
from pathlib import Path

app = QApplication()
db_name,  user,  password= 'TestDB', '', ''
db_file = Path(__file__).resolve().parent / f"{db_name}.sqlite"

connection: QSqlDatabase = QSqlDatabase.addDatabase("QSQLITE", db_name)
connection.setDatabaseName(str(db_file))

if not connection.open(user, password):
    print("Error:", connection.lastError().text())

model = SqlQueryModel()
model.setQuery('SELECT * FROM topics', connection)

view = QTableView()
view.setModel(model)
view.show()
app.exec()

I adjusted my connect function, so it is identically to the code above:

I also adjusted my data method:

def data(self, index:QModelIndex, role: int = ...):
    print(f"data called with {index = }, {role = }")
    data = None
    if role < QtCore.Qt.UserRole:
        data = super().data(item = index, role=role)
    else:
        print("else here")
        columnIdx = role - QtCore.Qt.UserRole - 1
        modelIndex = self.index(index.row(), columnIdx)
        data = super().data(modelIndex, QtCore.Qt.DisplayRole)
    print(f"fetched data: {data =} ")
    return data

When I print the results in my command line I get the correct results. So overall I think I understood how to fetch data and set up a QSqlQueryModel. The query execution is not done by myself.

For QML:

• I keep the model as member variable named '_topicmodel' of my database controller.
• I defined a property for the model

    @property
    def topicmodel(self):
        return self._topicmodel

• My 'main.qml' has a StackView. The relevant StackView Component has a TableView which is defined as follows:

TableView{
        id: topics
        width: 200
        anchors.top: btnRow.bottom
        anchors.left: parent.left
        anchors.bottom: parent.bottom
        model: DbController.topicmodel
        delegate: Item {
            width: topics.width
            height: 50
            Row{
                Text{
                    text: model.topic
                    color: 'white'
                }
            }

        }
    }

[SGaist]

@LS-KS what are the column names of your topics table ?

[LS-KS]

@SGaist
Right now it's a database for testing:

• id: integer, primary key
• topic: varcher(100)

[LS-KS]

@SGaist,

I know two ways of making a model accessible in qml:

• create a model at register it as QML context property (i think this is deprecated)
• use QML_ELEMENT macro

I just tested a QML TableModel which showed data. In my repositories I have examples how to use a tablemodel as context property and as QmlElement.

Since context properties are deprecated i wouldn't follow this.

From my understanding: Using QML_ELEMENT would make the class accessible in QML aka creating a Python/C++ object from QML. A TableModel being created in QML wouldn't be the same object as created in my database controller- correct?

This Overview suggests using QML_SINGLETON. But I'm not sure how this would work when using databases.

[LS-KS]

I finally did it.

Somehow it is not okay to keep a model as member of a singleton. The solution was to make the model also a QmlElement and/or QmlSingleton. I change the query using a signal.

Thank you, @SGaist !

[SGaist]

You're welcome !

That said, that singleton issue is surprising.

Do you still have the implementation at hand ?

[LS-KS]

@SGaist ,Here is the code, rebuilt from GitHub History:

Controller:

from pathlib import Path
import os
from PySide6.QtQml import QmlSingleton, QmlElement
from PySide6 import QtCore
from PySide6.QtSql import QSqlQueryModel, QSqlRelationalTableModel, QSqlDatabase, QSqlQuery
from PySide6.QtWidgets import QTableView

from viewmodel.models import SqlQueryModel
QML_IMPORT_NAME = "io.qt.textproperties"
QML_IMPORT_MAJOR_VERSION = 1



@QmlElement
@QmlSingleton
class DbController(QtCore.QObject):
    loginSuccess = QtCore.Signal(bool)
    logoutSuccess = QtCore.Signal()
    def __init__(self):
        super().__init__(None)
        self.db_name = ""
        self.db_columns = ['user', 'topic', 'description', 'year', 'date', 'start', 'end', 'duration']
        self.db_types = ['TEXT', 'TEXT', 'TEXT', 'INTEGER', 'TEXT', 'TEXT', 'TEXT', 'TEXT']
        self._topicmodel: SqlQueryModel = SqlQueryModel()
        self.topicmodel = QtCore.Property(SqlQueryModel, fget= self.get_topicmodel )
        self._entrymodel: QSqlRelationalTableModel = None

    def get_topicmodel(self):
        return self._topicmodel
    @QtCore.Slot(str, str, str)
    def connect(self, db_name, user, password):
        # catch empty inputs
        if any([db_name == "", user == "", password == ""]):
            self.loginSuccess.emit(False)
            return
        # resolve database path
        db_file = Path(__file__).resolve().parent.parent / 'data' / f"{db_name}.sqlite"
        # establish a connection
        connection: QSqlDatabase = QSqlDatabase.addDatabase("QSQLITE", db_name)
        connection.setDatabaseName(str(db_file))
        # open the database
        if not connection.open(user, password):
            print("Error:", connection.lastError().text())
            self.loginSuccess.emit(False)
            return
        # set topicmodel data
        if connection.isOpen():
            self.db_name = db_name
            self._topicmodel.setQuery('SELECT * FROM topics', connection)
            self.loginSuccess.emit(True)
        else:
            self.loginSuccess.emit(False)
        #index = QSqlQueryModel.index(self.topicmodel, 0, 1)
        #data = self.topicmodel.data(index, QtCore.Qt.UserRole + 2)


    @QtCore.Slot(str, result = bool)
    def check_database_name(self, db_name) -> bool:
        if db_name == "":
            return False
        db_file = Path(__file__).resolve().parent.parent / 'data' / f"{db_name}.sqlite"
        result = os.path.exists(db_file)
        return result

    @QtCore.Slot(str, str, str, result = bool)
    def create_database(self, db_name, user, password) -> bool:
        db_file = Path(__file__).resolve().parent.parent / 'data' / f"{db_name}.sqlite"
        db = QSqlDatabase.addDatabase("QSQLITE")
        db.setDatabaseName(str(db_file))
        db.setUserName(user)
        db.setPassword(password)
        if not db.open():
            print("Error:", db.lastError().text())
            return False
        creation_query = f"CREATE TABLE IF NOT EXISTS timecapturing ("
        creation_query += f"id INTEGER PRIMARY KEY, "
        for i, column in enumerate(self.db_columns):
            creation_query += f"{column} {self.db_types[i]}, "
        creation_query += ")"
        query = QSqlQuery(creation_query)
        query.exec()

        list_query = f"CREATE TABLE IF NOT EXISTS topics ("
        list_query += "id INTEGER PRIMARY KEY,"
        list_query += "topic VARCHAR(100) )"
        query = QSqlQuery(list_query)
        query.exec()
        db.close()
        return True

    @QtCore.Slot()
    def disconnect(self):
        self._topicmodel.query().clear()
        self._topicmodel.clear()
        QSqlDatabase.removeDatabase(self.db_name)
        self.db_name = ""
        self.logoutSuccess.emit()


    @QtCore.Slot(str)
    def addTopic(self, topic: str):
        pass
    @QtCore.Slot()
    def startEntry(self):
        pass

    @QtCore.Slot()
    def endEntry(self):
        pass

    @QtCore.Slot()
    def discardEntry(self):
        pass

QML:

TableView{
        id: topics
        property int selectedRow
        width: 200
        anchors.top: topicHeader.bottom
        anchors.left: parent.left
        anchors.bottom: parent.bottom
        model: DbController. topicmodel
        delegate: Rectangle {
            property bool selected: row == topics.selectedRow
            implicitHeight: 50
            implicitWidth:  100
            color: "black"
            Text{
                text: model.display + row
                color: column == 0? 'grey' : 'white'
            }
            
        }
}

Also i just realized the repository wasn't public. Now it is.

[SGaist]

I wonder if it is something related to Python's garbage collection.