def create():
"""新增一筆field資料
"""
if request.method == 'POST':
conn = mysql.get_db()
# 實際寫入一筆
sql = u"INSERT INTO `match` (`field_id`, `m_hometeam`, `m_awayteam`, `m_date`) VALUES (%s, %s, %s, %s)"
print(request.form.to_dict())
post = request.form.to_dict()
status = 'status' in request.form
with conn.cursor() as cursor:
conn.begin()
cursor.execute(sql, (
post['field_id'],
post['m_hometeam'],
post['m_awayteam'],
post['m_date'],
))
match_id = cursor.lastrowid
conn.commit()
return redirect(url_for('match.view', match_id=match_id))
else:
conn = mysql.get_db()
sql = u"SELECT * FROM `field`"
with conn.cursor() as cursor:
cursor.execute(sql)
fields = cursor.fetchall()
sql = u"SELECT * FROM team"
with conn.cursor() as cursor:
cursor.execute(sql)
teams = cursor.fetchall()
return render_template('match/create.html', fields=fields, teams=teams)
def update(play_id, ball_order):
"""修改一筆ball資料
若有post則修改後更新db
無post則查出ball並顯示修改頁
"""
conn = mysql.get_db()
if request.method == 'POST':
# 依 play_id 進行 update
sql = u"UPDATE ball SET `kind`=%s, `speed`=%s, `result`=%s WHERE `play_id`=%s and `ball_order`=%s"
print(request.form.to_dict())
post = request.form.to_dict()
status = 'status' in request.form
with conn.cursor() as cursor:
conn.begin()
cursor.execute(sql, (post['kind'],
post['speed'],
post['result'],
play_id,
ball_order)
)
conn.commit()
return redirect(url_for('play.view', play_id=play_id, ball_order=ball_order))
else:
# 查出單筆, assign給頁面進行修改
sql = u"SELECT * FROM ball WHERE `play_id`={0} and `ball_order`={1}".format(play_id, ball_order)
with conn.cursor() as cursor:
cursor.execute(sql)
ball = cursor.fetchone()
return render_template('ball/update.html', ball=ball)
def update(player_id):
"""修改一筆player資料
若有post則修改後更新db
無post則查出player並顯示修改頁
"""
conn = mysql.get_db()
if request.method == 'POST':
# 依 team_id 進行 update
sql = u"UPDATE player SET `player_name`=%s, `player_number`=%s, `player_salary`=%s, `player_position`=%s WHERE `player_id`=%s"
print(request.form.to_dict())
post = request.form.to_dict()
status = 'status' in request.form
with conn.cursor() as cursor:
conn.begin()
cursor.execute(
sql,
(post['player_name'], post['player_number'],
post['player_salary'], post['player_position'], player_id))
conn.commit()
return redirect(url_for('player.view', player_id=player_id))
else:
# 查出單筆, assign給頁面進行修改
sql = u"SELECT * FROM player WHERE player_id=%s"
with conn.cursor() as cursor:
cursor.execute(sql, player_id)
player = cursor.fetchone()
return render_template('player/update.html', player=player)
def view(match_id):
"""顯示某筆field的頁面與細節
"""
# 查出單筆, assign給頁面
conn = mysql.get_db()
sql = u"SELECT match_id, t3.name as place, t1.team_name as Home, t2.team_name as Away, m_date FROM `match` m INNER JOIN `field` t3 ON m.field_id= t3.field_id INNER JOIN team t1 ON t1.team_id = m.m_hometeam INNER JOIN team t2 ON t2.team_id = m.m_awayteam WHERE match_id=%s"
with conn.cursor() as cursor:
cursor.execute(sql, match_id)
match = cursor.fetchone()
return render_template('match/view.html', match=match)
def delete(play_id, ball_order):
"""刪除某筆play資料後,回到列表頁
"""
conn = mysql.get_db()
sql = u"DELETE FROM ball WHERE `play_id`={0} and `ball_order`={1}".format(play_id, ball_order)
with conn.cursor() as cursor:
conn.begin()
cursor.execute(sql)
conn.commit()
return redirect(url_for('play.simple_list'))
def view(team_id):
"""顯示某筆field的頁面與細節
"""
# 查出單筆, assign給頁面
conn = mysql.get_db()
sql = u"SELECT * FROM team WHERE team_id=%s"
with conn.cursor() as cursor:
cursor.execute(sql, team_id)
team = cursor.fetchone()
return render_template('team/view.html', team=team)
def delete(play_id):
"""刪除某筆play資料後,回到列表頁
"""
conn = mysql.get_db()
sql = u"DELETE FROM play WHERE `play_id`=%s"
with conn.cursor() as cursor:
conn.begin()
cursor.execute(sql, play_id)
conn.commit()
return redirect(url_for('play.simple_list'))
def update(play_id):
"""修改一筆play資料
若有post則修改後更新db
無post則查出player並顯示修改頁
"""
conn = mysql.get_db()
if request.method == 'POST':
# 依 play_id 進行 update
sql = u"UPDATE play SET `pitcher_id`=%s, `batter_id`=%s, `match_id`=%s, `inning`=%s, `half`=%s WHERE `play_id`=%s"
print(request.form.to_dict())
post = request.form.to_dict()
status = 'status' in request.form
with conn.cursor() as cursor:
conn.begin()
cursor.execute(sql, (post['pitcher_id'],
post['batter_id'],
post['match_id'],
post['inning'],
post['half'],
play_id)
)
conn.commit()
return redirect(url_for('play.view', play_id=play_id))
else:
# 查出單筆, assign給頁面進行修改
sql = u"SELECT * FROM play WHERE play_id=%s"
with conn.cursor() as cursor:
cursor.execute(sql, play_id)
play = cursor.fetchone()
conn = mysql.get_db()
sql = u"SELECT match_id, m_date, field.name FROM `match` JOIN `field` ON match.field_id=field.field_id"
with conn.cursor() as cursor:
cursor.execute(sql)
matches = cursor.fetchall()
sql = u"SELECT * FROM player WHERE player_position='P'"
with conn.cursor() as cursor:
cursor.execute(sql)
pitchers = cursor.fetchall()
sql = u"SELECT * FROM player"
with conn.cursor() as cursor:
cursor.execute(sql)
batters = cursor.fetchall()
return render_template('play/update.html', play=play, matches=matches, pitchers=pitchers, batters=batters)
def delete(team_id):
"""刪除某筆field資料後,回到列表頁,或著只是將status改為False
"""
conn = mysql.get_db()
sql = u"DELETE FROM team WHERE `team_id`=%s"
with conn.cursor() as cursor:
conn.begin()
cursor.execute(sql, team_id)
conn.commit()
return redirect(url_for('team.simple_list'))
def view(player_id):
"""顯示某筆field的頁面與細節
"""
# 查出單筆, assign給頁面
conn = mysql.get_db()
sql = u"SELECT * FROM player WHERE player_id=%s"
with conn.cursor() as cursor:
cursor.execute(sql, player_id)
player = cursor.fetchone()
return render_template('player/view.html', player=player)
def search(page=0, items=10):
"""加入搜尋條件的play列表
"""
conn = mysql.get_db()
query_condition = u""
if request.method == 'POST' and request.form['s_key'] and request.form['s_value']:
query_condition = u" WHERE {0} LIKE '%{1}%'".format(request.form['s_key'], request.form['s_value'])
session['s_key'] = request.form['s_key']
session['s_value'] = request.form['s_value']
elif 's_key' in session and 's_value' in session:
query_condition = u" WHERE {0} LIKE '%{1}%'".format(session['s_key'], session['s_value'])
if len(query_condition) != 0:
sql1 = u"SELECT COUNT(*) as total FROM play p " \
u"INNER JOIN player t1 ON t1.player_id = p.pitcher_id " \
u"INNER JOIN player t2 ON t2.player_id = p.batter_id " + query_condition
with conn.cursor() as cursor:
cursor.execute(sql1)
row = cursor.fetchone()
total = row[0]
print(total)
sort_condition = u""
if 'sort' in request.args and 'asc' in request.args:
sort_condition = u" ORDER BY {0} ".format(request.args.get('sort'))
if request.args.get('asc') == '0':
sort_condition += u" DESC"
else:
sort_condition += u" ASC"
else:
sort_condition = "ORDER BY play_id ASC"
sql2 = u"SELECT play_id, t1.player_name as pitcher_name, t2.player_name as batter_name, match_id, inning, half " \
u"FROM play p " \
u"INNER JOIN player t1 ON t1.player_id = p.pitcher_id " \
u"INNER JOIN player t2 ON t2.player_id = p.batter_id " + query_condition + sort_condition
skip = page * items
if skip >= total:
skip = 0
sql2 += u" LIMIT {0},{1}".format(skip, items)
# 執行 sql2 取得資料 (多筆)
plays = []
with conn.cursor() as cursor:
cursor.execute(sql2)
plays = cursor.fetchall()
print(plays)
misc = items_pagebar(total, page, items, 'play_id') # 計算pagebar需要之參數
misc['s_key'] = session['s_key'] or u''
misc['s_value'] = session['s_value'] or u''
return render_template('play/list.html', plays=plays, misc=misc, action='search')
else:
return redirect(url_for('play.simple_list'))
def search(page=0, items=10):
"""加入搜尋條件的team列表
"""
conn = mysql.get_db()
query_condition = u""
if request.method == 'POST' and request.form['s_key'] and request.form[
's_value']:
query_condition = u" WHERE {0} LIKE '%{1}%'".format(
request.form['s_key'], request.form['s_value'])
session['s_key'] = request.form['s_key']
session['s_value'] = request.form['s_value']
elif 's_key' in session and 's_value' in session:
query_condition = u" WHERE {0} LIKE '%{1}%'".format(
session['s_key'], session['s_value'])
if len(query_condition) != 0:
sql1 = u"SELECT COUNT(*) as total FROM team" + query_condition
with conn.cursor() as cursor:
cursor.execute(sql1)
row = cursor.fetchone()
total = row[0]
print(total)
sort_condition = u""
if 'sort' in request.args and 'asc' in request.args:
sort_condition = u" ORDER BY {0} ".format(request.args.get('sort'))
if request.args.get('asc') == '0':
sort_condition += u" DESC"
else:
sort_condition += u" ASC"
sql2 = u"SELECT * FROM team" + query_condition + sort_condition
skip = page * items
if skip >= total:
skip = 0
sql2 += u" LIMIT {0},{1}".format(skip, items)
# 執行 sql2 取得資料 (多筆)
teams = []
with conn.cursor() as cursor:
cursor.execute(sql2)
teams = cursor.fetchall()
print(teams)
misc = items_pagebar(total, page, items) # 計算pagebar需要之參數
misc['s_key'] = session['s_key'] or u''
misc['s_value'] = session['s_value'] or u''
return render_template('team/list.html',
teams=teams,
misc=misc,
action='search')
else:
return redirect(url_for('team.simple_list'))
def picther_analysis(page=0, items=10):
"""
顯示 html, 查出所有 pitcher, 並加入 select 中, 後續用 ajax 取得 dashboard 的資料(json)
"""
conn = mysql.get_db()
sql = u"SELECT * FROM player WHERE player_position='P'"
players = []
with conn.cursor() as cursor:
cursor.execute(sql)
players = cursor.fetchall()
print(players)
# return render_template('analysis/pitcher.html', players=players, action='simple_list')
return jsonify(players)
def simple_list(page=0, items=10):
"""簡單的match列表
查出依條件查出 符合條件的資料
可能有 LIMIT <from>,<how many>
可能有 SORY BY <column> ASC/DESC
然後 assign 給 html
有一些經過標準計算取得的數值要 assign 給清單控制分頁用
"""
conn = mysql.get_db()
# 先取得符合條件的 record 一共有多少筆
sql1 = u"SELECT COUNT(*) as total FROM `match`"
with conn.cursor() as cursor:
cursor.execute(sql1)
row = cursor.fetchone()
total = row[0]
print(total)
sort_condition = u""
if 'sort' in request.args and 'asc' in request.args: # 檢查是否同時提供排序的目標欄位及排序方法
sort_condition = u" ORDER BY {0} ".format(request.args.get('sort'))
if request.args.get('asc') == '0': # 表示不要asc, 即desc
sort_condition += u" DESC"
else: # 表示要asc
sort_condition += u" ASC"
sql2 = u"SELECT match_id, t3.name as place, t1.team_name as Home, t2.team_name as Away, m_date " \
u"FROM `match` m " \
u"INNER JOIN `field` t3 ON m.field_id= t3.field_id " \
u"INNER JOIN team t1 ON t1.team_id = m.m_hometeam " \
u"INNER JOIN team t2 ON t2.team_id = m.m_awayteam" + sort_condition
skip = page * items
if skip >= total:
skip = 0
sql2 += u" LIMIT {0},{1}".format(skip, items)
print(sql2)
# 執行 sql2 取得資料 (多筆)
matches = []
with conn.cursor() as cursor:
cursor.execute(sql2)
matches = cursor.fetchall()
print(matches)
misc = items_pagebar(total, page, items) # 計算pagebar需要之參數
return render_template('match/list.html',
matches=matches,
misc=misc,
action='simple_list')
def simple_list(page=0, items=10):
"""簡單的game列表
查出依條件查出 符合條件的資料
可能有 LIMIT <from>,<how many>
可能有 SORY BY <column> ASC/DESC
然後 assign 給 html
有一些經過標準計算取得的數值要 assign 給清單控制分頁用
"""
conn = mysql.get_db()
sql1 = u"SELECT COUNT(*) as total FROM play"
# 先取得符合條件的 record 一共有多少筆
with conn.cursor() as cursor:
cursor.execute(sql1)
row = cursor.fetchone()
total = row[0]
print(total)
sort_condition = ""
if 'sort' in request.args and 'asc' in request.args: # 檢查是否同時提供排序的目標欄位及排序方法
sort_condition = " ORDER BY {0} ".format(request.args.get('sort'))
if request.args.get('asc') == '0': # 表示不要asc, 即desc
sort_condition += " DESC"
else: # 表示要asc
sort_condition += " ASC"
else:
sort_condition = "ORDER BY play_id ASC"
sql2 = "SELECT play_id, t1.player_name as pitcher_name, t2.player_name as batter_name, p.match_id, inning, half, M.m_date, F.name " \
"FROM play p INNER JOIN player t1 ON t1.player_id = p.pitcher_id " \
"INNER JOIN player t2 ON t2.player_id = p.batter_id " \
"JOIN `match` AS M ON p.match_id = M.match_id " \
"JOIN `field` AS F ON M.field_id = F.field_id " + sort_condition
skip = page * items
if skip >= total:
skip = 0
sql2 += " LIMIT {0},{1}".format(skip, items)
print(sql2)
# 執行 sql2 取得資料 (多筆)
plays = []
with conn.cursor() as cursor:
cursor.execute(sql2)
plays = cursor.fetchall()
print(plays)
misc = items_pagebar(total, page, items, 'play_id') # 計算pagebar需要之參數
return render_template('play/list.html', plays=plays, misc=misc, action='simple_list')
def create():
"""新增一筆play資料
"""
if request.method == 'POST':
conn = mysql.get_db()
# 實際寫入一筆 t1.player_name as pitcher_name, t2.player_name as batter_name,
sql = u"INSERT INTO `play` (`pitcher_id`, `batter_id`, `match_id`, `inning`, `half`) VALUES (%s, %s, %s, %s, %s)"
print(request.form.to_dict())
post = request.form.to_dict()
print(post)
status = 'status' in request.form
with conn.cursor() as cursor:
conn.begin()
cursor.execute(sql, (post['pitcher_id'],
post['batter_id'],
post['match_id'],
post['inning'],
post['half'])
)
play_id = cursor.lastrowid
conn.commit()
return redirect(url_for('play.view', play_id=play_id))
else:
conn = mysql.get_db()
sql = u"SELECT match_id, m_date, field.name FROM `match` JOIN `field` ON match.field_id=field.field_id"
with conn.cursor() as cursor:
cursor.execute(sql)
matches = cursor.fetchall()
sql = u"SELECT * FROM player WHERE player_position='P'"
with conn.cursor() as cursor:
cursor.execute(sql)
pitchers = cursor.fetchall()
sql = u"SELECT * FROM player"
with conn.cursor() as cursor:
cursor.execute(sql)
batters = cursor.fetchall()
return render_template('play/create.html', matches=matches, pitchers=pitchers, batters=batters)
def show_entries():
"""導入頁
:return: None
"""
if 'user_id' in session:
conn = mysql.get_db()
sql = u"SELECT * FROM player WHERE player_position='P'"
with conn.cursor() as cursor:
cursor.execute(sql)
pitchers = cursor.fetchall()
print(pitchers)
return render_template('dashboard.html', pitchers=pitchers)
else:
flash('not a user')
return redirect(url_for('login'))
def simple_list(page=0, items=10):
"""簡單的team列表
查出依條件查出 符合條件的資料
可能有 LIMIT <from>,<how many>
可能有 SORY BY <column> ASC/DESC
然後 assign 給 html
有一些經過標準計算取得的數值要 assign 給清單控制分頁用
"""
conn = mysql.get_db()
# 先取得符合條件的 record 一共有多少筆
sql1 = u"SELECT COUNT(*) as total FROM team" # u 表示unicode
with conn.cursor() as cursor:
cursor.execute(sql1)
row = cursor.fetchone()
total = row[0]
print(total)
sort_condition = u""
if 'sort' in request.args and 'asc' in request.args: # 檢查是否同時提供排序的目標欄位及排序方法
sort_condition = u" ORDER BY {0} ".format(request.args.get('sort'))
if request.args.get('asc') == '0': # 表示不要asc, 即desc
sort_condition += u" DESC"
else: # 表示要asc
sort_condition += u" ASC"
sql2 = u"SELECT * FROM team" + sort_condition
skip = page * items
if skip >= total:
skip = 0
sql2 += u" LIMIT {0},{1}".format(skip, items)
print(sql2)
# 執行 sql2 取得資料 (多筆)
teams = []
with conn.cursor() as cursor:
cursor.execute(sql2)
teams = cursor.fetchall()
print(teams)
misc = items_pagebar(total, page, items, 'team_id') # 計算pagebar需要之參數
return render_template('team/list.html',
teams=teams,
misc=misc,
action='simple_list')
def create():
"""新增一筆team資料
"""
if request.method == 'POST':
conn = mysql.get_db()
# 實際寫入一筆
sql = u"INSERT INTO `team` (`team_name`, `status`) VALUES (%s, %s)"
print(request.form.to_dict())
post = request.form.to_dict()
print(post)
status = 'status' in request.form
with conn.cursor() as cursor:
conn.begin()
cursor.execute(sql, (post['team_name'], status))
team_id = cursor.lastrowid
conn.commit()
return redirect(url_for('team.view', team_id=team_id))
else:
return render_template('team/create.html')
def view(play_id):
"""顯示某筆play的頁面與細節
"""
# 查出單筆, assign給頁面
conn = mysql.get_db()
sql = u"SELECT play_id, t1.player_name as pitcher_name, t2.player_name as batter_name, p.match_id, inning, half, M.m_date, F.name " \
u"FROM play p INNER JOIN player t1 ON t1.player_id = p.pitcher_id " \
u"INNER JOIN player t2 ON t2.player_id = p.batter_id " \
u"JOIN `match` AS M ON p.match_id = M.match_id " \
u"JOIN `field` AS F ON M.field_id = F.field_id " \
u"WHERE p.play_id = %s"
with conn.cursor() as cursor:
cursor.execute(sql, play_id)
play = cursor.fetchone()
sql = u"SELECT ball.play_id, `ball_order`, `kind`, `speed`, `result` FROM play, ball WHERE ball.play_id=%s and play.play_id=ball.play_id "
with conn.cursor() as cursor:
cursor.execute(sql, play_id)
balls = cursor.fetchall()
return render_template('play/view.html', play=play, balls=balls)
def create():
"""新增一筆player資料
"""
if request.method == 'POST':
conn = mysql.get_db()
# 實際寫入一筆
sql = u"INSERT INTO `player` (`player_name`, `player_number`, `player_salary`, `player_position`) VALUES (%s, %s, %s, %s)"
print(request.form.to_dict())
post = request.form.to_dict()
print(post)
status = 'status' in request.form
with conn.cursor() as cursor:
conn.begin()
cursor.execute(sql,
(post['player_name'], post['player_number'],
post['player_salary'], post['player_position']))
player_id = cursor.lastrowid
conn.commit()
return redirect(url_for('player.view', player_id=player_id))
else:
return render_template('player/create.html')
def create(play_id):
"""新增一筆ball資料
"""
if request.method == 'POST':
conn = mysql.get_db()
sql = u"INSERT INTO `ball` (`play_id`, `ball_order`, `kind`, `speed`, `result`) VALUES (%s, %s, %s, %s, %s)"
print(request.form.to_dict())
post = request.form.to_dict()
print(post)
status = 'status' in request.form
with conn.cursor() as cursor:
conn.begin()
cursor.execute(sql, (play_id,
post['ball_order'],
post['kind'],
post['speed'],
post['result'])
)
conn.commit()
return redirect(url_for('play.view', play_id=play_id))
else:
return render_template('ball/create.html', play_id=play_id)
def login():
"""登入執行, 或顯示登入頁
"""
if request.method == 'POST':
print(request.form)
email = request.form['email']
sha1_pwd = sha1(request.form['password']).hexdigest()
sql = "SELECT * FROM user WHERE email=%s AND password=%s"
conn = mysql.get_db()
with conn.cursor() as cursor:
cursor.execute(sql, (email, sha1_pwd))
row = cursor.fetchone()
# 用 email 查 database, 取得該使用者的 id 與 password (如果有作登入系統的話)
if row[3] != sha1_pwd:
flash('password does not match!')
return redirect(url_for('login'))
session['user_id'] = str(row[0]) # set member id to session, objectId is not json serializable
session['user_name'] = row[2] # user_name from DB
flash('You have logged in.')
return redirect(url_for('show_entries'))
else:
return render_template('login.html')
def update(team_id):
"""修改一筆team資料
若有post則修改後更新db
無post則查出team並顯示修改頁
"""
conn = mysql.get_db()
if request.method == 'POST':
# 依 team_id 進行 update
sql = u"UPDATE team SET `team_name`=%s, `status`=%s WHERE `team_id`=%s"
print(request.form.to_dict())
post = request.form.to_dict()
status = 'status' in request.form
with conn.cursor() as cursor:
conn.begin()
cursor.execute(sql, (post['team_name'], status, team_id))
conn.commit()
return redirect(url_for('team.view', team_id=team_id))
else:
# 查出單筆, assign給頁面進行修改
sql = u"SELECT * FROM team WHERE team_id=%s"
with conn.cursor() as cursor:
cursor.execute(sql, team_id)
team = cursor.fetchone()
return render_template('team/update.html', team=team)
def update(match_id):
"""修改一筆field資料
若有post則修改後更新db
無post則查出field並顯示修改頁
"""
conn = mysql.get_db()
if request.method == 'POST':
# 依 field_id 進行 update
sql = u"UPDATE `match` SET `field_id`=%s, `m_hometeam`=%s, `m_awayteam`=%s, `m_date`=%s WHERE `match_id`=%s"
print(request.form.to_dict())
post = request.form.to_dict()
status = 'status' in request.form
with conn.cursor() as cursor:
conn.begin()
cursor.execute(sql, (post['field_id'], post['m_hometeam'],
post['m_awayteam'], post['m_date'], match_id))
conn.commit()
return redirect(url_for('match.view', match_id=match_id))
else:
# 查出單筆, assign給頁面進行修改
sql = u"SELECT * FROM `match` WHERE match_id=%s"
with conn.cursor() as cursor:
cursor.execute(sql, match_id)
match = cursor.fetchone()
sql = u"SELECT * FROM `field`"
with conn.cursor() as cursor:
cursor.execute(sql)
fields = cursor.fetchall()
sql = u"SELECT * FROM team"
with conn.cursor() as cursor:
cursor.execute(sql)
teams = cursor.fetchall()
return render_template('match/update.html',
match=match,
fields=fields,
teams=teams)
def search(page=0, items=10):
"""加入搜尋條件的field列表
"""
conn = mysql.get_db()
query_condition = u""
if request.method == 'POST' and request.form['s_key'] and request.form[
's_value']:
query_condition = u" WHERE {0} LIKE '%{1}%'".format(
request.form['s_key'], request.form['s_value'])
session['s_key'] = request.form['s_key']
session['s_value'] = request.form['s_value']
elif 's_key' in session and 's_value' in session:
query_condition = u" WHERE {0} LIKE '%{1}%'".format(
session['s_key'], session['s_value'])
if len(query_condition) != 0:
sql1 = u"SELECT COUNT(*) as total " \
u"FROM `match` m " \
u"INNER JOIN `field` t3 ON m.field_id= t3.field_id " \
u"INNER JOIN team t1 ON t1.team_id = m.m_hometeam " \
u"INNER JOIN team t2 ON t2.team_id = m.m_awayteam" + query_condition
with conn.cursor() as cursor:
cursor.execute(sql1)
row = cursor.fetchone()
total = row[0]
print(total)
sort_condition = u""
if 'sort' in request.args and 'asc' in request.args: # 檢查是否同時提供排序的目標欄位及排序方法
sort_condition = u" ORDER BY {0} ".format(request.args.get('sort'))
if request.args.get('asc') == '0': # 表示不要asc, 即desc
sort_condition += u" DESC"
else: # 表示要asc
sort_condition += u" ASC"
sql2 = u"SELECT match_id, t3.name as place, t1.team_name as Home, t2.team_name as Away, m_date " \
u"FROM `match` m " \
u"INNER JOIN `field` t3 ON m.field_id= t3.field_id " \
u"INNER JOIN team t1 ON t1.team_id = m.m_hometeam " \
u"INNER JOIN team t2 ON t2.team_id = m.m_awayteam" + query_condition + sort_condition
skip = page * items
if skip >= total:
skip = 0
sql2 += u" LIMIT {0},{1}".format(skip, items)
# 執行 sql2 取得資料 (多筆)
matches = []
with conn.cursor() as cursor:
cursor.execute(sql2)
matches = cursor.fetchall()
print(matches)
misc = items_pagebar(total, page, items) # 計算pagebar需要之參數
misc['s_key'] = session['s_key'] or u''
misc['s_value'] = session['s_value'] or u''
return render_template('match/list.html',
matches=matches,
misc=misc,
action='search')
else:
return redirect(url_for('match.simple_list'))
def pitcher_recent(player_id):
"""
SELECT DISTINCT kind FROM `ball`
得 4FB, CRV, SLD, 2FB, CUT, CHG
SELECT DISTINCT result FROM `ball`
得 f_ball, S, K, B, out, HR, 1h, BB
查詢此 pitcher 的最近 18 局表現, 以 json 格式回傳建立圖表
:param player_id: 目標 pitcher 的 player_id
:return:
"""
kind = ['4FB', 'CRV', 'SLD', '2FB', 'CUT', 'CHG']
conn = mysql.get_db()
temp_create = u"CREATE TEMPORARY TABLE `valid_inning`(`match_id` INT, `inning` INT, `m_date` DATE)"
with conn.cursor() as cursor:
cursor.execute(temp_create)
# INSERT INTO TEMP TABLE
match_ids = set()
sql = u"""INSERT INTO `valid_inning`(`match_id`,`inning`, `m_date`) SELECT P.match_id, P.inning, M.m_date
FROM `play` AS P
JOIN `match` AS M ON P.match_id = M.match_id
WHERE P.pitcher_id={0}
GROUP BY match_id, inning
ORDER BY m_date DESC, inning DESC
LIMIT 18""".format(player_id)
with conn.cursor() as cursor:
cursor.execute(sql)
# 從 TEMP TABLE開始小計
# 後續手動 join 小計表 (再產生 temp table也怪怪的)
result_map = {}
sql1 = u"""SELECT P.match_id, P.inning, kind, count(*) as total FROM `valid_inning` AS V
JOIN `play` AS P ON P.match_id=V.match_id AND P.inning=V.inning
JOIN `ball` AS B ON P.play_id=B.play_id
WHERE P.pitcher_id={1}
GROUP BY P.match_id, P.inning, kind
ORDER BY match_id DESC, inning DESC
""".format(u",".join(match_ids), player_id)
print(sql1)
with conn.cursor() as cursor:
cursor.execute(sql1)
for row in cursor:
if row[0] not in result_map:
result_map[row[0]] = {}
if row[1] not in result_map[row[0]]:
result_map[row[0]][row[1]] = [0] * 6
result_map[row[0]][row[1]][kind.index(row[2])] = row[3]
print(result_map)
# return jsonify(result_map)
data = [[
'match-inning', '4FB', 'CRV', 'SLD', '2FB', 'CUT', 'CHG', {
'role': 'annotation'
}
]]
for match_id in result_map:
for inning in result_map[match_id]:
row = ['match:{0}-inning:{1}'.format(match_id, inning)
] + result_map[match_id][inning] + ['']
data.append(row)
return jsonify(data)
