PyQT: The QSqlTableModel and how to wrap my head around it

[fschober]

Dear fellow Qtarians!

I am fairly new to the world of Qt/PyQt after, as a reasonable tkinter programmer, try to reach the next level of beautiful gui design. So far it is fantastic.
But since a few days I can't seem to wrap my head around qt's own model structure. The documentation for pyQt gets very fast very bad if you want to do more advanced stuff.

I am writing a program which uses sqlite3. So I read about these fantastic Sql Models. I tried it with QSqlTableModel (the others too, but for trying this will do),
but it won't work and the examples I found just confused me more than anything.

I want to display the vocabulary of a language in a treeview.

So here I have a little example for you, what I have so far.

from PyQt5.QtCore import *
from PyQt5.QtGui import *
from PyQt5.QtSql import *
 
class model(QSqlTableModel):
 
    def __init__(self, db_file, mode):
        super().__init__()
        self.db = QSqlDatabase.addDatabase('QSQLITE')
        self.db.setDatabaseName(db_file)
        self.setEditStrategy(QSqlRelationalTableModel.OnFieldChange)
        self.setTable("VOCABULARY") 
        self.select()
 
if mode == "create":
            self.create_db()
        elif mode == "load":
            self.load_db()
 
 
    def create_db(self):
 
        sql_create_vocab = '''CREATE TABLE VOCABULARY
                            ([generated_id] INTEGER PRIMARY KEY,
                            [word] TEXT NOT NULL,
                            [translation] TEXT NOT NULL,
                            [pos] TEXT NOT NULL,
                            [example_sentence] TEXT NOT NULL,
                            [example_translation] TEXT NOT NULL,
                            [description] TEXT NOT NULL,
                            [related_words] TEXT NOT NULL,
                            [related_image] BLOB NOT NULL)'''
 
 
 
        query = QSqlQuery(self.db)
        query.prepare(sql_create_vocab)    
        query.exec()
        self.setQuery(query)
 
        return True
 
 
    def load_db(self):
 
        if self.db.open():
            log.debug('MODEL: connect to SQL Server successfully')
            # return True
        else:
            log.error('MODEL: connection failed')
            # return False
 
        qry = QSqlQuery(self.db)
        log.info('MODEL: Processing Query')
        qry.prepare('SELECT * FROM VOCABULARY')
        qry.exec()
        self.setQuery(qry)
 
 
    def save_data(self):
        print("BEFORE " + self.record(0).value("related_words"))
        try:
            self.setData(self.index(0, 7), "XXXXX")
            self.submitAll()
            print("SUCCESS")
        except:
            print("FAIL")
 
        print("AFTER " + self.record(0).value("related_words"))

from PyQt5.QtCore import *
from PyQt5.QtGui import *
from PyQt5.QtSql import *

class model(QSqlTableModel):

    def __init__(self, db_file, mode):
        super().__init__()
        self.db = QSqlDatabase.addDatabase('QSQLITE')
        self.db.setDatabaseName(db_file)
        self.setEditStrategy(QSqlRelationalTableModel.OnFieldChange)
        self.setTable("VOCABULARY") 
        self.select()

if mode == "create":
            self.create_db()
        elif mode == "load":
            self.load_db()

    
    def create_db(self):

        sql_create_vocab = '''CREATE TABLE VOCABULARY
                            ([generated_id] INTEGER PRIMARY KEY,
                            [word] TEXT NOT NULL,
                            [translation] TEXT NOT NULL,
                            [pos] TEXT NOT NULL,
                            [example_sentence] TEXT NOT NULL,
                            [example_translation] TEXT NOT NULL,
                            [description] TEXT NOT NULL,
                            [related_words] TEXT NOT NULL,
                            [related_image] BLOB NOT NULL)'''


         
        query = QSqlQuery(self.db)
        query.prepare(sql_create_vocab)    
        query.exec()
        self.setQuery(query)

        return True


    def load_db(self):

        if self.db.open():
            log.debug('MODEL: connect to SQL Server successfully')
            # return True
        else:
            log.error('MODEL: connection failed')
            # return False

        qry = QSqlQuery(self.db)
        log.info('MODEL: Processing Query')
        qry.prepare('SELECT * FROM VOCABULARY')
        qry.exec()
        self.setQuery(qry)


    def save_data(self):
        print("BEFORE " + self.record(0).value("related_words"))
        try:
            self.setData(self.index(0, 7), "XXXXX")
            self.submitAll()
            print("SUCCESS")
        except:
            print("FAIL")
        
        print("AFTER " + self.record(0).value("related_words"))

To copy to clipboard, switch view to plain text mode 

Right here the last method is to check if it writes "XXXXX" to the db, but it won't.


And then I set the model for the treeview, but the treeview also refuses to display something, unless I query with "SELECT * FROM VOCABULARY". I know the code here won't work, but what I don't understand is,
if I have to query the database myself, or If I can use the high level model funtions - and if so, how does this work!

Any help would be greatly appreciated.

Cheers

Fabian

[ChristianEhrlicher]

First I don't think you need to set an executed query to the model - since you're working on a complete table setTable() should be enough.
Then you have added a lot of constraints (NOT NULL) so only setting related_words will not work.

[fschober]

Alright, for understanding the principle I stripped my model class to this:

class model(QSqlTableModel):
 
    def __init__(self, db_file, mode):
        super().__init__()
        self.db = QSqlDatabase.addDatabase('QSQLITE')
        print(db_file)
        self.db.setDatabaseName("start.db")
        self.setEditStrategy(QSqlRelationalTableModel.OnFieldChange)
        self.setTable("VOCABULARY") 
        self.select()
 
        print(self.rowCount())

class model(QSqlTableModel):

    def __init__(self, db_file, mode):
        super().__init__()
        self.db = QSqlDatabase.addDatabase('QSQLITE')
        print(db_file)
        self.db.setDatabaseName("start.db")
        self.setEditStrategy(QSqlRelationalTableModel.OnFieldChange)
        self.setTable("VOCABULARY") 
        self.select()

        print(self.rowCount())

To copy to clipboard, switch view to plain text mode 

I also removed all the constraints from the database.
But The Row Count is showing 0, and the treeview is still empty. There should be exactly 1 row in the table.

[d_stranz]

The problem might be that you are trying to use a hierarchical view (a tree) to display a non-hierarchical table. In principle, a tree view should display one top-level entry for each row in the table, but that's it. There's no drill-down, because a table model's rows by definition have no children.

Also, did you set the model on your view? (QAbstractItemView::setModel() in C++)

You might also want to add some code to check to see if your database is actually open. Your code assumes the DB file is in your current working directory, but what you think and what python thinks that location is might be two different places.

[fschober]

Ah, alright, thanks for the reply!
So firstly: at the beginning it actually worked to insert something in the treeview with model.setquery("SELECT * FROM VOCABULARY"). It inserted exactly what i wanted. But then it wasn't possible to do anything else. But through that (and from tkinter treeview) I know, that it only turns hirarchical if you want it to e.g. set parent items and give them child items.

Secondly, my controller does this to start the model:

def start_mode(self):
        self.start_vocab = model("data/start.db", "load")
        self.main_win.vocab_tv.setModel(self.start_vocab)

def start_mode(self):
        self.start_vocab = model("data/start.db", "load")
        self.main_win.vocab_tv.setModel(self.start_vocab)

To copy to clipboard, switch view to plain text mode 

(vocab_tv is the treeview)

And thirdly, I also checked the location of the file - tested it with the sqlite3 module simultaneously, if it works. Sqlite3 returns everything as plannend!

This is the craziest thing!

So here the logic broken down:

1.) Create and instantiate a model, inherited from QSqlTableModel.
2.) Add the Database QSQLITE (In this case)
3.) Set the DatabaseName: the database File
4.) Optional: Set EditStrategy
5.) Set the Table - which is the one it reads from the file
6.) Make it select()
7.) Create a Treeview or Listview
8.) Set the model for the Treeview or Listview

That should be it, right!?

[d_stranz]

I would move step 6 to the end. select() results in signals being issued by the model that the view uses to update itself. If you haven't connected the model to the view yet, no one is listening to the model's signals at the time you do the select().

[fschober]

So I called the select() method at the very last... moved it on different positions, but I think the very problem seems to lay with loading the data in the model, since the row count keeps being 0!

[d_stranz]

Don't know how else to help. Maybe use "DB Browser for SQLite" to make sure that your empty select actually does something there and then try to map that to python. I thought you would at least have to issue some kind of real SELECT statement - "SELECT * from MyTable" to get a populated result.

[fschober]

Yeah... thanks anyway for the troubleshooting with me! As newbie in Qt I felt really lost...

The thing is, I use the DB Browser for SQlite on a regular basis, and it checked out fine... when I select and print out the rows, everything checks out as planned.

I would hate to give up on PyQt... I will try it as isolated example again and post here what I have learned!

---

Added after 22 minutes:

Alright, I found it!! And this is why pyQt is so confusing:

It ways never clear to me how exactly the QSqlTableModel knows, where to connect to the database. You just set db = "Database.db" and that's it? Until know I have just read here that the databse is set to the variable db. So the logical exaplanation why it won't work is, that the QSqlTableMOdel simply gets initialized before the database gets added,
which is why it can't find data.

So I did this:

def __init__(self, db_file, mode):
        self.db = QSqlDatabase.addDatabase('QSQLITE')
        self.db.setDatabaseName(db_file)
        super().__init__()
 
        print(self.database())
        self.setEditStrategy(QSqlRelationalTableModel.OnFieldChange)
        self.setTable("VOCABULARY") 
        self.select()

def __init__(self, db_file, mode):
        self.db = QSqlDatabase.addDatabase('QSQLITE')
        self.db.setDatabaseName(db_file)
        super().__init__()
        
        print(self.database())
        self.setEditStrategy(QSqlRelationalTableModel.OnFieldChange)
        self.setTable("VOCABULARY") 
        self.select()

To copy to clipboard, switch view to plain text mode 

and it worked! It showed me the whole table as is in the tree_view. Next thing will be inserting and deleting records.

Thank you so much for the help!

[d_stranz]

Hmm. I am wondering if you need to derive your own model from QSqlTableModel at all. In my (limited) use of databases, I have used the QSqlTableModel as-is. If you look at the example code in the QSqlTableModel documentation Detailed Description section, it basically does everything you are doing without deriving a new class. Your problem stems entirely from the fact that you derived from the base class, and had to defer the base class initialization as a result.

[fschober]

Hmmm good point actually, after sleeping over it had the same realization. I just made my own class from it because I read somewhere that this QSqlTableModel should be used only with derivation, and not on its own.
But what do you know, you can't always believe everything on the internet...

[d_stranz]

There are some Qt models that generally must be used with derivation (eg. QSortFilterProxyModel if you need filtering not implemented in the class) and of course any of the QAbstract* classes, but QSqlTableModel is more or less complete.

But what do you know, you can't always believe everything on the internet...

What? Say it isn't so!