def getSectorDayAnalysisList(id_list):
'''
查询sectorpredict表
返回list中的板块对应的kline数据，如果list为空，则返回所有数据
返回所有版块的kline数据
'''
# db
conn = DBUtil.getConnection()
cursor = conn.cursor()
sql_query = "SELECT sectorID, sectorName FROM sectorinfo WHERE sectorID=%s;"
# 查找 list(tuple(id, name))
sectorDayAnalysisTupleList = []
sectorAnalysisList = []
if 0 == len(id_list):
sql_query = sql_query[:sql_query.index("WHERE")]
cursor.execute(sql_query)
sectorDayAnalysisTupleList = cursor.fetchall()
else:
sectorDayAnalysisTupleList = DBUtil.query_many(sql_query, id_list)
# 根据 list(tuple(id, name)) 查询k线数据
for sectorID, sectorName in sectorDayAnalysisTupleList:
sql_query = "SELECT * from sectorpredictioninfo WHERE sectorID=%s"
res_number = cursor.execute(sql_query, sectorID)
if 0 != res_number:
sectorData = cursor.fetchone()
sectorAnalysis = SectorAnalysis()
sectorAnalysis.sectorID = sectorData[0]
sectorAnalysis.sectorName = sectorName
sectorAnalysis.recordTime = sectorData[1].strip().split(",")
sectorAnalysis.lastTrade = [
round(float(x), 2) for x in sectorData[2].strip().split(",")
if x != ""
]
sectorAnalysis.changeAmount = [
round(float(x)) for x in sectorData[3].strip().split(",")
if x != ""
]
sectorAnalysis.changeRate = [
round(float(x)) for x in sectorData[4].strip().split(",")
if x != ""
]
sectorAnalysis.totalCapit = [
round(float(x)) for x in sectorData[5].strip().split(",")
if x != ""
]
sectorAnalysis.turnoverRate = [
round(float(x)) for x in sectorData[6].strip().split(",")
if x != ""
]
sectorAnalysisList.append(sectorAnalysis)
cursor.close()
conn.close()
return sectorAnalysisList
def saveAllPredictData():
sectorIDList = SectorAnalysisDao.getAllSectorID()
predictData = []
for sectorID in sectorIDList:
model = getModel(sectorID)
_, _, _, _, data = get_train_test_data(sectorID, rate=1)
lastData = data[-14:]
lastData = lastData / 1000
res = []
for i in range(7):
temp = model(
torch.tensor(lastData.reshape(-1, 14, 1)).float().to(device))
for i in range(1, len(lastData) - 1):
lastData[i] = lastData[i + 1]
lastData[-1] = temp
res.append(temp)
res = [round(item.cpu().data.numpy()[0][0] * 1000, 2) for item in res]
predictData.append(res)
conn = DBUtil.getConnection()
cursor = conn.cursor()
sql_update = "UPDATE predictresult set data=%s WHERE sectorID=%s"
for index in range(len(predictData)):
cursor.execute(
sql_update,
(",".join([str(i)
for i in predictData[index]]), sectorIDList[index]))
conn.commit()
cursor.close()
conn.close()
def getSectorDayAnalysis(sectorID: str, sectorName: str):
'''
根据id获取某一天的kline数据
'''
conn = DBUtil.getConnection()
cursor = conn.cursor()
sql_query = "SELECT * from sectorpredictioninfo WHERE sectorID=%s"
res_number = cursor.execute(sql_query, sectorID)
if 0 != res_number:
sectorData = cursor.fetchone()
sector = SectorAnalysis()
sector.sectorID = sectorData[0]
sector.sectorName = sectorName
sector.recordTime = sectorData[1].strip().split(",")
sector.lastTrade = [float(x) for x in sectorData[2].strip().split(",")]
sector.changeAmount = [float(x) for x in sectorData[3].strip().split(",")]
sector.changeRate = [float(x) for x in sectorData[4].strip().split(",")]
sector.totalCapit = [float(x) for x in sectorData[5].strip().split(",")]
sector.turnoverRate = [float(x) for x in sectorData[6].strip().split(",")]
cursor.close()
conn.close()
return sector
def getAllSectorID():
conn = DBUtil.getConnection()
cursor = conn.cursor()
sql_query = "SELECT sectorID FROM sectorinfo;"
cursor.execute(sql_query)
idList = cursor.fetchall()
idList = [item[0] for item in idList]
return idList
def getPredictData(sectorID: str):
'''用模型计算预测数据'''
sql_query = "select data from predictresult where sectorID=%s;"
conn = DBUtil.getConnection()
cursor = conn.cursor()
amount = cursor.execute(sql_query, sectorID)
if amount != 0:
res = cursor.fetchone()
return [float(item) for item in res[0].split(",")]
def getSectorInfoListByIDList(id_list):
'''
查询sectorinfo表
查询idlist中对应的版块的最新信息，如果list为空，那么返回所有版块的信息
'''
sql_query = "SELECT sectorID, sectorName, sectorType.sectorTypeName, recordTime, lastTrade, changeAmount, " \
"changeRate, totalCapit, turnoverRate, riseNumber, fallNumber " \
"FROM sectorinfo JOIN sectorType " \
"ON sectorinfo.sectorType=sectorType.sectorTypeID " \
"WHERE sectorinfo.sectorID=%s;"
conn = DBUtil.getConnection()
cursor = conn.cursor()
sectorInfoTupleList = []
sectorInfoList = []
if 0 == len(id_list):
sql_query = sql_query[:sql_query.index("WHERE")] + ";"
amount = cursor.execute(sql_query)
sectorInfoTupleList = cursor.fetchall()
else:
amount = cursor.executemany(sql_query, id_list)
sectorInfoTupleList = DBUtil.query_many(sql_query, id_list)
for item in sectorInfoTupleList:
sectorInfo = SectorInfo()
sectorInfo.sectorID = item[0]
sectorInfo.sectorName = item[1]
sectorInfo.sectorType = item[2]
sectorInfo.recordTime = str(item[3])
sectorInfo.lastTrade = item[4]
sectorInfo.changeAmount = item[5]
sectorInfo.changeRate = item[6]
sectorInfo.totalCapit = item[7]
sectorInfo.turnoverRate = item[8]
sectorInfo.riseNumber = item[9]
sectorInfo.fallNumber = item[10]
sectorInfoList.append(sectorInfo)
cursor.close()
conn.close()
return sectorInfoList
def saveSectorDayDataList(sectorInfo, start='20190101', end='20220101'):
'''给定一个(id, name)的元素的数组，查找对应的kline数据并保存到数据库'''
kline_day = "http://push2his.eastmoney.com/api/qt/stock/kline/get?cb=jQuery17207924994156058143_1585709402318&secid=90.{}&ut=fa5fd1943c7b386f172d6893dbfba10b&fields1=f1%2Cf2%2Cf3%2Cf4%2Cf5&fields2=f51%2Cf52%2Cf53%2Cf54%2Cf55%2Cf56%2Cf57%2Cf58&klt=101&fqt=0&beg={}&end={}&_=1585709782237"
for sectorID, sectorName in sectorInfo:
url = kline_day.format(sectorID, start, end)
response = requests.get(url)
raw_data = response.text
raw_data = raw_data[raw_data.index("(")+1: raw_data.index(")")]
data_json = json.loads(raw_data)
kline_data = data_json["data"]['klines']
sector_day_record = np.array([day.strip().split(",") for day in kline_data])
recordTime = sector_day_record[:, 0] # 记录时间
lastTrade = sector_day_record[:, 1] # 最新价变动
tradePrice = sector_day_record[:, 5] # 成交额
# 计算涨跌幅和涨跌额
closePrice = np.array(sector_day_record[:, 1], dtype=float) # 收盘价
changeAmount, changeRate = generateChangeRate_Amount(closePrice)
# 将数组转化为字符串
recordTimeStr = ",".join([str(i) for i in recordTime])
lastTradeStr = ",".join([str(i) for i in lastTrade])
changeAmountStr = ",".join([str(i) for i in changeAmount])
changeRateStr = ",".join([str(i) for i in changeRate])
# print(recordTimeStr)
# 入库
conn = DBUtil.getConnection()
cursor = conn.cursor()
# 检查是否已经存在
sql_query = "select * from sectorpredictioninfo where sectorID=%s"
changelines = cursor.execute(sql_query, sectorID)
if changelines != 0:
# 存在就更新
sql_update = "UPDATE sectorpredictioninfo SET recordTime=%s, lastTrade=%s, " \
"changeAmount=%s, changeRate=%s, totalCapit=%s, turnoverRate=%s WHERE sectorID=%s"
cursor.execute(sql_update, (recordTimeStr, lastTradeStr, changeAmountStr, changeRateStr, "", "", sectorID))
else:
# 不存在就插入
sql_insert = "INSERT INTO sectorpredictioninfo " \
"(sectorID, recordTime, lastTrade, changeAmount, changeRate, totalCapit, turnoverRate) " \
"VALUES (%s, %s, %s, %s, %s, %s, %s);"
cursor.execute(sql_insert, (sectorID, recordTimeStr, lastTradeStr, changeAmountStr, changeRateStr, "", ""))
conn.commit()
cursor.close()
conn.close()
def getUserInfo(username: str):
sql_query = "select username , password from user where username=%s;"
conn = DBUtil.getConnection()
cursor = conn.cursor()
amount = cursor.execute(sql_query, username)
if amount == 0:
return None
userInfo = cursor.fetchone()
cursor.close()
conn.close()
return userInfo
def saveUserInfo(username: str, password: str, email: str):
sql_query = "select username from user where username=%s;"
conn = DBUtil.getConnection()
cursor = conn.cursor()
amount = cursor.execute(sql_query, username)
if amount != 0:
return False
sql_insert = "INSERT INTO user(username, password, email) VALUES(%s, %s, %s);"
cursor.execute(sql_insert, (username, password, email))
conn.commit()
cursor.close()
conn.close()
return True
def updateSectorData():
'''
从sectorinfo表中读取所有版块的id和name，然后爬取他们的kline数据
:return: NULL
'''
conn = DBUtil.getConnection()
cursor = conn.cursor()
sql_query = "SELECT sectorID, sectorName FROM sectorinfo"
amount = cursor.execute(sql_query)
if amount == 0:
cursor.close()
conn.close()
return
sectorList = cursor.fetchall()
saveSectorDayDataList(sectorList, start="20200101", end="22000101")
saveSectorMonthDataList(sectorList, start="20190101", end="22000101")
saveSectorWeekDataList(sectorList, start="20190101", end="22000101")
cursor.close()
conn.close()