仮想サーファーの波乗り

仮想化エンジニアの日常

プログラミング・SNS分析・仮想通貨・自動化などに関してよく書く雑記ブログ

PythonでSlackBot開発⑨「Twitterのつぶやき情報をMySQLに保存する」


PythonでSlackBotを開発しよう企画の第9回目。

今回は、tweepyとslackAPIを活用して、slackで入力を受け付けてTwitter上の特定のユーザーのつぶやき情報をMySQLに保存していく処理を実装していきます。


MySQLにテーブル作成

まずは、MySQLにテーブルを作成しておきます。以下のようなテーブルでテーブルを作成します。

# 「ツイッターツイート」テーブル
CREATE TABLE twitter_tweet
  (tweet_id int AUTO_INCREMENT COMMENT 'ツイートID',
   twitter_user_id int NOT NULL COMMENT 'ツイッターユーザーID',
   twitter_tweet_id int NOT NULL COMMENT 'ツイッターツイートID',
   tweet_text varchar(255) NOT NULL COMMENT 'ツイート本文',
   tweet_datetime datetime NOT NULL COMMENT 'ツイート日時',
   ins_datetime datetime NOT NULL COMMENT '作成日時',
   upd_datetime datetime NOT NULL COMMENT '更新日時',
   INDEX(tweet_id),
   PRIMARY KEY (tweet_id),
   FOREIGN KEY (twitter_user_id) REFERENCES twitter_user(twitter_user_id)
   ) ENGINE=InnoDB CHARSET=utf8 COMMENT='ツイッターツイート';

MySQLのテーブル準備ができたら、プログラムを書いていきます。


ツイートをMySQLに保存する処理

今回はの処理は、以下のような流れで進んでいきます。
run.pyを起動

slackbot起動

slackに文字列入力

slackbot_template.pyを呼び出し

twitter_service.pyを呼び出し、Twitterからデータ取得

twitter_tweet_repository.py(とcommon_repository.py)を呼び出し

twitter_tweet_entity.pyでORマッピングしてMySQLにアクセスしデータ保存

以上の処理を実現するために、「run.py」、「slackbot_template.py」、「twitter_service.py」、「twitter_tweet_repository.py」「twitter_tweet_entity.py」を以下のように記述していきます。

run.py

# coding=utf-8
from slackbot.bot import Bot
from template import slackbot_template
from service import batch

def main():
    bot = Bot()
    bot.run()

if __name__ == '__main__':
    main()

slackbot_template.py

from slackbot.bot import respond_to
from service import twitter_service

@respond_to('collectTweet (.*)')
def collect_user_tweet(message, word):
    twitter_service.collect_user_tweet(word)

twitter_service.py

# coding=utf-8
import tweepy
import os
from repository import twitter_tweet_repository

def prepare_twitter_api():
    """
    TwitterのAPIアクセスキーを取得
    """
    auth = tweepy.OAuthHandler(os.environ['CONSUMER_KEY'], os.environ['CONSUMER_SECRET'])
    auth.set_access_token(os.environ['ACCESS_TOKEN'], os.environ['ACCESS_TOKEN_SECRET'])
    return tweepy.API(auth)

def collect_user_tweet(user_screen_name):
    """
    Twitterのuser_screen_name(@taroのtaroの部分)のユーザーのつぶやきをDBに登録します。
    """
    for status in search_user_tweet(user_screen_name):
        user_id = status.user.id
        tweet_id = status.id
        tweet_text = status.text
        tweet_datetime = status.created_at
        twitter_tweet_repository.add_tweet(user_id, tweet_id, tweet_text, tweet_datetime)

def search_user_tweet(user_screen_name):
    """
    Twitterのuser_screen_name(@taroのtaroの部分)のユーザーのつぶやきを200件取得します。
    """
    api = prepare_twitter_api()
    return api.user_timeline(screen_name=user_screen_name, count=200)

twitter_tweet_repository.py

# coding=utf-8
from repository import common_repository
from entity.twitter_tweet_entity import TwitterTweet

# 登録処理
def add_tweet(user_id, tweet_id, text, datetime):
    # トランザクション開始
    session = common_repository.create_session()
    # user追加
    test_user = TwitterTweet(user_id=user_id, twitter_tweet_id=tweet_id, tweet_text=text, tweet_datetime=datetime)
    session.add(test_user)
    # 変更をコミット
    session.commit()

common_repository.py

# coding=utf-8
import os
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

# 環境変数から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)

def create_session():
    Session = sessionmaker(bind=engine)
    return Session()

twitter_tweet_entity.py

import os
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from datetime import datetime

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 TwitterTweet(Base):
    __tablename__ = 'twitter_tweet'

    tweet_id = Column(Integer, primary_key=True)
    user_id = Column(Integer, nullable=False)
    twitter_tweet_id = Column(Integer, nullable=False)
    tweet_text = Column(String, nullable=False)
    tweet_datetime = Column(DATETIME, nullable=False)
    ins_datetime = Column(DATETIME, default=datetime.now, nullable=False)
    upd_datetime = Column(DATETIME, default=datetime.now, nullable=False)

if __name__ == "__main__":
    # create table
    Base.metadata.create_all(engine)


以上の変更ができたら、「$ forego run python run.py」でrun.pyを起動します。slackで「@virtual-surfer-bot collectTweet {user_screen_name}」でそのスクリーン名を持っているユーザーの直近の200件のツイートをMySQLに保存してくれるはずです。実行してみます。

f:id:virtual-surfer:20180413010009p:plain


terminalに出力されているMySQLの状況を確認しようとすると、エラーになってる...。

sqlalchemy.exc.DataError: (_mysql_exceptions.DataError) (1264, "Out of range value for column 'twitter_tweet_id' at row 1") [SQL: 'INSERT INTO twitter_tweet (user_id, twitter_tweet_id, tweet_text, tweet_datetime, ins_datetime, upd_datetime) VALUES (%s, %s, %s, %s, %s, %s)'] [parameters: (79119874, 984403750214250496, 'RT @crypto: Bitcoin surged the most on an intraday basis since December https://t.co/BQchaGCmB3 https://t.co/xxLX36ad6b', datetime.datetime(2018, 4, 12, 12, 11, 58), datetime.datetime(2018, 4, 13, 0, 34, 13, 355369), datetime.datetime(2018, 4, 13, 0, 34, 13, 355384))] (Background on this error at: http://sqlalche.me/e/9h9h)

どうやら、twitter_idの値が大きすぎてint型では扱えないようですね。IDが98京って...。これまでのTwitter社のツイート数の蓄積恐るべし。


INT型だと持てないので、mysqlで以下のコマンドで該当カラムをBIGINT型に変換します。

mysql > alter table twitter_tweet change twitter_tweet_id twitter_tweet_id bigint;

twitter_tweet_entity.pyの型もBIGINTに変えておきます。

twitter_tweet_entity.py

...
class TwitterTweet(Base):

    ...
    twitter_tweet_id = Column(BigInteger, nullable=False)
    ...
...


これでMySQLに保存できるか...!!再度挑戦します。

sqlalchemy.exc.OperationalError: (_mysql_exceptions.OperationalError) (1366, "Incorrect string value: '\\xF0\\x9F\\xA4\\x94 h...' for column 'tweet_text' at row 1") [SQL: 'INSERT INTO twitter_tweet (user_id, twitter_tweet_id, tweet_text, tweet_datetime, ins_datetime, upd_datetime) VALUES (%s, %s, %s, %s, %s, %s)'] [parameters: (79119874, 984350522177028096, 'RT @ha_chu: 漫画村の件で、ちょっが再RTされまくりだ🤔 https://t.co/8DBsCtypGf', datetime.datetime(2018, 4, 12, 8, 40, 27), datetime.datetime(2018, 4, 13, 0, 40, 5, 248947), datetime.datetime(2018, 4, 13, 0, 40, 5, 248958))] (Background on this error at: http://sqlalche.me/e/e3q8)

またもや失敗...(T ^ T)

今回は絵文字の対応ができるようにMySQLの設定をしてなかったのが問題だったようですね。「mysql > show variables like 'character%';」でMySQLの文字コードを確認します。

mysql> show variables like 'character%';

+--------------------------+------------------------------------------------------+
| Variable_name            | Value                                                |
+--------------------------+------------------------------------------------------+
| character_set_client     | utf8                                                 |
| character_set_connection | utf8                                                 |
| character_set_database   | utf8                                                 |
| character_set_filesystem | binary                                               |
| character_set_results    | utf8                                                 |
| character_set_server     | utf8                                                 |
| character_set_system     | utf8                                                 |
| character_sets_dir       | /usr/local/Cellar/mysql/5.7.21/share/mysql/charsets/ |
+--------------------------+------------------------------------------------------+
8 rows in set (0.00 sec)

UTF-8になっているので、これを4バイトのUTF-8 Unicodeエンコーディングに設定変更します。terminalで「$ sudo vi /etc/my.cnf」でmy.cnfファイルに以下のように記述します。

/etc/my.cnf

#character-set-server = utf8
character-set-server = utf8mb4
#default-character-set = utf8
default-character-set = utf8mb4

これで再度mysqlにログインしなおして、文字コードの確認をすると...。

mysql> show variables like 'character%';

+--------------------------+------------------------------------------------------+
| Variable_name            | Value                                                |
+--------------------------+------------------------------------------------------+
| character_set_client     | utf8mb4                                              |
| character_set_connection | utf8mb4                                              |
| character_set_database   | utf8                                                 |
| character_set_filesystem | binary                                               |
| character_set_results    | utf8mb4                                              |
| character_set_server     | utf8                                                 |
| character_set_system     | utf8                                                 |
| character_sets_dir       | /usr/local/Cellar/mysql/5.7.21/share/mysql/charsets/ |
+--------------------------+------------------------------------------------------+
8 rows in set (0.00 sec)

utf8mb4に設定変更できてますね!!これで絵文字もエラーになることなく登録できるはずです!


再度ツイートの保存処理を走らせます...。

sqlalchemy.exc.IntegrityError: (_mysql_exceptions.IntegrityError) (1062, "Duplicate entry '984403750214250496' for key 'twitter_tweet_id'") [SQL: 'INSERT INTO twitter_tweet (user_id, twitter_tweet_id, tweet_text, tweet_datetime, ins_datetime, upd_datetime) VALUES (%s, %s, %s, %s, %s, %s)'] [parameters: (79119874, 984403750214250496, 'RT @crypto: Bitcoin surged the most on an intraday basis since December https://t.co/BQchaGCmB3 https://t.co/xxLX36ad6b', datetime.datetime(2018, 4, 12, 12, 11, 58), datetime.datetime(2018, 4, 13, 1, 22, 10, 416377), datetime.datetime(2018, 4, 13, 1, 22, 10, 416389))] (Background on this error at: http://sqlalche.me/e/gkpj)

またダメ...(´・_・`)

今度は、twitter_tweet_idにユニークキーを貼っていたので再度同じtwitter_tweet_idで登録しようとしてエラーになってしまいました。暫定対応として、すでに登録されているものはエラーをキャッチして無視するようにします。

twitter_service.py

...
def collect_user_tweet(user_screen_name):
    """
    Twitterのuser_screen_name(@taroのtaroの部分)のユーザーのつぶやきをDBに登録します。
    """
    for status in search_user_tweet(user_screen_name):
        user_id = status.user.id
        tweet_id = status.id
        tweet_text = status.text
        tweet_datetime = status.created_at
        # ここで例外キャッチ
        try:
            twitter_tweet_repository.add_tweet(user_id, tweet_id, tweet_text, tweet_datetime)
        except:
            print('何らかのエラー発生')
...

これで、すでに同じtwitter_tweet_idが登録されているものはエラーが発生するけど、そのエラーを無視して次のツイート情報の登録処理に移行することができるようになったはずです。(例外発生原因を握りつぶしているので、本番開発時にやると悲惨な目に遭います...。)

再度動かします!

MySQLに保存できているかSELECTしてテーブル内のレコードを確認すると...

mysql> select * from twitter_tweet;

+----------+----------+--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+---------------------+---------------------+
| tweet_id | user_id  | twitter_tweet_id   | tweet_text                                                                                                                                                                                                                                                                                                                                                                                                                 | tweet_datetime      | ins_datetime        | upd_datetime        |
+----------+----------+--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+---------------------+---------------------+
|        1 |        1 |                  1 | test                                                                                                                                                                                                                                                                                                                                                                                                                       | 2018-04-12 23:45:37 | 2018-04-12 23:45:37 | 2018-04-12 23:45:37 |

...
|      223 | 79119874 | 983612619306106881 | 副業禁止とか、ほんとデメリットですねぇ。 https://t.co/JlgMtvrjkt                                                                                                                                                                                                                                                                                                                                                           | 2018-04-10 07:48:17 | 2018-04-13 01:32:41 | 2018-04-13 01:32:41 |
|      224 | 79119874 | 983612275146686468 | やりますね〜! https://t.co/3EBwE3Hnle                                                                                                                                                                                                                                                                                                                                                                              | 2018-04-10 07:46:55 | 2018-04-13 01:32:41 | 2018-04-13 01:32:41 |
+----------+----------+--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+---------------------+---------------------+
195 rows in set (0.00 sec)

ちゃんと登録できていますね!これでつぶやき情報をMySQLに保存することができました!(疲れた...。)


まとめ

今回のMySQLにツイートデータを保存する処理と、前回の自動定期実行処理を組み合わせれば、特定のユーザーのつぶやきデータを全て保存しておくこともできます。いろんなことに応用できると思うので、またいろいろ試してみます。


では!