Get content of cell from QTableView

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

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.