お馬の写真 別館

「お馬の写真」管理者による徒然なるブログ

F#: MySQLへのアクセス、XMLの出力

      2015/04/06

a0002_009631

以前から遊んでいたF#の話。
目標としては、JRA-VAN DataLab.から取得したデータを元にこの形式のページを作ること。

まず、JRA-VAN DataLab.からデータを取得するのはPC-KEIBAでOK。
PC-KEIBAで取得したMySQL上のデータを使って必要な情報を抽出する部分をF#でプログラムして作ろうとしている次第。

まず1つ目のソース。1頭の1レース分の情報を持つクラス。

module SharedHorseInfoPrinter.HorseRaceInfo
type HorseRaceInfo(kaisainengappi,keibajo,name,joken,grade,track,kyori,kishumei,futan,chakujun,ninki,tosu,odds,time,agari3f,shiba,dirt,aite,timesa) =
member val Kaisainengappi : System.DateTime = kaisainengappi
member val Keibajo : string = keibajo
member val Name : string = name
member val Joken : string = joken
member val Grade : string = grade
member val Track : string = track
member val Kyori : int16 = kyori
member val Kishumei : string = kishumei
member val Futan : decimal = futan
member val Chakujun : int16 = chakujun
member val Ninki : int16 = ninki
member val Tosu : int16 = tosu
member val Odds : decimal = odds
member val Time : decimal = time
member val Agari3F : decimal = agari3f
member val Shiba : string = shiba
member val Dirt : string = dirt
member val Aite : string = aite
member val Timesa : decimal = timesa

次に馬1頭分の情報を持つクラス。

module SharedHorseInfoPrinter.HorseInfo
open SharedHorseInfoPrinter.HorseRaceInfo;
type HorseInfo(kettoTorokuBango,bamei,seibetsu,moshoku,seinengappi,shozoku,chokyoshi,sire,broodmare,bms,race) =
member val KettoTorokuBango : string = kettoTorokuBango
member val Bamei : string = bamei
member val Seibetsu : string = seibetsu
member val Moshoku : string = moshoku
member val Seinengappi : System.DateTime = seinengappi
member val Shozoku : string = shozoku
member val Chokyoshi : string = chokyoshi
member val Sire : string = sire
member val Broodmare : string = broodmare
member val BMS : string = bms
member val Race : list<HorseRaceInfo> = race

RDBMSから情報を取得して、上記HorseRaceInfo、HorseInfoを生成するクラス。SQLダラダラなのとreaderからの取得箇所がダサいのがなんとかならないかなぁ…

module SharedHorseInfoPrinter.HorseInfoCreator
open System.Data.Common;
open SharedHorseInfoPrinter.HorseRaceInfo;
open SharedHorseInfoPrinter.HorseInfo;
type HorseInfoCreator(conn : DbConnection) =
let getCommand (conn : DbConnection) sql paramName =
let command = conn.CreateCommand()
command.CommandText <- sql
let param = command.CreateParameter()
param.ParameterName <- paramName
ignore (command.Parameters.Add(param))
command.Prepare()
command
let paramName = @"KettoTorokuBango"
let sqlHorse =
@"select ketto_toroku_bango,bamei,b.content,c.content," +
@"seinengappi,d.content,chokyoshimei_ryakusho,ketto1_bamei," +
@"ketto2_bamei,ketto5_bamei " +
@"from jvd_kyosoba_master a,jvd_seibetsu_code b," +
@"jvd_moshoku_code c,jvd_tozai_shozoku_code d " +
@"where ketto_toroku_bango=@KettoTorokuBango " +
@"and a.seibetsu_code=b.code " +
@"and a.moshoku_code=c.code " +
@"and a.tozai_shozoku_code=d.code"
let sqlRace =
@"select a.kaisai_nengappi,b.content,c.kyosomei_ryakusho_6," +
@"d.content,e.content,f.content,c.kyori,a.kishumei_ryakusho," +
@"a.futan_juryo,a.kakutei_chakujun,a.tansho_ninkijun,c.shusso_tosu," +
@"a.tansho_odds,a.soha_time,a.kohan_3f,g.content,h.content," +
@"a.aite1_bamei,a.time_sa " +
@"from jvd_umagoto_race_joho a,jvd_keibajo_code b," +
@"jvd_race_shosai c,jvd_kyoso_joken_code d,jvd_grade_code e," +
@"jvd_track_code f,jvd_babajotai_code g,jvd_babajotai_code h " +
@"where ketto_toroku_bango=@KettoTorokuBango " +
@"and c.data_kubun in ('7','A','B') " +
@"and a.keibajo_code=b.code " +
@"and a.race_code=c.race_code " +
@"and c.kyoso_joken_code_saijakunen=d.code " +
@"and c.grade_code=e.code " +
@"and c.track_code=f.code " +
@"and c.shiba_babajotai_code=g.code " +
@"and c.dirt_babajotai_code=h.code " +
@"order by kaisai_nengappi desc"
let commandHorse = getCommand conn sqlHorse paramName
let commandRace = getCommand conn sqlRace paramName
let rec createRaceInfoList (reader : DbDataReader) =
if reader.Read() then
HorseRaceInfo(reader.GetDateTime(0),reader.GetString(1),
reader.GetString(2),reader.GetString(3),reader.GetString(4),
reader.GetString(5),reader.GetInt16(6),reader.GetString(7),
reader.GetDecimal(8),reader.GetInt16(9),reader.GetInt16(10),
reader.GetInt16(11),reader.GetDecimal(12),
reader.GetDecimal(13),reader.GetDecimal(14),
reader.GetString(15),reader.GetString(16),
reader.GetString(17),reader.GetDecimal(18))
:: createRaceInfoList reader
else
[]
let createRaceInfoList kettoTorokuBango =
commandRace.Parameters.Item(paramName).Value <- kettoTorokuBango
use reader = commandRace.ExecuteReader()
createRaceInfoList reader
member HorseInfoCreator.createHorseInfo kettoTorokuBango =
let raceInfoList = createRaceInfoList kettoTorokuBango
commandHorse.Parameters.Item(paramName).Value <- kettoTorokuBango
use reader = commandHorse.ExecuteReader()
if reader.Read()
then
(Some (
HorseInfo(
reader.GetString(0),reader.GetString(1),reader.GetString(2),
reader.GetString(3),reader.GetDateTime(4),reader.GetString(5),
reader.GetString(6),reader.GetString(7),reader.GetString(8),
reader.GetString(9),raceInfoList)))
else
None

最後にエントリポイントとXML出力部分。
※このコードを使うと妙なエンコーディングで出力されるので、この記事にて修正版ソースを記載しています。

module SharedHorseInfoPrinter.Main
open System.Xml;
open SharedHorseInfoPrinter.HorseRaceInfo;
open SharedHorseInfoPrinter.HorseInfo;
open SharedHorseInfoPrinter.HorseInfoCreator;
let getHorseInfoArray conn kettoTorokuBango =
let creator = HorseInfoCreator conn
Seq.map creator.createHorseInfo kettoTorokuBango
let getDateString (x : System.DateTime) = x.ToString(@"yyyy-MM-dd")
let getHorseInfoXml horseInfoArray =
use strWriter = new System.IO.StringWriter()
use writer = new XmlTextWriter(strWriter)
writer.Formatting <- Formatting.Indented
writer.WriteStartDocument()
writer.WriteStartElement(@"HorseList")
Seq.iter (fun (x : HorseInfo option) ->
match x with
| Some y ->
writer.WriteStartElement(@"Horse")
writer.WriteAttributeString(@"KettoTorokuBango", y.KettoTorokuBango)
writer.WriteAttributeString(@"Bamei", y.Bamei)
writer.WriteAttributeString(@"Seibetsu", y.Seibetsu)
writer.WriteAttributeString(@"Moshoku", y.Moshoku)
writer.WriteAttributeString(@"Seinengappi", getDateString y.Seinengappi)
writer.WriteAttributeString(@"Shozoku", y.Shozoku)
writer.WriteAttributeString(@"Chokyoshi", y.Chokyoshi)
writer.WriteAttributeString(@"Sire", y.Sire)
writer.WriteAttributeString(@"Broodmare", y.Broodmare)
writer.WriteAttributeString(@"BMS", y.BMS)
writer.WriteStartElement(@"Races")
Seq.iter (fun (z : HorseRaceInfo) ->
writer.WriteStartElement(@"Race")
writer.WriteAttributeString(@"Kaisainengappi", getDateString z.Kaisainengappi)
writer.WriteAttributeString(@"Keibajo", z.Keibajo)
writer.WriteAttributeString(@"Name", z.Name)
writer.WriteAttributeString(@"Joken", z.Joken)
writer.WriteAttributeString(@"Grade", z.Grade)
writer.WriteAttributeString(@"Track", z.Track)
writer.WriteAttributeString(@"Kyori", z.Kyori.ToString())
writer.WriteAttributeString(@"Kishumei", z.Kishumei)
writer.WriteAttributeString(@"Futan", z.Futan.ToString())
writer.WriteAttributeString(@"Chakujun", z.Chakujun.ToString())
writer.WriteAttributeString(@"Ninki", z.Ninki.ToString())
writer.WriteAttributeString(@"Tosu", z.Tosu.ToString())
writer.WriteAttributeString(@"Odds", z.Odds.ToString())
writer.WriteAttributeString(@"Time", z.Time.ToString())
writer.WriteAttributeString(@"Agari3F", z.Agari3F.ToString())
writer.WriteAttributeString(@"Shiba", z.Shiba)
writer.WriteAttributeString(@"Dirt", z.Dirt)
writer.WriteAttributeString(@"Aite", z.Aite)
writer.WriteAttributeString(@"Timesa", z.Timesa.ToString())
writer.WriteEndElement()
) y.Race
writer.WriteEndElement()
writer.WriteEndElement()
| None -> ()
) horseInfoArray
writer.WriteEndDocument()
strWriter.ToString()
[<EntryPoint>]
let main argv =
use conn = new MySql.Data.MySqlClient.MySqlConnection
@"userid=XXX;password=XXX;database=XXX;Host=XXX"
conn.Open()
getHorseInfoArray conn argv |> getHorseInfoXml |> printfn "%s"
0 // 整数の終了コードを返します

これを使うとこんな感じの出力が出ます。

<?xml version="1.0" encoding="utf-16"?>
<HorseList>
<Horse KettoTorokuBango="2012104639" Bamei="ヴァンガードシチー" Seibetsu="牡" Moshoku="黒鹿毛" Seinengappi="2012-02-16" Shozoku="美浦" Chokyoshi="古賀慎明" Sire="シンボリクリスエス" Broodmare="シャイニングスルー" BMS="スペシャルウィーク">
<Races>
<Race Kaisainengappi="2015-03-22" Keibajo="中山" Name="" Joken="未勝利" Grade="" Track="芝・右" Kyori="2000" Kishumei="北村宏司" Futan="56.0" Chakujun="2" Ninki="4" Tosu="16" Odds="4.9" Time="121.2" Agari3F="35.9" Shiba="良" Dirt="" Aite="ウイングチップ" Timesa="0.2" />
<Race Kaisainengappi="2015-02-21" Keibajo="東京" Name="" Joken="未勝利" Grade="" Track="芝・左" Kyori="1800" Kishumei="後藤浩輝" Futan="56.0" Chakujun="4" Ninki="9" Tosu="16" Odds="15.9" Time="110.8" Agari3F="34.6" Shiba="良" Dirt="" Aite="メローハーモニー" Timesa="0.6" />
<Race Kaisainengappi="2014-11-29" Keibajo="東京" Name="" Joken="未勝利" Grade="" Track="芝・左" Kyori="2000" Kishumei="後藤浩輝" Futan="55.0" Chakujun="12" Ninki="3" Tosu="16" Odds="7.4" Time="123.5" Agari3F="36.9" Shiba="良" Dirt="" Aite="タンタアレグリア" Timesa="1.5" />
<Race Kaisainengappi="2014-11-23" Keibajo="東京" Name="" Joken="未勝利" Grade="" Track="芝・左" Kyori="1800" Kishumei="後藤浩輝" Futan="55.0" Chakujun="2" Ninki="1" Tosu="18" Odds="2.2" Time="110.5" Agari3F="34.6" Shiba="良" Dirt="" Aite="キングイマデショウ" Timesa="0.1" />
<Race Kaisainengappi="2014-10-25" Keibajo="東京" Name="" Joken="新馬" Grade="" Track="芝・左" Kyori="1800" Kishumei="北村宏司" Futan="55.0" Chakujun="3" Ninki="4" Tosu="16" Odds="12.9" Time="110.5" Agari3F="34.4" Shiba="良" Dirt="" Aite="サトノクラウン" Timesa="0.5" />
</Races>
</Horse>
<Horse KettoTorokuBango="2012104647" Bamei="アルギュロス" Seibetsu="牝" Moshoku="黒鹿毛" Seinengappi="2012-03-26" Shozoku="栗東" Chokyoshi="池江泰寿" Sire="マンハッタンカフェ" Broodmare="シルヴァースカヤ" BMS="Silver Hawk">
<Races />
</Horse>
</HorseList>

これをXSLTでHTML化する予定。

 - プログラミング