list_query_templates
Access predefined query templates to analyze Japanese horse racing data from JRA-VAN without SQL.
Instructions
利用可能なクエリテンプレート一覧を取得
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
No arguments | |||
Implementation Reference
- src/jvlink_mcp_server/server.py:632-640 (handler)The MCP tool handler for 'list_query_templates'. It is decorated with @mcp.tool() and calls get_templates_list() (imported as alias from database/query_templates.py) to return a list of available query templates.
@mcp.tool() def list_query_templates() -> dict: """利用可能なクエリテンプレート一覧を取得""" templates = get_templates_list() return { "templates": templates, "total": len(templates), "usage": "execute_template_query(template_name, **params)" } - The actual implementation of list_templates() which iterates over QUERY_TEMPLATES dict and returns a list of template info dicts (name, description, parameters). This is imported as 'get_templates_list' in server.py.
def list_templates() -> List[Dict[str, Any]]: """利用可能なテンプレート一覧を返す Returns: テンプレート情報のリスト - name: テンプレート名 - description: 説明 - parameters: パラメータ定義 Examples: >>> templates = list_templates() >>> for t in templates: ... print(f"{t['name']}: {t['description']}") """ result = [] for name, template in QUERY_TEMPLATES.items(): result.append({ "name": name, "description": template["description"], "parameters": template["parameters"], }) return result - The QUERY_TEMPLATES dictionary containing all template definitions. Each template has name, description, parameters (with type, description, required flag, optional defaults), and SQL template string.
QUERY_TEMPLATES = { "favorite_win_rate": { "description": "人気別勝率を計算", "parameters": { "ninki": { "type": "int", "description": "人気順位（1-18）", "required": True, }, "venue": { "type": "str", "description": "競馬場名（札幌、函館、福島、新潟、東京、中山、中京、京都、阪神、小倉）", "required": False, }, "year_from": { "type": "str", "description": "集計開始年（YYYY形式）", "required": False, }, }, "sql": """ SELECT COUNT(*) as total_races, SUM(CASE WHEN KakuteiJyuni = 1 THEN 1 ELSE 0 END) as wins, SUM(CASE WHEN KakuteiJyuni <= 3 THEN 1 ELSE 0 END) as top3, ROUND(100.0 * SUM(CASE WHEN KakuteiJyuni = 1 THEN 1 ELSE 0 END) / COUNT(*), 1) as win_rate, ROUND(100.0 * SUM(CASE WHEN KakuteiJyuni <= 3 THEN 1 ELSE 0 END) / COUNT(*), 1) as top3_rate FROM NL_SE WHERE Ninki = {ninki} {venue_condition} {year_condition} AND KakuteiJyuni IS NOT NULL AND KakuteiJyuni > 0 """, }, "jockey_stats": { "description": "騎手成績を集計", "parameters": { "jockey_name": { "type": "str", "description": "騎手名（部分一致可）", "required": False, }, "year": { "type": "str", "description": "対象年（YYYY形式）", "required": False, }, "limit": { "type": "int", "description": "表示件数", "required": False, "default": 20, }, }, "sql": """ SELECT KisyuRyakusyo as jockey_name, COUNT(*) as total_rides, SUM(CASE WHEN KakuteiJyuni = 1 THEN 1 ELSE 0 END) as wins, SUM(CASE WHEN KakuteiJyuni <= 2 THEN 1 ELSE 0 END) as top2, SUM(CASE WHEN KakuteiJyuni <= 3 THEN 1 ELSE 0 END) as top3, ROUND(100.0 * SUM(CASE WHEN KakuteiJyuni = 1 THEN 1 ELSE 0 END) / COUNT(*), 1) as win_rate, ROUND(100.0 * SUM(CASE WHEN KakuteiJyuni <= 3 THEN 1 ELSE 0 END) / COUNT(*), 1) as top3_rate FROM NL_SE WHERE KakuteiJyuni IS NOT NULL AND KakuteiJyuni > 0 {jockey_condition} {year_condition} GROUP BY KisyuRyakusyo ORDER BY wins DESC, win_rate DESC LIMIT {limit} """, }, "frame_stats": { "description": "枠番別成績を集計", "parameters": { "venue": { "type": "str", "description": "競馬場名", "required": False, }, "kyori": { "type": "str", "description": "距離（メートル単位、例：1600）", "required": False, }, }, "sql": """ SELECT Wakuban as frame_number, COUNT(*) as total_runs, SUM(CASE WHEN KakuteiJyuni = 1 THEN 1 ELSE 0 END) as wins, SUM(CASE WHEN KakuteiJyuni <= 3 THEN 1 ELSE 0 END) as top3, ROUND(100.0 * SUM(CASE WHEN KakuteiJyuni = 1 THEN 1 ELSE 0 END) / COUNT(*), 1) as win_rate, ROUND(100.0 * SUM(CASE WHEN KakuteiJyuni <= 3 THEN 1 ELSE 0 END) / COUNT(*), 1) as top3_rate FROM NL_SE WHERE KakuteiJyuni IS NOT NULL AND KakuteiJyuni > 0 {venue_condition} {kyori_condition} GROUP BY Wakuban ORDER BY Wakuban """, }, "race_result": { "description": "レース結果を取得（レース情報とJOIN）", "parameters": { "year": { "type": "str", "description": "開催年（YYYY形式）", "required": True, }, "month_day": { "type": "str", "description": "開催月日（MMDD形式）", "required": True, }, "jyo_cd": { "type": "str", "description": "競馬場コード（01-10）", "required": True, }, "kaiji": { "type": "str", "description": "開催回次", "required": True, }, "nichiji": { "type": "str", "description": "開催日次", "required": True, }, "race_num": { "type": "str", "description": "レース番号（1-12）", "required": True, }, }, "sql": """ SELECT r.Hondai as race_name, r.GradeCD as grade, r.Kyori as distance, r.TrackCD as track_code, s.KakuteiJyuni as finish_position, s.Wakuban as frame_number, s.Umaban as horse_number, s.Bamei as horse_name, s.KisyuRyakusyo as jockey_name, s.Ninki as popularity, s.Odds as odds, s.Time as time, s.HaronTimeL3 as last_3f, s.BaTaijyu as weight FROM NL_RA r JOIN NL_SE s ON r.Year = s.Year AND r.MonthDay = s.MonthDay AND r.JyoCD = s.JyoCD AND r.Kaiji = s.Kaiji AND r.Nichiji = s.Nichiji AND r.RaceNum = s.RaceNum WHERE r.Year = {year} AND r.MonthDay = '{month_day}' AND r.JyoCD = '{jyo_cd}' AND r.Kaiji = {kaiji} AND r.Nichiji = {nichiji} AND r.RaceNum = {race_num} ORDER BY CAST(s.KakuteiJyuni AS INTEGER) """, }, "grade_race_list": { "description": "重賞レース一覧を取得", "parameters": { "grade": { "type": "str", "description": "グレード（G1、G2、G3、リステッド）", "required": False, }, "year": { "type": "str", "description": "対象年（YYYY形式）", "required": False, }, "venue": { "type": "str", "description": "競馬場名", "required": False, }, "limit": { "type": "int", "description": "表示件数", "required": False, "default": 50, }, }, "sql": """ SELECT r.Year as year, r.MonthDay as month_day, r.JyoCD as venue_code, r.Hondai as race_name, r.GradeCD as grade, r.Kyori as distance, r.TrackCD as track_code, r.SyussoTosu as horse_count FROM NL_RA r WHERE r.GradeCD IN ('A', 'B', 'C', 'D') {grade_condition} {year_condition} {venue_condition} ORDER BY r.Year DESC, r.MonthDay DESC LIMIT {limit} """, }, "horse_pedigree": { "description": "馬の血統情報を取得", "parameters": { "horse_name": { "type": "str", "description": "馬名（部分一致可）", "required": True, }, }, "sql": """ SELECT u.Bamei as horse_name, u.KettoNum as ketto_num, u.SexCD as sex_code, u.BirthDate as birth_date, u.Ketto3InfoBamei1 as sire, u.Ketto3InfoBamei2 as dam, u.Ketto3InfoBamei5 as broodmare_sire, u.SanchiName as birthplace, u.BreederName as breeder, u.BanusiName as owner FROM NL_UM u WHERE u.Bamei LIKE {horse_name} ORDER BY u.Bamei LIMIT 20 """, }, "sire_stats": { "description": "種牡馬別成績を集計", "parameters": { "sire_name": { "type": "str", "description": "種牡馬名（部分一致可）", "required": False, }, "year": { "type": "str", "description": "対象年（YYYY形式）", "required": False, }, "limit": { "type": "int", "description": "表示件数", "required": False, "default": 20, }, }, "sql": """ SELECT u.Ketto3InfoBamei1 as sire_name, COUNT(*) as total_runs, SUM(CASE WHEN s.KakuteiJyuni = 1 THEN 1 ELSE 0 END) as wins, SUM(CASE WHEN s.KakuteiJyuni <= 3 THEN 1 ELSE 0 END) as top3, ROUND(100.0 * SUM(CASE WHEN s.KakuteiJyuni = 1 THEN 1 ELSE 0 END) / COUNT(*), 1) as win_rate, ROUND(100.0 * SUM(CASE WHEN s.KakuteiJyuni <= 3 THEN 1 ELSE 0 END) / COUNT(*), 1) as top3_rate FROM NL_SE s JOIN NL_UM u ON s.KettoNum = u.KettoNum WHERE s.KakuteiJyuni IS NOT NULL AND s.KakuteiJyuni > 0 AND u.Ketto3InfoBamei1 IS NOT NULL AND LENGTH(u.Ketto3InfoBamei1) > 0 {sire_condition} {year_condition} GROUP BY u.Ketto3InfoBamei1 HAVING COUNT(*) >= 10 ORDER BY wins DESC, win_rate DESC LIMIT {limit} """, }, # === NAR（地方競馬）テンプレート === "nar_favorite_win_rate": { "description": "NAR地方競馬の人気別勝率を計算", "parameters": { "ninki": {"type": "int", "description": "人気順位（1-18）", "required": True}, "venue": {"type": "str", "description": "地方競馬場名（大井、船橋、川崎、浦和、名古屋、園田等）", "required": False}, "year_from": {"type": "str", "description": "集計開始年（YYYY形式）", "required": False}, }, "sql": """ SELECT COUNT(*) as total_races, SUM(CASE WHEN KakuteiJyuni = 1 THEN 1 ELSE 0 END) as wins, SUM(CASE WHEN KakuteiJyuni <= 3 THEN 1 ELSE 0 END) as top3, ROUND(100.0 * SUM(CASE WHEN KakuteiJyuni = 1 THEN 1 ELSE 0 END) / COUNT(*), 1) as win_rate, ROUND(100.0 * SUM(CASE WHEN KakuteiJyuni <= 3 THEN 1 ELSE 0 END) / COUNT(*), 1) as top3_rate FROM NL_SE_NAR WHERE Ninki = {ninki} {venue_condition} {year_condition} AND KakuteiJyuni IS NOT NULL AND KakuteiJyuni > 0 """, }, "nar_jockey_stats": { "description": "NAR地方競馬の騎手成績を集計", "parameters": { "jockey_name": {"type": "str", "description": "騎手名（部分一致可）", "required": False}, "year": {"type": "str", "description": "対象年（YYYY形式）", "required": False}, "limit": {"type": "int", "description": "表示件数", "required": False, "default": 20}, }, "sql": """ SELECT KisyuRyakusyo as jockey_name, COUNT(*) as total_rides, SUM(CASE WHEN KakuteiJyuni = 1 THEN 1 ELSE 0 END) as wins, SUM(CASE WHEN KakuteiJyuni <= 3 THEN 1 ELSE 0 END) as top3, ROUND(100.0 * SUM(CASE WHEN KakuteiJyuni = 1 THEN 1 ELSE 0 END) / COUNT(*), 1) as win_rate, ROUND(100.0 * SUM(CASE WHEN KakuteiJyuni <= 3 THEN 1 ELSE 0 END) / COUNT(*), 1) as top3_rate FROM NL_SE_NAR WHERE KakuteiJyuni IS NOT NULL AND KakuteiJyuni > 0 {jockey_condition} {year_condition} GROUP BY KisyuRyakusyo ORDER BY wins DESC, win_rate DESC LIMIT {limit} """, }, "nar_venue_stats": { "description": "NAR地方競馬場別の1番人気成績を集計", "parameters": { "year_from": {"type": "str", "description": "集計開始年", "required": False}, }, "sql": """ SELECT JyoCD as venue_code, COUNT(*) as total_races, SUM(CASE WHEN KakuteiJyuni = 1 THEN 1 ELSE 0 END) as wins, ROUND(100.0 * SUM(CASE WHEN KakuteiJyuni = 1 THEN 1 ELSE 0 END) / COUNT(*), 1) as win_rate FROM NL_SE_NAR WHERE Ninki = 1 AND KakuteiJyuni IS NOT NULL AND KakuteiJyuni > 0 {year_condition} GROUP BY JyoCD ORDER BY win_rate DESC """, }, "race_search": { "description": "レース名で検索（国内限定オプション付き）", "parameters": { "race_name": { "type": "str", "description": "レース名（部分一致、例: ダービー）", "required": True, }, "year_from": { "type": "str", "description": "集計開始年（YYYY形式）", "required": False, }, "domestic_only": { "type": "bool", "description": "国内（JRA）レースのみに限定する（JyoCD 01-10）。デフォルトtrue", "required": False, "default": True, }, "limit": { "type": "int", "description": "表示件数", "required": False, "default": 50, }, }, "sql": """ SELECT r.Year as year, r.MonthDay as month_day, r.JyoCD as venue_code, r.Hondai as race_name, r.GradeCD as grade, r.Kyori as distance, r.TrackCD as track_code, r.SyussoTosu as horse_count FROM NL_RA r WHERE r.Hondai LIKE ? {domestic_condition} {year_condition} ORDER BY r.Year DESC, r.MonthDay DESC LIMIT {limit} """, }, "track_condition_stats": { "description": "馬場状態別成績を分析（特定の馬）", "parameters": { "horse_name": { "type": "str", "description": "馬名（部分一致可）", "required": True, }, }, "sql": """ SELECT s.Bamei as horse_name, r.TrackCD as track_code, COUNT(*) as total_runs, SUM(CASE WHEN s.KakuteiJyuni = 1 THEN 1 ELSE 0 END) as wins, SUM(CASE WHEN s.KakuteiJyuni <= 3 THEN 1 ELSE 0 END) as top3, ROUND(100.0 * SUM(CASE WHEN s.KakuteiJyuni = 1 THEN 1 ELSE 0 END) / COUNT(*), 1) as win_rate FROM NL_SE s JOIN NL_RA r ON s.Year = r.Year AND s.MonthDay = r.MonthDay AND s.JyoCD = r.JyoCD AND s.Kaiji = r.Kaiji AND s.Nichiji = r.Nichiji AND s.RaceNum = r.RaceNum WHERE s.Bamei LIKE {horse_name} AND s.KakuteiJyuni IS NOT NULL AND s.KakuteiJyuni > 0 GROUP BY s.Bamei, r.TrackCD ORDER BY total_runs DESC """, }, } - src/jvlink_mcp_server/server.py:27-31 (registration)The import statement in server.py that imports 'list_templates' from database/query_templates.py, aliased as 'get_templates_list'.
from .database.query_templates import ( list_templates as get_templates_list, render_template, get_template_info, )