Get content of cell from QTableView

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

JonB

@Karoluss96 said in Get content of cell from QTableView:

This what you sent to me works correct!

Do you mean the code block I wrote a couple of posts ago above? You should be able to get that working so long as you do not yet have any None values in your data, have you tested that, does it work fully so long as no None values? Please answer!

change automatic the default python NoneType to sql's NULL

The problem is that there is no "sql's NULL" in Python, so we don't yet know what to do about Nones to get what we need across to SQL.

If and when you confirm all is working from my code with no Nones involved, I will have a think about None/NULL. But not until you confirm my code works without Nones!

Karoluss96

@JonB said in Get content of cell from QTableView:

Do you mean the code block I wrote a couple of posts ago above? You should be able to get that working so long as you do not yet have any None values in your data, have you tested that, does it work fully so long as no None values? Please answer!

If I go through the all values (by tabs or putting blank space or one digit sign) code work without Exception!

The problem is that there is no "sql's NULL" in Python, so we don't yet know what to do about Nones to get what we need across to SQL.
If and when you confirm all is working from my code with no Nones involved, I will have a think about None/NULL. But not until you confirm my code works without Nones!

That the main reason for all this discussion- how to auto-change None to NULL?!

JonB

@Karoluss96 said in Get content of cell from QTableView:

That the main reason for all this discussion- how to auto-change None to NULL?!

I can give you some suggestions to try. But first I know you are using Python but not which Python binding you are using for Qt. It could be any of: PySide2, PyQt5, PySide6, PyQt6. Which one?

Karoluss96

I suppose PyQt5

as at beginng appears a line:

from PyQt5.QtCore import QSettings

JonB

@Karoluss96
In place of the raise Exception(...) line try the following:

val = None. If this works it would be simplest! It means that binding a None, does result in a NULL, so you can remove the special case completely.
val = QVariant(). Try this next. You may find you need a from PyQt5.QtCore import QVariant statement at the head of the file near the other PyQt5 imports.

If #2 does not work I would be interested to know if you get an error message anywhere (e.g. perhaps on the query.exec_()) and what the message says.

Karoluss96

Well... that all makes (and maybe also you) me tired. My chef will help me with this...

If finally works I'll informe you!

JonB

@Karoluss96
Did you not just try those two? Having spent this much time I am keen to know now...!

Karoluss96

Well... I tried, but I won't to answer it's correct or not as the code with previous versions become messy.

I saw that you're solved a little problem here: https://forum.qt.io/topic/90363/inexplicable-qsqlquerymodel-handling-of-null-value/14

JonB

@Karoluss96
That is so long ago I barely remember! Except that I didn't feel I got much from the PyQt author/mailing list :(

In the last post there a guy says:

though Oracle treats empty strings as NULL

You seem to be using PLSQL. So another thing to try, if no joy from my previous two suggestions, might be val = "" for the None case if that really is true.

Karoluss96

Yes, but now I try by my chef's suggestions with a little returing for a starting point.
I'll answer if finally goes... or returning to your version if doesn't go

Karoluss96

After a long fight my chef find the working answer:

for col in range(model.columnCount()):
            rowData = [model.index(row, col).data() for col in range(model.columnCount()) ]
   
            letersBind = [':a',':bb',':bc',':bd',':be',':bf',':bg',':bh',':bi',':bj',':bk',':bl',':bm',':bn',':bo',':bp',':cba',':cbb',':cbc',':cbd',
                          ':cbe',':cbf',':cbg',':cbh',':cbi',':cbj',':cbk',':cbl',':cbn',':cbo',':cbp',':cca',':ccb',':ccc']    
            if rowData[col] is None:
                query.addBindValue('')
                print(col,' 1')
            else:
                query.bindValue(letersBind[col],rowData[col])
        
        query.exec_()
        print (query.lastError().text())
        while query.next():
            self.model4.insertRow(row)

I' don't from what come the letters, which exchange the ? singns in () for Values

JonB

@Karoluss96 said in Get content of cell from QTableView:

After a long fight my chef find the working answer:

        if rowData[col] is None:
            query.addBindValue('')

@JonB said in Get content of cell from QTableView:

You seem to be using PLSQL. So another thing to try, if no joy from my previous two suggestions, might be val = "" for the None case if that really is true.