What's New Qiita

20190607のMySQLに関する記事は3件です。

  • 投稿日：2019-06-07T20:58:02+09:00

500 エラーのしらべかた【DB Lock 編】

株式会社オズビジョンのユッコ (@terra_yucco) です。
最近一部の API でたまーに 500 が出ていましたが、今日晴れて原因を特定できたので、そこまでの過程をメモ書きしていこうと思います。

※タイトルでネタバレしていますが、原因は DB の Lock にありました。

きっかけ

監視及びユーザさんからのお問い合わせで、特定の API が HTTP レスポンスコードに 500 を返しているらしいことがわかりました。
ただし、その API 自体はログなどは残しておらず、どこでどんな原因で 500 になったのかは特定ができていませんでした。

1) とりあえず救う

第 1 フェーズでは、とりあえず別処理でリトライさせることで救うことに。

2) とりあえずエラーを特定する

FW が出力していたログと、導入した New Relic で、原因がある程度絞り込めました。

FW のログ

ERROR - 2019-06-07 02:19:37 --> Query error: Lock wait timeout exceeded; try restarting transaction

New Relic

APM > 該当アプリ > EVENTS - Errors

image.png

この Message のリンク先で、実際のエンドポイントがアクセスログと一致していることも確認できました。
また、特定の処理ではなく、共通部分 (insert をするところ) でエラーになっていることを確認できました。

3) 原因を調べる

定期的に show processlist を保存するようなことはしていましたが、それだけではロック先とロック主を正確には特定できないので、以下のような処理を一時的に書きました。
※今から思えば両テーブルを JOIN すればよかった

investigation.sh
#!/bin/bash -u
while [ 1 ]; do

mysql --default-character-set=utf8 --verbose \
  -h <endpoint> \
  -u <user> -p<pass> -D information_schema \
  -e"set session time_zone = 'asia/tokyo'; select now(); select * from innodb_trx\G select * from innodb_lock_waits; select now();" >> /path/to/log/$( date +%Y%m%d_investigation.log )

sleep 300
done

この処理でずっと記録を取るつもりでしたが、落ちた時のことを考えて cron を以下のように設定しました。
flock --timeout=1 は、利用 flock の ver が 0 に対応していなかったための設定で、多重起動をお手軽に抑止するために使いました。

crontab
*/5 * * * *     flock --timeout=1 /path/to/lock /path/to/script/investigation.sh

4) クエリを特定する

ログ

以下のようなログが取得できました。

--------------
set session time_zone = 'asia/tokyo'
--------------

--------------
select now()
--------------

now()
2019-06-07 11:42:35
--------------
select * from innodb_trx
--------------

*************************** 1. row ***************************
                    trx_id: 25511719128
                 trx_state: LOCK WAIT
               trx_started: 2019-06-07 02:42:18
     trx_requested_lock_id: 25511719128:44:952896:791
          trx_wait_started: 2019-06-07 02:42:18
                trx_weight: 3
       trx_mysql_thread_id: 4979812
                 trx_query: INSERT INTO table_A (<columns>) VALUES (<values>)
       trx_operation_state: inserting
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 376
           trx_rows_locked: 1
         trx_rows_modified: 1
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
                    trx_id: 25511719119
                 trx_state: LOCK WAIT
               trx_started: 2019-06-07 02:42:18
     trx_requested_lock_id: 25511719119:44:952896:791
          trx_wait_started: 2019-06-07 02:42:18
                trx_weight: 3
       trx_mysql_thread_id: 4980011
                 trx_query: INSERT INTO table_A (<columns>) VALUES (<values>)
       trx_operation_state: inserting
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 376
           trx_rows_locked: 1
         trx_rows_modified: 1
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 3. row ***************************
                    trx_id: 25511719100
                 trx_state: LOCK WAIT
               trx_started: 2019-06-07 02:42:18
     trx_requested_lock_id: 25511719100:44:952896:791
          trx_wait_started: 2019-06-07 02:42:18
                trx_weight: 3
       trx_mysql_thread_id: 4979976
                 trx_query: INSERT INTO table_A (<columns>) VALUES (<values>)
       trx_operation_state: inserting
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 376
           trx_rows_locked: 1
         trx_rows_modified: 1
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 4. row ***************************
                    trx_id: 25511719086
                 trx_state: LOCK WAIT
               trx_started: 2019-06-07 02:42:18
     trx_requested_lock_id: 25511719086:44:952896:791
          trx_wait_started: 2019-06-07 02:42:18
                trx_weight: 3
       trx_mysql_thread_id: 4979714
                 trx_query: INSERT INTO table_A (<columns>) VALUES (<values>)
       trx_operation_state: inserting
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 376
           trx_rows_locked: 1
         trx_rows_modified: 1
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 5. row ***************************
                    trx_id: 25511716742
                 trx_state: LOCK WAIT
               trx_started: 2019-06-07 02:42:08
     trx_requested_lock_id: 25511716742:44:952896:791
          trx_wait_started: 2019-06-07 02:42:08
                trx_weight: 3
       trx_mysql_thread_id: 4979817
                 trx_query: INSERT INTO table_A (<columns>) VALUES (<values>)
       trx_operation_state: inserting
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 376
           trx_rows_locked: 1
         trx_rows_modified: 1
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 6. row ***************************
                    trx_id: 25511667859
                 trx_state: RUNNING
               trx_started: 2019-06-07 02:40:38
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 557234
       trx_mysql_thread_id: 4974505
                 trx_query: delete from table_A where a_date < DATE_ADD(NOW(),INTERVAL -180 DAY) and status = 1 order by id limit 500
       trx_operation_state: fetching rows
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 556894
     trx_lock_memory_bytes: 376
           trx_rows_locked: 49379043
         trx_rows_modified: 340
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
--------------
select * from innodb_lock_waits
--------------

requesting_trx_id       requested_lock_id       blocking_trx_id blocking_lock_id
25511719128     25511719128:44:952896:791       25511667859     25511667859:44:952896:791
25511719119     25511719119:44:952896:791       25511667859     25511667859:44:952896:791
25511719100     25511719100:44:952896:791       25511667859     25511667859:44:952896:791
25511719086     25511719086:44:952896:791       25511667859     25511667859:44:952896:791
25511716742     25511716742:44:952896:791       25511667859     25511667859:44:952896:791
--------------
select now()
--------------

now()
2019-06-07 11:42:35

クエリの特定

innodb_lock_waits 側を見ると、ロックを要求しているトランザクションは全部で 5 本あります。
トランザクション ID は

  • 25511719128
  • 25511719119
  • 25511719100
  • 25511719086
  • 25511716742

ですが全部同じ 25511667859 にブロックされています。

innodb_trx 側を見ると、待っている側は全て insert で、ブロックしている側は delete でした。
不要なデータを消して全体を軽くするはずの処理が、必要なデータの登録をブロックしてしまうとは... :cold_sweat:

5) 改修

は、まだできていませんが、削除クエリを発行している側の処理をどうにかすれば対処はできそうです。

付記

削除クエリ側は explain をとってみると一応インデックスを使うようになっているので、なぜロックがかかるのだろうと思いましたが、おそらくこのあたりの挙動なのだろうと思います。

一般に、ロック読み取り、UPDATE、または DELETE では、SQL ステートメントの処理時にスキャンされるすべてのインデックスレコード上に、レコードロックが設定されます。 行を除外する WHERE 条件がステートメント内に存在するかどうかは、関係ありません。InnoDB には正確な WHERE 条件が記憶されませんが、スキャンされたインデックスの範囲は認識されます。

Conclusion

  • 500 エラーの原因はきちんと調べるべし
  • UPDATE や DELETE では想定外のロックがかかることがある、心して実装するべし
  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2019-06-07T12:48:14+09:00

Virtual Box+Vagrantを使ってローカル環境で既存のCakePHPアプリを動かす

はじめに

転職先でリリース済みサービスの開発に途中から参加することになり、テスト環境としてローカル環境を作ってアプリを動すために構築した手順を備忘録として残したいと思い投稿しました。

目次

  1. VirtualBox,Vagrantのインストール
  2. Vagrantfileの作成、各種編集
  3. vagrant upの実行
  4. 既存アプリのクローン
  5. Apacheのhttpd.confの編集
  6. MySQLの設定、アプリとの接続
  7. 本番環境DBのダンプ→ローカルDBへリストア
  8. ブラウザで接続確認

手順

1. VirtualBox,Vagrantのインストール

それぞれのHPからOSにあったものをダウンロードします。

Virtual Box↓
https://www.virtualbox.org/
Vagrant↓
https://www.vagrantup.com/

2. Vagrantfileの作成,各種編集

①Vagrantfileの作成

vagrantfileを設置するディレクトリを作成し「vagrant init」で初期化、vagrantfileを作成します。
自分はデスクトップ上でディレクトリを確認したかったので ~/desktop/work/vagrant内にvagrantfileを作成します。（※1）
また、vagrantディレクトリと同じ階層に仮想マシンとホストマシンと共有するための共有ディレクトリ（src）を作成します。（※2）
(※1)work：仮想マシンを複数立ち上げる場合、仮想マシンの数ぶんのディレクトリを作成するので、その大元のディレクトリになります。
(※2)共有ディレクトリ：実際のアプリは仮想マシン内にクローンしますが、共有ディレクトリを設定しておくことでホストマシンにも仮想マシンでクローンしたアプリが共有され、ホストマシンで使い慣れたエディタでアプリのソースをいじることができるようになります。

#workディレクトリ、vagrantディレクトリの作成
~$       cd desktop
desktop$ mkdir work

#workディレクトリに移動しvagrantとsrcディレクトリを作成
desktop$ cd work
work$    mkdir vagrant (Vagrantfileを作成するディレクトリ)
work$    mkdir src　　　(仮想マシンとの共有ディレクトリ)
work$    ls            (vagrantとsrcが作成されているか確認)
src vagrant　←が表示されればOK！

#Vagrantfileの作成
work$    cd vagrant
vagrant$ vagrant init "bento/centos-7.4"

↑"bento/centos-7.4"はvagrant boxと呼ばれる仮想マシンのテンプレート,雛形のようなものです。
1からOSをインストールして行くのは大変ですが vagrant init box名 で環境を構築することができます。

②Vagrantfileの編集

・vimの起動

vagrant$ vim Vagrantfile

(1)ネットワーク設定

# config.vm.network "private_network", ip: "192.168.33.10"

上記のコメントアウトを外して・・・

config.vm.network "private_network", ip: "192.168.33.10"

このようにします。
こちらで設定したIPアドレスにブラウザでアクセスしてアプリの動作を確認します。

(2).共有ディレクトリ、パーミッションの設定
「# config.vm.synced_folder」のコメントアウトを外して下記のように編集します。

config.vm.synced_folder "../src", "/vagrant", mount_options:['dmode=777','fmode=777']

「config.vm.synced_folder "ホストマシンのパス", "仮想マシンのパス"」 で共有ディレクトリを設定します。
「mount_options:['dmode=777','fmode=777']」は「config.vm.synced_folder」のオプションで、dmodeはディレクトリの権限、fmodeはファイルの権限を設定します。

(3)プロビジョニング（vagrant up時に行うプロセスを設定）
「# config.vm.provision」の記述を編集（コマンドアウトされていたら外してください）

config.vm.provision "shell", :path => "provision.sh"

↑これで「vagrant up」時に「provision.sh」が実行されるように設定されました。
「provision.sh」内に各種インストールの記述をしておけば「vatrant up」時に自動的にインストールを行ってくれます。

(4)「provision.sh」の作成,編集
先ほどVagrantfileに設定した「provision.sh」はまだ作成していないのでVagrantfileがある階層で「provision.sh」を作成し編集を行います。

#Vagrantfileがあるvagrantディレクトリ内にprovision.shを作成
vagrant$ touch provision.sh

自分の環境に合わせて「provision.sh」に以下の記述を修正しペースト

# Apache,unzip,gitのインストール
yum -y install httpd unzip git

# MySQLのインストール
yum -y install http://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm
yum -y install mysql-community-server

# PHPのインストール
yum -y install epel-release
yum -y install http://rpms.famillecollet.com/enterprise/remi-release-7.rpm
※remi-release-7の数字部分は仮想マシンのCentOSのバージョンに合わせる
yum -y install --enablerepo=remi,remi-php56 php php-devel php-mbstring php-intl php-mysql php-xml
※remi-php56の数字部分はインストールしたいPHPのバージョンに合わせる（今回は既存アプリの環境がPHP5.6）

# ApacheがVagrant up時に起動する設定
systemctl enable httpd.service
systemctl start httpd.service

# MySQLがVagrant up時に起動する設定
systemctl enable mysqld.service
systemctl start mysqld.service

# Vagrantの共有フォルダにパスを設定
rm -rf /var/www/html
ln -fs /vagrant /var/www/html
3.vagrant upの実行

Vagrantfileがあるvagrantディレクトリに移動し「vagrant up」を実行します。

vagrant$ vagrant up

各種設定やインストールを自動的に行うので時間がかかるかもしれないです。しばらく待ちましょう。

「vagrant up」が終わったら

vagrant$ vagrant ssh

↑を実行し

[vagrant@localhost ~]$

$マークの前が↑のように切り替わっていればログイン成功です！
ログインが成功したら「provision.sh」に記述したインストールが成功しているか確認してみます。

# Apacheバージョン確認
[vagrant@localhost ~]$ httpd -version

# MySQLバージョン確認
[vagrant@localhost ~]$ mysql --version

#PHPバージョン確認
[vagrant@localhost ~]$ php -v

それぞれのコマンド実行後にバージョン情報が返ってくればインストール成功です。
仮想マシンの環境構築は完了です。

4.既存アプリのクローン

GitHubやGitLabのリモートリポジトリから共有ディレクトリ内にアプリのディレクトリをクローンしてきます。

#共有ディレクトリに移動
[vagrant@localhost ~]$ cd /var/www/html

#共有ディレクトリ内でgitのクローンコマンドを実行
[vagrant@localhost html]$ git clone "クローン用のURL"

クローンが完了したら

[vagrant@localhost html]$ ls

↑で共有ディレクトリ内の中身を確認しアプリ名のディレクトリがあればクローン成功です！
クローンが成功するとホストマシンの共有ディレクトリからもアプリを確認できると思います。
（今回はデスクトップ上から確認できます。）

5.Apacheのhttpd.confの編集

Apacheのconfigファイルの記述を編集します。

#viの起動
[vagrant@localhost ~]$ sudo vi /etc/httpd/conf/httpd.conf

#ドキュメントルートの変更
DocumentRoot "/var/www/html"
↑上記のデフォルト設定から
DocumentRoot "/var/www/html/アプリのディレクトリ名"
に変更

# AllowOverride NoneをAllに変更
<Directory "/var/www/html">
    ...
    AllowOverride None
</Directory>
↓下記に変更
<Directory "/var/www/html">
    ...
    AllowOverride All
</Directory>

編集後、Apadcheを再起動し変更を反映させます。

# Apcheの再起動
[vagrant@localhost ~]$ sudo systemctl restart httpd

これでApacheの設定は完了です。

6. MySQLの設定、アプリとの接続

①MySQLの設定
MySQLにログインし
・新規ユーザーの作成
・作成ユーザーへの権限付与
・アプリと接続するDBの作成
を行います。

#rootユーザーでMySQLに接続
[vagrant@localhost ~]$ mysql -u root

#ユーザーの作成
mysql> create user "ユーザー名"@localhost (identified by "パスワード");
↑パスワードが必要な場合は（）内も記述

#mysqlというDBに移動
mysql> use mysql;

#作成したユーザーに全権限を付与
mysql> grant all on *.* to "ユーザー名"@localhost (identified by "パスワード");

ここまで行ったら一度mysqlからログアウトします。

mysql> exit

rootでログアウトしたら作成したユーザーで再度ログインし、アプリ用に使用するDBを作成します。

#作成したユーザーでログイン
[vagrant@localhost ~]$ mysql -u "ユーザー名"

#DBの作成
mysql> create database "DB名";

#DBの確認
mysql> show databases;
↑で作成したDBが表示されていればOK

②アプリとの接続
アプリ内のapp.phpを編集します。

#viを起動しapp.phpを編集
[vagrant@localhost html] /~/app.php

#Datasources内のusername,password,databaseを修正
'Datasources' => [
        'default' => [
            ...            
            'username' => 'mysqlで作成したユーザー名',                  
            'password' => 'mysqlユーザーのパスワード',                  
            'database' => '作成したDB名',                    
            ...

MySQLのユーザー作成時にパスワードを設定しなかった場合は「'password' => ''」とします。

7.本番環境DBのダンプ→ローカルDBへリストア

本番環境のDBをローカル環境に移行するためにMySQLのダンプ、リストアを行います。

#本番環境のMySQLからDBを指定しdumpを実行
[vagrant@localhost ~] mysqldump -u "ユーザー名" -h "ホスト名" -p --database "DB名" > "dumpファイル名"

#dumpしたファイルを指定しローカル環境のDBにリストア(ユーザー、DBは先ほどローカルのMySQLで作成したDB)
[vagrant@localhost ~]　mysqldump -u "ユーザー名" -p "DB名" < "dumpファイル名"

#念の為MySQLに接続しリストアできてるか確認
[vagrant@localhost ~]$ mysql -u "ユーザー名"

#リストア先のDBを指定し中身を確認
mysql> use "DB名";
mysql> show tables;
本番環境のDBが反映されていればOK！
8.ブラウザで接続確認

Vagrantfileで設定したIPアドレスにブラウザでアクセスしTOPページが表示されれば成功です！！

最後に

最終的に上手くローカルで動かせるようになるまで様々なエラーが発生し苦しめられました、、、
しかしその度に、エラーの原因を把握し解消するために色々調べ物をするのでその分知識はついたかなと感じています。
スクール受講時にもvagrant環境を作りましたが、その時はテキストに書かれているがまま思考停止でコマンドを打っていたので何が起きているのか全く分からずじまいだったのでこのタイミングでじっくり勉強できたのはいい経験になったなと思います。
自分のような初学者はエラーが発生すると同じようなエラーが発生した記事を参考にコードをパパッとコピペしがちですが、エラーの原因を分からないままにしてしまうと何も身につかず何度も同じエラーで苦しめられる羽目になり時間とエネルギーを無駄にしてしまう恐れがあります、、、
なので「急がば回れ」理論でエラーに向き合い理解した上で1歩1歩進んでいくことが大事なのかなと感じました。

ここまでお読みいただきありがとうございました。

※記述や認識に誤りがあればご指摘いただけると大変助かります！

参考にさせていただいた記事

https://qiita.com/kenta0629/items/574251c140387779b681
https://qiita.com/tatsuo-iriyama/items/e9d63f2eaa901d1e9132
https://akamist.com/blog/archives/648

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2019-06-07T10:48:35+09:00

【個人開発】ランキング自動生成サイト(Python/Django)

やりたかったこと

個人でWEBサイトを作成して、HTML・CSS・（なんちゃって）JavaScriptを使ってきましたが、更新作業は面倒な部分もあります。自動で内容が更新されるWEBサイトを作成したいなと思いました。

サイト概要

「世界の絶景100選」などを紹介しているサイトにでてくる絶景について、頻出度、goole検索結果、独自ポイントを付与して、世界の絶景ランキングを自動で生成する。(更新頻度：1日1回 or 2回)

1. 人気の世界の絶景10
絶景まとめサイトでよくでてくる絶景 + 自分ポイントを付与 ＋ googleの検索結果を加味する

2. 知られざる世界の絶景10
絶景まとめサイトで書いてはあるけど、そんなにでてこない絶景をランダム表示 ＋ googleの検索結果の下位順を加味

3. Google MAP API を利用して、それぞれのランキングの上位5つの絶景の場所にピンを立てる

公開サイト

b1.png

使用技術

  • Python3.6 にてスクレイピング(BeautifulSoup)
  • Django2.0 + MySQL
  • Google Map API v3
  • Google Custom API

　※Pythonによるスクレイピング・Djangoもはじめて触りました。

手順 と ポイント

1: ランキングの作成(Python)

①世界の絶景を紹介しているページを5～10サイトほど、pythonにてスクレイピングし、世界の絶景の名称を取得（更新頻度：1日1回）し、DB（MySQL）に格納する。

スクレイピングの例:

import urllib.request, urllib.error
from bs4 import BeautifulSoup

def getURL(urlInfo, tagInfo):
    url = urlInfo

    # ヘッダーセット
    ua = 'Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/69.0.3497.100 Safari/537.36'
    req = urllib.request.Request(url, headers={'User-Agent': ua})

    try:
        # URLにアクセスする htmlが帰ってくる
        html = urllib.request.urlopen(req)
        # htmlをBeautifulSoupで扱う
        soup = BeautifulSoup(html, 'html.parser')
        # 要素全てを摘出する
        return soup.find_all(tagInfo)
    except:
        pass

def makeList(tagInfo, findInfo, urlInfo):

    # 取得結果をループし文字列を取得
    for t1 in tagInfo:

        text = t1.string
        if text is None:
            pass
        else:
            if findInfo is None:
                list.append([text, urlInfo])
            else:
                index = text.find(findInfo)
                if index != -1:
                    list.append([text, urlInfo])

list = []

#スクレイピングの実施
url1 = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxx' #URLをセットする
getInfo = getURL(url1, 'h2')     #URLとタグ情報をセット
if getInfo is None:
    print('データ取得失敗')
else:
    # makeListにてlistを作成
    makeList(getInfo, '位', url1)    #検索ワードを渡す

②あらかじめ作成しておいたMy絶景リスト（一度スクレイピングした情報から独自に作成）をDBにアップロードしておく

DBへのアップロードの例:

import mysql.connector
import csv

#手持ちのデータリストmylistを読み込み、mysql table mylistにupdate/insertする
# MySQL接続
cnt = mysql.connector.connect(
    host='xxxxxxxx',
    db='xxxxxxx',
    user='xxxxxxx',
    password='xxxxxxx',
    charset='utf8'
)

# カーソル取得
db = cnt.cursor(buffered=True)

# mylistを読み込み、dbをselect、データがなければinsert、あればupdate
with open("./mylist.txt", "r", encoding="utf-8_sig") as f:
    reader = csv.reader(f, delimiter='\t')
    for row in reader:
        keyword = row[0]
        name = row[1]
        country = row[2]
        point = row[3]
        # dbデータをkeywordでselect
        sqlselect = "select * from ranks_mylist where keyword='" + keyword + "';"
        db.execute(sqlselect)
        row1 = db.fetchall()
        dbcnt = len(row1)
        # dbにデータがあればupdate
        if dbcnt > 0:
            for rdata in row1:
                id = rdata[0]
                sqlupdate = 'UPDATE ranks_mylist SET name="' + name + '", country="' + country +'", point="' + point +'" where id="' + str(id) + '";'
                db.execute(sqlupdate)
        # dbにデータがなければinsert
        else:
            sqlinsert = 'INSERT INTO ranks_mylist(keyword, name, country, point) VALUES ("' + keyword + '", "' + name + '", "' + country + '",' + point + ')'
            db.execute(sqlinsert)

# カーソル終了
db.close()
# コミット
cnt.commit()
# MySQL切断
cnt.close()

③My絶景リストにある絶景とスクレイピングした情報をぶつけて、ランキングを作成し、DBに格納。その際、グーグルの画像検索結果を1件取得し、その画像URLも同時に格納する。

2. ランキングサイトの表示(Django + GoogleMapAPI)

①作成されたランキングテーブルを使用して、サイトを表示する

テーブルへの書き出しは、javascriptでdocument.writeを使用してループする。リンクには、googleの通常検索と画像検索を埋め込む。

<caption>人気の世界の絶景</caption>
<thead class="tablehead1">
  <tr>
    <td>1位</td><td>2位</td><td>3位</td><td>4位</td><td>5位</td>
  </tr>
</thead>
<tbody>
  <tr>
   <script type="text/javascript">
     var flg = 1;
     {% for rank in ranks.all %}
       if(flg <= 5){
         document.write("<td><a href='http://www.google.com/search?q={{ rank.name }}' target='_blank'>{{ rank.name }}</a><br>{{ rank.country }}</td>");
       }
       flg ++;
     {% endfor %}
   </script>
 </tr>
 <tr>
   <script type="text/javascript">
     flg = 1;
     {% for rank in ranks.all %}
       if(flg <= 5){
         document.write("<td><a href='http://www.google.com/search?q={{ rank.name }}&tbm=isch' target='_blank'><img src = {{ rank.imageurl }} width='200' height='150'></td>");
       }
       flg ++;
     {% endfor %}
   </script>
 </tr>
</tbody>

②Google MAP API v3 を利用して、それぞれのランキングの上位5つの絶景の場所にピンを立てる

→　別記事参考（google map：名称にて場所を検索する）

b2.png

まとめ

  • スクレイピングの実施、ランキングを作成するPythonプログラムは、1日1回 or 2回 Cronにて実施している。
  • スクレイピングを実施するサイトを増やしたり、自分ポイントの付与を変えたりするとランキングが動く。（特に、スクレイピングで1件しかとれなかった絶景はランダム表示なので、ランキングが頻繁に変わる）

→　スクレイピングによる情報や自分のロジック(ルール)などで、情報が自動更新されるサイトの作成ができました。

参考URL

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

日付別

タグ一覧