def _deleteRequest(self, tableId, **kwargs): """Delete from input table records identified by the keyword value pairs provided as input arguments -""" startTime = time.time() if self.__debug: logger.debug("Starting _deleteRequest at %s", time.strftime("%Y %m %d %H:%M:%S", time.localtime())) ret = False try: iOpened = False if self.__dbCon is None: self._open() iOpened = True # tableName = self.__sd.getTableName(tableId) tableDefObj = self.__sd.getTable(tableId) # # myQ = MyDbQuery(dbcon=self.__dbCon, verbose=self.__verbose, log=self.__lfh) myAd = MyDbAdminSqlGen(self.__verbose, self.__lfh) # # Create the attribute and value list for template -- # vList = [] aList = [] for atId, kwId in self._getAttributeParameterMap(tableId): if kwId in kwargs and kwargs[kwId] is not None: vList.append(kwargs[kwId]) aList.append(atId) sqlT = myAd.idDeleteTemplateSQL(self.__databaseName, tableDefObj, conditionAttributeIdList=aList) if self.__debug: logger.debug("_deleteRequest delete sql: %s", sqlT) logger.debug("_deleteReuqest delete values: %r", vList) ret = myQ.sqlTemplateCommand(sqlTemplate=sqlT, valueList=vList) if iOpened: self._close() except Exception as e: status = " delete operation error " + str(e) logger.error("_deleteRequest %s", status) if self.__verbose: logger.exception("In _deleteRequest") if self.__debug: endTime = time.time() logger.debug("Completed _deleteRequest at %s (%.3f seconds)", time.strftime("%Y %m %d %H:%M:%S", time.localtime()), endTime - startTime) return ret
def testBirdInsertImport(self): """Test case - import loadable data via SQL inserts""" startTime = time.time() self.__lfh.write( "\nStarting BirdLoaderTests testBirdInsertImport at %s\n" % time.strftime("%Y %m %d %H:%M:%S", time.localtime())) try: self.testPrdPathList() bsd = BirdSchemaDef() sml = SchemaDefLoader(schemaDefObj=bsd, verbose=self.__verbose, log=self.__lfh) self.__lfh.write("Length of path list %d\n" % len(self.__loadPathList)) # tableDataDict, _containerNameList = sml.fetch(self.__loadPathList) databaseName = bsd.getDatabaseName() tableIdList = bsd.getTableIdList() myQ = MyDbQuery(dbcon=self.__dbCon, verbose=self.__verbose, log=self.__lfh) myAd = MyDbAdminSqlGen(self.__verbose, self.__lfh) # for tableId in tableIdList: tableDefObj = bsd.getTable(tableId) tableName = tableDefObj.getName() tableAttributeIdList = tableDefObj.getAttributeIdList() tableAttributeNameList = tableDefObj.getAttributeNameList() if tableId in tableDataDict: rowList = tableDataDict[tableId] for row in rowList: vList = [] aList = [] for tid, nm in zip(tableAttributeIdList, tableAttributeNameList): if len(row[tid]) > 0 and row[id] != r"\N": vList.append(row[tid]) aList.append(nm) insertTemplate = myAd.insertTemplateSQL( databaseName, tableName, aList) ok = myQ.sqlTemplateCommand(sqlTemplate=insertTemplate, valueList=vList) if self.__verbose and not ok: # self.__lfh.write("\n\n+ERROR insert fails for table %s row %r\n" % (tableName,vList)) # ts=insertTemplate % tuple(vList) # self.__lfh.write("\n%s\n" % ts) pass except: # noqa: E722 pylint: disable=bare-except traceback.print_exc(file=self.__lfh) self.fail() endTime = time.time() self.__lfh.write( "\nCompleted BirdLoaderTests testBirdInsertImport at %s (%.2f seconds)\n" % (time.strftime("%Y %m %d %H:%M:%S", time.localtime()), endTime - startTime))
def _updateRequest(self, tableId, contextId, **kwargs): """Update the input table using the keyword value pairs provided as input arguments - The contextId controls the handling default values for unspecified parameters. """ startTime = time.time() if self.__debug: logger.debug("Starting _updateRequest at %s", time.strftime("%Y %m %d %H:%M:%S", time.localtime())) ret = False try: iOpened = False if self.__dbCon is None: self._open() iOpened = True # # tableName = self.__sd.getTableName(tableId) tableDefObj = self.__sd.getTable(tableId) # myQ = MyDbQuery(dbcon=self.__dbCon, verbose=self.__verbose, log=self.__lfh) myAd = MyDbAdminSqlGen(self.__verbose, self.__lfh) defaultValD = self._getParameterDefaultValues(contextId=contextId) cIdList = self._getConstraintParameterMap(tableId) # # create the value list for template -- # vList = [] aList = [] cList = [] for atId, kwId in self._getAttributeParameterMap(tableId): if (atId, kwId) in cIdList: continue if kwId in kwargs and kwargs[kwId] is not None: vList.append(kwargs[kwId]) aList.append(atId) else: if kwId in defaultValD and defaultValD[kwId] is not None: vList.append(defaultValD[kwId]) aList.append(atId) for atId, kwId in cIdList: if kwId in kwargs and kwargs[kwId] is not None: vList.append(kwargs[kwId]) cList.append(atId) sqlT = myAd.idUpdateTemplateSQL(self.__databaseName, tableDefObj, updateAttributeIdList=aList, conditionAttributeIdList=cList) if self.__debug: logger.debug("update sql: %s", sqlT) logger.debug("update values: %r", vList) ret = myQ.sqlTemplateCommand(sqlTemplate=sqlT, valueList=vList) if iOpened: self._close() except Exception as e: status = " update operation error " + str(e) logger.error("_updateRequest %s", status) if self.__verbose: logger.exception("%s", status) if self.__debug: endTime = time.time() logger.debug("Completed _updateRequest %s (%.3f seconds)\n", time.strftime("%Y %m %d %H:%M:%S", time.localtime()), endTime - startTime) return ret
def _insertRequest(self, tableId, contextId, **kwargs): """Insert into the input table using the keyword value pairs provided as input arguments - The contextId controls the handling default values for unspecified parameters. """ startTime = time.time() if self.__debug: logger.debug("Starting _insertRequest() %s", time.strftime("%Y %m %d %H:%M:%S", time.localtime())) ret = False try: iOpened = False if self.__dbCon is None: self._open() iOpened = True # # tableName = self.__sd.getTableName(tableId) tableDefObj = self.__sd.getTable(tableId) # myQ = MyDbQuery(dbcon=self.__dbCon, verbose=self.__verbose, log=self.__lfh) myAd = MyDbAdminSqlGen(self.__verbose, self.__lfh) defaultValD = self._getParameterDefaultValues(contextId=contextId) # # Create the attribute and value list for template -- # vList = [] aList = [] for atId, kwId in self._getAttributeParameterMap(tableId=tableId): if kwId in kwargs and kwargs[kwId] is not None: vList.append(kwargs[kwId]) aList.append(atId) else: # use the default values if these exist if kwId in defaultValD and defaultValD[kwId] is not None: vList.append(defaultValD[kwId]) aList.append(atId) else: # appropriate null handling -- all fields must be assigned on insert -- vList.append(tableDefObj.getSqlNullValue(atId)) aList.append(atId) sqlT = myAd.idInsertTemplateSQL(self.__databaseName, tableDefObj, insertAttributeIdList=aList) if self.__debug: logger.debug("_insertRequest aList %d vList %d", len(aList), len(vList)) logger.debug("_insertRequest insert template sql=%s", sqlT) logger.debug("_insertRequest insert values vList=%r", vList) # sqlC = sqlT % vList # self.__lfh.write("+%s.%s insert sql command =\n%s\n" % (self.__class__.__name__, sys._getframe().f_code.co_name, sqlC)) ret = myQ.sqlTemplateCommand(sqlTemplate=sqlT, valueList=vList) if iOpened: self._close() except Exception as e: status = " insert operation error " + str(e) logger.error("%s", status) if self.__verbose: logger.exception("Exception in _insertRequest") if self.__debug: endTime = time.time() logger.debug("_insertRequest completed at %s (%.3f seconds)", time.strftime("%Y %m %d %H:%M:%S", time.localtime()), endTime - startTime) return ret