エックスサーバーのMySQLにPythonで接続してみた

こんな疑問にお答えします。

エックスサーバーのMySQLにPythonで接続しようと考え、試行錯誤したときのメモを記録しておきます。

きっかけ↓↓

データ分析をするとなったときにPythonとJupyter Notebook、Pandasの組み合わせは必須で、データはよくMySQLで管理されているため、取得してゴニョゴニョする機会がいつかあるかな〜と思い、準備しておこうと考えました。

これから紹介するコードはパラメータをAWSのRDS用に書き換えればそのまま利用できるはずです。

エックスサーバーのMySQLにPythonで接続できるの？

答えはYesです。

本記事ではPythonを使ったエックスサーバーのMySQLへの接続方法をがっつし紹介します！

環境

• Jupyter Notebook
• Python3
• Docker
• MySQL（今回はこのブログのエックスサーバーを利用）

下準備

Jupyter Notebookの環境を準備します。

作業用のDockerを立ち上げ、Docker内にJupyter NotebookやMySQLへの接続に必要なライブラリをセットアップします。

環境構築は以下の記事を参考↓

今回はSSHとMySQLに接続するために下記のライブラリをインストールします。

conda install -c conda-forge sshtunnel -y
conda install -c anaconda pymysql -y

以上で下準備は完了です。

PythonでエックスサーバーのMySQLに接続

まずはコードをぺたり。

from sshtunnel import SSHTunnelForwarder
import pymysql

class DBHelper:

    def __init__(self):
        self.db_host = "localhost"
        self.db_user = "xxxxxxxxxxx"
        self.db_password = "xxxxxxxxxxx"
        self.db_name = "xxxxxxxxxxx"
        
        self.ssh_host = 'xxxxxxxxxxx.xserver.jp'
        self.ssh_port = 10022
        self.ssh_user = 'xxxxxxxxxxx'
        self.ssh_pkey = './xxxxxxxxxxx.key'
        self.ssh_mysql_host = 'mysqlxxxxxx.xserver.jp'
        self.ssh_mysql_port = 3306
        
    def __connect__(self):
        self.server = SSHTunnelForwarder(
            (self.ssh_host, self.ssh_port),
            ssh_username=self.ssh_user,
            ssh_pkey=self.ssh_pkey,
            remote_bind_address=(self.ssh_mysql_host,self.ssh_mysql_port),
        )
        self.server.start()
        self.con = pymysql.connect(
            host=self.db_host,
            port=self.server.local_bind_port,
            user=self.db_user,
            passwd=self.db_password,
            db=self.db_name,
            # Select結果をタブルではなく辞書で受け取る
            cursorclass=pymysql.cursors.DictCursor
        )
        self.cur = self.con.cursor()

    def __disconnect__(self):
        self.con.close()
        self.server.stop()

    def fetch(self, sql):
        self.__connect__()
        self.cur.execute(sql)
        result = self.cur.fetchall()
        self.__disconnect__()
        return result

    def execute(self, sql):
        self.__connect__()
        self.cur.execute(sql)
        self.__disconnect__()

このコードを使うためにはクラス内の init に接続に必要な情報を記載します。

接続に必要な情報の取得方法は↓で紹介しています。

init に必要な情報を記述したらあとはこのクラスを使うだけです。

fetch と execute の最後に __disconnect__ メソッドを置いており、DBへの接続をクローズしています。

SQLを実行

例えば、以下の記事の情報を取得してみます。

この記事のIDは2104なので以下のようにSQLを書いて実行します。

db = DBHelper()
sql = 'SELECT * FROM wp_posts WHERE ID = 2104'
res = db.fetch(sql)
print(res)

すると次の結果を得ることができます。

[{'ID': 2104,
  'post_author': 1,
  'post_date': datetime.datetime(2021, 3, 14, 10, 43, 30),
  'post_date_gmt': datetime.datetime(2021, 3, 14, 1, 43, 30),
  'post_content': 'コンテンツの中身のため省略',
  'post_title': 'エックスサーバーのMySQLにPC（Sequel Pro）から接続する方法',
  'post_excerpt': '',
  'post_status': 'publish',
  'comment_status': 'open',
  'ping_status': 'open',
  'post_password': '',
  'post_name': 'xserver-mysql-pc',
  'to_ping': '',
  'pinged': '',
  'post_modified': datetime.datetime(2021, 3, 14, 10, 48, 28),
  'post_modified_gmt': datetime.datetime(2021, 3, 14, 1, 48, 28),
  'post_content_filtered': '',
  'post_parent': 0,
  'guid': 'https://tarovlog.com/?p=2104',
  'menu_order': 0,
  'post_type': 'post',
  'post_mime_type': '',
  'comment_count': 0}]

完璧ー。

おわりに

PythonでエックスサーバーのMySQLに接続する方法を紹介しました。

ここまで準備ができたのでブログの解析やMySQLを使ったデータ分析をやる際は有効活用していこうと思います。

データ分析におすすめ書籍