PythonでSlackBotを開発しよう企画の第7回目。今回は前回用意したMySQLに対して、SQLAlchemyというO/Rマッパーを利用してアクセスしてデータの登録・更新・削除をしていきたいと思います。今回でSQLAlchemyの使い方を学べれば、あとはTwitterAPIとDocomoAPIを駆使していろいろ遊べる未来が待っているはず...！
前回の第6回目では、↑ のように、Pythonアプリケーションで受け取ったデータの保存先データベースであるMySQLを用意しました。
今回は ↑ のように、PythonアプリケーションでSQLAlchemyを利用し、データベースへの処理を直接SQLを書かずに実現できるようにしていきます。
O/Rマッパー・SQLAlchemyとは？
「そもそもO/Rマッパーて何？」という質問に対して明確に説明できなかったので、調べてまとめました。
アプリケーションはオブジェクト指向、データベースはリレーショナルモデルで正規化されているため、アプリケーションをSQL直書きで書いていると、データベースのテーブル構成の変更などによってアプリケーション側のSQLを全部書き換えないといけなくなる...。小さいアプリケーションならまだしも、大規模なアプリケーションでSQLを書いている箇所を洗い出すとなると、もはやバグが生まれること必至...。デスマーチはすぐそこ。となる問題を解決するために生まれたのがO/Rマッパーという仕組み。
O/Rマッパーはオブジェクトとリレーショナルデータベースをそれぞれマッピング（関係づけ）し、相互の構造の違いを吸収し、データやり取りを最適化することができます。O/Rマッパーの主要な機能としては以下のようなものがあります。
【O/Rマッパーの機能】
・コネクション管理機能...データベースとの接続やコネクションプーリングが管理できるようになる。
・自動マッピング機能...外部ファイルにオブジェクトの属性名とテーブルの列名に関するマッピング定義を設定することにより、自動的にマッピング処理を行う。
・マッピングファイル、DTO、DAOの自動生成機能...O/Rマッピングツールの提供する自動生成ツールにより、マッピングファイル、DTO、DAOといったファイルを自動で作成する。
・接続情報の管理機能...JDBCドライバやデータベースの接続情報を、O/Rマッピングツール側で管理する。
・キャッシュ機能...一度取得した検索結果をメモリ上に保持しておいて、同じ検索処理が行われた場合は、メモリ上にある検索結果を返す。これによって、データベースへのアクセスを減らし、パフォーマンスがあがる。
（参考：[ThinkIT] 第1回：O/Rマッピングとは？ (3/3)）
次に、O/Rマッパーの処理の流れを見てみる。
【O/Rマッパーの処理の流れ】
①データベースの接続情報をファイルに定義（データベース設定ファイル）
②クラスのフィールドとテーブルカラムの対応付けをファイルに定義（マッピング・ファイル）
③O/Rマッピングツールがデータベース設定ファイルとマッピングファイルを読み込んで自動的にマッピング処理を行う
④アプリケーションからO/RマッピングツールのAPIを介してデータベースへアクセス
（参考：[ThinkIT] 第1回：O/Rマッピングとは？ (3/3)）
O/Rマッパーを利用するメリットは？
【O/Rマッパーを利用するメリット】
・ソースコードからデータベースにアクセスする処理の部分を切り離すことがでるので、変更に強くなる。
・オブジェクトとリレーショナルデータベースとの構造的な違いを意識することなく開発できる。
・自動生成機能を利用することで、ソースファイルなどの作成の手間が省けるので開発効率があがる。
・データベースの差（SQL方言の差）を吸収できる。
（参考：[ThinkIT] 第1回：O/Rマッピングとは？ (3/3)）
以上、O/Rマッパーとは何か？O/Rマッパーの処理の流れはどうなっているのか？O/Rマッパーを利用すると何が嬉しいのか？を見てきました。前置きが長くなりましたが、ここからは実際に多くのPythonアプリケーションで利用されているO/Rマッパー「SQLAlchemy」を触っていきます。
SQLAlchemyのインストール
まずは、PythonアプリケーションでSQLAlchemyを利用できるようにpipでインストールします。
$ pip install sqlalchemy
SQLAlchemyをインストールすることができたら、「models.py」というファイルを作って、ファイルに以下のように記述します。
models.py
import os from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.ext.declarative import declarative_base from datetime import datetime # 環境変数からDB接続情報取得 MYSQL_USER = os.environ['MYSQL_USER'] MYSQL_PASSWORD = os.environ['MYSQL_PASSWORD'] MYSQL_HOST = os.environ['MYSQL_HOST'] MYSQL_DB = os.environ['MYSQL_DB'] # データベースエンジン作成 engine = create_engine('mysql://{user}:{password}@{host}/{db}' .format(user=MYSQL_USER, password=MYSQL_PASSWORD, host=MYSQL_HOST, db=MYSQL_DB), encoding='utf-8', echo=False) metadata = MetaData(engine) Base = declarative_base() # データベースのテーブル情報のマッピング定義 class TwitterUser(Base): __tablename__ = 'twitter_user' twitter_user_id = Column(Integer, primary_key=True) user_name = Column(String, nullable=False) user_screen_name = Column(String, nullable=False) profile_image_link = Column(String, nullable=False) ins_datetime = Column(DATETIME, default=datetime.now, nullable=False) upd_datetime = Column(DATETIME, default=datetime.now, nullable=False)
↑ 処理としては、まずos.environの箇所で.envファイルから環境変数を読み取っています（Gitにあげた時にユーザー名やパスワードを知られたくないので環境変数から取得するようにしています）。そして、SQLAlchemyでデータベースにアクセスする基盤となるデータベースエンジンを作成します。ここに前回用意したMySQLのデータベース名やユーザー名を指定します。そして、前回MySQLに直書きで作成した「TwitterUser」というテーブルのテーブル名・カラム情報を定義しておきます。
環境変数からデータを取得したいので、「.env」ファイルを編集しておきましょう（xxxxxには自身の設定しているMySQL情報を反映します）。
.env
MYSQL_USER=xxxxx MYSQL_PASSWORD=xxxxx MYSQL_HOST=xxxxx MYSQL_DB=xxxxx
これでデータベースにアクセスするためのマッピングファイルを作成することができました。
次に、マッピングファイル「models.py」を読み込んでデータベースにレコード登録処理をする「twitter_user_add.py」ファイルを作成し、以下のように記述します。
twitter_user_add.py
# coding=utf-8 import os from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker from models import TwitterUser # 環境変数からDB接続情報取得 MYSQL_USER = os.environ['MYSQL_USER'] MYSQL_PASSWORD = os.environ['MYSQL_PASSWORD'] MYSQL_HOST = os.environ['MYSQL_HOST'] MYSQL_DB = os.environ['MYSQL_DB'] # MySQL接続 engine = create_engine('mysql://{user}:{password}@{host}/{db}' .format(user=MYSQL_USER, password=MYSQL_PASSWORD, host=MYSQL_HOST, db=MYSQL_DB), encoding='utf-8', echo=False) # トランザクション開始 Session = sessionmaker(bind=engine) session = Session() # user追加 test_user = TwitterUser(user_name='testマン', user_screen_name='testman', profile_image_link='test/link') session.add(test_user) # user複数追加 session.add_all([ TwitterUser(user_name='taro', user_screen_name='taro', profile_image_link='taro/link'), TwitterUser(user_name='hanako', user_screen_name='hanako', profile_image_link='hanako/link'), TwitterUser(user_name='pochi', user_screen_name='pochi', profile_image_link='pochi/link')]) # 変更をコミット session.commit()
↑ 処理の内容としては、まず「models.py」ファイルから「TwitterUser」というクラスをインポートしてきて参照できる状態にしています。一連の処理が完了すれば変更内容をコミット（完了）、どこかで不具合が発生したら処理の始まる前の状態に戻るというトランザクションを開始し、TwitterUserテーブルに1レコード(1ユーザー分のデータ登録)を追加する処理を行い、次に3レコードの登録処理を行って、最後に変更内容をコミットしています。
ここまでできたら、「twitter_user_model.py」ファイルを実行してみましょう。
$ forego run python twitter_user_model.py
コンソールログに出力する処理などを特に入れていなかったので、特に反応は得られません。
MySQLにログインして、実際にデータが登録されているか確認してみましょう。「$ mysql -u username -p」でMySQLにログイン（usernameとpasswordは自身の設定しているものになります）し、「mysql> select * from twitter_user;」でテーブルのレコード一覧を取得します。
$ mysql -u username -p Enter password: mysql> select * from twitter_user; +-----------------+------------+------------------+--------------------+---------------------+---------------------+ | twitter_user_id | user_name | user_screen_name | profile_image_link | ins_datetime | upd_datetime | +-----------------+------------+------------------+--------------------+---------------------+---------------------+ | 1 | testマン | testman | test/link | 2018-04-07 20:37:56 | 2018-04-07 20:37:56 | | 2 | taro | taro | taro/link | 2018-04-07 20:37:56 | 2018-04-07 20:37:56 | | 3 | hanako | hanako | hanako/link | 2018-04-07 20:37:56 | 2018-04-07 20:37:56 | | 4 | pochi | pochi | pochi/link | 2018-04-07 20:37:56 | 2018-04-07 20:37:56 | +-----------------+------------+------------------+--------------------+---------------------+---------------------+ 4 rows in set (0.00 sec)
ちゃんと登録できていますね！！
次に、削除処理も実装しておきます。
登録処理を記述した「twitter_user_add.py」ファイルと同じ要領で、「twitter_user_delete.py」ファイルを作成し、以下のように実装します。
twitter_user_delete.py
# coding=utf-8 import os from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker from models import TwitterUser # 環境変数からDB接続情報取得 MYSQL_USER = os.environ['MYSQL_USER'] MYSQL_PASSWORD = os.environ['MYSQL_PASSWORD'] MYSQL_HOST = os.environ['MYSQL_HOST'] MYSQL_DB = os.environ['MYSQL_DB'] # MySQL接続 engine = create_engine('mysql://{user}:{password}@{host}/{db}' .format(user=MYSQL_USER, password=MYSQL_PASSWORD, host=MYSQL_HOST, db=MYSQL_DB), encoding='utf-8', echo=False) # トランザクション開始 Session = sessionmaker(bind=engine) session = Session() # user削除 row = session.query(TwitterUser).filter_by(twitter_user_id=2).one() session.delete(row) # 変更をコミット session.commit()
↑ twitter_user_idが2のユーザーを削除する処理になっていますね。実行してMySQLの中を確認してみると...
$ forego run python twitter_user_delete.py
mysql> select * from twitter_user; +-----------------+------------+------------------+--------------------+---------------------+---------------------+ | twitter_user_id | user_name | user_screen_name | profile_image_link | ins_datetime | upd_datetime | +-----------------+------------+------------------+--------------------+---------------------+---------------------+ | 1 | testマン | testman | test/link | 2018-04-07 20:37:56 | 2018-04-07 20:37:56 | | 3 | hanako | hanako | hanako/link | 2018-04-07 20:37:56 | 2018-04-07 20:37:56 | | 4 | pochi | pochi | pochi/link | 2018-04-07 20:37:56 | 2018-04-07 20:37:56 | +-----------------+------------+------------------+--------------------+---------------------+---------------------+ 3 rows in set (0.00 sec)
taroくんは消え去ってしまいました。処理通り削除されていることがわかりますね。
まとめ
今回はSQLAlchemyを触ってみて、LocalでそれぞれMySQLアクセスしてデータやり取りできることを確認しました。ちなみに、SQLAlchemyの日本語翻訳されたドキュメントは ↓ こちら。読んでみるとSQLAlchemyの処理の流れが理解できて楽しいです。
データベースエンジン — SQLAlchemy 0.6.5 ドキュメント (和訳)
今回までの実装で、アプリケーション開発・TwitterAPIでデータ取得・Slack投稿・雑談Bot機能開発・データベースアクセスの方法を知ることができたので
Twitterからデータを取得してきて、ネガポジ判定してSlackに送信する
話しかけたら有名人が返答してくれる擬似体験ができるツイッターBot
仮想通貨・ブロガーなど、特定界隈でのTwitter影響力調査機能
などを実装していこうと思います！
会社の先輩で、5年前くらいにTwitterアカウント300個くらいスクリプトで自動生成してアフィリエイトで月70万稼いでた話聞いて衝撃。。。
— 仮想サーファー@さぼりエンジニア (@virtual_techX) February 9, 2018
今はバンされやすくなってるからできないのかな
↑ のツイートをしてた頃は、「TwitterのBot運用するとかマジすげえ...」て思ってたけど、今となってはそんなにすごいと思わないっすね。会社終わり3日くらい没頭すれば作れそう。アフィリエイトBotの運用て完全不労収益になると思うので、実際にどのくらい収益になるのか、どれくらい自動化したらアカウント凍結されるのかなど、挑戦してみて面白い発見あったら書きます！
あとBotの開発に関して手慣れてきた感あるので、「こんなBot作って欲しい！」「こんな機能作ってみて欲しい！」ていう要望あればコメントかTwitterにDMください〜。どうせなら使われるものを作りたいので、気が向いたら趣味で作ります〜(・ω・)ノ
ではでは！
