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.