PySide6.QDataWidgetMapper - how to store NULL from mapped widget into DB?

StarterKit

Hi all,
I have a code to display/edit data from database. It has a custom widget which has str User property to be used with QDataWidgetMapper - it stores NULL into DB if widget value is 0 and actual value otherwise. It works well with PySide2 but fails with PySide6. (Actually I would prefer to use int property but QDataWidgetMapper doesn't set int property if DB has NULL value).

I checked why my code fails and found that my widget indeed returns None but then Qt5 and Qt6 behaves differently:
PySide2 stores it as NULL value in DB but PySide6 stores an empty string.
So, the question is - if my widget returns None how to store it as NULL value into database with PySide6? (I really need NULL as otherwise I'll get foreign key violation in database).

Below is an example of code to reproduce the problem. It has a 2 text fields mapped to 2 fields in database and 2 buttons (1st widget is a standard QTextEdit - just to see that everything works, 2nd widget is a cusomized one - it will replace empty string with None value). Button "Load from DB" gets data from DB into textfields and button "Save to DB" puts them back.
You may see that if PySide6 used then empty string from Field2 widget will go directly to database. But if you replace PySide6 with PySide2 then NULL will be stored in database field2 if Field2 has empty text.

from PySide6.QtCore import Property, Signal
from PySide6.QtSql import QSqlDatabase, QSqlTableModel
from PySide6.QtWidgets import QApplication, QLineEdit, QMainWindow, QWidget, QPushButton, QDataWidgetMapper, QVBoxLayout


class CustomEdit(QLineEdit):
    changed = Signal()

    def __init__(self, parent=None):
        super().__init__(parent)

    def getValue(self):
        if self.text() == '':
            return None
        else:
            return self.text()

    def setValue(self, new_value):
        self.setText(new_value)
        self.changed.emit()

    value = Property(str, getValue, setValue, notify=changed, user=True)


class MyWnd(QMainWindow):
    def __init__(self):
        super().__init__()
        self.layout = QVBoxLayout()
        self.widget = QWidget()
        self.widget.setLayout(self.layout)
        self.setCentralWidget(self.widget)
        self.Field1 = QLineEdit(self)
        self.layout.addWidget(self.Field1)
        self.Field2 = CustomEdit(self)
        self.layout.addWidget(self.Field2)
        self.load_button = QPushButton("Load from DB", self)
        self.load_button.clicked.connect(self.load_db)
        self.layout.addWidget(self.load_button)
        self.save_button = QPushButton("Save to DB", self)
        self.save_button.clicked.connect(self.save_db)
        self.layout.addWidget(self.save_button)

        self.db = QSqlDatabase.addDatabase("QSQLITE", "CONNECTION_NAME")
        self.db.setDatabaseName("/home/user/db.sqlite")
        self.db.open()

        self.model = QSqlTableModel(parent=None, db=self.db)
        self.model.setTable("db_table")

        self.mapper = QDataWidgetMapper(self.model)
        self.mapper.setModel(self.model)
        self.mapper.addMapping(self.Field1, self.model.fieldIndex("field1"))
        self.mapper.addMapping(self.Field2, self.model.fieldIndex("field2"))
        self.mapper.setSubmitPolicy(QDataWidgetMapper.AutoSubmit)

    def load_db(self):
        self.model.select()
        self.mapper.toFirst()

    def save_db(self):
        self.mapper.submit()
        if not self.model.submitAll():
            print(f"ERROR: {self.model.lastError().text()}")
        else:
            print("DONE")
        self.db.commit()


app = QApplication()
wnd = MyWnd()
wnd.show()
app.exec_()

JonB

@StarterKit
I feel your pain. I had similar problems with NULL and data/setData() model methods in the past when i was using PyQt5, but I haven't used PySide2/6.

I would say PySide6's removal of distinct behaviour for NULL between, say, a str versus an int is "correct", in that there is no reason why they should handle NULL differently as you say they used to.

If PySide6 allows it you might try QVariant() instead of your None as the value, 1% chance that might work.

Otherwise I can say that PySide6 has PySide6.QtSql.QSqlRecord.setNull(). That is for a QSqlRecord, and presumably maps to/from NULL in the SQL database. I know you are using QDataWidgetMapper, I don't know how/whether you can make any use of this, perhaps not but it's a thought/something to investigate.

See also https://www.qtcentre.org/threads/35832-Custom-QLineEdit-to-store-NULL-with-QDataWidgetMapper for a (admittedly C++) discussion about QDataWidgetMapper and NULL.

StarterKit

@JonB yeah, luckily setNull() and assignment of None directly to QSqlRecord works - I tried to do it handling signals beforeInsert and beforeUpdate of QSqlTableModel class.
But... it's a real pain to put extra code just to put back a right value that was already returned by the widget itself... In past I had similar problem with int only in PySide2 and overcame it by switching to str. Now str also doesn't work and we need this dirty workaround with cheating in before/afterInsert.

With regards to https://www.qtcentre.org/threads/35832-Custom-QLineEdit-to-store-NULL-with-QDataWidgetMapper - I think I read it before when I faced problems with int. This topics looks familiar to me. And as I wrote - my widget worked fine with PySide2 but it stores empty string instead of NULL with PySide6.

JonB

@StarterKit
As I said earlier, I can only see that not working with str in the same way as not working with int is "correct" --- the fact that there was different behaviour in PySide2 sounds "wrong" to me, they should either both work or not work.

StarterKit

@JonB ah, didn't get your point before, now it is clear.
Agree with you point of view - both should work or both shouldn't, to be consistent.
So... let's assume that they did it consistent and from now on it won't work anymore.
Then it's not a bug but a feature and we may close this topic :)