【Python3】MySQLを使う【SQLAlchemy】【TinyDB】

Pythonによるスクレイピング&機械学習

Pythonによるスクレイピング&機械学習 開発テクニック BeautifulSoup,scikit-learn,TensorFlowを使ってみようより
学習ログに少し加筆したり。3-2章。

前回

二章では、スクレイピングやクローリング、APIに関する知識を習得しました。
以下にまとめておきます。

• [Python3]ログイン機能付サイトでスクレイピング[requests][BeautifulSoup]
• [Python3]ブラウザを経由したスクレイピング(動的なページなど)[Selenium]
• [Python3]WebAPIを試す[requests]
• [cron]定期的に処理を行う

この章で得られるもの

3-2では

• データベースとは
• Pythonによるデータベースの扱い

について学んでいきます。

環境

docker 17.03.1-ce
ubuntu:16.04
Python 3.5.2
mysqlclient (1.3.10)
SQLAlchemy (1.1.10)
tinydb (3.3.0)

コード

こちら(Git)より

データベース(DB)って何？

データベース。略してDB(でぃーびー)。一年ぐらい前、僕が初めて聞いた感覚では、データを保存する、なにか規模の大きいもののような気がしました。いま「データベース」という文字列をみて、同じ感覚を持った方にもわかるよう、できるだけ噛み砕いて説明していきたいと思います。

一般的にPC上でデータを保存したり、アクセスしたりする際、大きく分けて２つの方法が挙げられます。

• ファイルで保存
• データベースに保存

僕らが普段使っているデータは全て前者の方法で保存されています。Excelで何かしらのデータを集計したら.csv形式の「ファイル」で保存しますし、スクリーンショットは.png形式の「ファイル」で保存されます。
このように、データベース専用のソフトウェアを要さず、アクセスや保存が手軽に行えるものが「ファイル」です。

一方、顧客情報や、商品情報、掲示板のテキスト情報など、データが大量になったり、複雑になったときにはファイル形式では扱いにくいことがあります。１００００人の顧客情報をExcelで開いたらめっちゃ重いはずです。ここでデータベースを使うと便利です。
そもそも、データベースは、大量のデータを扱うために開発されているものなので、大量のデータを扱うのに長けているのです。

データベースのメリット

データベースを用いることで、以下のようなメリットを享受できます。
以下DBと表記します。

データを一元管理できる。

総顧客データや、総商品データなど、膨大なデータを一元化できます。

様々なデータを関連づけて格納できる。

Twitterを例にすると、1ユーザには名前、アイコン画像、説明文などの単純なデータの他にも、フォロー、フォロワーなど、　構造を持ったデータとの関連をつけて格納することができます。このようにデータを関連づけて保存できるDBをRDB(Relational DataBase)と呼びます。

Twitterの例のように、かなり便利なものなので開発現場では普通、RDBを用います。

重複データを認めないなどの制約をつけてデータを格納できる。

ユーザidの重複や、何かしら一意性のあるデータの重複など、ヒューマンエラーで起こりうる問題を解決できます。

データの一貫性(整合性)を確保できる

あるユーザだけ名前という属性自体が無いなんてことは許されません。このようなエラーを防ぐために、しっかりデータモデルの定義から行うことで、一貫性を確保します。

複数人でデータを共有や同時アクセスができる

かなり大事な機能です。Excelでそれぞれ情報を集めてから、結合する、なんてめんどくさい処理も省けます。
また、Twitterなどのサービスでは、こっちでこの情報を変更して、こっちでこの情報を返却して、みたいな処理がまあ大量に同時に行われていますが、それを一つのDBに対して行うことができるのです。

このように、蓄積したデータの共有や、データの同時アクセスが考えられる場合は、確実にDBを採用した方が良いでしょう。同じファイルに対して、同時に読み書きすると、ファイルは容易に壊れてしまいます。
この点、DBでは、排他処理などがしっかりしているので、安全にデータにアクセスすることができるのです。
特に、データファイルを複数のプログラムから参照する場合や、データを常に収集しつつ、機械学習を試す場合など、ファイルの読み書きのタイミングによって、データが壊れてしまうことがありえます。
また、Webから収集したデータは重複が多いので、DBで排除する制約を簡単につけることができるのもメリットでしょう。

ファイルで管理すると。。。

Webを触り始めのころに、フレームワークもなしに、jsとcgiでメモをページ上に貼り付け、チャットライクなことができるアプリケーションを作りました。そのころはDBのDの字も知らなかったので、単純に.txtファイルにJSONを書くようなシステムで管理していました。
ユーザがメモを貼ったら、JSONにメモデータを追加するようリクエストをcgiに送り、メモを消したら、JSONにメモデータを削除するように。。という感じです。
一人で使ってみると、まあ成り立つんですが、友達とやってみて複数人で使ってみると酷いもので、すぐにデータの破綻が起きます。一応ここに残しています。

データベースを選ぶ

データベースと一口に言っても、むちゃくちゃあります。
主要なデータベースでも以下のようなものがあります。

• MySQL/MarinaDB
• PostgreSQL
• MongoDB(NoSQL)
• Tiny(NoSQL)
• Microsoft SQL Server(商用)
• Oracle Database（商用）
• SQLite

それぞれのデータベースに特徴があり、一概にどれが良いとは言えません。
ここでは、オープンソースのデータベースとしてよく使われるMySQL/MarinaDBとSQLAlchemy、NoSQLのTinyDBについて、紹介します。
本書ではSQLAlchemyでなく、SQLiteについてやってたんですが、個人的な好みでSQLAlchemyでやっていきます。

dockerでPython+MySQL

dockerの練習に、dockerでやっていきます。

環境構築

dockerで仮想環境上に、Python+MySQLの環境を整えます。ベースイメージにUbuntuを用います。

terminal

# DockerにUbuntuのイメージを挿入
$ docker pull ubuntu:16.04
# イメージの確認
$ docker images
# Ubuntuを実行しシェルにログイン
$ docker run -it ubuntu:16.04

シェルにログインしたら、Python及び、MySQLの環境を整えます。

terminal

# Python3 と pip3
$ apt-get update
$ apt-get install -y python3 python3-pip
# MySQL をインストール
$ apt-get install -y mysql-server

MySQLをインストールしている途中で、ルートパスワードを決めるよう求められます。
「passward」としました。
また、PythonからMySQLに接続するためのmysqlclientをインストールします。

terminal

# mysqlclient
$ pip3 install mysqlclient

mysqlclientをインストールしようとした時、以下のようなエラーが出ました。

terminal

# pip3 install mysqlclient
Collecting mysqlclient
  Downloading mysqlclient-1.3.10.tar.gz (82kB)
    100% |████████████████████████████████| 92kB 1.8MB/s
    Complete output from command python setup.py egg_info:
    /bin/sh: 1: mysql_config: not found
    Traceback (most recent call last):
      File "<string>", line 1, in <module>
      File "/tmp/pip-build-zy5vcx7v/mysqlclient/setup.py", line 17, in <module>
        metadata, options = get_config()
      File "/tmp/pip-build-zy5vcx7v/mysqlclient/setup_posix.py", line 44, in get_config
        libs = mysql_config("libs_r")
      File "/tmp/pip-build-zy5vcx7v/mysqlclient/setup_posix.py", line 26, in mysql_config
        raise EnvironmentError("%s not found" % (mysql_config.path,))
    OSError: mysql_config not found

    ----------------------------------------
Command "python setup.py egg_info" failed with error code 1 in /tmp/pip-build-zy5vcx7v/mysqlclient/

mysql_configというモジュールが見つからないようです。ググると

terminal

apt-get install libmysqlclient-dev

でインストールできるよう。実行して、解決。

以上で環境が構築できたので、一旦終了して、Dockerにubuntu-mysqlという名前で保存します。

terminal

# コンテナを終了
$ exit
# 先ほどまで起動していたコンテナを確認 -> COUNTAINER ID(89826230d4f1)を確認
$ docker ps -a
# コンテナをイメージとして保存
$ docker commit 89826230d4f1 ubuntu-mysql
# イメージの確認
$ docker images

そして、改めて、以下のコマンドでUbuntuを起動します。

terminal

$ docker run -it -v $HOME:$HOME \
      -e LANG=ja_JP.UTF-8 \
      -e PYTHONIOENCODING=utf_8 \
      ubuntu-mysql /bin/bash

MySQLからDBを作成

以下のコマンドでMySQLを起動します。

terminal

$ service mysql start

MySQLに接続してDBを作成します。パスワードを聞かれるので、設定したパスワードを入力します。
僕の場合だと「passward」です。
オプションは -u がユーザ名指定で、引数がrootになります。-pはパスワード入力になります。

terminal

$ mysql -u root -p

MySQLにログインできたら、ちょっとDBを覗いてみましょう。

mysql

show databases;

デフォルトでいくつかDBが入ってますね。これは、色々なconfigデータが格納されているようですが、よくわかっていません。変にいじらないのが吉です。

ここにDBを追加しましょう。

mysql

create database test;

もう一度確認してみると、

確かにtestという名前のDBが追加されました。

pythonからDBに接続して操作

sqlalchemy

O/Rマッパーのsqlalchemyをインストールしてください。

terminal

$ pip3 install sqlalchemy

DBが追加できましたので、Pythonからデータベースを操作していきましょう。

3-2mysql-test.py

import sqlalchemy as sa

# MySQLに接続。
url = 'mysql+mysqldb://root:passward@localhost/test?charset=utf8'
engine = sa.create_engine(url, echo=True)

engine.execute('DROP TABLE IF EXISTS {}'.format("items"))
# テーブルを作成
engine.execute('''
    CREATE TABLE items (
    item_id INTEGER PRIMARY KEY AUTO_INCREMENT,
    name TEXT, 
    price INTEGER
    )
    ''')

# データを挿入。
# SQL文に「?」が使用できないので、代わりに「%s」を使用
ins = "INSERT INTO items (name, price) VALUES (%s, %s)"
data = [("Banana", 300),("Mango" , 640),("Kiwi"  , 280)]
for d in data:
    engine.execute(ins,d)

# データを抽出
rows = engine.execute('SELECT * FROM items')

# 表示
for row in rows:
    print(row)

executeで生のSQLコマンドを実行できるので、メソッドを覚える必要はあまりないかもしれません。
実行結果は以下です。

terminal

1 Banana 300
2 Mango 640
3 Kiwi 280

sqlalchemyでは、テーブル定義をクラスで宣言することができます。
また、sqlalchemyにもSQLコマンドに相当するメソッドが用意されています。
書き直してみました。

3-2mysql-test.py

from sqlalchemy import Column, Integer, String, Text
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

Base = declarative_base()


class items(Base):
    """テーブルの定義"""
    __tablename__ = 'items'

    id = Column(Integer, primary_key=True)
    name = Column(Text)
    price = Column(Integer)


if __name__ == "__main__":
    # MySQLに接続。
    url = 'mysql+mysqldb://root:passward@localhost/test?charset=utf8'
    engine = create_engine(url, echo=True)

    engine.execute('DROP TABLE IF EXISTS {}'.format("items"))

    # テーブル作成
    Base.metadata.create_all(engine)

    # セッションの作成
    Session = sessionmaker(bind=engine)
    session = Session()

    data = [("Banana", 300),("Mango" , 640),("Kiwi"  , 280)]
    item_list = [items(name=d[0],price=d[1]) for d in data]

    session.add_all(item_list)

    for row in session.query(items).all():
        print(row.id,row.name,row.price)

    session.commit()

これでも同じ結果が得られます。

NoSQL/TinyDB

これまで見たMySQLやsqlalchemyでは、データ操作言語であるSQLを介してDBにアクセスするものでした。これらのDBは、リレーショナル・データベース管理システム(RDBMS)と呼ばれており、DBを利用する多くのシステムはRDBMSを利用しています。また、RDBMSを利用しているDBのことをリレーショナル・データベース(RDB)と呼びます。

これに対してNoSQLというRDBMSの構造とは一線を画するDBがあります。代表的なものに、ドキュメント指向型DBのMongoDBがあります。RDBMSではテーブルを作る際に、どんなデータを作るかをCREATE TABLEを用いてスキーマを設定しなければなりませんでした。ドキュメント指向型DBでは、特にスキーマを定義する必要はありません。そのため比較的手軽に利用することができます。

firebaseのRealtimeDatabaseはNoSQLですね。サーバ構築要らずで、便利だし、無料である程度使えるので、ぜひ使って見てください

今回はTinyDBというライブラリを使っていきます。TinyDBは、pipでインストールするだけでDBも使えるようです。

terminal

pip3 install tinydb

以下実装です。

3-2tinydb-test.py

# TinyDBを使うためライブラリのインポート
from tinydb import TinyDB, Query

# データベースに接続
filepath = "test-tynydb.json"
db = TinyDB(filepath)

# 既存のデータがあれば破棄
db.purge_table('fruits')

# テーブルを得る
table = db.table('fruits')

# データをデータベースに挿入
table.insert( {'name': 'Banana', 'price': 600} )
table.insert( {'name': 'Orange', 'price': 1200} )
table.insert( {'name': 'Mango', 'price': 840} )

# 全データを抽出して表示
print(table.all())

# 特定のデータを抽出して表示
# Orangeを検索
Item = Query()
res = table.search(Item.name == 'Orange')
print('Orange is ', res[0]['price'])

# 値段が800円以上のものを抽出
print("800円以上のもの:")
res = table.search(Item.price >= 800)
for it in res:
    print("-", it['name'])

TinyDBではDB1つがjson形式のファイルになっています。これって、同時アクセスにも対応できるんでしょうか。。

実行結果

terminal

$ python3 3-2\ tinydb-test.py
[{'name': 'Banana', 'price': 600}, {'name': 'Orange', 'price': 1200}, {'name': 'Mango', 'price': 840}]
Orange is  1200
800円以上のもの:
- Orange
- Mango

いけました。

まとめ

今回はDBをPythonから操作することについて学びました。
SQLコマンドとか、SQLがどういうものなのかなど、時間の都合で、はしょってしまいました。
あと、適当な事もかなり言っているので、間違いなどあれば、ご指摘の方お願いいたします。
また、時間の空いた時に、追加でこっそり編集していこうと思います。