PyQT5 multiple QTableView for same database but different tables

DieterV

Hi,

Pretty new to PyQT, first time working with a database. I've searched for answers but either used the wrong search terms or didn't what to look for exactly. Question might be pretty obvious...

I have a database that I access through a QSqlTableModel. I have a gui that has two tabs, one with a QTableView that represents one table ('persons'), another one that presents data from the table 'rooms'.

After setting up the first QTableView with multiple .setHeaderData(0, QtCore.Qt.Horizontal, 'Reference') and .removeColumns(6, 1) I select the model. This works for the first QTableView.

However, when I add the second one (configured in a similar matter, but using another table with .setTable() and ending with .select(), obviously the same table is used for both QTableViews. I understand this.

So I used the tabWidget.currentChanged.connect()method to a function that checks the active tab (and thus QTableView), and updates setTable() and .select().

However, the removed columns and header data is shared between both. So my question:

Should I

Write two different models?
Use two instances of the same model?
Change the layout (columns removed and headers updated) each time I switch between tabs?

JonB

@DieterV
Hello and welcome.

One model per table, one table view per model, one tab per table view, would be my recommendation in your case. Simplest, instead of having swap stuff each time.

DieterV

So it's ok/acceptable/correct (pick one) to have two instances of the same model (using the same class)? Or should I create two model classes?

Right now I have one class, subclassing QsqlTableModel, where I initialize the database in the __init__() method. If I would be using two instances of the same class, they would both create an QSqlDatabase using the same SQLite db.

My current (simplified) code:

from PyQt5.QtSql import QSqlDatabase, QSqlTableModel, QSqlQuery
from person.person import Person

class BBMSModel(QSqlTableModel):
    def __init__(self, db_filename:str):
        self.db = QSqlDatabase('QSQLITE')
        self.db.setDatabaseName(db_filename)
        self.db.open()
        super().__init__(db=self.db)

    def get_person_by_ref(self, reference):
        query = QSqlQuery(db=self.db)
        query.prepare(
            'select person_reference,person_name, person_lastname, '
            'from persons where person_reference = :ref',
        )
        query.bindValue(':ref', reference)
        if not query.exec_():
            raise ValueError
        query.first()
        return Person(
            reference=query.value(0),
            name=query.value(1),
            last_name=query.value(2),
        )

Should I do change it like this so both instances of the model share the same QSqlDatabase object:

class BBMSModel(QSqlTableModel):
    def __init__(self, qsqldatabase = PyQt5.QtSql.QSqlDatabase ):
        self.db = qsqldatabase
        super().__init__(db=self.db)

    def get_person_by_ref(self, reference):
        query = QSqlQuery(db=self.db)
        <snip>

db = QSqlDatabase('QSQLITE')
db.setDatabaseName(db_file)
db.open()
model1= BBMSModel(db)
model2=BBMSModel(db)

Or is it ok to have each model create a new QSqlDatabase object.

I might be overthinking/overcomplicating things, I know. Hard to decide what is important and what not.

I'm sure I would be able to get it to work some way, but it likely wouldn't be nice. So as I'm just starting with this I prefer to ask for the 'correct' way to do it.

Thanks, your help is much appreciated!

JonB

@DieterV
Second case. One QSqlDatabase, opened only once outside of the models. Separate models using it, your models will differ per table.

Why do you have two BBMSModels? You said you have two distinct tables, persons & rooms. One model per table. Different model subclasses, to allow you to access their separate columns or send different queries.

DieterV

@JonB

Great!

@JonB said in PyQT5 multiple QTableView for same database but different tables:

Why do you have two BBMSModels? You said you have two distinct tables, persons & rooms. One model per table. Different model subclasses, to allow you to access their separate columns or send different queries.

I don't have two models, at the moment I have just the first one. In the first code block I showed what I had, in the second block how I thought it should be done (opening the QSqlDatabase and passing it to both model instances).

So (unless I misunderstood):

I create one QSqlDatabase instance
one subclass of QSqlTableModel for the first table
a seconds subclass of QSqlTableModel for the second table
code used for the first tab/table goes into the first model, the second table in the second.

That makes sense. I wasn't sure whether there were reasons not to have more than one model. In the book I've used (Create GUI Applications with Python & Qt5 from Martin Fitzpatrick, super happy with this book!) as well as in most examples/tutorials I found all code is limited to one model. I understand this is most likely due to the limited functionality in the examples.

In any case, lots of thanks!

JonB

@DieterV said in PyQT5 multiple QTableView for same database but different tables:

I don't have two models

You have:

model1= BBMSModel(db)
model2=BBMSModel(db)

That's two models. That means two instances, of the same class. You will (presumably) end up with

model1= BBMSModel(db)
model2=SomeOtherModel(db)

DieterV

@JonB said in PyQT5 multiple QTableView for same database but different tables:

That's two models. That means two instances, of the same class. You will (presumably) end up with

Yes, I understood that from your last message. When I wrote that (two instances of the same class) I had not yet realized I should write separate classes/models for the different QTableViews. As I have code that is shared between both, I was still thinking about putting it all together.

But I will create two classes. If I have too much duplicate code, I'll write a class for the common code and write two subclasses, one per table.