2018

PythonでスクレイピングしたデータをDB登録してやんよ!!!

mysql
python

Pocket

特定のサイトからデータを収集して自身のサイトで利用する場合、
自身のサイトにアクセスしたユーザーのリクエストの度にスクレイピングするのは相手側にとってとっても迷惑に話で、そんな奴はいつアクセス制限かけられても文句は言えません！

できれば一度取得したデータは自身のデータベースに保管し、その後のリクエストには自身のDBからデータを出力するようにして相手側へも配慮し自身の運用にも費用対効果を高めましょう。

※相手サイトが許可してない場合はそもそもスクレイピングしちゃダメです！

スクレイピングをするための前準備とデータを取得

まずはスクレイピングを行うための準備です、pythonはインストール済みの前提です。

beautifulsoup4のインストール

$ pip install beautifulsoup4

インストールができたら新規ファイル「scraip.py」を作成。
手始めにスクレイピング本体の実行コードです。

from bs4 import BeautifulSoup
import urllib.request as req

# 対象は「ときどきweb」
url = "http://tokidoki-web.com"

html = req.urlopen(url)

# HTMLを解析
soup = BeautifulSoup(html, "html.parser")


# メニュー要素を指定
menu_block = soup.select("#sideCatBox > ul")

# 各メニューのテキストを抽出してリスト格納
titles = []
for ul_tag in menu_block:
    for li_tag in ul_tag.find_all('li'):
        a_tag = li_tag.find('a')
        titles.append(a_tag.string)

print(titles)

当サイトのカテゴリー一覧を取得してリストに格納し、print出力するだけの単純なものです。
実際に実行してカテゴリ一覧を取得してみます。

# スクレイピング実行
$ python scraip.py
['Androidアプリ', 'APIとかWEBサービス', 'HTML5 + CSS3', 'JavaScript', 'jQuery', 'MySQL', 'Node.js', 'PHP', 'Python', 'Ruby on Rails', 'スマートフォンサイト関係', '未分類']

とれましたね？
これで基本的なスクレイピングの準備ができました。

スクレイピングデータを保管するデータベースを作成する

次に上記のスクレイピングの取得データを保存するために
ローカル環境にMySQLデータベースを作成します。

MySQLをインストール

$ brew install mysql
# インストールされてるか確認 ※mysqlに"d"が付いてるので注意
$ mysqld --version

インストールができたら起動してみる。

brewのアップデートなどで権限が初期化されたらエラーになる場合があるので、その時はユーザー所有権を更新して再起動コマンド。

# サーバー起動
$ mysql.server start

# 失敗したら多分こうなる
Starting MySQL
. ERROR! The server quit without updating PID file (/usr/local/var/mysql/xxxxx.local.pid).

# その場合はユーザー所有権を更新
$sudo chown -R _mysql:_mysql /usr/local/var/mysql

# 再起動
$ mysql.server start

データベースとユーザーを作成する

MySQLをインストールできたら次はMySQLにアクセスしてデータを保管するテーブルと通常アクセスするユーザーを作成しましょう。

# root権限でmysqlアクセス
$ mysql -u root -p
> Enter password: 

# 新規データベースを作成
mysql> CREATE DATABASE 新規データベース名 DEFAULT CHARACTER SET utf8;

# 新規ユーザーを作成
mysql> CREATE USER 新規ユーザー名@localhost IDENTIFIED BY 'パスワード';

# 作成ユーザーに新規テーブル操作権限を与える(5.x 系)
mysql> GRANT ALL ON 新規データベース名.* TO 新規ユーザー名@localhost IDENTIFIED BY 'パスワード';

# 上記がエラーになる場合はMySQLのバージョンが違うかもなのでバージョンによってはこっち(8.x 系)
mysql> GRANT ALL PRIVILEGES ON 新規データベース名.* TO '新規ユーザー名'@'localhost';

# 権限状態を更新して一度終了する
mysql> FLUSH PRIVILEGES;
mysql> exit;

# 作成したユーザーでログインできるか確認
$ mysql -u '新規ユーザー名' -h 'localhost' -p;
Enter password: # パスワード

# 「Welcome to the MySQL monitor.  〜」 と出たらアクセス成功

u オプションはユーザーを指し、root（初期アカウント）で。p オプションはパスワードを指します。

アクセス時rootユーザーのパスワードを設定していない場合はそのままEnterでアクセス。

アクセスできたらテーブルを作成。文字コードもUTF-8に設定しておきます。
データベースに接続するメインユーザーを作成します。ユーザーやパスワードは予約語に気を付けて命名してください。

最後に作成した新規ユーザーにテーブル編集を操作できる権限を与えて一度終了させましょう。

ユーザー作成に失敗したら

以前、なにかしらの機会にMySQLをインストール済みでしばらくバージョンアップしていなかった場合は、ユーザー作成の際に1. Please use mysql_upgrade to fix this error.というメッセージが出てユーザー作成ができない場合があります。

「Ctrl」+「D」で一旦MySQLを終了させて「mysql_upgrade」でアップグレードしてからもう一度アクセスしましょう。

そもそも失敗したら

最初は色々と失敗して無駄にDBやユーザーを作ってしまう事もあると思うので、その時は一回削除してやり直しちゃいましょう。

ちゃんと削除できたかは「SHOW DATABASES;」コマンドで一覧表示できますので無駄なDBは残さない様にしましょう。

# データベース削除
mysql> DROP DATABASE データベース名;

# データベース一覧を確認
mysql> SHOW DATABASES;

# ユーザー削除
mysql> DROP USER ユーザー名;

# 全ユーザー一覧
mysql> SELECT Host, User FROM mysql.user;

※rootは初期ユーザーなので削除しちゃダメ、絶対！（つかできない？）

けっこう色んな理由でMySQLが起動できない事もあるのでその場合はこの辺りを・・・

MySQL起動できなくなった　The server quit without updating PID file

PythonファイルからMySQLに接続する

データベースの作成ができたので、いよいよpython実行ファイルとの紐付けです。

MySQL 公式の「mysql-connector」というパッケージがあるのでインストールしましょう

「mysqlclient」を使います。

# mysql-connectorパッケージをインストール
$ pip install mysql-connector-python-rf

カテゴリーテーブルを作成

再度、MySQLにログインしてカテゴリメニュー用のテーブルを作成します。

⓪[テーブル名] : category_list
①[テーブルID] : id
②[カテゴリー名] : category_name

# データベースに再ログイン
$ mysql -u '新規ユーザー名' -h 'localhost' -p;
>>> Enter password:

# 操作するデータベースを指定
>>> USE データベース名;

# カテゴリー用のテーブルを作成
>>> create table category_list (id int(5) NOT NULL AUTO_INCREMENT, category_name varchar(255),PRIMARY KEY (id));

# データベース内のテーブルを確認
>>> SHOW TABLES FROM データベース名;

# テーブルのカラム構成を確認
>>> SHOW COLUMNS FROM category_list;

# とりあえず１個データを挿入してみる
>>> INSERT INTO `category_list`(category_name) VALUES('test');

# 挿入したデータの確認
>>> SELECT * FROM `category_list`;
+----+---------------+
| id | category_name |
+----+---------------+
|  1 | test          |
+----+---------------+
1 row in set (0.00 sec)

DBに接続用ファイルを作成

MySQLに接続するpythonファイル「db_connect.py」を作成します。

import mysql.connector # MySQL

# DB接続と選択
def data_registration_single(self):
    connection = mysql.connector.connect(
        user='作成したユーザー名', 
        password='作成したユーザー パスワード', 
        host='localhost', 
        database='category_list',
        charset='utf8'
    )
    cursor = connection.cursor()
    cursor.execute("USE category_list")
    connection.commit()

    # データを挿入
    cursor.execute("INSERT INTO category_list(category_name) VALUES (%s)", (self['category_name']))
    connection.commit()
    print('DB登録成功')

    cursor.close()
    connection.close()