Need help with QSqlModel - PySide6

[LS-KS]

I started a new project for learning purposes.
This controller shall handle everything related to a sqlite database.

I want to render the data in my MainScreen.qml: The table a set of 'topics' shall rendered in a ListView and the related entires shall be rendered in the TableView.

As far as I can see I can establish a connection to the database successfully. However, when the connect function is called there is no data rendered in my QML listview.

Second problem: although I cleared models and queries I get an error when I log out... why?

In my mind the pseudo-code steps are:

• take db-name, user and passphrase and establish a connection to the database
• pass the connection to the QSqlQueryModel and RelationalTableModel
• now when Qml requests data the query is executed (?)

What do I miss?

Relevant code snippet:

@QtCore.Slot(str, str, str)
    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"
        self.connection = QSqlDatabase.addDatabase("QSQLITE", db_name)
        self.connection.setDatabaseName(str(db_file))
        if not self.connection.open(user, password):
            print("Error:", self.connection.lastError().text())
            self.loginSuccess.emit(False)
            return
        if self.connection.isOpen():
            self.db_name = db_name
            self._topicmodel.setQuery("SELECT topic FROM topics", db= self.connection)
            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)

Error when disconnect is executed:

QSqlDatabasePrivate::removeDatabase: connection 'TestDB' is still in use, all queries will cease to work.

I hope anybody can help :-)

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

Hi,

Because there's more work to be done for QML to get data from your model. The usual roles are not used as with the widget views.

See this Qt Wiki article especially the bottom part that is a bit more generique.

As for the error message, you ignored one of the main rules: do not store QSqlDatabase objects as member variable. Retrieve the connection when needed. If you only have one database connection, just don't name the connection and the default one will be used.

[LS-KS]

Thank you for your help, @SGaist ! And sorry for choosing the wrong thread in the forum.

I was able to fix the error.

@SGaist said in Need help with QSqlModel - PySide6:

Because there's more work to be done for QML to get data from your model. The usual roles are not used as with the widget views.
See this Qt Wiki article especially the bottom part that is a bit more generique

So I need to subclass the SqlQueryModel like I would use QAbstractTableModel ?!? This link made me think I could use the model without implementing the usual methods.

[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.