def testBirdBatchInsertImport(self):
"""Test case - import loadable data via SQL inserts -"""
startTime = time.time()
self.__lfh.write(
"\nStarting BirdLoaderTests testBirdBatchInsertImport 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()
sqlL = []
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)
sqlL.append(
(myAd.insertTemplateSQL(databaseName, tableName,
aList), vList))
ok = myQ.sqlBatchTemplateCommand(sqlL)
if self.__verbose and not ok:
# self.__lfh.write("\n\n+ERROR batch insert fails for table %s row %r\n" % (tableName,sqlL))
pass
except: # noqa: E722 pylint: disable=bare-except
traceback.print_exc(file=self.__lfh)
self.fail()
endTime = time.time()
self.__lfh.write(
"\nCompleted BirdLoaderTests testBirdBatchInsertImport at %s (%.2f seconds)\n"
% (time.strftime("%Y %m %d %H:%M:%S",
time.localtime()), endTime - startTime))
def __batchInsertImport(self,
tableId,
rowList=None,
containerNameList=None,
deleteOpt="selected"):
"""Load the input table using bacth inserts of the input list of dictionaries (i.e. d[attributeId]=value).
The containerNameList corresponding to the data within loadable data in rowList can be provided
if 'selected' deletions are to performed prior to the the batch data inserts.
deleteOpt = ['selected','all'] where 'selected' deletes rows corresponding to the input container
list before insert. The 'all' options truncates the table prior to insert.
Deletions are performed in the absence of loadable data.
"""
startTime = time.time()
myQ = MyDbQuery(dbcon=self.__dbCon,
verbose=self.__verbose,
log=self.__lfh)
myQ.setWarning(self.__warningAction)
sqlGen = MyDbAdminSqlGen(self.__verbose, self.__lfh)
#
databaseName = self.__sD.getDatabaseName()
tableDefObj = self.__sD.getTable(tableId)
tableName = tableDefObj.getName()
tableAttributeIdList = tableDefObj.getAttributeIdList()
tableAttributeNameList = tableDefObj.getAttributeNameList()
#
sqlDeleteList = None
if deleteOpt in ["selected", "delete"
] and containerNameList is not None:
deleteAttributeName = tableDefObj.getDeleteAttributeName()
sqlDeleteList = sqlGen.deleteFromListSQL(databaseName,
tableName,
deleteAttributeName,
containerNameList,
chunkSize=10)
if self.__verbose:
self.__lfh.write(
"+SchemaDefLoader(batchInsertImport) delete SQL for %s : %r\n"
% (tableId, sqlDeleteList))
elif deleteOpt in ["all", "truncate"]:
sqlDeleteList = [sqlGen.truncateTableSQL(databaseName, tableName)]
sqlInsertList = []
for row in rowList:
vList = []
aList = []
for tid, nm in zip(tableAttributeIdList, tableAttributeNameList):
if len(row[tid]) > 0 and row[tid] != r"\N":
vList.append(row[tid])
aList.append(nm)
sqlInsertList.append(
(sqlGen.insertTemplateSQL(databaseName, tableName,
aList), vList))
ret = myQ.sqlBatchTemplateCommand(sqlInsertList,
prependSqlList=sqlDeleteList)
if self.__verbose:
if ret:
self.__lfh.write(
"+SchemaDefLoader(__batchInsertImport) batch insert completed for table %s rows %d\n"
% (tableName, len(sqlInsertList)))
else:
self.__lfh.write(
"+SchemaDefLoader(__batchInsertImport) batch insert fails for table %s length %d\n"
% (tableName, len(sqlInsertList)))
endTime = time.time()
if self.__verbose:
self.__lfh.write(
"+SchemaDefLoader(__batchInsertImport) completed at %s (%.3f seconds)\n"
% (time.strftime("%Y %m %d %H:%M:%S",
time.localtime()), endTime - startTime))
return ret