def loadProbabilityData(self, probability):
tableName = self.getTableName(probability)
print(tableName)
ishave = SqlHabdleGlobal.isHaveTable(tableName)
if (not ishave):
ishave = self.createTable(tableName)
if (ishave):
lastData = self.getLastData(tableName)
else:
print("创建频率表失败！")
return
if (lastData):
minData = self.getlastNotAnalyseFC3DData(lastData["outNO"],
probability)
else:
minData = self.getlastNotAnalyseFC3DData(0, probability)
connection = SqlHabdleGlobal.connectionDb()
with connection.cursor() as cursor:
while minData["lastData"] != None:
sql = 'call pr_insterOneProbabilityDataToTable(%s,%s,%s);'
cursor.execute(
sql, (str(probability), tableName, minData["lastData"]))
lastData = self.getLastData(tableName)
minData = self.getlastNotAnalyseFC3DData(
lastData["outNO"], probability)
print("计算频率：" + str(lastData["outNO"]) + tableName)
print("===>频率：%d 频率表：%s 更新完毕" % (probability, tableName))
def getRecommendMax(self, beginData, endData, probability, recommendOutNO,
maxCount):
probabilityTableName = self.getTableName(probability)
recommendTableName = self.getRecommendtableName(
beginData, endData, probability)
ishave = SqlHabdleGlobal.isHaveTable(recommendTableName)
if (not ishave):
ishave = self.createRecommendTable(recommendTableName)
if (ishave):
connection = SqlHabdleGlobal.connectionDb()
with connection.cursor() as cursor:
sql = "call pr_getRecommendData( " + str(
probability
) + ",'" + probabilityTableName + "' ,'" + recommendTableName + "'," + str(
recommendOutNO) + "," + str(beginData) + "," + str(
endData) + ");"
cursor.execute(sql)
tablerows = cursor.fetchall()
outData = self.getOutNOData(recommendOutNO)
if (len(tablerows) == 1):
teturnData = self.getRecommendCode(tablerows[-1], outData,
probability, maxCount)
return teturnData
else:
return None
else:
print("创建概率表失败！")
return
def reloadOmitData(self):
connection = SqlHabdleGlobal.connectionDb();
tableName = self.getOmitTableName()
# 查看表是否存在，不存在就创建
if(not SqlHabdleGlobal.isHaveTable(tableName)):
if(not self.createTable(tableName)):
print('创建表' + tableName + "失败")
return;
lastData = self.getLastOmitDatd(tableName)
if(lastData):
minData = self.getlastNotAnalyseFC3DData(lastData["outNO"])
else:
minData = self.getlastNotAnalyseFC3DData(0)
with connection.cursor() as cursor:
while minData["lastData"] != None:
sql = 'call pr_insterOneOmitDataToTable(%s,%s);'
cursor.execute(sql, (tableName, minData["lastData"]));
connection.commit()
lastData = self.getLastOmitDatd(tableName)
minData = self.getlastNotAnalyseFC3DData(lastData["outNO"])
print("计算遗漏值:" + str(lastData["outNO"]));
connection.close()
def getFC3DDataBalanceFatherType(self):
connection = SqlHabdleGlobal.connectionDb()
with connection.cursor() as cursor:
sql = " select * from FC3DDataBalance_t group by fatherType"
cursor.execute(sql)
tablerows = cursor.fetchall()
return tablerows
def isHaveBalanceData(self, data):
connection = SqlHabdleGlobal.connectionDb()
with connection.cursor() as cursor:
sql = 'select * from FC3DDataBalance_t WHERE fatherType=' + data[
"fatherType"] + ' and fatherCout=' + data["fatherCout"]
cursor.execute(sql)
tablerows = cursor.fetchall()
return len(tablerows) > 0
def getFrequencyData(self, outdate, probability):
endDate = "90000000"
beginDate = "00000000"
balancedata = self.getFC3DDataBalance()
balanceDic = {}
for key, value in enumerate(balancedata):
balanceKey = str(value["fatherType"]) + "_" + str(
value["fatherCout"])
balanceDic[balanceKey] = value["balance"]
probabilityTableName = self.getTableName(probability)
recommendTableName = self.getRecommendtableName(probability)
ishave = SqlHabdleGlobal.isHaveTable(recommendTableName)
if (not ishave):
ishave = self.createRecommendTable(recommendTableName)
if (ishave):
connection = SqlHabdleGlobal.connectionDb()
with connection.cursor() as cursor:
sql = "call pr_getRecommendData( " + str(
probability
) + ",'" + probabilityTableName + "' ,'" + recommendTableName + "'," + str(
outdate) + "," + str(beginDate) + "," + str(endDate) + ");"
cursor.execute(sql)
tablerows = cursor.fetchall()
if (len(tablerows) == 1):
result = tablerows[0]
result["outdate"] = str(result["outdate"])
for key, value in enumerate(result.keys()):
if (type(result[value]) != float): continue
blancekey = str(probability) + '_' + str(
(int)(result[value]))
if (blancekey not in balanceDic.keys()):
result["balance" + value] = 0.0
else:
result["balance" + value] = balanceDic[blancekey]
return result
else:
return None
else:
print("创建概率表失败！")
return
def createRecommendTable(self, tableName):
connection = SqlHabdleGlobal.connectionDb()
with connection.cursor() as cursor:
sql = 'call p_creatFC3DRecommendTable( "' + tableName + '" );'
cursor.execute(sql)
connection.commit()
connection.close()
return True
return False
def getLastData(self, tableName):
connection = SqlHabdleGlobal.connectionDb()
with connection.cursor() as cursor:
sql = 'select* from ' + tableName + ' order by outNO desc limit 0,1;'
cursor.execute(sql)
tablerows = cursor.fetchall()
if (len(tablerows) == 1):
return tablerows[-1]
else:
return None
return None
def getOutNOData(self, outNO):
connection = SqlHabdleGlobal.connectionDb()
with connection.cursor() as cursor:
sql = 'select * from FC3DData_t where outNO=' + str(outNO) + ';'
cursor.execute(sql)
tablerows = cursor.fetchall()
if (len(tablerows) == 1):
return tablerows[-1]
else:
return None
return None
'''
def replaceFC3DDataBalance(self, data):
connection = SqlHabdleGlobal.connectionDb()
with connection.cursor() as cursor:
sql = ""
if (self.isHaveBalanceData(data)):
sql = 'UPDATE FC3DDataBalance_t set balance =' + data[
"balance"] + ' WHERE fatherType=' + data[
"fatherType"] + ' and fatherCout=' + data["fatherCout"]
else:
sql = 'INSERT FC3DDataBalance_t values (' + data[
"fatherType"] + ',' + data["fatherCout"] + ',' + data[
"balance"] + ');'
cursor.execute(sql)
tablerows = cursor.rowcount
return tablerows
def getlastNotAnalyseFC3DData(self, lastOutON):
connection = SqlHabdleGlobal.connectionDb();
with connection.cursor() as cursor:
if(lastOutON != 0):
sql = 'select min(outNO) as lastData from FC3DData_t where outNO>' + str(lastOutON) + ';'
else:
sql = 'select outNO as lastData from FC3DData_t order by outNO limit 0,1;';
cursor.execute(sql)
tablerows = cursor.fetchall()
if(len(tablerows) == 1):
return tablerows[-1];
else:
return None;
return None
def getRecommendData(self, beginData, endData, probability, outtype):
connection = SqlHabdleGlobal.connectionDb()
with connection.cursor() as cursor:
tableName = self.getTableName(probability)
sql = 'select ' + outtype + ' ,count(*) as count from ' + tableName + ' where outdate>%s and outdate<%s group by ' + outtype + ';'
cursor.execute(sql, (beginData, endData))
tablerows = cursor.fetchall()
if (len(tablerows) > 0):
returnDic = {}
for row in tablerows:
returnDic[row[outtype]] = row['count']
return returnDic
else:
return None
return None
'''