def getPridictionMatch(session, matchId):
'''输入比赛ID，获取主客队的预测数据，根据分别的主客场前五场平均数据作为预测输入数据'''
result = {'host': [], 'guest': []}
matchInfoSql = "SELECT *,FROM_UNIXTIME(match_time) from nba_match_list_{session} where match_id={matchId} limit 1".format(
session=session, matchId=matchId)
matchInfo = db.find(matchInfoSql)
if len(matchInfo) <= 0:
print('该比赛不存在')
exit()
hostPridictionSql = "select CAST(sum(FG)/5 as SIGNED) as FG,CAST(sum(3P)/5 as SIGNED) AS 3P,CAST(sum(FT)/5 as SIGNED) as FT,ceil(sum(`FG%`)/5) as 'FG%',ceil(sum(`3P%`)/5) as '3P%' from (select *,FROM_UNIXTIME(match_time) from nba_match_data_{session} where team_id={team_id} limit 5) as a;".format(
session=session, team_id=matchInfo[0]['host_id'])
hostPridictionData = db.find(hostPridictionSql)
guestPridictionSql = "select CAST(sum(FG)/5 as SIGNED) as FG,CAST(sum(3P)/5 as SIGNED) AS 3P,CAST(sum(FT)/5 as SIGNED) as FT,ceil(sum(`FG%`)/5) as 'FG%',ceil(sum(`3P%`)/5) as '3P%' from (select *,FROM_UNIXTIME(match_time) from nba_match_data_{session} where team_id={team_id} limit 5) as a;".format(
session=session, team_id=matchInfo[0]['guest_id'])
guestPridictionData = db.find(guestPridictionSql)
result['host'].append(1)
result['host'].append(hostPridictionData[0]['FG'])
result['host'].append(hostPridictionData[0]['3P'])
# result['host'].append(hostPridictionData[0]['FT'])
result['host'].append(hostPridictionData[0]['FG%'])
result['host'].append(hostPridictionData[0]['3P%'])
result['guest'].append(0)
result['guest'].append(guestPridictionData[0]['FG'])
result['guest'].append(guestPridictionData[0]['3P'])
# result['guest'].append(guestPridictionData[0]['FT'])
result['guest'].append(guestPridictionData[0]['FG%'])
result['guest'].append(guestPridictionData[0]['3P%'])
return result
def push():
sql = "SELECT * from ds_push where is_push=0";
pushData = db.find(sql)
mailTitle = '334规则'
mailContent = ''
pushIds = []
for i in range(0, len(pushData)):
tmpContent = pushData[i]['league_name'] + "\r\n<br>" + pushData[i]['host_name'] + "（" + str(pushData[i]['host_goal_num']) + "）：（" + str(pushData[i]['guest_goal_num']) + "）" + pushData[i]['guest_name'] + "\r\n<br>"
mailContent = tmpContent + mailContent
pushIds.append(str(pushData[i]['match_id']))
if pushIds:
updateSql = "UPDATE ds_push SET is_push=1 WHERE match_id in (" + str(','.join(pushIds)) + ")"
db.update(updateSql)
#历史记录
historySql = "SELECT * FROM ds_push order by id desc limit 20"
historyData = db.find(historySql)
historyContent = "\r\n<br>\r\n<br>历史记录\r\n<br>"
if historyData:
for k in range(0, len(historyData)):
status = ''
result = historyData[k]['result']
if str(result) == '0':
status = '<span style="color:#FFB848">未结算</span>'
elif str(result) == '1':
status = '<span style="color:red">红</span>'
elif str(result) == '-1':
status = '<span style="color:black">黑</span>'
else:
status = '未知错误'
tmp = " " + historyData[k]['league_name'] + "\r\n<br>(" + status + ")---" + historyData[k]['host_name'] + historyData[k]['guest_name'] + "\r\n<br>"
historyContent = historyContent + tmp
#历史rate
winNumSql = "SELECT count(*) as win from ds_push where result=1"
loseNumSql= "SELECT count(*) as lose from ds_push where result=-1"
win = db.find(winNumSql)
lose = db.find(loseNumSql)
winNum = int(win[0]['win'])
loseNum = int(lose[0]['lose'])
rate = round(100*winNum/(winNum+loseNum), 2)
mailContent += str(rate) + "%<br>"
mailContent += historyContent
mail(mailTitle, mailContent)
def _index():
if not request.values.get("song"):
return redirect("/?song=0")
page = int(request.values.get("song"))
if request.method == "POST":
song = request.files.getlist('song[]')
for song in song:
name = song.filename
if not name.endswith(".mp3"):
return "Only MP3 files are supported."
name = uuid.uuid4().hex+".mp3"
with open("static/songs/"+name, 'wb') as file:
file.write(song.read())
obj = Reader("static/songs/"+name)
title = obj.getValue("title")
if not title:
title = song.filename.split(".mp3")[0]
artist = obj.getValue("performer")
if not artist:
artist = "Unknown"
year = obj.getValue("year")
url = "../static/songs/"+name
if not db.find("songs", {"title":title}):
db.insert("songs", {"title":title, "artist":artist, "year":year, "url":url})
else:
os.remove("static/songs/"+name)
songs = db.find("songs", "all")
playlists = db.find("playlists", "all")
if songs:
try:
first = songs[page]
except IndexError:
return redirect("/")
else:
first = []
if not playlists:
playlists = []
if not songs:
songs = []
if page > 0:
autoplay = {"autoplay":"autoplay", "play":"pause"}
else:
autoplay = {"autoplay":"", "play":"play"}
return render_template("index.html", autoplay=autoplay, page=page, first=first, songs=songs, playlists=playlists)
def insertData(matchId, leagueName, hostName, guestName, hostGoalNum, guestGoalNum):
sql = "SELECT count(*) as num FROM ds_push WHERE match_id={matchId}".format(matchId = matchId)
data = db.find(sql)
num = data[0]['num']
if num == 0:
keys = ['match_id', 'league_name', 'host_name', 'guest_name', 'host_goal_num', 'guest_goal_num', 'created_at'];
values = [[matchId, leagueName, hostName, guestName, hostGoalNum, guestGoalNum, currentTime]];
db.insertBatch('ds_push', keys, values)
def getInitAloRank(twoTeam):
initAloRank = {'host': defaultAloRank, 'guest': defaultAloRank}
sql = "SELECT alo_rank FROM {session} WHERE team_id={team} AND PTS>0 AND alo_rank>0 ORDER BY ID DESC LIMIT 1".\
format(session = matchDataSession, team = twoTeam['host']['team_id'])
data = db.find(sql)
if len(data) > 0:
initAloRank['host'] = data[0]['alo_rank']
sql = "SELECT alo_rank FROM {session} WHERE team_id={team} AND PTS>0 AND alo_rank>0 ORDER BY ID DESC LIMIT 1".\
format(session = matchDataSession, team = twoTeam['guest']['team_id'])
data = db.find(sql)
if len(data) > 0:
initAloRank['guest'] = data[0]['alo_rank']
return initAloRank
def searchWinPercentage():
# WHERE match_time>UNIX_TIMESTAMP('2014-3-1') and match_time<UNIX_TIMESTAMP('2014-4-1')
sqlMatchList = "SELECT * FROM {session}".format(session=matchDataSession)
matchList = db.find(sqlMatchList)
matchNum = 0
winNum = 0
for k in range(0, len(matchList)):
matchId = matchList[k]['match_id']
sql = "SELECT * FROM {session} where match_id={match} and is_host=1 and alo_win_percentage>60.00 limit 1".format(
session=matchDataSession, match=matchId)
data = db.find(sql)
if (len(data) > 0):
matchNum += 1
alo_rank_before = data[0]['alo_rank_before']
alo_rank = data[0]['alo_rank']
alo_win_percentage = data[0]['alo_win_percentage']
is_host = data[0]['is_host']
if alo_rank > alo_rank_before:
winNum += 1
print(winNum)
print(matchNum)
print(round(100 * winNum / matchNum, 2))
def updateResult(resultString):
beforeTime = int(time.time()) - 86400*2
needUpdateSql = "SELECT match_id FROM ds_push WHERE result=0 and created_at>{beforeTime}".format(beforeTime = beforeTime)
needUpdateData= db.find(needUpdateSql)
if len(needUpdateData) <= 0:
return
resultJson = json.loads(resultString)
matchList = resultJson['rs']
for i in range(0, len(matchList)):
matchData = matchList[i]
#比赛状态，int为分钟，’半‘表示半场结束，’全‘表示全场结束
matchStatus = str(matchData['status'])
if matchStatus == '-1':
continue
events = matchData['events_graph']['events']
matchId = matchData['id']
tmp = False
for k in range(0, len(needUpdateData)):
if str(matchId) == str(needUpdateData[k]['match_id']):
tmp = True
if tmp == False:
continue
num = 0;
if events:
for j in range(0, len(events)):
t = events[j]['t']
status = events[j]['status']
if (status < '30') and ((t == 'gg') or (t == 'hg')):
num += 1
elif (status >= '30') and ((t == 'gg') or (t == 'hg') or (t == 'gp') or (t == 'hp')):
num += 1
if num >= 4:
updateResult = 1
updateSql = "UPDATE ds_push SET result={updateResult} WHERE match_id={matchId}".format(updateResult = updateResult, matchId = matchId)
db.update(updateSql)
continue
if matchStatus == '半' or matchStatus == '全' or matchStatus > '45':
updateResult = -1
updateSql = "UPDATE ds_push SET result={updateResult} WHERE match_id={matchId}".format(updateResult = updateResult, matchId = matchId)
db.update(updateSql)
continue
def get_data(teamId):
match_result = []
sql = "select *,FROM_UNIXTIME(match_time) from nba_match_list_15_16 where host_id=" + str(
teamId) + " or guest_id=" + str(teamId)
data = db.find(sql)
for value in data:
if ((value['host_id'] == teamId) &
(value['host_score'] > value['guest_score'])):
match_result.append(1)
elif ((value['guest_id'] == teamId) &
(value['guest_score'] > value['host_score'])):
match_result.append(1)
else:
match_result.append(-1)
return match_result
def getData():
'''获取一场已经比赛完了，但还没有计算埃罗积分的比赛'''
sql = "SELECT * FROM {session} WHERE PTS>0 and alo_rank=0 limit 2".format(session = matchDataSession)
data = db.find(sql)
if len(data) <= 0:
print("没有比赛啦")
exit()
twoTeam = {}
for i in range(0, 2):
if(data[i]['is_host'] == 1):
twoTeam['host'] = data[i]
else:
twoTeam['guest'] = data[i]
if(twoTeam['host']['match_id'] != twoTeam['guest']['match_id']):
print("数据错误，这俩队不是一场比赛的", twoTeam)
return twoTeam
def getXYdata():
Xdata = []
Ydata = []
gameSession = ['13_14', '14_15', '15_16', '16_17']
for i in range(0, len(gameSession)):
sql = "SELECT * FROM nba_match_data_{session} limit 1300".format(
session=gameSession[i])
data = db.find(sql)
if len(data) > 0:
for j in range(0, len(data)):
Xtemp = []
Ytemp = []
Xtemp = [
data[j]['is_host'],
data[j]['FG'],
# data[j]['FGA'],
data[j]['3P'],
# data[j]['3PA'],
# data[j]['FT'],
# data[j]['FTA'],
# data[j]['ORB'],
# data[j]['DRB'],
# data[j]['TRB'],
# data[j]['AST'],
# data[j]['STL'],
# data[j]['BLK'],
data[j]['FG%'],
data[j]['3P%']
]
Xdata.append(Xtemp)
Ytemp = [data[j]['PTS']]
Ydata.append(Ytemp)
result = {'x': Xdata, 'y': Ydata}
return result
def getTeamData(matchId):
sql = "SELECT * FROM {table} where match_id={id} AND alo_win_percentage!=50".format(
table=matchDataSession, id=matchId)
data = db.find(sql)
return data
def getMatchList():
sql = "SELECT * FROM {table}".format(table=matchListSession)
data = db.find(sql)
return data
import sys
sys.path.append("..")
import db.db as db
import matplotlib.pyplot as plt
db = db.Db()
sql = "select * from nba_team where tid=7 limit 10"
teamList = db.find(sql)
def get_data(teamId):
match_result = []
sql = "select *,FROM_UNIXTIME(match_time) from nba_match_list_15_16 where host_id=" + str(
teamId) + " or guest_id=" + str(teamId)
data = db.find(sql)
for value in data:
if ((value['host_id'] == teamId) &
(value['host_score'] > value['guest_score'])):
match_result.append(1)
elif ((value['guest_id'] == teamId) &
(value['guest_score'] > value['host_score'])):
match_result.append(1)
else:
match_result.append(-1)
return match_result
def getDayMatch(begin, end):
sql = "SELECT * FROM nba_match_list_{0} WHERE match_time between UNIX_TIMESTAMP('{1}') and UNIX_TIMESTAMP('{2}')".format(
pridiction_session, begin, end)
matchList = db.find(sql)
return matchList
def get_project(name):
"""Get a Project Node based on it's name"""
p = db.find("project", property_key="name", property_value=name)
return _first(p)
def getData():
global date
sql = "SELECT * FROM {tableName} WHERE match_time>={date} and match_time<{endDate} order by id desc".format(
tableName=global_table_name, date=date, endDate=endDate)
data = db.find(sql)
return data
def pridictMatch(matchBeginDay, matchEndDay):
sqlMatchList = "SELECT * FROM nba_match_list_{0} WHERE match_time between UNIX_TIMESTAMP('{1}') and UNIX_TIMESTAMP('{2}')".format(
session, matchBeginDay, matchEndDay)
matchList = db.find(sqlMatchList)
if len(matchList) <= 0:
print('所选日期没有比赛')
exit()
for i in range(0, len(matchList)):
matchId = matchList[i]['match_id']
hostId = matchList[i]['host_id']
hostName = matchList[i]['host_name']
guestId = matchList[i]['guest_id']
guestName = matchList[i]['guest_name']
sql = "SELECT * FROM {session} WHERE match_id={match_id}".format(
session=matchDataSession, match_id=matchId)
data = db.find(sql)
if len(data) > 0:
result = ''
if data[0]['alo_win_percentage'] > 50 and data[0][
'alo_rank'] > data[0]['alo_rank_before']:
result = "命中"
elif data[0]['alo_win_percentage'] < 50 and data[0][
'alo_rank'] < data[0]['alo_rank_before']:
result = "命中"
else:
result = "失败"
print(
"已赛 ----------------------------------------------------------------------------------{0}"
.format(result))
print("{0} 赛前alo积分{1} alo积分{2} 比分{3} 预测胜率{4} 预测欧赔{5} 实际欧赔{6}\
".format(data[0]['team_name'], data[0]['alo_rank_before'],
data[0]['alo_rank'], data[0]['PTS'],
data[0]['alo_win_percentage'],
round(90 / data[0]['alo_win_percentage'],
2), matchList[i]['host_odd']))
print("{0} 赛前alo积分{1} alo埃罗积分{2} 比分{3} 预测胜率{4} 预测欧赔{5} 实际欧赔{6}\
".format(data[1]['team_name'], data[1]['alo_rank_before'],
data[1]['alo_rank'], data[1]['PTS'],
data[1]['alo_win_percentage'],
round(90 / data[1]['alo_win_percentage'],
2), matchList[i]['guest_odd']))
print("")
else:
print("未赛 {0}".format(
time.strftime("%Y-%m-%d %H-%I",
time.localtime(matchList[i]['match_time']))))
twoTeam = {'host': {}, 'guest': {}}
twoTeam['host']['team_id'] = hostId
twoTeam['host']['PTS'] = 0
twoTeam['guest']['team_id'] = guestId
twoTeam['guest']['PTS'] = 0
#获取两支队伍之前的积分情况
beforeAloRank = getInitAloRank(twoTeam)
#计算两支队的胜率和最终积分
calResult = calAloWinPercentage(twoTeam, beforeAloRank)
print("{0} 预测胜率{1}% 预测欧赔{2}% 实际欧赔{3}".format(
hostName, round(calResult['host']['alo_win_percentage'], 2),
round(90 / round(calResult['host']['alo_win_percentage'], 2),
2), matchList[i]['host_odd']))
print("{0} 预测胜率{1}% 预测欧赔{2}% 实际欧赔{3}".format(
guestName, round(calResult['guest']['alo_win_percentage'], 2),
round(90 / round(calResult['guest']['alo_win_percentage'], 2),
2), matchList[i]['guest_odd']))
print("")
def getData(page, limit=1000):
m = (page - 1) * limit
sql = "SELECT * from ds_finished_match limit " + str(m) + "," + str(limit)
data = db.find(sql)
return data
def get_user(username):
"""Get a User Node based on a username."""
u = db.find("user", property_key="username", property_value=username)
return _first(u)