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)