def getObjectByModule(moduleName): tableSql = "SELECT objectName FROM moduleObject WHERE objectType = '1' AND modulename = '%s'" % ( moduleName) processSql = "SELECT objectName FROM moduleObject WHERE objectType = '2' AND modulename = '%s'" % ( moduleName) viewSql = "SELECT objectName FROM moduleObject WHERE objectType = '3' AND modulename = '%s'" % ( moduleName) tableName = sqliteExecute(tableSql) tableName = listsToList(tableName) tableList = [] for table in tableName: tableList.append(table.upper()) processName = sqliteExecute(processSql) processName = listsToList(processName) processList = [] for process in processName: processList.append(process.upper()) viewName = sqliteExecute(viewSql) viewName = listsToList(viewName) viewList = [] for view in viewName: viewList.append(view.upper()) return tableList, processList, viewList
def getSetupList(): moduleObjectSql = "select moduleName,objectName,objectType from moduleObject" logging.info(moduleObjectSql) moduleObjects = sqliteExecute(moduleObjectSql) if (len(moduleObjects) == 0): return [] for moduleObject in moduleObjects: functionSql = "select functionQuotaName from objectFunctionQuota where objectName = '%s' and objectType='%s' and functionQuotaType=1" % ( moduleObject[1], moduleObject[2]) functionResult = sqliteExecute(functionSql) functionResult = listsToList(functionResult) moduleObject.append(functionResult) quotaSql = "select functionQuotaName from objectFunctionQuota where objectName = '%s' and objectType='%s' and functionQuotaType=2" % ( moduleObject[1], moduleObject[2]) quotaResult = sqliteExecute(quotaSql) quotaResult = listsToList(quotaResult) moduleObject.append(quotaResult) if (moduleObject[2] == 1): moduleObject[2] = "表" elif (moduleObject[2] == 2): moduleObject[2] = "存储过程" elif (moduleObject[2] == 3): moduleObject[2] = "视图" return moduleObjects
def createBackupTable(beginTime, endTime, tableList, backupVersionId): startId = getbackupObjectId() for list in tableList: logging.info(getbackupFieldKey(list)) fieldList = getbackupFieldKey(list) fieldList = fieldList[0] fieldStr = '' for field in fieldList: fieldStr += field + ',' lenthField = len(fieldStr) fieldStr = fieldStr[0:lenthField - 1] if 'CREATE_DATE' in fieldList: createSql = 'create table %s as select %s from %s WHERE CREATE_DATE between %s and %s' \ % ('backup' + str(startId), fieldStr, list, beginTime, endTime) elif 'VC_UPDATETIME' in fieldList: createSql = 'create table %s as select %s from %s WHERE VC_UPDATETIME between %s and %s' \ % ('backup' + str(startId), fieldStr, list, str(beginTime)+'000000',str(endTime)+'000000') else: createSql = 'create table %s as select %s from %s ' \ % ('backup' + str(startId), fieldStr, list) if list == 'TDPF_TASKBUSIPARAMS': createSql = 'create table %s as select %s from %s ' \ % ('backup' + str(startId), fieldStr, list) #无时间 #createSql = 'create table %s as select %s from %s ' \ # % ('backup' + str(startId), fieldStr, list) logging.info(createSql) oracleNoFetch(createSql) insertNameListSql = 'insert into backupObjectNameList (backupVersion,objectName,backupObjectName,ObjectType) ' \ 'values("%s","%s","%s",1)' % (backupVersionId, list, startId) sqliteExecute(insertNameListSql) startId += 1
def getCurContrastInfo(): curBackupVersion = getCurContrastVersion() if len(curBackupVersion) == 0: return [[], [], [], [], {}, {}] curBackupVersion = curBackupVersion[0] tableSql = "Select objectName From backupObjectNameList Where objectType=1 and backupVersion = '%s' " % ( curBackupVersion) tableList = sqliteExecute(tableSql) tableList = listsToList(tableList) produceSql = "Select objectName From backupObjectNameList Where objectType=2 and backupVersion = '%s' " % ( curBackupVersion) produceList = sqliteExecute(produceSql) produceList = listsToList(produceList) viewSql = "Select objectName From backupObjectNameList Where objectType=3 and backupVersion = '%s' " % ( curBackupVersion) viewList = sqliteExecute(viewSql) viewList = listsToList(viewList) fieldDicts = {} for table in tableList: fieldList = getbackupFieldKey(table)[0] fieldDicts[table] = fieldList resultDicts = {} for table in tableList: resultList = getDiffNumByTableName(table) resultDicts[table] = resultList return [ str(curBackupVersion), tableList, produceList, viewList, fieldDicts, resultDicts ]
def deleteModule(moduleName): moduleObjectsql = "delete from moduleObject where moduleName = '%s' and isSystemDefine = 0 " % ( moduleName) modulesql = "delete from moduleList where moduleName = '%s' and isSystemDefine = 0 " % ( moduleName) sqliteExecute(moduleObjectsql) sqliteExecute(modulesql)
def deleteBackup(backupVersion): tableList = getObjectByVersion(backupVersion)[0] for table in tableList: delTableSql = "drop table %s " % ('backup' + str(table[1])) oracleNoFetch(delTableSql) delBackupObjectNameList = "delete from backupObjectNameList where backupVersion = '%s' " % ( backupVersion) delBackupInformation = "delete from backupInformation where backupVersion = '%s' " % ( backupVersion) sqliteExecute(delBackupInformation) sqliteExecute(delBackupObjectNameList)
def getFunctionQuotaInfo(): functionSql = "SELECT functionQuotaName FROM functionQuotaList where functionQuotaType=1" quotaSql = "SELECT functionQuotaName FROM functionQuotaList where functionQuotaType=2" functionList = sqliteExecute(functionSql) functionList = listsToList(functionList) quotaList = sqliteExecute(quotaSql) quotaList = listsToList(quotaList) return functionList, quotaList
def deleteModuleList(setupList): if (setupList[2] == "表"): setupList[2] = 1 elif (setupList[2] == "存储过程"): setupList[2] = 2 elif (setupList[2] == "视图"): setupList[2] = 3 moduleObjectsql = "delete from moduleObject where moduleName = '%s' and objectName='%s' and objectType='%s' and isSystemDefine= '0' " % ( setupList[0], setupList[1], setupList[2]) sqliteExecute(moduleObjectsql)
def getObjectByVersion(backupVersion): tableSql = "SELECT objectName,backupObjectName FROM backupObjectNameList WHERE backupVersion =%s and objectType='1'" % ( backupVersion) processSql = "SELECT objectName,backupObjectName FROM backupObjectNameList WHERE backupVersion =%s and objectType='2'" % ( backupVersion) viewSql = "SELECT objectName,backupObjectName FROM backupObjectNameList WHERE backupVersion =%s and objectType='3'" % ( backupVersion) tableList = sqliteExecute(tableSql) processList = sqliteExecute(processSql) viewList = sqliteExecute(viewSql) return tableList, processList, viewList
def getbackupFieldKey(tableName): fieldSql = 'SELECT fieldChosed from backupFieldKey where tableName = "%s" and fieldType = 1 ' % ( tableName) keySql = 'SELECT fieldChosed from backupFieldKey where tableName = "%s" and fieldType = 2' % ( tableName) # allfieldSql = 'SELECT DISTINCT(fieldChosed) from backupFieldKey where tableName = "%s"'%(tableName) fieldList = sqliteExecute(fieldSql) fieldList = listsToList(fieldList) keyList = sqliteExecute(keySql) keyList = listsToList(keyList) # allList = sqliteExecute(allfieldSql) # allList = listsToList(allList) return [fieldList, keyList]
def checkSystemDb(): sql = "SELECT * FROM sqlite_master" system_table_info = sqliteExecute(sql) if (len(system_table_info) == 0): logging.info("系统数据库信息不存在") count = 0 # 读取行数 sql = "" # 拼接的sql语句 with open('DBBuild.sql', "r", encoding="utf-8") as f: for each_line in f.readlines(): # 过滤数据 if not each_line or each_line == "\n": continue # 读取2000行数据,拼接成sql else: sql += each_line count += 1 # 读取达到2000行数据,进行提交,同时,初始化sql,count值 # 当读取完毕文件,不到2000行时,也需对拼接的sql 执行、提交 sqlList = sql.split(";") sqliteConn = getSqliteConnection() sqliteCusor = sqliteConn.cursor() try: for subSqlList in sqlList: sqliteCusor.execute(subSqlList) except sqlite3.Error as msg: logging.debug(msg) logging.debug(subSqlList) sqliteConn.commit() sqliteConn.close()
def getContrastData(tableName, type, pageNum): recordPerPage = 30 [fieldList, keyList] = getbackupFieldKey(tableName) fieldStr = fieldListToStr(fieldList) keyNum = len(keyList) beginId = (pageNum - 1) * recordPerPage * keyNum endId = pageNum * recordPerPage * keyNum resultList = getKeysByTableNameAndType(tableName, type, beginId, endId) resultNum = len(resultList) keyDataList = [] for i in range(recordPerPage): subList = [] for j in range(keyNum): if (resultNum > i * keyNum + j): subList.append(resultList[i * keyNum + j][1]) else: subList.append([]) keyDataList.append(subList) curBackupVersion = getCurContrastVersion() if len(curBackupVersion) == 0: return [] curBackupVersion = curBackupVersion[0] getBackupTableSql = 'select backupObjectName from backupObjectNameList where backupVersion = "%s" and objectName = "%s"' % ( curBackupVersion, tableName) backTableVersion = sqliteExecute(getBackupTableSql)[0] if len(backTableVersion) == 0: return [] backupObjectName = "backup" + str(backTableVersion[0]) CurData = getDataByKeys(tableName, keyList, fieldList, keyDataList) backupData = getDataByKeys(backupObjectName, keyList, fieldList, keyDataList) return CurData, backupData
def getBackupTime(backupVersion): checkSystemDb() backInformationList = sqliteExecute( 'select beginTime,endTime from backupInformation where backupVersion = "%s"' % (backupVersion)) return backInformationList
def getObjectByType(typeCode): if (typeCode != 1 & typeCode != 2 & typeCode != 3): logging.info("参数不合法") sql = "SELECT * FROM backupObjectNameList WHERE backupVersion ='' AND ObjectType = " + str( typeCode) + "" objectList = sqliteExecute(sql) return objectList
def getCurContrastVersion(): getCurConSql = "select backupVersion from backupInformation where hasContrast = 1 " curBackupVersion = sqliteExecute(getCurConSql) if len(curBackupVersion) == 0: return [] curBackupVersion = curBackupVersion[0] if len(curBackupVersion) == 0: return [] return curBackupVersion
def getbackupObjectId(): askSql = 'SELECT max(backupObjectName) from backupObjectNameList' version = sqliteExecute(askSql)[0] logging.info(len(version)) if ((len(version) == 0) | (version[0] == '') | (version[0] == None)): backupObjectName = 1 else: backupObjectName = int(version[0]) + 1 return backupObjectName
def getbackupVersionId(): askSql = 'SELECT max(backupVersion) from backupInformation' versionList = sqliteExecute(askSql) versionList = listsToList(versionList) if (versionList == [None]): backupVersion = 1 else: backupVersion = versionList[0] + 1 return backupVersion
def insertModuleObjectsField(dicts1, dicts2): moduleName = dicts1.get('module') functionList = dicts1.get('function') quotaList = dicts1.get('quota') tableList = dicts1.get('table') processList = dicts1.get('process') viewList = dicts1.get('view') """" 新增配置关系 """ if len(tableList) != 0: for objectName in tableList: tableSql = "insert into moduleObject (moduleName,objectName,objectType,isSystemDefine) values ('%s','%s',1,0)" % ( moduleName, objectName) sqliteExecute(tableSql) if len(processList) != 0: for objectName in processList: processSql = "insert into moduleObject (moduleName,objectName,objectType,isSystemDefine) values ('%s','%s',1,0)" % ( moduleName, objectName) sqliteExecute(processSql) if len(viewList) != 0: for objectName in viewList: viewSql = "insert into moduleObject (moduleName,objectName,objectType,isSystemDefine) values ('%s','%s',1,0)" % ( moduleName, objectName) sqliteExecute(viewSql) """ 新增备份字段与对比主键 """ for table in tableList: backupFileds = dicts2.get(table).get('field') keyFields = dicts2.get(table).get('key') for backupFiled in backupFileds: fieldSql = "insert into backupFieldKey (tableName,fieldChosed,fieldType,isSystemDefine) values " \ "('%s','%s',1,0)" % (table, backupFiled) sqliteExecute(fieldSql) for backupFiled in keyFields: keySql = "insert into backupFieldKey (tableName,fieldChosed,fieldType,isSystemDefine) values " \ "('%s','%s',2,0)" % (table, backupFiled) sqliteExecute(keySql) """
#!/usr/bin/env python # encoding: utf-8 ''' @author: zwd @contact: [email protected] @file: addFieldScript.py @time: 2019/8/23 15:51 @function:获取字段并插入 @inputParam: @returnParam: ''' from backGround.setupSql import getOracleInfo, tuplesToList, listsToList from backGround.testConnection import sqliteExecute, oracleExcute [tableList, produceList, viewList, fieldDicts] = getOracleInfo() tableList = sqliteExecute("select distinct(objectName) from moduleObject ") tableList = tuplesToList(tableList) tableList = listsToList(tableList) for table in tableList: try: fieldList = fieldDicts[table] for field in fieldList: insertSql= "insert into backupFieldKey (tableName,fieldChosed,fieldType,isSystemDefine) values " \ "('%s','%s','1','1')"%(table,field) sqliteExecute(insertSql) except Exception as err: print(table + "不存在")
def changeCurContrast(backupVersion): removeSql = "update backupInformation set hasContrast = '0' " setSql = "update backupInformation set hasContrast = '1' where backupVersion='%s'" % ( backupVersion) sqliteExecute(removeSql) sqliteExecute(setSql)
def getBackupInfomation(): checkSystemDb() backInformationList = sqliteExecute( "select backupVersion,backupTime from backupInformation") return backInformationList
def getModuleInfo(): sql = "SELECT moduleName FROM moduleList" moduleInfo = sqliteExecute(sql) moduleInfo = listsToList(moduleInfo) return moduleInfo
def insertModule(moduleName): sql = "INSERT INTO moduleList(moduleName,isSystemDefine) VALUES('%s',0);" % ( moduleName) sqliteExecute(sql)
def insertBackupInformation(backupVersion, beginTime, endTime): getDataSql = "select datetime(CURRENT_TIMESTAMP,'localtime');" curData = sqliteExecute(getDataSql)[0][0] insertSql = "insert into backupInformation (backupVersion,backupTime,beginTime,endTime,hasContrast)values('%s','%s','%s','%s','%s') " % ( backupVersion, curData, beginTime, endTime, 0) sqliteExecute(insertSql)