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 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 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 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'))