Get content of cell from QTableView

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

JonB

@Karoluss96 said in Get content of cell from QTableView:

Then, you suggest that:
query.bindValue(None, '')

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

Did you actually read my response? I have now said twice

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.

How can you keep saying I suggest query.bindValue(None, '') when I have said that I do not suggest it and it is wrong?

I have asked you to think about what you have. You don't seem to have done so. I have asked you what overload of QSqlQuery.bindValue() you are trying to use in query.bindValue(rowData[0], '') when the other (non-None, correct) one is query.bindValue(0,rowData[0]), and why you are trying to use a different overload. You don't seem to have done so. I have suggested you get it working fully when no value is None, yet you are still talking about the None case. I have said you need to print out the error message from the QSqlError object.

If you want help please act on the responses you are given. And please think about your code, people here don't write code for you, they help you with questions about the code you try yourself after considering the problem, analyzing it and acting on the responses you are given. We are trying to help you to help yourself, not write code for you. You need to understand, not just copy/paste other people's stuff, else you will always be stuck on the next thing....

Karoluss96

It takes error: ORA-01722 (uncorrect number)

JonB

@Karoluss96
What takes that error? Are we supposed to guess? Oh, is that "it" supposed to be the QSqlError?? So you can Google for that yourself. Presumably (at least one of) your values are wrong.

JonB

@Karoluss96
I suggest you try (something like, untested):

sql = query.prepare('INSERT INTO ZARZADZANIE.ZAMOWIENIA (ID,TERYT,ZAM....)'
                            'VALUES (?,?,?....)')       
index = self.dlg.tableView_3.selectionModel().currentIndex()
row = index.row()
model = self.dlg.tableView_3.model()
for col in range(model.columnCount()):
    val = model.index(row, col).data()
    if val is None:
        raise Exception("Do not try to do a value of `None` yet, get it working for non-`None` values first!!!")
    # print("Binding parameter number", col, "to value", str(val))
    query.bindValue(col, val)  
if query.exec_():
    print("Worked!")
else
    print(query.lastError().text())

Karoluss96

From Beginning:

My first version of the code to make sql query worked!

I need to change it as there 2 problems:

Date formats (cannot be concatenated with string values, like it was:

dn = iden + ',\'' + teryt + '\',\'' +zam + '\',\'+ ...

)

User will be put to 13th value (the last which is compulsory to make valid query). Rest 20 only If (s)he need, and (s)he don't want to use Tabulation (Tab key) to go through the whole table, which automatically changing None to ' '.

So that Now I testify the scenerio where the user put first 13th values and don't watch for next ones (so stays None), but sql need to change to NULL.
If this problem will be solve I'll go to data format, as the first date format is on 16th place of value (so in non-compulsory).

Is my point of view (and thinking) clear and understand?

JonB

@Karoluss96 said in Get content of cell from QTableView:

dn = iden + ',\'' + teryt + '\',\'' +zam + '\',\'+ ...

We have no idea what is in variables iden, teryt, zam, ....
What sort of string are you expecting this to produce (if it worked)? Give an example so we know!
Where are you trying to use this dn variable?

If you want a response, answer all 3 questions....

OK, hang on....
So now I see iden, teryt, zam, ... are assigned from iden =query.bindValue(rowData[0], '') etc. So what does QSqlQuery.bindValue() return as its result, look at the documentation, you tell me? When you discover that method does not return any value (no RETURN TYPE documented) you will realize what the problem is.....

Karoluss96

iden, teryt, zam come from (slashes for better understand):

if (rowData[0] is None):
                iden/teryt/zam =query.bindValue(rowData[0/1/2], '')
                print (iden)
            else:
                iden/teryt/zam = query.addBindValue(rowData[0/1/2])# firsty here was: iden= rowData[0]
                print (iden)

possible result:

7150, '1401', 'UM14', ....

dn was later to sql query

sql =INSERT INTO ZARZADZANIE.ZAMOWIENIA (ID,TERYT,ZAM...) VALUES (
 sqlFull = sql + dn + ')'
query.exec_(sqlFull)

JonB

@Karoluss96
Your response crossed with the "OK, hang on....` amd subsequent I appended to my previous. Please read that. Your code makes no sense.

possible result:

7150, '1401', 'UM14', ....

Your code won't produce anything like the above.

sqlFull = sql + dn + ')'

query.exec_(sqlFull)

Don't know what you're trying to achieve here, but don't do it. It's concerning that, if I understand/guess right, you have not understood the basics about binding values and how they are used. Either understand bound variables and use them, or decide you want to produce your own literal string for the INSERT statement which you will ensure is correct and don't use bound variables, but don't try to mix them, which I think is what you are trying to do.

Karoluss96

This what you sent to me works correct!

My point of view is that (little psuedo-code):

The user put some from 33 values.

Those attributes were puts values:
get as value to sql query

Those attributes weren't put any value:
change automatic the default python NoneType to sql's NULL and get later to sql query