Update record in tableView by QSqlQuery

Karoluss96

Hi everyone,

I try some options to change values in TableView (which is selectable) by using sql's UPDATE query.

I built a code:

 query = QSqlQuery(db)
        iden = self.dlg.label_32.text()#Pobiera Identyfikator rekordu
        teryt = self.dlg.comboBox_10.currentText()#Pobiera teryt obiektu
        sql = "UPDATE ZARZADZANIE.ZAMOWIENIA SET ZAMAWIAJACY_KOD=:a,OBSZAR_KOD=:bb,...DATA_MOD_REK=:cca,TERYT=:ccb WHERE ID=:id"   
        sql = query.prepare(sql) 
        index =self.dlg.tableView_3.selectionModel().currentIndex()
        row = index.row()
        print (row)
        model = self.dlg.tableView_3.model()
        for col in range(model.columnCount()):
            rowData = [model.index(row, col).data() for col in range(model.columnCount()) ]
            literyBind = [':a',':bb',..':cca',':ccb',':id']    
            print (rowData[col])
            if rowData[col] is None:
                query.bindValue(literyBind[col],'')
            else:
                a=query.bindValue(literyBind[col],rowData[col])

inspired by: https://www.qtcentre.org/threads/50864-qsqlquery-UPDATE-how-to
(i'm not sure is it correct, but query.exec_() gains TRUE. I test it by changing 1 attribute in tableView)

but I don't know how to put changes in Table View.

I've tried by self.model4.appendRow(), or self.model4.setItem(row, 0, QStandardItem(str(query.record().value(a[0])))) or

QStandardItem(col.get(row))# col is self.model4.columnCount()
but none of this work.

Thanks for every help

Karoluss96

current end of code is:

 if query.exec_():#query.next():
            Uprow=QStandardItem(col.row) 
            self.model4.appendRow(Uprow) 
            print (Uprow)
        else:
            print (query.lastError().text())

JonB

@Karoluss96
If you use QSqlQuery to do the UPDATE, you will have to re-issue your original query to re-SELECT the (all the) row(s) you want after the database change. And then because you are using a QStandardItemModel you will have to re-populate it from the SQL result set.

You are making it really complicated by interposing any kind of QStandardItemModel. Abandon your way of doing it and change over to using QSqlTableModel directly. Then not only will there be no copying but also it will do the table UPDATE for you and read the updated row back into the model, which the QTableView will see automatically.

Karoluss96

@JonB said in Update record in tableView by QSqlQuery:

You are making it really complicated by interposing any kind of QStandardItemModel. Abandon your way of doing it and change over to using QSqlTableModel directly. Then not only will there be no copying but also it will do the table UPDATE for you and read the updated row back into the model, which the QTableView will see automatically.

so at first I need to change?:

query=QSqlTableModel(db)

I change only one row- this which is current select.

JonB

@Karoluss96
No, you need to start by reading how to to use QTableModel, verify it will let you do what you want to do, rather than randomly replacing a QSqlQuery with a QSqlTableModel. Above all else you need to decide whether you have any reason to interpose a QStandardItemModel on top of your SQL.

Karoluss96

@JonB said in Update record in tableView by QSqlQuery:

decide whether you have any reason to interpose a QStandardItemModel on top of your SQ

So query.bind value won't be useful?

JonB

@Karoluss96
If you use QTableModel bound to QTableView, and you are happy there will be one table column per each column in the SQL table, the rows will be editable directly and you won't need to write the UPDATE statement or bind any values. It will also allow for inserting or deleting rows.

I am not going to guess whether this is or is not what you want. If you want to stick with your way, and you want to re-read a SQL row after you have UPDATEd it and copy that into your QStandardItemModel, or you want to get the new values directly out of the values you bind to the UPDATE query and put them into the QStandardItemModel without re-querying the SQL database, go ahead and do so.

Karoluss96

OK: If you suggest the road through QSqlRecord: show/find me an example
The road through QStandardItemModel was my first Idea, but I don't how to finish it!

JonB

@Karoluss96 said in Update record in tableView by QSqlQuery:

OK: If you suggest the road through QSqlRecord: show/find me an example

Try Googling yourself, instead of asking me to show/find for you, I have other things to do. I already gave you a link to an example in the Qt docs. I am not here to show you examples all the time, it's you who is coding.

Karoluss96

Well, Update is difficult as it change values after select another record.
So that in my point of view (check If I'm thinking good!) binding values is compulsory, wheras QSqlRecord and QueryModel make values to be stable!?

Karoluss96

OK, I find a way:
QSqlTableModel::updateRowInTable(int row, const QSqlRecord &values) -> row I have, QSqlRecord::value(int index) but about the 2nd I'm not sure is good enough

Karoluss96

Now it looks that:

tm =QSqlTableModel()
        rec= QSqlRecord()
        mod = QSqlQueryModel()
        for col in range(model.columnCount()):
            rowData = [model.index(row, col).data() for col in range(model.columnCount()) ]
            print (rowData[0])
            literyBind = [':a',':bb',':bc'...':cca',':ccb',':id']
            if rowData[col] is None:
                query.bindValue(literyBind[col],'')
                rec.setNull('')
                rec=rec.value('')
            else:
                query.bindValue(literyBind[col],rowData[col])
        print(query.executedQuery())
        if query.exec_():#query.next():
            if rec is None or tm is None:
                rec= ''
                tm = ''
            for i in rowData:
                Uprow=tm.updateRowInTable(row,rowData[i])
                print (Uprow)

in rowData[i] shout: list indices must be integers or slices, not str, but this table (a list!) cannot be converted into int, as there's only one integer value in this table

jsulm

@Karoluss96 said in Update record in tableView by QSqlQuery:

list indices must be integers or slices, not str, but this table (a list!) cannot be converted into int,

What about

for i in range(len(rowData)):

?
Is a simple Python thing...

Karoluss96

@jsulm said in Update record in tableView by QSqlQuery:

range(len(rowData))

this function looks better but still have an error:
updateRowInTable(self, int, QSqlRecord): argument 2 has unexpected type 'str'

Meanwhile I'd found the setValue function in QSqlRecord, so now it looks that:

 if rowData[col] is None:
                query.bindValue(literyBind[col],'')
                rec.setNull('')
                rec=rec.value('')
            else:
                query.bindValue(literyBind[col],rowData[col])
        print(query.executedQuery())
        if query.exec_():#query.next():
            if rec is None or tm is None or rowData[col] is None:
                rec= ''
                tm = ''
                sv= rec.setValue(literyBind[col],'') 
            else:
              for i in range(len(rowData)):
                Uprow=tm.updateRowInTable(row,rowData[i])
                #sv.rec.setValue(literyBind[col],rowData[i])
                print (Uprow)
        else:
            print (query.lastError().text())

JonB

@Karoluss96
I have no idea what your question is. I do not understand your code or your approach. You have a rec= QSqlRecord(). What is rec.setNull('') supposed to do for you? What about rec=rec.value('') which changes what rec is? What does print(query.executedQuery()) tell you if you have not executed query yet? Where you go rec= '' you follow that with sv= rec.setValue(), how is that supposed to work? Why are you using QTableModel::updateRowInTable() when the docs usually advise you would not call this? Why are you calling it with rowData[i] (for QSqlRecord values) which seems to be one column in a row?

You should be stepping through your code in a debugger to discover for yourself how it is behaving, not just throwing code at us and asking what is wrong.

I will leave others to respond now, my polite advice is that you need to understand what you are doing and what you need to do before trying all this code you have.

Karoluss96

Ah wait I forgot show you this:

 tm =QSqlTableModel()
 rec= QSqlRecord()
mod = QSqlQueryModel()

And after that I cleaned the code to:

 if rec is None or tm is None or rowData[col] is None:#Na wypadek jakby się pojawił
                rec= ''
                tm = ''    
                #query.bindValue(literyBind[col],'')
                sv= rec.setValue(literyBind[col],'')
            # else:
            #   pass  
                #query.bindValue(literyBind[col],rowData[col])
        print(query.executedQuery())
        if query.exec_():#query.next():
              for i in range(len(rowData)):
                sv=rec.setValue(literyBind[col],rowData[i])
                Uprow=tm.updateRowInTable(row,sv)
                print (Uprow)

Yes, I also need a break and do sth diffrent in code

JonB

@Karoluss96 said in Update record in tableView by QSqlQuery:

            rec= ''
            tm = ''    
            #query.bindValue(literyBind[col],'')
            sv= rec.setValue(literyBind[col],'')

If you just look at this how can it make any sense to set rec to an empty string and then call sv= rec.setValue()??

            sv=rec.setValue(literyBind[col],rowData[i])
            Uprow=tm.updateRowInTable(row,sv)

What does rec.setValue() return? Nothing or a boolean? Is that really what you want to try putting into your row?

Karoluss96

The print in query.executedQuery():

UPDATE ZARZADZANIE.ZAMOWIENIA SET ZAMAWIAJACY_KOD=:a,OBSZAR_KOD=:bb,....TERYT=:ccb WHERE ID=:id

So maybe problem lays on query level

JonB

@Karoluss96
And? Did you look at its documentation?

Returns the last query that was successfully executed.

You place it immediately before query.exec_(). Why?

So maybe problem lays on query level

Sorry, but your whole code needs rewriting, more importantly you need to take time to understand what you are doing. Same with some of your Python. Sorry, but true. Best of luck.