Get content of cell from QTableView

jsulm

@Karoluss96 said in Get content of cell from QTableView:

eg. ?
iden =query.addBindValue( rowData[0])

yes, for every value in your query you want to bind you need to call addBindValue as you can clearly see in the example:

QSqlQuery query;
query.prepare("INSERT INTO person (id, forename, surname) "
                  "VALUES (?, ?, ?)");
query.addBindValue(1001);
query.addBindValue("Bart");
query.addBindValue("Simpson");
query.exec();

JonB

@Karoluss96
Indeed, for each element in rowData in a loop. And you must make sure your INSERT query has the right number of column names and ? (or :<name> if you are using those) placeholders in the VALUES (...) clause to match the bound values added.

Karoluss96

@jsulm said in Get content of cell from QTableView:

query.prepare(

Now I have this:

query = QSqlQuery(db)
 sql = query.prepare('INSERT INTO ZARZADZANIE.ZAMOWIENIA (ID,TERYT,...) VALUES (?,?,...)')       
        index =self.dlg.tableView_3.selectionModel().currentIndex()
        dane= self.dlg.tableView_3.model().index(index.row(),0).data() 
        row = index.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()) ]
            iden =query.BindValue(rowData[0], '')
            print (iden)
...

with error:
AttributeError: 'QSqlQuery' object has no attribute 'BindValue'

db is a global value:

db = QSqlDatabase.addDatabase("QOCISPATIAL")

jsulm

@Karoluss96 said in Get content of cell from QTableView:

AttributeError: 'QSqlQuery' object has no attribute 'BindValue'

Please , isn't this apparent?! Really...

jsulm

@Karoluss96 said in Get content of cell from QTableView:

db is a global value:
db = QSqlDatabase.addDatabase("QOCISPATIAL")

There should not be any such global db variables! Again: it is explained in the documentation which you should read: https://doc.qt.io/qt-6/qsqldatabase.html
"Warning: It is highly recommended that you do not keep a copy of the QSqlDatabase around as a member of a class, as this will prevent the instance from being correctly cleaned up on shutdown. If you need to access an existing QSqlDatabase, it should be accessed with database(). If you chose to have a QSqlDatabase member variable, this needs to be deleted before the QCoreApplication instance is deleted, otherwise it may lead to undefined behavior."

It is even in red color...

Karoluss96

Ah, Maybe I chose the wrong option for bind value from documentation.

I can't change line with db, because it was make by my head-chef and it's important for anothers sql-questions which are in this app.

JonB

@Karoluss96 said in Get content of cell from QTableView:

iden =query.BindValue(rowData[0], '')

AttributeError: 'QSqlQuery' object has no attribute 'BindValue'

Please use your own efforts to resolve an error such as this, instead of just asking every time. The forum is not intended for people to type in responses to every error you might encounter. The method is named and shown as all examples as bindValue(), you now choose to type BindValue(), your own knowledge of Python should tell you what the issue is....

Karoluss96

@JonB said in Get content of cell from QTableView:

Please use your own efforts to resolve an error such as this, instead of just asking every time. The forum is not intended for people to type in responses to every error you might encounter. The method is named and shown as all examples as bindValue(), you now choose to type BindValue(), your own knowledge of Python should tell you what the issue is....

Eh... making so quick can't help in solving problem.

Looking at the subject this discussion goes total out of topic.

Have I continue it here or finish, not to spam for future's users?

JonB

@Karoluss96
If it's still questions related to "Get content of cell from QTableView" you might as well continue in this topic.

Karoluss96

To be honest, I've tried some versions (look at 2 versions of sql variable ), but It haven't work yet.

Now I have:

sql = 'INSERT INTO ZARZADZANIE.ZAMOWIENIA (ID,TERYT,ZAM...) VALUES ('
#sql = query.prepare('INSERT INTO ZARZADZANIE.ZAMOWIENIA (ID,TERYT,ZAM...) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)')

and later:

for col in range(model.columnCount()):
            rowData = [ model.index(row, col).data() for col in range(model.columnCount()) ]
            if (rowData[0] is None):
                iden =query.bindValue(rowData[0], '')
                print (iden)
            else:
                iden = rowData[0]
                print (iden)
            if (rowData[1] is None):    
                teryt = query.bindValue(rowData[1],'')
                print (teryt)
            else:
                teryt = rowData[1]
                print (teryt)
            if (rowData[2] is None):    
                zam = query.bindValue(rowData[2],'')
                print (zam)
...

and take here error:

dane2 =  iden + ',\'' + teryt + '\',\'' +zam +  ....+ '\''
        print (dane2)
        sqlCale = sql + dane2 + ')'

can only concatenate str (not "NoneType") to str.

On console for added data (in Table View, from which this question take values) I recive e.g. 7150,1401,UM14 if is fill or NONE it is not.

So still the same, but bindValue separate None from filled attributes

JonB

@Karoluss96 said in Get content of cell from QTableView:

can only concatenate str (not "NoneType") to str.

And which exact line (not 3 lines) does this error report? Does it even have anything to do with bindValue() call? You have already asked this. Python does not let you concatenate (i.e. join with + sign) None into a string, just as one might expect, so do something about it.

Karoluss96

...and that's the problem is in dane2 line

JonB

@Karoluss96
...so that is where you need to deal with the issue, it's a (not unexpected) Python issue you need to address, what else is there to say?

JonB

@Karoluss96
BTW: what are you actually joining together in dane2 = iden + ',\'' + teryt + '\',\'' +zam + ....+ '\'', and why? What are those variables? Since you are now using bindValue() for the values, the only things you would need to join into a comma-separated list are the column names for the INSERT statement, and no column would/should have a None name. So I don't know what you are up to anyway.

Karoluss96

It's a rest from previous version.

I'll continue that tommorow as I forgot about more importat thing: coverting date formats!
(I'm sure that I solve on my own)

Karoluss96

Still I have problem

There's no error, but on console I see only none.

Current code looks that:

 sql = query.prepare('INSERT INTO ZARZADZANIE.ZAMOWIENIA (ID,TERYT,ZAM....)'
                            'VALUES (?,?,?....)')       
 index =self.dlg.tableView_3.selectionModel().currentIndex()
 dane= self.dlg.tableView_3.model().index(index.row(),0).data() 
 row = index.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()) ]
            if (rowData[0] is None):
                iden =query.bindValue(rowData[0], '')
                print (iden)
            else:
                iden = query.bindValue(0,rowData[0])
     
                print (iden)
            if (rowData[1] is None):    
                teryt = query.bindValue(rowData[1],'')
                print (teryt)
            else:
                teryt = query.bindValue(1,rowData[1])
                print (teryt)
  print (sql)
  y=query.exec_()
  print (y)
...

sql take True, but "y" False

JonB

@Karoluss96

Your code approach makes little sense. Why do you have any for col in range(model.columnCount()): loop at all, given that you do not access col, and you do all binding explicitly for each column by number in the body?
If your code is really as shown you have 3+ values to bind but you only access rowdata elements 0 & 1.
You have query.bindValue(0,rowData[0]), which is correct, but for the if (rowData[0] is None) case you have put bindValue(rowData[0], ''). You can see that is a quite different overload, why have chosen that? You can see that evaluates to bindValue(None, ''), which you can tell is not going to be helpful.
When query.exec_() returns False you should access QSqlError QSqlQuery::lastError() const to discover information about what went wrong, which may tell you why it is not working.

Karoluss96

Ok, so first:

Are you suggeest to change (e.g.):

bindValue(rowData[7],'')# here's 33 attributes and values so that I cut fo this forum to first 3

to

bindValue(None, ' ')

Bofore this example I've tried with (e.g.)

addBindValue(rowData[19])

but It take the same result

query.lastError() gains: <PyQt5.QtSql.QSqlError object at 0x000002A469F43AC0>

for col in range(model.columnCount()):

was presented to me in this disscution some threads above

JonB

@Karoluss96 said in Get content of cell from QTableView:

Are you suggeest to change (e.g.):

No I am not! I am pointing out what your code does, and inviting you to realize that it cannot be correct.

Please stop, look at your code and think about it:

            if (rowData[0] is None):
                iden =query.bindValue(rowData[0], '')
                print (iden)
            else:
                iden = query.bindValue(0,rowData[0])

When rowData[0] is not None this binds positional parameter #0 (the first ?) to the value of rowData[0], which is reasonable. What happens when rowData[0] is None? It uses a quite different overload of QSqlQuery::bindValue() --- why? And since rowData[0] is None it will evaluate to query.bindValue(None, '') --- what is that supposed to achieve?

I suggest you start by getting your code working when none of the elements in rowData is None. I don't care how you achieve that, just make sure rowData does not contain any Nones, it's just a test. Is the INSERT statement now fully working? If & when it is, time to address None --- discover what value you have to bind in order for it to be acceptable and result in NULL in the final INSERT. I don't know what value that is from Python, only from C++. Maybe None works, maybe QVariant() works, I don't know. Come back here when it's all working other than for None values if you can't figure it.

query.lastError() gains: <PyQt5.QtSql.QSqlError object at 0x000002A469F43AC0>

You are supposed to figure out what to print from a QSqlError that is a human-readable description of the error, not just blindly put in print(query.lastError()) which gives the above.

for col in range(model.columnCount()):

was presented to me in this disscution some threads above

Just because something is "presented" you don't just put it into your code automatically. You are supposed to understand what you do and don't need a loop for.

Karoluss96

rowData = [model.index(row, col).data() for col in range(model.columnCount()) ]

is compulsory as the values for sql query come from Table View, where the user put by own!

Then, you suggest that:

query.bindValue(None, '')

is much better than with rowData[0,1,2....33]!?

To check the full values is difficult, because there a lot of in date format, about which I mentioned her I also have probles to convert it correctly.
Now here's:

query.addBindValue(datetime.strptime(rowData[15],'%d.%m.%Y')

During the fights with this code one version went good (query.exec_() get true), but It's diffuclt to reconstruct it