def updateWordFrequency(cursor):
try:
sql = "SELECT NEW_WORD, SUM(WORD_FREQUENCY) COUNTS FROM DATAPREDICT_OPINION_OLD_WORD GROUP BY NEW_WORD,WORD_FREQUENCY"
cursor.execute(sql)
rows = cursor.fetchall()
if rows != []:
# 创建人信息
path = os.path.realpath(sys.argv[0])
modifyName = getConf("common", "modifyName", path)
modifyId = getConf("common", "modifyId", path)
petition_df = pd.DataFrame(rows)
petition_df.columns = ['NEW_WORD', 'WORD_FREQUENCY']
for index, row in petition_df.iterrows(): # 获取每行的index、row
# 查询词信息
sqlGet = "SELECT OID,WORD_FREQUENCY from DATAPREDICT_OPINION_INFO WHERE NEW_WORD = '%s' " % (
row['NEW_WORD'])
cursor.execute(sqlGet)
rowsW = cursor.fetchall()
if (rowsW != []):
petition_dfW = pd.DataFrame(rowsW)
petition_dfW.columns = ['OID', 'WORD_FREQUENCY']
data = str(
time.strftime('%Y-%m-%d %H:%M:%S',
time.localtime(time.time())))
#更新词频
for indexW, rowW in petition_dfW.iterrows():
sqlW = "UPDATE DATAPREDICT_OPINION_INFO SET WORD_FREQUENCY = '%s' ,MODIFY_DATE = '%s', MODIFY_NAME = '%s', MODIFY_ID = '%s' where OID = '%s'" % (
row['WORD_FREQUENCY'], data, modifyName, modifyId,
rowW['OID'])
cursor.execute(sqlW)
logging.info("updateWordFrequency Success!")
except Exception as ex:
logging.error("updateWordFrequency Failed: %s\t" % (ex))
def updateOpinionWord(cursor, newWords, rowAll):
try:
newWords = newWords[:-1] #截取从头开始到倒数第三个字符之前
arr = newWords.split(",")
# 创建人信息
path = os.path.realpath(sys.argv[0])
createName = getConf("common", "createName", path)
createId = getConf("common", "createId", path)
for ai in arr:
# 查询新的ISSUE_REGION_NAME
sql = "SELECT ISSUE_REGION_NAME FROM ORGANIZATION_SET where ISSUE_REGION_CODE like '" + rowAll.get(
"ISSUE_REGION_CODE") + "%'"
cursor.execute(sql)
rowsWp = cursor.fetchall()
if (rowsWp != []):
petition_dfWp = pd.DataFrame(rowsWp)
petition_dfWp.columns = ['ISSUE_REGION_NAME']
# print petition_dfWp
ISSUE_REGION_NAME = petition_dfWp.iat[0, 0]
# print ISSUE_REGION_NAME
# 插入新词
aiArr = ai.split(" ")
uuid = shortuuid.ShortUUID().random(length=20)
data = str(
time.strftime('%Y-%m-%d %H:%M:%S',
time.localtime(time.time())))
# time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time()))
sql= "INSERT INTO DATAPREDICT_OPINION_WORD (OID, NEW_WORD, WORD_FREQUENCY, WORD_PART,CREATE_DATE, CREATOR_NAME, CREATOR_ID, MODIFY_DATE, MODIFY_NAME, MODIFY_ID,ISSUE_REGION_FLAG,REGION_CODE,REGION_NAME,ISSUE_REGION_CODE,ISSUE_REGION_NAME,PETITION_BASIC_INFO_OID) " \
"VALUES ('%s', '%s', %s, '%s','%s', '%s', '%s','%s', '%s', '%s', %s, '%s','%s', '%s', '%s', '%s');" \
% (uuid,aiArr[0],aiArr[2],aiArr[1],data,createName,createId,data,'','',int(rowAll.get("ISSUE_REGION_FLAG")),rowAll.get("REGION_CODE"),rowAll.get("REGION_NAME"),rowAll.get("ISSUE_REGION_CODE"),ISSUE_REGION_NAME,rowAll.get("PETITION_BASIC_INFO_OID"))
# logging.info(sql)
cursor.execute(sql)
except Exception, ex:
logging.error("updateOpinionWord Failed : %s\t" % (ex))
def findQuData(qu_data, cursor, today, conn, sup, flag):
qu = qu_data[qu_data.qws == 2] # 区
result = []
for i in list(set(qu['Issue_Region_Name'])):
# quName = org_df[org_df.Issue_Region_Code4 == i].iloc[0, 0]
dfQu = qu_data[qu_data.Issue_Region_Name == i]
listD = dealData(dfQu)
# 调用fpgrowth算法
# print u'调用fp-growth算法'
for itemset, support in fp.find_frequent_itemsets(listD, sup, True):
result.append((itemset, support, i))
dic = {}
for i in list(set(qu['Issue_Region_Name'])):
df = mapK(cursor, i, flag) # key 行政级别 value 问题类别list 用来插补数据 df
dic[i] = df
# 循环算法返回结果
dicA = {}
for itemset, support, area in result:
# 判断list长度，过滤不符合条件的集合
if (len(itemset) > 1):
# itemset[0] 问题类别code
# itemset[1] 行政级别code
dic[area] = dic[area][
(dic[area]['ISSUE_TYPE_CODE'] != itemset[0]) | (dic[area]['OBJECT_CLASS_CODE'] != itemset[1])]
dicA[area] = dic[area] # key 区名 value 区对应的需要插补的数据
codeFindName(cursor, itemset, today, conn, support, area, flag)
path = os.path.realpath(sys.argv[0])
createName = getConf("common", "createName", path)
createId = getConf("common", "createId", path)
for i in list(set(qu['Issue_Region_Name'])):
for k, v in dicA.items():
if i == k:
# dfchabu.index = range(len(dfchabu)) # 修改序号
for index, row in v.iterrows():
infoIss = 'select ISSUE_TYPE_NAME from DATAPREDICT_FPM_QL_INFO WHERE ISSUE_TYPE_CODE = ' + "'" + \
row['ISSUE_TYPE_CODE'] + "' and PETITION_DATE_FLAG = " + "'" + flag + "'"
logging.info(" q select DATAPREDICT_FPM_QL_INFO infoIss sql: %s\t" % (infoIss))
cursor.execute(infoIss)
infoIss = cursor.fetchall()
infoObj = 'select OBJECT_CLASS_NAME from DATAPREDICT_FPM_QL_INFO WHERE OBJECT_CLASS_CODE = ' + "'" + \
row['OBJECT_CLASS_CODE'] + "' and PETITION_DATE_FLAG = " + "'" + flag + "'"
logging.info(" q select DATAPREDICT_FPM_QL_INFO infoObj sql: %s\t" % (infoObj))
cursor.execute(infoObj)
infoObj = cursor.fetchall()
try:
sqlInsert = "insert into DATAPREDICT_FPM_QL_ALL_DATA_INFO(OID, ISSUE_TYPE_CODE, ISSUE_TYPE_NAME, OBJECT_CLASS_CODE, OBJECT_CLASS_NAME, SUPPORT, REGIN_NAME, REGIN_CODE, PETITION_DATE_FLAG,CREATE_DATE,CREATOR_NAME,CREATOR_ID,MODIFY_DATE,MODIFY_NAME,MODIFY_ID) VALUES ( '%s', '%s', '%s', '%s', '%s', %s,'%s','%s','%s', date('%s'), '%s', '%s', date('%s'), '%s', '%s')" % (
str(shortuuid.ShortUUID().random(length=20)), row['ISSUE_TYPE_CODE'], infoIss[0][0],
row['OBJECT_CLASS_CODE'], infoObj[0][0],
0, i, '', flag, today, createName, createId, today, '', '')
logging.info(" q insert DATAPREDICT_FPM_QL_ALL_DATA_INFO sql: %s\t" % (sqlInsert))
cursor.execute(sqlInsert)
except Exception as e:
logging.error("q insert DATAPREDICT_FPM_QL_ALL_DATA_INFO error : %s\t" % (e))
conn.rollback() # 回滚
def updateSettCT(cursor, CURR_ROUND):
logging.info("Start updateSettCT!")
try:
path = os.path.realpath(sys.argv[0])
modifyName = getConf("common", "modifyName", path)
modifyId = getConf("common", "modifyId", path)
data = str(time.strftime('%Y-%m-%d', time.localtime(time.time())))
sql = "update DATAPREDICT_OPINION_SETTING set CURR_STRIP = %s,MODIFY_DATE = '%s', MODIFY_NAME = '%s', MODIFY_ID = '%s';" % (
CURR_ROUND, data, modifyName, modifyId)
cursor.execute(sql)
except Exception as ex:
logging.error("updateSettCT Failed: %s\t" % (ex))
logging.info("End updateSettCT!")
def findCityData(petition_df, cursor, today, conn, sup, flag):
scity = petition_df[petition_df.qws == 1] # 市
listD = dealData(scity)
resultCity = []
# 调用fpgrowth算法
for itemset, support in fp.find_frequent_itemsets(listD, sup, True):
resultCity.append((itemset, support))
shi = u'上海市'
df = mapK(cursor, shi, flag) # key 行政级别 value 问题类别list 用来插补数据 df
dfchabu = pd.DataFrame()
# 循环算法输出结果存入数据库
for itemset, support in resultCity:
# 判断list长度，过滤不符合条件的集合
if (len(itemset) > 1):
# itemset[0] 问题类别code
# itemset[1] 行政级别code
df = df[(df['ISSUE_TYPE_CODE'] != itemset[0]) | (df['OBJECT_CLASS_CODE'] != itemset[1])]
dfchabu = df
# 比较dataframe是否含有相同的值，有则删除这行数据 返回一个新的dataframe
# dfNew = df.drop(int(str(df[(df['ISSUE_TYPE_CODE'] == itemset[0]) & (df['OBJECT_CLASS_CODE'] == itemset[1])].iat[0, 0])))
# 通过code查找name, 并插入数据库
codeFindName(cursor, itemset, today, conn, support, u'上海市', flag)
logging.info(" Number of data to be interpolated: %s\t" % (len(dfchabu)))
# dfchabu.index = range(len(dfchabu)) # 修改序号
path = os.path.realpath(sys.argv[0])
createName = getConf("common", "createName", path)
createId = getConf("common", "createId", path)
for index, row in dfchabu.iterrows():
infoIss = 'select ISSUE_TYPE_NAME from DATAPREDICT_FPM_QL_INFO WHERE ISSUE_TYPE_CODE = ' + "'" + row[
'ISSUE_TYPE_CODE'] + "' and PETITION_DATE_FLAG = " + "'" + flag + "'"
infoObj = 'select OBJECT_CLASS_NAME from DATAPREDICT_FPM_QL_INFO WHERE OBJECT_CLASS_CODE = ' + "'" + row[
'OBJECT_CLASS_CODE'] + "' and PETITION_DATE_FLAG = " + "'" + flag + "'"
logging.info(" find data infoIss: %s\t" % (infoIss))
logging.info(" find data infoObj: %s\t" % (infoObj))
cursor.execute(infoIss)
infoIss = cursor.fetchall()
cursor.execute(infoObj)
infoObj = cursor.fetchall()
try:
sqlInsert = "insert into DATAPREDICT_FPM_QL_ALL_DATA_INFO(OID, ISSUE_TYPE_CODE, ISSUE_TYPE_NAME, OBJECT_CLASS_CODE, OBJECT_CLASS_NAME, SUPPORT, REGIN_NAME, REGIN_CODE, PETITION_DATE_FLAG,CREATE_DATE,CREATOR_NAME,CREATOR_ID,MODIFY_DATE,MODIFY_NAME,MODIFY_ID) VALUES ( '%s', '%s', '%s', '%s', '%s', %s,'%s','%s','%s', date('%s'), '%s', '%s', date('%s'), '%s', '%s')" % (
str(shortuuid.ShortUUID().random(length=20)), row['ISSUE_TYPE_CODE'], infoIss[0][0],
row['OBJECT_CLASS_CODE'], infoObj[0][0],
0, u'上海市', '', flag, today, createName, createId, today, '', '')
logging.info(" s insert DATAPREDICT_FPM_QL_ALL_DATA_INFO sql: %s\t" % (sqlInsert))
cursor.execute(sqlInsert)
except Exception as e:
logging.error("s insert DATAPREDICT_FPM_QL_ALL_DATA_INFO error : %s\t" % (e))
conn.rollback() # 回滚
def updateSettDealFlag(cursor):
logging.info("Start updateSettDealFlag!")
try:
path = os.path.realpath(sys.argv[0])
modifyName = getConf("common", "modifyName", path)
modifyId = getConf("common", "modifyId", path)
data = str(time.strftime('%Y-%m-%d', time.localtime(time.time())))
sql = "update DATAPREDICT_OPINION_SETTING set deal_flag = 1,MODIFY_DATE = '%s', MODIFY_NAME = '%s', MODIFY_ID = '%s' where deal_flag != 1" % (
data, modifyName, modifyId)
print sql
cursor.execute(sql)
except Exception as ex:
logging.error("updateSettDealFlag Failed: %s\t" % (ex))
logging.info("End updateSettDealFlag!")
def updateOpiAnaly(cursor, dealUuid):
path = os.path.realpath(sys.argv[0])
topNum = getConfInt("opinion", "topNum", path)
try:
sql = "select * from (SELECT ROW_NUMBER() OVER(ORDER BY sum(WORD_FREQUENCY) DESC) AS ROWNUM, NEW_WORD, sum(WORD_FREQUENCY) counts, WORD_PART FROM DATAPREDICT_OPINION_WORD group by NEW_WORD,WORD_PART)where ROWNUM >= 1 and ROWNUM <= %s;" % (
topNum)
cursor.execute(sql)
rows = cursor.fetchall()
if rows != []:
# 有新词 则处理之前的词
if len(dealUuid) > 0:
try:
# 更新数据库 更新词状态为处理
updateDealFlag(cursor, dealUuid)
logging.info("updateDealFlag Success!")
except Exception as ex:
logging.error("save to userDict Failed : %s\t" % (ex))
# 删除数据库里的无用词典
deleteOtherWord(cursor)
# 数据库写入新词
petition_df = pd.DataFrame(rows)
petition_df.columns = [
'ROWNUM', 'NEW_WORD', 'WORD_FREQUENCY', 'WORD_PART'
]
# 创建人信息
# path = os.path.realpath(sys.argv[0])
createName = getConf("common", "createName", path)
createId = getConf("common", "createId", path)
for index, row in petition_df.iterrows(): # 获取每行的index、row
uuid = shortuuid.ShortUUID().random(length=20)
data = str(
time.strftime('%Y-%m-%d %H:%M:%S',
time.localtime(time.time())))
# time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time()))
sql= "INSERT INTO DATAPREDICT_OPINION_INFO (OID, NEW_WORD, WORD_FREQUENCY, WORD_PART, EDIT_WORD, DICTIONARY_FLAG, IGNORE_FLAG, DEAL_FLAG,WORD_USED_FLAG , CREATE_DATE, CREATOR_NAME, CREATOR_ID, MODIFY_DATE, MODIFY_NAME, MODIFY_ID,CODE_TYPE) " \
"VALUES ('%s', '%s', %s, '%s', '%s', '%s', '%s', '%s','%s', '%s', '%s', '%s', '%s', '%s', '%s','%s');" \
% (uuid,row['NEW_WORD'],row['WORD_FREQUENCY'],row['WORD_PART'],row['NEW_WORD'],'0','0','0','0',data,createName,createId,data,'','','WTXZ')
cursor.execute(sql)
logging.info("updateWord Success!")
except Exception as ex:
logging.error("updateOpiAnaly Failed: %s\t" % (ex))
def updateDealFlag(cursor, oids):
# 创建人信息
path = os.path.realpath(sys.argv[0])
modifyName = getConf("common", "modifyName", path)
modifyId = getConf("common", "modifyId", path)
data = str(time.strftime('%Y-%m-%d', time.localtime(time.time())))
try:
sql = "update DATAPREDICT_OPINION_INFO set DEAL_FLAG = '1' ,MODIFY_DATE = '%s', MODIFY_NAME = '%s', MODIFY_ID = '%s' where " % (
data, modifyName, modifyId)
for i in oids:
sql += "oid = '" + i + "' or "
sql = sql[0:-3]
# print sql
# sql = "SELECT STOP_WORD_DICT_URL, TEMPORARY_WORD_DICT_URL, NEW_DICT_NUM, MAX_DICT_NUM,CLASS_WORD_DICT_URL FROM DATAPREDICT_OPINION_DICT;"
# sql = "SELECT OID, NEW_WORD, WORD_FREQUENCY, WORD_PART, EDIT_WORD, DICTIONARY_FLAG, IGNORE_FLAG, DEAL_FLAG, WORD_USED_FLAG FROM DATAPREDICT_OPINION_INFO;"
cursor.execute(sql)
except Exception as ex:
logging.error("updateDealFlag Failed: %s\t" % (ex))
def codeFindName(cursor, itemset, today, conn, support, area, flag):
# 根据问题类别code,处理方式code查找信息
# print u'根据问题类别code,处理方式code查找信息'
info = 'select a.ISSUE_TYPE_NAME,b.DEAL_TYPE_NAME ,b.ISSUE_REGION_CODE from PETITION_ISSUE_INFO a,PETITION_BASIC_INFO b WHERE a.Region_Code = b.Region_Code and a.PETITION_NO = b.PETITION_NO and a.ISSUE_TYPE_CODE = ' + "'" + \
itemset[0] + "' and b.DEAL_TYPE_CODE =" + "'" + itemset[1] + "'"
cursor.execute(info)
info = cursor.fetchall()
path = os.path.realpath(sys.argv[0])
createName = getConf("common", "createName", path)
createId = getConf("common", "createId", path)
try:
sqlInsert = "insert into DATAPREDICT_FPM_QD_INFO(OID, ISSUE_TYPE_CODE, ISSUE_TYPE_NAME, DEAL_TYPE_CODE, DEAL_TYPE_NAME, SUPPORT, REGIN_NAME, REGIN_CODE, PETITION_DATE_FLAG,CREATE_DATE,CREATOR_NAME,CREATOR_ID,MODIFY_DATE,MODIFY_NAME,MODIFY_ID) VALUES ('%s', '%s', '%s', '%s', '%s', %s,'%s','%s','%s', date('%s'), '%s', '%s', date('%s'), '%s', '%s')" % (
str(shortuuid.ShortUUID().random(length=20)), str(itemset[0]), info[0][0], itemset[1], info[0][1],
support, area, info[0][2], flag, today, createName, createId, today, '', '')
logging.info(" codeFindName() sql: %s\t" % (sqlInsert))
cursor.execute(sqlInsert)
except Exception as e:
logging.error("insert DATAPREDICT_FPM_QD_INFO error : %s\t" % (e))
conn.rollback() # 回滚
def updateOpinionOldWord(cursor, newWords):
# 创建人信息
path = os.path.realpath(sys.argv[0])
createName = getConf("common", "createName", path)
createId = getConf("common", "createId", path)
try:
newWords = newWords[:-1] # 截取从头开始到倒数第三个字符之前
arr = newWords.split(",")
for ai in arr:
aiArr = ai.split(" ")
uuid = shortuuid.ShortUUID().random(length=20)
data = str(time.strftime('%Y-%m-%d', time.localtime(time.time())))
# time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time()))
sql = "INSERT INTO DATAPREDICT_OPINION_OLD_WORD (OID, NEW_WORD, WORD_FREQUENCY, WORD_PART,CREATE_DATE, CREATOR_NAME, CREATOR_ID, MODIFY_DATE, MODIFY_NAME, MODIFY_ID) " \
"VALUES ('%s', '%s', %s, '%s','%s', '%s', '%s','%s', '%s', '%s');" \
% (uuid, aiArr[0], aiArr[2], aiArr[1],data,createName,createId,data,'','')
# sql.encode('utf8')
cursor.execute(sql)
except Exception as ex:
logging.error("updateOpinionOldWord Failed : %s\t" % (ex))
def saveDealUuid(cursor, dealUuid, allRound, onceNum):
try:
logging.info("Start saveDealUuid!")
if (len(dealUuid) > 0):
dealUuidS = ','.join(dealUuid)
else:
dealUuidS = ''
# 创建人信息
path = os.path.realpath(sys.argv[0])
createName = getConf("common", "createName", path)
createId = getConf("common", "createId", path)
uuid = shortuuid.ShortUUID().random(length=20)
data = str(time.strftime('%Y-%m-%d', time.localtime(time.time())))
sql = "INSERT INTO DATAPREDICT_OPINION_SETTING (OID, ALL_ROUND,CURR_ROUND, CURR_STRIP,ONCE_STRIP,CREATE_DATE, CREATOR_NAME, CREATOR_ID, MODIFY_DATE, DEAL_UUID,SAVE_WORD_FLAG,DEAL_FLAG) VALUES ('%s', %s, %s,%s,%s,'%s', '%s', '%s', '%s', '%s',%s,%s) " % (
uuid, allRound, 0, 0, onceNum, data, createName, createId, data,
dealUuidS, 0, 0)
cursor.execute(sql)
logging.info("End saveDealUuid!")
except Exception as ex:
logging.error("saveDealUuid Failed : %s\t" % (ex))
def mainDeal(df, cursor, conn, today, flag, regionFlag):
lisAll = []
for index, row in df.iterrows():
try:
sqlSelect = 'select ISSUE_TYPE_NAME from ISSUE_TYPE_INFO where PETITION_BASIC_INFO_OID = ' + "'" + \
row[
'oid'] + "'"
cursor.execute(sqlSelect)
rows = cursor.fetchall()
pro = pd.DataFrame(rows)
lis = []
for i in pro.iloc[:, 0]:
lis.append(i)
lisAll.append(lis)
except Exception as e:
logging.error(" mainDeal() Exception: %s\t" % (e))
resultCity = []
for itemset, support in fp.find_frequent_itemsets(lisAll, 0, True):
resultCity.append((itemset, support))
df = mapK(cursor, regionFlag, flag) # key 行政级别 value 问题类别list 用来插补数据 df
path = os.path.realpath(sys.argv[0])
createName = getConf("common", "createName", path)
createId = getConf("common", "createId", path)
for itemset, support in resultCity:
# 判断list长度，过滤不符合条件的集合
if (len(itemset) == 2): # 统计问题关联关系
name1 = 'select ISSUE_TYPE_CODE from PETITION_ISSUE_INFO WHERE ISSUE_TYPE_NAME = ' + "'" + \
itemset[0] + "'"
cursor.execute(name1)
name1 = cursor.fetchall()
name2 = 'select ISSUE_TYPE_CODE from PETITION_ISSUE_INFO WHERE ISSUE_TYPE_NAME = ' + "'" + \
itemset[1] + "'"
cursor.execute(name2)
name2 = cursor.fetchall()
df = df[(df['iss1'] != itemset[0]) | (df['iss2'] != itemset[1])]
try:
sqlInsert = "insert into DATAPREDICT_TOP5_ALL_DATA_INFO(OID, ISSUE_TYPE_CODE_1, ISSUE_TYPE_NAME_1,ISSUE_TYPE_CODE_2,ISSUE_TYPE_NAME_2, SUPPORT,qws, PETITION_DATE_FLAG,CREATE_DATE,CREATOR_NAME,CREATOR_ID,MODIFY_DATE,MODIFY_NAME,MODIFY_ID) VALUES ( '%s', '%s', '%s', '%s', '%s', %s, '%s', '%s', date('%s'), '%s', '%s', date('%s'), '%s', '%s')" % (
str(shortuuid.ShortUUID().random(length=20)), name1[0][0],
str(itemset[0]), name2[0][0], str(
itemset[1]), support, regionFlag, flag, str(today),
createName, createId, str(today), '', '')
logging.info(" itemset 2 insert into sql: %s\t" % (sqlInsert))
cursor.execute(sqlInsert)
except Exception as e:
logging.error(" itemset 2 sql error : %s\t" % (e))
conn.rollback() # 回滚
if (len(itemset) == 1): # 统计问题top5 个数
name1 = 'select ISSUE_TYPE_CODE from PETITION_ISSUE_INFO WHERE ISSUE_TYPE_NAME = ' + "'" + \
itemset[0] + "'"
cursor.execute(name1)
name1 = cursor.fetchall()
try:
sqlInsert = "insert into DATAPREDICT_TOP5_COUNT_INFO(OID, ISSUE_TYPE_CODE, ISSUE_TYPE_NAME, SUPPORT,qws, PETITION_DATE_FLAG,CREATE_DATE,CREATOR_NAME,CREATOR_ID,MODIFY_DATE,MODIFY_NAME,MODIFY_ID) VALUES ( '%s', '%s', '%s', %s, '%s', '%s', date('%s'), '%s', '%s', date('%s'), '%s', '%s')" % (
str(shortuuid.ShortUUID().random(length=20)), name1[0][0],
str(itemset[0]), support, regionFlag, flag, str(today),
createName, createId, str(today), '', '')
logging.info(
"insert DATAPREDICT_TOP5_COUNT_INFO sql : %s\t" %
(sqlInsert))
cursor.execute(sqlInsert)
except Exception as e:
logging.error(" itemset 1 sql error : %s\t" % (e))
conn.rollback() # 回滚
for index, row in df.iterrows():
name1 = 'select ISSUE_TYPE_CODE from PETITION_ISSUE_INFO WHERE ISSUE_TYPE_NAME = ' + "'" + \
row['iss1'] + "'"
cursor.execute(name1)
name1 = cursor.fetchall()
name2 = 'select ISSUE_TYPE_CODE from PETITION_ISSUE_INFO WHERE ISSUE_TYPE_NAME = ' + "'" + \
row['iss2'] + "'"
cursor.execute(name2)
name2 = cursor.fetchall()
try:
sqlInsert = "insert into DATAPREDICT_TOP5_ALL_DATA_INFO(OID, ISSUE_TYPE_CODE_1, ISSUE_TYPE_NAME_1,ISSUE_TYPE_CODE_2,ISSUE_TYPE_NAME_2, SUPPORT,qws, PETITION_DATE_FLAG,CREATE_DATE,CREATOR_NAME,CREATOR_ID,MODIFY_DATE,MODIFY_NAME,MODIFY_ID) VALUES ( '%s', '%s', '%s', '%s', '%s', %s, '%s', '%s', date('%s'), '%s', '%s', date('%s'), '%s', '%s')" % (
str(shortuuid.ShortUUID().random(length=20)), name1[0][0],
row['iss1'], name2[0][0], row['iss2'], 0, regionFlag, flag,
str(today), createName, createId, str(today), '', '')
logging.info(
"insert DATAPREDICT_TOP5_ALL_DATA_INFO 2 sql : %s\t" %
(sqlInsert))
cursor.execute(sqlInsert)
except Exception as e:
logging.error(" itemset 2 buquan sql error : %s\t" % (e))
conn.rollback() # 回滚
def mainDeal(df, cursor, conn, today, flag, regionFlag):
lisAll = []
for index, row in df.iterrows():
try:
sqlSelect = 'select ISSUE_TYPE_NAME from ISSUE_TYPE_INFO where PETITION_BASIC_INFO_OID = ' + "'" + \
row[
'oid'] + "'"
logging.info("mainDeal sql : %s\t" % (sqlSelect))
cursor.execute(sqlSelect)
rows = cursor.fetchall()
pro = pd.DataFrame(rows)
lis = []
for i in pro.iloc[:, 0]:
lis.append(i)
lisAll.append(lis)
except Exception as e:
logging.error("mainDeal() Exception : %s\t" % (e))
resultCity = []
for itemset, support in fp.find_frequent_itemsets(lisAll, 0, True):
resultCity.append((itemset, support))
path = os.path.realpath(sys.argv[0])
createName = getConf("common", "createName", path)
createId = getConf("common", "createId", path)
for itemset, support in resultCity:
# 判断list长度，过滤不符合条件的集合
if (len(itemset) == 2): # 统计问题关联关系
name = 'select a.ISSUE_TYPE_CODE,b.ISSUE_TYPE_CODE from PETITION_ISSUE_INFO as a ,PETITION_ISSUE_INFO as b WHERE a.ISSUE_TYPE_NAME = ' + "'" + itemset[0] + "'"' and b.ISSUE_TYPE_NAME = ' + \
"'" + itemset[1] + "'"' fetch first 1 rows only'
logging.info(" itemset 2 sql: %s\t" % (name))
cursor.execute(name)
name = cursor.fetchall()
try:
sqlInsert = "insert into DATAPREDICT_TOP5_INFO(OID, ISSUE_TYPE_CODE_1, ISSUE_TYPE_NAME_1,ISSUE_TYPE_CODE_2,ISSUE_TYPE_NAME_2, SUPPORT,qws, PETITION_DATE_FLAG,CREATE_DATE,CREATOR_NAME,CREATOR_ID,MODIFY_DATE,MODIFY_NAME,MODIFY_ID) VALUES ( '%s', '%s', '%s', '%s', '%s', %s, '%s', '%s', date('%s'), '%s', '%s', date('%s'), '%s', '%s')" % (
str(shortuuid.ShortUUID().random(length=20)), name[0][0],
itemset[0], name[0][1], itemset[1], support, regionFlag,
flag, today, createName, createId, today, '', '')
logging.info("insert DATAPREDICT_TOP5_INFO 2 sql : %s\t" %
(sqlInsert))
cursor.execute(sqlInsert)
except Exception as e:
logging.error("insert DATAPREDICT_TOP5_INFO 2 error: %s\t" %
(e))
conn.rollback() # 回滚
if (len(itemset) == 1): # 统计问题top5 个数
name1 = 'select ISSUE_TYPE_CODE from PETITION_ISSUE_INFO WHERE ISSUE_TYPE_NAME = ' + "'" + \
itemset[0] + "'"
cursor.execute(name1)
name1 = cursor.fetchall()
try:
sqlInsert = "insert into DATAPREDICT_TOP5_INFO(OID, ISSUE_TYPE_CODE_1, ISSUE_TYPE_NAME_1,ISSUE_TYPE_CODE_2,ISSUE_TYPE_NAME_2, SUPPORT,qws, PETITION_DATE_FLAG,CREATE_DATE,CREATOR_NAME,CREATOR_ID,MODIFY_DATE,MODIFY_NAME,MODIFY_ID) VALUES ( '%s', '%s', '%s', '%s', '%s', %s, '%s', '%s', date('%s'), '%s', '%s', date('%s'), '%s', '%s')" % (
str(shortuuid.ShortUUID().random(length=20)), name1[0][0],
str(itemset[0]), '', "", support, regionFlag, flag,
str(today), createName, createId, str(today), '', '')
logging.info("insert DATAPREDICT_TOP5_INFO 1 sql : %s\t" %
(sqlInsert))
cursor.execute(sqlInsert)
except Exception as e:
logging.error("insert DATAPREDICT_TOP5_INFO 1 error: %s\t" %
(e))
conn.rollback() # 回滚
def mainQC(cursor, conn, modelCycle):
try:
start = time.clock()
path = os.path.realpath(sys.argv[0])
createName = getConf("common", "createName", path) # 获取创建人
createId = getConf("common", "createId", path) # 获取创建人ID
today = datetime.datetime.now().strftime('%Y-%m-%d') # 当前日期
cur = datetime.datetime.now()
startTimeM, endTimeM = monthOfMission(cur, today) # 一个月的时间
startTimeQ, endTimeQ = quarterlyOfMission(cur, today) # 季度的时间
# startTimeS, endTimeS = sixMonthsOfMission(cur, today) # 半年的时间
startTimeY, endTimeY = yearOfMission(cur, today) # 一年的时间
logging.info("startTimeM time : %s\t" % (startTimeM))
logging.info("endTimeM time : %s\t" % (endTimeM))
logging.info("startTimeQ time : %s\t" % (startTimeQ))
logging.info("endTimeQ time : %s\t" % (endTimeQ))
logging.info("startTimeY time : %s\t" % (startTimeY))
logging.info("endTimeY time : %s\t" % (endTimeY))
dfQWY = summaryQW(cursor, startTimeY, endTimeY) # 一年数据
dfQQ = summaryQW(cursor, startTimeQ, endTimeQ) # 季度数据
dfMM = summaryQW(cursor, startTimeM, endTimeM) # 一个月数据
org_df = selectOrganization(cursor) # 取出所有组织机构
if dfQWY.empty != True:
petition_df = pd.merge(org_df, dfQWY,
on=['Issue_Region_Code4']) # 合并 查区域名字
findQuData(petition_df, cursor, today, conn, "y", createName,
createId) # 查找区一年数据
findWeiData(petition_df, cursor, today, conn, "y", createName,
createId) # 查找派驻一年数据
if dfQQ.empty != True:
petition_df = pd.merge(org_df, dfQQ,
on=['Issue_Region_Code4']) # 合并 查区域名字
findQuData(petition_df, cursor, today, conn, "q", createName,
createId) # 查找区季度数据
findWeiData(petition_df, cursor, today, conn, "q", createName,
createId) # 查找派驻季度数据
if dfMM.empty != True:
petition_df = pd.merge(org_df, dfMM,
on=['Issue_Region_Code4']) # 合并 查区域名字
findQuData(petition_df, cursor, today, conn, "m", createName,
createId) # 查找区一个月数据
findWeiData(petition_df, cursor, today, conn, "m", createName,
createId) # 查找派驻一个月数据
# dfS = summaryS(cursor)
dfSY = summaryS(cursor, startTimeY, endTimeY) # 一年数据
dfSQ = summaryS(cursor, startTimeQ, endTimeQ) # 季度数据
dfSM = summaryS(cursor, startTimeM, endTimeM) # 一个月数据
if dfSY.empty != True:
findCityData(dfSY, cursor, today, conn, 'y', createName, createId)
if dfSQ.empty != True:
findCityData(dfSQ, cursor, today, conn, 'q', createName, createId)
if dfSM.empty != True:
findCityData(dfSM, cursor, today, conn, 'm', createName, createId)
# 该语句将清除表中所有数据，但由于这一操作会记日志，因此执行速度会相对慢一些，另外要注意的是，
# 如果表较大，为保证删除操作的成功，应考虑是否留有足够大的日志空间
yesTime = datetime.datetime.now() + datetime.timedelta(
days=-modelCycle)
yesTimeNyr = yesTime.strftime('%Y-%m-%d') # 格式化输出 前n天
sqlDelete = "delete from DATAPREDICT_QC_INFO where substr(CREATE_DATE,1,10)<='" + yesTimeNyr + "'" # 删表数据
logging.info(" delete table sql: %s\t" % (sqlDelete))
cursor.execute(sqlDelete) # 执行
end = time.clock() # 运行结束时间
logging.info(" 存储完毕:，用时： %s\t" % (end - start))
except Exception as e:
logging.error("Exception: %s\t" % (e)) # 打印异常
finally:
# cursor.close() # 关闭连接
pass