Update record in tableView by QSqlQuery

[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.