Problems with NULL values from mySQL
-
Hello I would appreciate your help with a small problem.
I am using python 3.4.6 with PyQt5 version 5.5.1 and Qt 5.6.2.
This might be a trivial problem but I could not find any solution when I searched for it. I am also connecting to a mariaDB.I have derived a class from QSqlTableModel.
class HSqlTableModel(QtSql.QSqlTableModel): def __init__(self, parent, db): super().__init__(parent, db) self.translate = False self.dirtyData = False self.rLib = dict() # this dict contains the replace info self.db = db #in current table and column ind, replace from str_table, column str_fieldA with str_fieldB def setReplacement(self, ind, str_table, str_fieldA, str_fieldB, active=False): slist = SqlList(self.db,str_table, active, colA=str_fieldA, colB=str_fieldB) self.rLib[ind] = slist self.replace = True # a shorter version for helper tables, def setReplacement2(self, ind, str_table, active=False): slist = SqlList(self.db,str_table, active) self.rLib[ind] = slist self.replace = True # return Data def data(self, modind, role = QtCore.Qt.DisplayRole ): col = modind.column() var = super().data(modind, role) if var == None: var = QtCore.QVariant(0) var.clear() return var if (not col in self.rLib): return var if role == QtCore.Qt.EditRole: #print("data: editRole Var = ", var) if self.translate: trValue = self.rLib[col].getBatAval(var) return trValue else : return var if role == QtCore.Qt.DisplayRole: return self.rLib[col].getBatAval(var) elif role == QtCore.Qt.ForegroundRole: if self.rLib[col].isActive(): blueText = QtGui.QBrush(QtCore.Qt.darkBlue) return blueText else : return var #setData ( const QModelIndex & index, const QVariant & value, int role = Qt::EditRole ) def setData(self, modind, value, role ): #print("setData: role = ", role, " value = ", value) # if value is None, nothing is done if value == None or value =="": return False if not role == QtCore.Qt.EditRole: return super().setData(modind, value, role) col = modind.column() if col in self.rLib: if isinstance(value,int): return super().setData(modind, value, role) trValue = self.rLib[col].getAatBval(value) #print(" transformed Value: ", trValue) if trValue == None: return False else: ans = super().setData(modind, trValue, role) if ans == True: self.dirtyData = True return ans else: ans = super().setData(modind, value, role) if ans == True: self.dirtyData = True return ansI display the table with a QTableView. However I have a problem to update rows that contain NULL values (from the mariaDB server, NOT NULL pointers). While I can change them in the QTableView, I can't save the changes to the mariaDB server. When I try no error is displayed, but the row reverts back to the values before the change.
However I can add new rows, that contain NULL values. This behaviour is mystifying to me. Any suggestions?
Thanks for your help. -
Thank you Denni for your answer. I think I do understand roughly what you are saying. This sound like a good design concept. How would I implement this in QT. Is there are class that I can use as a controller?
There is also a model-view concept in Qt, where QSqlTalbeModel is the model and QTableView is the view. But I guess you mean that the controller would be between Qt (the view) and mariaDB (the model). How can I stick something between them? Do you have any suggestions about that?
Thanks again for your suggestion. -
Thank you Denni for your answer. I can see why this is a good idea, but it is a bit above my knowledge and requirements. The database server is on my own computer and the program is written purely for my use. I have decided to rename NULL values in the database. I have set new default values. Of course this is not portable at all but it is a quick fix and solves my problems.
Maybe once I have more time and more knowledge I will try to implement your idea. Thanks for taking the time for your answer. I will mark this thread as solved.