ボートレース競走結果 収集クローラ作成④ ~データベースアクセス~

概要

 このブログでは、スクレイピングでとってきたデータをデータベースに登録する部分について説明する。
 今回は、MySQLRubymysqlライブラリを用いてアクセスした。
クローラの全体像については、こちらを参照

なぜMySQLを選んだか

 MySQLを選んだ理由は、まず金銭的に厳しいので、OSSを使用することを決めた。
 次に、ボートレースの結果のようなレース結果、選手データ、直前情報などのたくさんの種類の複雑な情報を扱うのに適していると考え、RDBMSを選択した。
 最終的にMySQLPostgreSQLで、迷ったが、過去に使用した経験があったためとっつきやすかったためMySQLを使用することに決めた。

db-study.com academy.gmocloud.com

データベース定義

race_idを主キーにした。 race_idは単純に開催場の番号*1、ラウンド、日時を結合しただけ。また第3正規形にした。
 round_infoは、そのラウンド固有の水面天候状況、払い戻しなどの情報をを格納する。race_infoは、それぞれの艇のレース結果を格納する設計になっている。

/*use test;*/
use boat;

create table round_info(
        race_id varchar(12) not null,/*place + round_no + day*/
        primary key(race_id),
        place varchar(8),
        round_no int,
        day varchar(10),
        
        /*気象条件*/
        temp float(3,1),
        sky varchar(8),
        wind int,
        water_temp float(3,1),
        wave int,

        /*払い戻し*/
        3tan_kumi varchar(10),
        3tan_money int,
        3tan_pop int,
        3puku_kumi varchar(10),
        3puku_money int,
        3puku_pop int,
        2tan_kumi varchar(10),
        2tan_money int,
        2tan_pop int,
        2puku_kumi varchar(10),
        2puku_money int,
        2puku_pop int,
        kaku1_kumi varchar(10),
        kaku1_money int,
        kaku1_pop int,
        kaku2_kumi varchar(10),
        kaku2_money int,
        kaku2_pop int,
        kaku3_kumi varchar(10),
        kaku3_money int,
        kaku3_pop int,
        tan_kumi varchar(10),
        tan_money int,
        fuku1_kumi varchar(10),
        fuku1_money int,
        fuku2_kumi varchar(10),
        fuku2_money int, 

        /*決まり手*/
        kimarite varchar(12),

        /*返還*/
        return_money varchar(1)

) engine=InnoDB;

create table race_info(
        race_id varchar(12),
        boat_no varchar(1),
        primary key(race_id,boat_no),
        race_rank varchar(1),
        racer_no varchar(4),
        race_time varchar(8),
        course varchar(1),
        st_time varchar(4),

        foreign key(race_id) references round_info(race_id)
) engine=InnoDB;

データベースアクセス

スクレイピングでとってきたデータを成型してデータベースに入れていく。
冗長なので、一例を記載している。

#データベースアクセス
connection = Mysql::connect("localhost", "username", "password", "databasename")

# 文字コードをUTF8に設定
connection.query("set character set utf8")

.
.

puts("insert into race_info(race_id, boat_no, race_rank, racer_no, race_time, course, st_time) values(\"#{query[1]+query[0]+query[2]}\", \"#{boat1[1]}\", \"#{boat1[0]}\", \"#{boat1[2]}\", \"#{boat1[5]}\", \"#{course[0]}\", \"#{st_time[0]}\" )")

.
.

# コネクションを閉じる
connection.close

手間取ったところ

データを挿入するのにとても長いSQL文を書いた。
これを書いてちゃんとデータがあってるか確認するのが大変だった。

connection.query("insert into round_info(race_id, place, round_no, day, temp, sky, wind, water_temp, wave, kimarite, return_money, 3tan_kumi, 3tan_money, 3tan_pop, 3puku_kumi, 3puku_money, 3puku_pop, 2tan_kumi, 2tan_money, 2tan_pop, 2puku_kumi, 2puku_money, 2puku_pop, kaku1_kumi, kaku1_money, kaku1_pop, kaku2_kumi, kaku2_money, kaku2_pop, kaku3_kumi, kaku3_money, kaku3_pop, tan_kumi, tan_money, fuku1_kumi, fuku1_money, fuku2_kumi, fuku2_money)  
values(\"#{query[1]+query[0]+query[2]}\", \"#{prace_name(query[1])}\", #{query[0].to_i}, \"#{query[2]}\", #{weather_s[0]}, \"#{weather_s[1]}\", #{weather_s[2]}, #{weather_s[3]}, #{weather_s[4]}, \"#{win_tec[0]}\", \"#{return_money[0]}\", \"#{p3tan[1]}\", #{p3tan[2].to_i}, #{p3tan[3].to_i}, \"#{p3puku[1]}\", #{p3puku[2].to_i}, #{p3puku[3].to_i}, \"#{p2tan[1]}\", #{p2tan[2].to_i}, #{p2tan[3].to_i}, \"#{p2puku[1]}\", #{p2puku[2].to_i}, #{p2puku[3].to_i}, \"#{pkaku[1]}\", #{pkaku[2].to_i}, #{pkaku[3].to_i}, \"#{pkaku[4]}\", #{pkaku[5].to_i}, #{pkaku[6].to_i}, \"#{pkaku[7]}\", #{pkaku[8].to_i}, #{pkaku[9].to_i}, \"#{ptan[1]}\", #{ptan[2].to_i}, \"#{ppuku[1]}\", #{ppuku[2].to_i}, \"#{ppuku[3]}\", #{ppuku[4].to_i} )")

*1:ボートレースの開催場には、電話投票用に番号が振られている。