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