スクレイピングしMYSQLに書き込む方法を聞きたいです。


http://q.hatena.ne.jp/1434818671 続き。

スクレイピングに関して、ご回答が大変参考になりました。
phpも捨てきれていませんが、a-kuma3さんのご回答でrubyを考えています。

a-kuma3さんのご回答の続きで、
1.MYSQLに書き込むこと、
2.新たに更新されたデータを書き込むこと、
を知りたいです。

問題は2.で、書き込まれたデータベースのデータとリンク先で新たに変更されたデータを追加で書き込む方法はどうしようかと。
イメージがわかないのですが、見比べる方法が必要なのでしょうか。

回答の条件
  • 1人30回まで
  • 登録:2015/07/05 15:17:48
  • 終了:2015/08/04 02:29:14

ベストアンサー

id:TransFreeBSD No.1

TransFreeBSD回答回数665ベストアンサー獲得回数2672015/07/06 23:09:22

ポイント500pt

イメージがわかないのですが、見比べる方法が必要なのでしょうか。

なければ登録、という時はどうしても既にあるかどうかというチェックは必要になります。

もっとも厳密であろう方法はすべての項目を比較というか、一致するレコードが存在するか確認する方法です。
http://mislead.jp/1025.html
上記はPHPですがざっくりとはこんな感じかなと。ただ、値は直じゃなくプレースホルダを使います。
http://d.hatena.ne.jp/takehikom/20080623/1214170792
https://rubyist.g.hatena.ne.jp/yumimue/20071031/1193816175
rubyで詳しくだとこんな感じ?

SELECT * FROM テーブル名 WHERE カラム1=? AND カラム2=? AND ...

これで有無を判定して、なければinsertで追加。
ただ、これ1秒に何度も呼ばれる可能性のあるWebアプリとかだと重いのでどうにかして避ける事を考えます。
今回はレコード数も何万とあるわけではないし、cronでたまに回すだけだから、負荷は上がっても多分大きな問題は起きない気がします。

とはいえせっかくだし、DBには便利に使える仕組みがあるのでそれ使う方法も考えてみます。
http://q.hatena.ne.jp/1434818671#ac113090
この辺でa-kuma3さんが書いてますが「プライマリキー」というのが何度か出てきます。
これはDBに登録された1件1件の記録(レコード)を特定するための項目を差します。

質問データは、質問番号がプライマリキーなんですけど

このばあい、1434818671とか1436077068とかが質問番号で、これさえあれば人力検索での質問が特定出来ます。
つまり、全ての項目を見なくても同じ質問か分かるし、この番号が既に登録されていれば質問データが登録されている事が分かる、ということです。
ただ、更新がないとか常に最新に更新とかの場合は簡単なんですが、そうでない場合はコメントにあるように工夫が必要になったりします。

さて、今回の場合は、更新があった場合、更新前のデータはそのままに、新たに1件記録するわけですね。
で、問題は何をもって新規とか更新とかを判断するかですが、一番それっぽいのは更新日時ですね。
前提としてまったく同じ日時での更新はないと仮定します。
するとこの日時で登録あれば既に登録されているし、内容が変わったなら別の日時になる、という事になります。
ただ、それは「まったく同じ日時での更新はないと仮定」した場合です。もしこれがあると同じ日時の記録は同じ内容と判断されどれか一つだけ記録される事になります。
それを回避するには更新日時に加えて他の項目も加えます。といっても今回の場合、確実にそう保証出来るものはないというか、結局は全部比較して……みたいな話になってしまいますので、妥協点として発生日時+更新日時もしくは発生日時+都県名+更新日時でどうかな?って思います。

これらの項目をプライマリキーにしてやることで、それらが重複したものは記録で着なくなる=単純にinsertしてプライマリキーによるエラーは無視すればOKになります。
プライマリキーについては以下とかでどうでしょう。
http://northqra.com/table_3.html
http://mysql.akarukutanoshiku.com/category6/entry30.html
複数カラムにも設定出来ます。
http://www.dbonline.jp/mysql/table/index8.html

というような所でどうでしょうか。


以下予談
上記で妥協があったり、スクレイピングとかの場合、書式が変わったりエラーになったりで記録できていない事はよくあります。
恒久的な事もありますが、例外的な記述のために数日間だけごっそりない、元データは既に更新済みとか、cronで回してるとありがちです。
なので、容量に余裕があれば、取ってきたページ内容をhtmlのまま、それ様テーブルに記録しておくとかするといざと言うとき助かります。
#その時は取得日時も記録して90日とか容量にあわせ一定期間で消しておいたほうが良いです。
#それで容量食いつぶして本来の記録がエラーになるとか本末転倒なんで。
あと、最初のうちは何件登録したとか概要を標準出力に出すようにして、メールで確認しとくとか。

他1件のコメントを見る
id:a-kuma3

ちょろちょろ見てた感じだと、停電理由くらいしか更新されないのかな、という気はしますが、もしかすると地区の変更があるのかも。
という感じだったので、プライマリキーはこんな感じでしょうか。

  • 発生日時(発生日時、復旧日時を <BR> で分割する)
  • 都県名
  • 市区町村名
  • 更新日時

上書きする前のデータをしばらくプールしておく、というのは大いに賛成です。

2015/07/13 14:36:11
id:FREEz

あああ 明日忘れて間に合わないといけないから今のうちに先に質問閉じておかなきゃと思ったら、ポイントさらに追加できるのを忘れていた、、
調べているのだが、どうしてはてなポイントを通常で贈れないのだろう、、

2015/08/04 02:45:56

その他の回答(0件)

id:TransFreeBSD No.1

TransFreeBSD回答回数665ベストアンサー獲得回数2672015/07/06 23:09:22ここでベストアンサー

ポイント500pt

イメージがわかないのですが、見比べる方法が必要なのでしょうか。

なければ登録、という時はどうしても既にあるかどうかというチェックは必要になります。

もっとも厳密であろう方法はすべての項目を比較というか、一致するレコードが存在するか確認する方法です。
http://mislead.jp/1025.html
上記はPHPですがざっくりとはこんな感じかなと。ただ、値は直じゃなくプレースホルダを使います。
http://d.hatena.ne.jp/takehikom/20080623/1214170792
https://rubyist.g.hatena.ne.jp/yumimue/20071031/1193816175
rubyで詳しくだとこんな感じ?

SELECT * FROM テーブル名 WHERE カラム1=? AND カラム2=? AND ...

これで有無を判定して、なければinsertで追加。
ただ、これ1秒に何度も呼ばれる可能性のあるWebアプリとかだと重いのでどうにかして避ける事を考えます。
今回はレコード数も何万とあるわけではないし、cronでたまに回すだけだから、負荷は上がっても多分大きな問題は起きない気がします。

とはいえせっかくだし、DBには便利に使える仕組みがあるのでそれ使う方法も考えてみます。
http://q.hatena.ne.jp/1434818671#ac113090
この辺でa-kuma3さんが書いてますが「プライマリキー」というのが何度か出てきます。
これはDBに登録された1件1件の記録(レコード)を特定するための項目を差します。

質問データは、質問番号がプライマリキーなんですけど

このばあい、1434818671とか1436077068とかが質問番号で、これさえあれば人力検索での質問が特定出来ます。
つまり、全ての項目を見なくても同じ質問か分かるし、この番号が既に登録されていれば質問データが登録されている事が分かる、ということです。
ただ、更新がないとか常に最新に更新とかの場合は簡単なんですが、そうでない場合はコメントにあるように工夫が必要になったりします。

さて、今回の場合は、更新があった場合、更新前のデータはそのままに、新たに1件記録するわけですね。
で、問題は何をもって新規とか更新とかを判断するかですが、一番それっぽいのは更新日時ですね。
前提としてまったく同じ日時での更新はないと仮定します。
するとこの日時で登録あれば既に登録されているし、内容が変わったなら別の日時になる、という事になります。
ただ、それは「まったく同じ日時での更新はないと仮定」した場合です。もしこれがあると同じ日時の記録は同じ内容と判断されどれか一つだけ記録される事になります。
それを回避するには更新日時に加えて他の項目も加えます。といっても今回の場合、確実にそう保証出来るものはないというか、結局は全部比較して……みたいな話になってしまいますので、妥協点として発生日時+更新日時もしくは発生日時+都県名+更新日時でどうかな?って思います。

これらの項目をプライマリキーにしてやることで、それらが重複したものは記録で着なくなる=単純にinsertしてプライマリキーによるエラーは無視すればOKになります。
プライマリキーについては以下とかでどうでしょう。
http://northqra.com/table_3.html
http://mysql.akarukutanoshiku.com/category6/entry30.html
複数カラムにも設定出来ます。
http://www.dbonline.jp/mysql/table/index8.html

というような所でどうでしょうか。


以下予談
上記で妥協があったり、スクレイピングとかの場合、書式が変わったりエラーになったりで記録できていない事はよくあります。
恒久的な事もありますが、例外的な記述のために数日間だけごっそりない、元データは既に更新済みとか、cronで回してるとありがちです。
なので、容量に余裕があれば、取ってきたページ内容をhtmlのまま、それ様テーブルに記録しておくとかするといざと言うとき助かります。
#その時は取得日時も記録して90日とか容量にあわせ一定期間で消しておいたほうが良いです。
#それで容量食いつぶして本来の記録がエラーになるとか本末転倒なんで。
あと、最初のうちは何件登録したとか概要を標準出力に出すようにして、メールで確認しとくとか。

他1件のコメントを見る
id:a-kuma3

ちょろちょろ見てた感じだと、停電理由くらいしか更新されないのかな、という気はしますが、もしかすると地区の変更があるのかも。
という感じだったので、プライマリキーはこんな感じでしょうか。

  • 発生日時(発生日時、復旧日時を <BR> で分割する)
  • 都県名
  • 市区町村名
  • 更新日時

上書きする前のデータをしばらくプールしておく、というのは大いに賛成です。

2015/07/13 14:36:11
id:FREEz

あああ 明日忘れて間に合わないといけないから今のうちに先に質問閉じておかなきゃと思ったら、ポイントさらに追加できるのを忘れていた、、
調べているのだが、どうしてはてなポイントを通常で贈れないのだろう、、

2015/08/04 02:45:56
id:FREEz

質問者から

FREEz2015/07/15 08:06:18

返答が遅れて申し訳なかったです。体調を崩しておりました。
引き続き勉強を続けていきたいと思っていますので、よろしくお願いします。

Rubyによるクローラー開発技法 巡回・解析機能の実装と21の運用例
http://www.amazon.co.jp/gp/product/4797380357?ref_=cfb_at_prodpg 
この本を購入しました。
高いだけありかなりいい本ですが、若干使い勝手は違うか、、
phpなども多そうなので、良い本があれば教えて頂きたいですね。


追加:はてなってポイントを贈呈するにはどうしたらいいんですか;;
あれIEだから悪いのかな、、ポイントも星も贈れないのだが。
質問中は終わったら贈れるものかと思っていたが、全然ダメだなぁ。

  • id:tezcello
    > phpも捨てきれていませんが
    PHP しか分かりませんのでコメント欄へ


    肝心なのは、どのようにしてDB挿入用の配列データを生成するかだと思います。
    (前回の回答ではキチンと位置出しするのが面倒で実際には使えないようですね)

    function getData()
    {
    $result = array();
    // 何度もアクセスするのは申し訳ないのでローカルに保存したファイル名を
    // 指定しました。運用時は URL に変更してください。
    $src = file_get_contents('./day003-j.html');
    $src = mb_convert_encoding($src,'UTF-8', 'sjis-win');
    $src = str_replace("\r\n","\n", $src);
    // 抜き出したいデータ部分を特定
    // ソース中の最初の tbody 部だと仮定
    if (preg_match('!<tbody>(.+?)</tbody>!s', $src, $mch)) {
    $tbody = $mch[1];
    } else {
    throw new \UnexpectedValueException('no match "tbody"');
    }
    // 表の各行のデータを抽出
    if (preg_match_all('!<tr>(.+?)</tr>!s', $tbody, $mch)) {
    $rows = $mch[1];
    } else {
    throw new \UnexpectedValueException('no match "tr"');
    }

    // 下準備
    $ary = array(); // 1行分のデータ一時保持用変数
    $span = array(); // 各列の rowspan の値を保持する変数

    // 各行ごとに処理する
    foreach ($rows as $row) {
    // rowspan の値と列データを抽出
    if (preg_match_all('!<td(?:.*?)(?:rowspan="([^"]+)")?>(.+?)</td>!us', $row, $mch)) {
    // rowspan の次の行は列データの数が少なくなるので
    // これを頼りに処理を分ける
    if (count($ary) > count($mch[2])) {
    // extra row
    // rowspan の値を調べつつ処理する
    foreach ($span as $i=>&$sp) {
    // rowspan が 2 未満の列はこの行で新しく値がセットされる
    if ($sp-- < 2) {
    $ary[$i] = array_shift($mch[2]) ?: ''; // td の数が合わない場合は空だとする
    // この行から新しく rowspan が始まる場合の対応
    $s = array_shift($mch[1]) ? : 1;
    if ($s > 1) {
    $sp = $s;
    }
    }
    }
    } else {
    // normal row
    $ary = $mch[2];
    $span = $mch[1];
    }
    $result[] = $ary;

    } else {
    throw new \UnexpectedValueException('no match "td"');
    }
    }
    return $result;
    }

    これで、DB挿入用の2次元配列が得られます。
    それを foreach() で回してプリペアドステートメントを実行すればよいでしょう。

    DB挿入時は、何をもって更新と判断するかが問題でしょう。
    地域が拡大するかもしれませんし、復旧時間も訂正されるかも知れませんよね?
    全く同じもの以外は全て挿入しておいて、目視でチェックするのが確実かも知れません。

    それなら全てのカラムを結合したものがユニークであるように制約をつければどこか一つでも違わないと挿入できないのでお手軽だと思います。
    (制約に反する場合は ignore で無視すればOKだと)

    全列データを連結してハッシュをとり、それをユニーク制約がついた列に収めるようにするのがあるべき姿かもしれません。


    ここまで書いてきましたが、「停電地域」が rowspan で書かれている場合、それらを別の行としてDBに挿入すると「停電件数」が合算なのか個別なのか区別できなくなりませんか?
    件数に意味が無ければ問題視する必要もないでしょうが。
  • id:FREEz
    わざわざありがとうございました。
    phpでも全然かまいません。助かります。
    むしろ、回答頂いてなるほどという視野が広がっても、
    どうも時間が取れなくて、自分の手を進められていないことがかなりもどかしい状況でして、、
    最長設定にしたのに、困ったなぁ。

    バックアップというほど大事でなくてもテキストで毎日保存して置くだけでも全然違うので、それをまたローカル環境でスクレイピングでチェックするのもいいと思いました。
    ただ、チェックするのがそんなに多くないので、そこはちゃんと保存されているかを確認する点を踏まえてもひとつずつ自力でやる方がいい気もします。
    スクレイパーは許せないのかもしれませんがw
    まずデータベースに書き込むのを作らないとなぁ、と。とりあえずノーチェックでまず書き込んで、それでいいかな。
    検索できればいいので。
    たとえば、理想で言うなら最初の書き込みにはAという値を追加で付加して、後で変化が見られたらそこを変更ではなくBとして新しく追加して、AとBとが並んでいていいと思うんです。
    まぁ、エクセルとかのように統計で数値化して調べるならダメなのでしょうが、それが目的ではなく人の目で判断したいので。
    しかしまぁ、時間取れないなら自らの勉強は後にしていっそ有料で誰かに依頼するかとか考えたが、訊いて高いのが怖くてそちらにいけないという、、w

  • id:tezcello
    > ローカル環境でスクレイピング
    デバッグで何度もアクセスするのは気が引けるのでこのようにしました。
    リブラハック事件もあり、頻繁にアクセスするのが怖いので。

    簡単に試すなら SQLite がお手軽なのでその例で示します。
    MySQL なら、 DSN と SQL を少々変更するだけで良いでしょう。
    __SQL の OR が多分不要だと思う

    hash を求めてそれを主キーにする事にします。
    その為、先の関数の一部を下記に変更します。
    $result[] = $ary;

    $rowValue = implode(',', $ary);
    $hash = hash('sha256', $rowValue);
    $result[] = explode(',', $hash.','.$rowValue);


    テーブルはこんな感じ。
    CREATE TABLE blackouts
    (
    hash TEXT NOT NULL PRIMARY KEY,
    occurAndRestored TEXT NOT NULL,
    pref TEXT NOT NULL,
    municipal TEXT NOT NULL,
    district TEXT NOT NULL,
    scale TEXT NOT NULL,
    reason TEXT NOT NULL,
    renewed TEXT NOT NULL
    )


    try{
    $dbh = new PDO('sqlite:D:/web/hatena/hatena.sqlite', null, null);
    $dbh->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
    $sth = $dbh->prepare('INSERT OR IGNORE INTO blackouts VALUES (?,?,?,?,?,?,?,?)');
    foreach (getData() as $row) {
    $sth->execute($row);
    }

    } catch (PDOException $e) {
    echo $e->getMessage();
    } catch (UnexpectedValueException $e) {
    echo $e->getMessage();
    } catch (ErrorException $e) {
    echo $e->getMessage();
    }
  • id:FREEz
    さらにありがとうございました。perlとrubyだけでphpが自前に入っていなかったので、環境整えて試してみたいと思います。

    まとめて抜いてくることに比べて、それぞれに分けることがこんなに大変とは思いませんでした。

    SQLiteは携帯内部のデータベースと思っていたのですが、携帯の仕様はまた新しいやつになったんですね。
    そのため小さいイメージがあったのですが、調べた感じそこまで良いとも悪いともないのですが、、
    スクレイピングだとSQLiteの方がいい理由があるのでしょうか、、
    LAMP環境の名残?でMYSQLを使おうとしていますが。

    あれこれ繋がっているのかな、、
    レンタルサーバの自分の領域でも試したいけど、まずは自前のPCで、、、
    ぅーん、一つずつ勉強してもオンライン完結への道は遠そうだ、、手作業で更新した方が早いかなw


  • id:tezcello
    MySQL を使われるのが分かっていたのに SQLite で回答したのはただの手抜きですごめんなさい。
    MySQL を使うとDBを作ってユーザに権限を割り当てて...削除もあれもこれもだったり...で面倒だったのが正直なところです。

    最近の PHP には SQLite が同梱されているハズですので、利用環境を選ばないし、メインテナンスは楽だし、それなりのパフォーマンスは出るのでもっと使われてもいいと思うのですけど。


    > 手作業で更新した方が早い
    状況判断をロジックにできない場合はそうなります。
    その場合でもある程度まで出来るのであれば、最終判断だけ目視でやるのが現実的かと。
  • id:TransFreeBSD
    >手作業で更新した方が早いかなw
    そいうのを防ぐのがAPIの役割の一つですね。
    こういうのもAPI化してくれるとありがたいけど、表の雰囲気から、元データはエクセル?とか思ってみたり。
  • id:FREEz
    なるほど、ではこれに対応できれば他のデータでもスクレイピング対応できるかもしれませんね。
    エクセル→ウェブは一般的でしょうし、、
    むしろそういうのは既にありそうだが、、
    エクセルからスクレイピングは見かけましたが、ウェブに上がるから大変なのかな。
    確かに、統一してくれると助かりますね。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません