Put data in TableView after SQl query

Karoluss96

Hi,
I have a problem how to put data form sql query. Every query has got a different structure as is come from anothers tables.
I'm not sure that I have to use QStandardItemModel or/with QStandardItem and how to put query result into TableView

def zestawienia (self): 
        con= self.dlg.comboBox_6.currentText()
        global model
        model = QStandardItemModel()
        res=QStandardItem()
        if con == Zest[0]:
           sql2 = "SELECT * FROM ZARZADZANIE.ZAMOWIENIA_WG_STATUS_V"
           q=query.exec_(sql2)
           a=self.dlg.tableView_5.setModel(model)#i'm not sure it's need to use
           while query.next():
               res=QStandardItem(mod.get('STATUS ZAMOWIENIA'))#the same- 'Status zamowienia' is the name of 1st attribute
               res.insertColumns(2,2)
               model.appendRow(res)
           self.dlg.tableView_5.show()#to final present data
        if con == Zest[1]:    #2nd question to present difference   
            plan22 = "SELECT * FROM ZARZADZANIE.ZAMOWIENIA where ROK_PLANOWANIA=2022"
            q=query.exec_(plan22)
            a=self.dlg.tableView_5.setModel(model)
            print (a)
            while query.next():
                res=QStandardItem(mod.get('ID'))
                res.insertColumns(2,2)
                print (res)
            self.dlg.tableView_5.show()

Thanks for all helpful answers

Karoluss96

I've tried sth by adding:

q=query.exec_(sql2)
           model.setQuery(q)
           a=self.dlg.tableView_5.setModel(model)

inspiring by this question in forum:
https://forum.qt.io/topic/102277/how-to-insert-data-to-table-from-another-table-sql-in-qt/8

NYBL

please add a minimal reproducible example.

Karoluss96

@NYBL You mean by SQL?
I can't because the database is big and keep sensitive data.
I make this code for an app for QGIS plug-in, in which every question is make after choosing current option in ComboBox

JonB

@Karoluss96
You should not be using QStandard... for records from a SQL database. You should use either QSqlTableModel or QSqlQueryModel for the database query and a QTableView with that as a model. Then it should display your received rows/columns. You will not need to use insertColumn() or appendRow() either. the query should fill the model and the view should display what is in the model.

Karoluss96

I'm trying by using your option:

 global model
        q= QSqlQueryModel()
        model = QTableView()
        res=QStandardItem()
        if con == Zest[0]:
           sql2 = "SELECT * FROM ZARZADZANIE.ZAMOWIENIA_WG_STATUS_V"#zapytanie nie działa :-(
           query.exec_(sql2)
           a=q.setQuery(sql2)
           self.dlg.tableView_5.setModel(a)
           while query.next():
               a=res.setQuery(q)
               b=res.insertColumns(a)
               model.appendRow(b)
           self.dlg.tableView_5.show()
        if con == Zest[1]:       
            plan22 = "SELECT * FROM ZARZADZANIE.ZAMOWIENIA where ROK_PLANOWANIA=2022"
            query.exec_(plan22)
            a=q.setQuery(plan22)
            self.dlg.tableView_5.setModel(a)
                        print (a)
                        while query.next():
                            a=model.setQuery(q)
                            b=res.insertColumns(a)
                            model.appendRow(b)
                        self.dlg.tableView_5.show()

but nothing appears in Table View. Sth should I make wrong?

jsulm

@Karoluss96 You should put more context - where is this code actually located? I guess some method?
The problem is probably that you are creating local variables which are destroyed as soon as they go out of scope.

Karoluss96

It's a part of function to put data from selected sql in combobox to table View.

Begins of it looks that:

 def zestawienia (self): #obsługa zakładki zestawień
        con= self.dlg.comboBox_6.currentText()
        global model
        q= QSqlQueryModel()
        model = QTableView()
        res=QStandardItem()
        if con == Zest[0]:
...

It vision is easy- from combo box show SQL's anwser in TableView below
na forum.PNG

jsulm

@Karoluss96 As I wrote: you are creating local variables, make them class members instead...

Karoluss96

You mean by put some variables in definition of function - next to (self) in brackets?
Or put eg. ..setquery and insertColumns at the beginning before if loop?

jsulm

@Karoluss96 For example:

self.model = QTableView()

Karoluss96

I've done:

 self.q= QSqlQueryModel()
 self.model = QTableView()
if con == Zest[0]:
           sql2 = "SELECT * FROM ZARZADZANIE.ZAMOWIENIA_WG_STATUS_V"#zapytanie nie działa :-(
           query.exec_(sql2)
           a=q.setQuery(sql2)
           self.dlg.tableView_5.setModel(a)

but i gains error in loof if: local variable 'q' referenced before assignment

jsulm

@Karoluss96 said in Put data in TableView after SQl query:

local variable 'q' referenced before assignment

it is self.q now, not just q...

Karoluss96

so where i have to put

self.model = QTableView()

as you suggested?

jsulm

@Karoluss96 In the code you already posted!

def zestawienia (self): #obsługa zakładki zestawień
        con= self.dlg.comboBox_6.currentText()
        global model
        self.q= QSqlQueryModel()
        self.model = QTableView()
        res=QStandardItem()
        if con == Zest[0]:

JonB

@Karoluss96
You need to understand about things like variable scopes as a part of Python not Qt. Just saying.

I see you still have a QStandardItem. I cannot imagine why. As I said, you simply fill your model from SQL and attach a QTableView to it, that's it.

And please do not name as model something which is a QTableView, it's incredibly confusing.

Karoluss96

I changed assigning QTableView from model to table.
Then I put it into

self.dlg.tableView_5.setModel(table)

but it makes an error

TypeError: setModel(self, QAbstractItemModel): argument 1 has unexpected type 'QTableView'

jsulm

@Karoluss96 said in Put data in TableView after SQl query:

but it makes an error

Would be nice if you would tell us what error it is...

Karoluss96

I put the text of error in a previous post /\

jsulm

@Karoluss96 The error is quite clear: you can't set QTableView (which is a view) as model...
What @JonB suggested is to use https://doc.qt.io/qt-6/qsqltablemodel.html or https://doc.qt.io/qt-6/qsqlquerymodel.html as model to get data from your SQL database and set that model in self.dlg.tableView_5