Solved PySide6.QDataWidgetMapper - how to store NULL from mapped widget into DB?
-
Hi all,
I have a code to display/edit data from database. It has a custom widget which hasstr
User property to be used with QDataWidgetMapper - it storesNULL
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 useint
property but QDataWidgetMapper doesn't setint
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 asNULL
value in DB but PySide6 stores an empty string.
So, the question is - if my widget returnsNone
how to store it asNULL
value into database with PySide6? (I really needNULL
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 thenNULL
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_()
-
@StarterKit
I feel your pain. I had similar problems withNULL
anddata
/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, astr
versus anint
is "correct", in that there is no reason why they should handleNULL
differently as you say they used to.If PySide6 allows it you might try
QVariant()
instead of yourNone
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/fromNULL
in the SQL database. I know you are usingQDataWidgetMapper
, 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
andNULL
. -
@JonB yeah, luckily
setNull()
and assignment ofNone
directly toQSqlRecord
works - I tried to do it handling signalsbeforeInsert
andbeforeUpdate
ofQSqlTableModel
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 withint
only in PySide2 and overcame it by switching tostr
. Nowstr
also doesn't work and we need this dirty workaround with cheating inbefore/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. -
@StarterKit
As I said earlier, I can only see that not working withstr
in the same way as not working withint
is "correct" --- the fact that there was different behaviour in PySide2 sounds "wrong" to me, they should either both work or not work. -
@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 :) -