PythonでSSH経由のMySQLアクセスをして、WordPressの投稿情報を取得する

今回のお題

外部接続が許可されていないMySQLにSSH経由でログインし、localhostとしてMySQLに接続し、データを取得する。
WordPressのDBにアクセスし、期間・ユーザ・記事の場所を検索条件にし、記事を書いたかどうか調べる。

SSHTunnelForwarderを導入し、SSH接続のプログラムを記載する

ssh.py

# -*- coding: utf-8 -*-
from sshtunnel import SSHTunnelForwarder
# モジュール読み込み
import pymysql.cursors

# SSH関連の設定
with SSHTunnelForwarder(
  ("SSH接続先ホスト名", SSH接続ポート),
  ssh_host_key="SSHホストキー（使用しないならNone）",
  ssh_pkey="SSHファイルの鍵のパス",
  ssh_username="接続ユーザ名(使用しないならNone)",
  ssh_password="接続パスワード or 鍵ファイルのパスフレーズ(使用しないならNone)",
  remote_bind_address=("127.0.0.1(ローカルホストとしてMySQLに接続するので)", MySQLのポート)
 ) as ssh:

 # MySQL接続処理を書く

大体こんな感じですかね。上記でSSH接続はできるはずなんで、次にMySQL接続しデータを取得します。
別に記載したこちらの記事を元に、MySQL接続の内容を記述する。
Python2.7でPyMySQLを使用してSQLを実行する

ssh.py

~ ここまで上記のソース ~
    # MySQLに接続する
    conn = pymysql.connect(host='127.0.0.1（localhostなので）',
                                user='ユーザ名',
                                password='パスワード',
                                db='DB名',
                                charset='utf8',
                                cursorclass=pymysql.cursors.DictCursor)
    # select
    # SQLを実行する
    cursor = conn.cursor()
    sql = "show tables"
    cursor.execute(sql)

    # Select結果を取り出す
    rets = cursor.fetchall()
    for r in rets:
        print(r)

    # MySQLから切断する
    conn.close()

と書くと、下記の形式でWordPressのテーブル名が全て取れました。
問題なく動いたので、今度はWordPressの対象テーブルから特定のデータを抽出します。
{u'Tables_in_DB名': u'wp_テーブル名'}

WordPressのテーブルの中身を見てみる。

実際に見てみると、そこまで難しいテーブル構成はしておりません。
https://wpdocs.osdn.jp/%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9%E6%A7%8B%E9%80%A0
こちらを参照すると、内容と関連性が分かりますね。

必要そうなテーブルは、とりあえず下記の2つで大丈夫そうです。

1. wp_users(ユーザ情報)
2. wp_posts（ページ情報）

検索条件を確認する

• wp_posts.post_author = wp_users.idで間違いなさそうです。
• wp_usersで該当ユーザのidを取得し、wp_posts.post_authorの検索条件にすれば該当ユーザの記事を取得できそうです。
• wp_posts.post_dateで、作成日時が分かりますので、日付指定はこちらを使用すれば行けそうです。
• wp_posts.post_statusで、投稿状態が見れます。とりあえずpublishで大丈夫そうです。
• wp_posts.post_typeで、postを選べば投稿になりそうです。

おそらくこの辺りだと思います。
以上を踏まえ、対象ユーザが対象期間中に記事を投稿したかどうかは、下記のSQLで行けそうです。

count.sql

select
	count(id)
from
	wp_posts
where
	post_author = 対象ユーザのID(wp_usersのid)
	and
	post_date > '年月日'
	and
	post_status = 'publish'
	and
	post_type = 'post'

対象データがある場合、1がちゃんと返ってきました。
細かいことは別途検証するとして、とりあえずはこれで行きましょう。

例で、特定ユーザが特定期間に書いているならtrue、なければfalseとなるような関数はこのようなかたちかと。

wordpress.py

# -*- coding: utf-8 -*-
from sshtunnel import SSHTunnelForwarder
# モジュール読み込み
import pymysql.cursors

def is_wordpress_write(id, date):
~ 上記のSSH接続のソース ~ 

  ~ 上記のMySQLの接続部分 ~
            # SQLを実行する
            sql = "select count(id) as count" \
                    " from " \
	                " wp_posts " \
                    " where " \
	                " post_author = %s " \
	                " and " \
	                " post_date > %s " \
	                " and " \
	                " post_status = 'publish' " \
	                " and " \
	                " post_type = 'post' "
            # IDと日付をメソッドの引数から指定する
            cursor.execute(sql, (id, date))

            # Select結果を取り出す
            rets = cursor.fetchall()
            is_write = False
            for r in rets:
                if r["count"] >= 1:
                    is_write = True

            # cursorクローズ
            cursor.close

        # MySQLから切断する
        connection.close()
  return is_write

print(is_wordpress_write(1, '2017-11-11'))

で実行したところ、記事があればTrueが、なければFalseが返ってきました。

SSHトンネルからの、MySQL接続をし、データの状態によってTrue/Falseが変わる処理ができたと思います。