Solved 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. -
When dealing with a Database (regardless of the type) always interface with it on a native level then when moving that information to the Gui you run it through a translator of a sorts. I am not sure how your specific Model is working but as a rule I never ever tie anything on the front-end directly to something on the back-end -- I always use a Controller/Handler in between -- this inbetweener might be as simple as a pass through function or it might be a fairly complex reparsing of the data for the Gui and unparsing of the data for Database. So in a nutshell you have View (Gui) <-> Controller/Handler <-> Model (Database) -- or the the ever so popular term MVC.
To perhaps reiterate but to say it slightly differently -- your View (Front-End or Gui or Presentor of Data) should never know or even care how the Model (Back-End or Database or whatever the Data Source is) renders the data as that is the job of the Controller/Handler/Interpreter and its job is to do just that -- it makes all the calls to the Database APIs and supplies all the APIs for the Gui to make calls to. This way if the back-end Data Source should ever change the only thing effected is the Controller's interface to the Back-End (and of course the Back-End since you basically just gutted it in favor of you new Back-End) -- further this works the same for the Front-End if you go from a PC to Android or to Web-Based presenter the Back-End is never going to know or even care as the Controller still references it the same way.
I hope that helps, although it might not be the answer you were looking for.
Note this does not mean you cannot use that QSqlTableModel it just means its only going to be used in the context of the Front-End and when that data is to be saved into the database it is sent to the Controller where it is manipulated so that it can be saved into the Database as it should.
I have run into instances where one database handled Nulls one way and another database handled them in an incompatible manner and yet the Gui had to deal with both databases. In this case the V-C-M-M worked wonderfully well and demonstrated why you want to use some version of that methodology when working with a Data Source or Sources
-
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. -
Well the logical step within PyQt5 to take once you plan to go down this road is to push your Database into its own Thread but that is a different issue and one that can be implemented easier if you start off with it in a Classy manner (aka Class your Database module). I used sqlite3 for the database but the basic concept is still the same. Your Database module needs to be self-contained such that neither the Gui nor the Controller (or Router) needs to know how it connects and talks to the database as such you need something like this:
class pgDatabase: # This handles everything pertinent to the Database def __init__(self, parent): self.__SQLDef = '' self.__dbasePathName = '' self.Parent = parent def Connect(self, SourceDbase): # Establish Full Path to Database DbaseFilePath = '' if len(SourceDbase) > 0 and osPath.isfile(SourceDbase): DbaseFilePath = SourceDbase else: DbaseFilePath = str(QSettings('Company,'Project').value('LastFullPath')) if len(DbaseFilePath) == 0: DbaseFilePath = self.SetDatabase() #This is that handles a QFileDialog and such # If DbaseFilePath is still empty the user has chosen not to supply a valid *.sql so abort the program if len(DbaseFilePath) < 1: self.Parent.QuitRequest() else: QSettings('Company,'Project').setValue('LastFullPath', DbaseFilePath) self.__dbasePathName = DbaseFilePath def OpenConn(self): pass # This is for opening a connection each time you send a command to the database def CloseConn(self): self.dbConn.close() #This is for closing that connection each time you finish said command def dbQuery(self): #Used for Querying try: self.OpenConn() self.dbCrsr.execute(self.dbSQLStr) return self.dbCrsr.fetchall() except Exception as err: print("ERROR : dbQuery :",err) print(self.dbSQLStr) sysExit() finally: self.CloseConn() def dbExecute(self): #Used for Insert, Update, Delete try: self.OpenConn() self.dbCrsr.execute(self.dbSQLStr) self.dbConn.commit() except Exception as err: print("ERROR : dbExecute :",err) print("Query : ",self.dbSQLStr) sysExit() finally: self.CloseConn() def GetSpecificValues(self): # This is how to design and use a Query and dbSQLStr is a property of this class self.dbSQLStr = 'SELECT Value1, Value2 FROM tblTableName ' self.dbSQLStr += 'ORDER BY Value2' Sq3RecordObj = self.dbQuery() return Sq3RecordObj # If anyone wants more extensive free help I run an online lab-like classroom-like # message server feel free and drop by you will not be able to post until I clear # you as a student as this prevents spammers so if interested here is the invite # https://discord.gg/3D8huKCThen all you need to do is defined each of the Queries, Updates, Inserts, Deletions that you wish to use either via an in code declaration as I have shown or by creating a Stored Procedure in your database and calling it via code -- note sqlite3 does not support Stored Procedures or I would have used them. Basically I would put as much into the Database as I could since it is going to handle the data manipulation better than python would.
So basically all the Router gets is a Recordset if doing a query and a yeah I did it if it does anything else successfully. Also you get to choose what you use to implement passing the data in as well as out maybe you do not want to use a dictionary like recordset or your database does not support that. Next regardless of what the database returns to your Router - it in turn is going to adjust the data to facilitate the Gui before passing that data to the Gui -- maybe you want to have more data than the Gui needs for other reasons then get all the data in one call and parse out to the Gui whatever it needs when it needs it
Lastly this is a very rough template as such you cannot just plug-in-play it you will have to adapt it to whatever you are doing
-
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.
