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)
