def __getSqlDeleteList(self,
tableId,
containerNameList=None,
deleteOpt="all"):
"""Return the SQL delete commands for the input table and container name list."""
databaseName = self.__sD.getDatabaseName()
sqlGen = SqlGenAdmin(self.__verbose)
databaseName = self.__sD.getDatabaseName()
tableDefObj = self.__sD.getSchemaObject(tableId)
tableName = tableDefObj.getName()
sqlDeleteList = []
if deleteOpt in ["selected", "delete"
] and containerNameList is not None:
deleteAttributeName = tableDefObj.getDeleteAttributeName()
sqlDeleteList = sqlGen.deleteFromListSQL(databaseName,
tableName,
deleteAttributeName,
containerNameList,
chunkSize=50)
elif deleteOpt in ["all", "truncate"]:
sqlDeleteList = [sqlGen.truncateTableSQL(databaseName, tableName)]
logger.debug("Delete SQL for %s : %r\n", tableId, sqlDeleteList)
return sqlDeleteList
def __batchInsertImport(self, tableId, rowList=None, containerNameList=None, deleteOpt="selected"):
""" Load the input table using batch 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)
myQ.setWarning(self.__warningAction)
sqlGen = SqlGenAdmin(self.__verbose)
#
databaseName = self.__sD.getDatabaseName()
tableDefObj = self.__sD.getSchemaObject(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:
logger.debug("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[id]) > 0 and row[id] != r'\N':
if row[tid] is not None 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 ret:
logger.debug("Batch insert completed for table %s rows %d\n", tableName, len(sqlInsertList))
else:
logger.error("Batch insert fails for table %s length %d\n", tableName, len(sqlInsertList))
endTime = time.time()
if self.__verbose:
logger.debug("Completed at %s (%.3f seconds)\n", time.strftime("%Y %m %d %H:%M:%S", time.localtime()), endTime - startTime)
return ret
def __cockroachInsertImport(self, tableId, rowList=None, containerNameList=None, deleteOpt="selected", sqlMode="many"):
"""Load the input table using sql cockroach templated inserts of the input rowlist 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.
Args:
tableId (TYPE): Description
rowList (None, optional): Description
containerNameList (None, optional): Description
deleteOpt (str, optional): Description
sqlMode (str, optional): Description
Returns:
TYPE: Description
"""
startTime = time.time()
crQ = CockroachDbQuery(dbcon=self.__dbCon, verbose=self.__verbose)
sqlGen = SqlGenAdmin(self.__verbose)
#
databaseName = self.__sD.getVersionedDatabaseName()
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()
logger.debug("tableName %s delete attribute %s", tableName, deleteAttributeName)
sqlDeleteList = sqlGen.deleteFromListSQL(databaseName, tableName, deleteAttributeName, containerNameList, chunkSize=10)
# logger.debug("Delete SQL for %s : %r" % (tableId, sqlDeleteList))
elif deleteOpt in ["all", "truncate"]:
sqlDeleteList = [sqlGen.truncateTableSQL(databaseName, tableName)]
#
lenC = len(rowList)
logger.debug("Deleting from table %s length %d", tableName, lenC)
crQ.sqlCommandList(sqlDeleteList)
endTime1 = time.time()
logger.debug("Deleting succeeds for table %s %d rows at %s (%.3f seconds)", tableName, lenC, time.strftime("%Y %m %d %H:%M:%S", time.localtime()), endTime1 - startTime)
logger.debug("Delete commands %s", sqlDeleteList)
if not rowList:
logger.debug("Skipping insert for table %s length %d", tableName, len(containerNameList))
return True
#
logger.debug("Insert begins for table %s with row length %d", tableName, len(rowList))
sqlInsertList = []
tupL = list(zip(tableAttributeIdList, tableAttributeNameList))
if sqlMode == "many":
aList = []
for tId, nm in tupL:
aList.append(tId)
#
vLists = []
for row in rowList:
vList = []
for tId, nm in tupL:
if row[tId] and row[tId] != r"\N":
vList.append(row[tId])
else:
vList.append(None)
vLists.append(vList)
#
ret = crQ.sqlTemplateCommandMany(sqlTemplate=sqlGen.idInsertTemplateSQL(databaseName, tableDefObj, aList), valueLists=vLists)
endTime = time.time()
if ret:
logger.debug("Insert succeeds for table %s %d rows at %s (%.3f seconds)", tableName, lenC, time.strftime("%Y %m %d %H:%M:%S", time.localtime()), endTime - endTime1)
else:
logger.error("Insert fails for table %s %d rows at %s (%.3f seconds)", tableName, lenC, time.strftime("%Y %m %d %H:%M:%S", time.localtime()), endTime - endTime1)
else:
lenT = -1
lenR = -1
aList = []
for tId, nm in tupL:
aList.append(nm)
#
for row in rowList:
vList = []
for tId, nm in tupL:
if row[tId] is not None and row[tId] != r"\N":
vList.append(row[tId])
else:
vList.append(None)
sqlInsertList.append((sqlGen.insertTemplateSQL(databaseName, tableName, aList), vList))
#
lenT = len(sqlInsertList)
lenR = crQ.sqlTemplateCommandList(sqlInsertList)
#
ret = lenR == lenT
endTime = time.time()
if ret:
logger.debug(
"Insert succeeds for table %s %d of %d rows at %s (%.3f seconds)",
tableName,
lenR,
lenT,
time.strftime("%Y %m %d %H:%M:%S", time.localtime()),
endTime - endTime1,
)
else:
logger.error(
"Insert fails for table %s %d of %d rows at %s (%.3f seconds)", tableName, lenR, lenT, time.strftime("%Y %m %d %H:%M:%S", time.localtime()), endTime - endTime1
)
return ret
def __crateInsertImport(self,
tableId,
rowList=None,
containerNameList=None,
deleteOpt="selected",
sqlMode="many",
refresh=True):
"""Load the input table using sql crate templated inserts of the input rowlist 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()
sqlRefresh = None
crQ = CrateDbQuery(dbcon=self.__dbCon, verbose=self.__verbose)
sqlGen = SqlGenAdmin(self.__verbose)
#
databaseName = self.__sD.getVersionedDatabaseName()
tableDefObj = self.__sD.getSchemaObject(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)
logger.debug("Delete SQL for %s : %r", tableId, sqlDeleteList)
elif deleteOpt in ["all", "truncate"]:
sqlDeleteList = [sqlGen.truncateTableSQL(databaseName, tableName)]
#
logger.debug("Deleting from table %s length %d", tableName,
len(containerNameList))
crQ.sqlCommandList(sqlDeleteList)
logger.debug("Delete commands %s", sqlDeleteList)
if not rowList:
return True
if refresh:
sqlRefresh = sqlGen.refreshTableSQLCrate(databaseName, tableName)
crQ.sqlCommand(sqlRefresh)
#
logger.info("Insert begins for table %s with row length %d", tableName,
len(rowList))
sqlInsertList = []
tupL = list(zip(tableAttributeIdList, tableAttributeNameList))
if sqlMode == "many":
aList = []
for tId, nm in tupL:
aList.append(nm)
#
vLists = []
for row in rowList:
vList = []
for tId, nm in tupL:
if row[tId] and row[tId] != r"\N":
vList.append(row[tId])
else:
vList.append(None)
vLists.append(vList)
#
lenT = len(vLists)
lenR = crQ.sqlTemplateCommandMany(
sqlTemplate=sqlGen.insertTemplateSQLCrate(
databaseName, tableName, aList),
valueLists=vLists)
ret = lenR == len(vLists)
else:
aList = []
for tId, nm in tupL:
aList.append(nm)
#
for row in rowList:
vList = []
for tId, nm in tupL:
if row[tId] is not None and row[tId] != r"\N":
vList.append(row[tId])
else:
vList.append(None)
sqlInsertList.append(
(sqlGen.insertTemplateSQLCrate(databaseName, tableName,
aList), vList))
#
lenT = len(sqlInsertList)
lenR = crQ.sqlTemplateCommandList(sqlInsertList)
ret = lenR == lenT
if refresh:
sqlRefresh = sqlGen.refreshTableSQLCrate(databaseName, tableName)
crQ.sqlCommand(sqlRefresh)
#
endTime = time.time()
if ret:
logger.info(
"Insert succeeds for table %s %d of %d rows at %s (%.3f seconds)",
tableName, lenR, lenT,
time.strftime("%Y %m %d %H:%M:%S",
time.localtime()), endTime - startTime)
else:
logger.info(
"Insert fails for table %s %d of %d rows at %s (%.3f seconds)",
tableName, lenR, lenT,
time.strftime("%Y %m %d %H:%M:%S",
time.localtime()), endTime - startTime)
return ret
