【Python】Xserverのデータベースを操作する

PythonでXserverのデータベースを操作する方法が絶妙にわかりにくいので画像多めで必要な情報の収集場所とそれを使ったサンプルプログラムを作りました！

これがあれば外部からブログ用の情報投入が簡単になると思いますのでぜひ活用ください
※MariaDB、MySQLどちらでも使えます

Xserverの画面で必要になる項目
Pythonのプログラム
あとがき
1. 関連

Xserverの画面で必要になる項目

プログラムの中で必要になる値がXserverのサーバーパネル各画面にちりばめられているので拾いに行きます

トップ画面にある項目など

取得する項目を赤枠、この先使う画面を緑枠で囲っています

項目	値	備考
サーバーID	xs******	上部にある初期値なら”xs”で始まる値のはず
サーバー番号	sv*****	左部のアカウントデータにある初期値なら”sv”で始まる値のはず

アカウント：SSH設定画面にある項目など

まず「SSH設定タブ」で状態を”ONにする“へ設定する

この画面でしれっと”接続ポート“の補足があるので控えておく

項目	値	備考
SSH接続ポート	10022	XserverじゃないSSH接続の場合は一般的に22らしい

「公開鍵認証用鍵ペアの生成タブ」では”パスフレーズ“を設定する

項目	値	備考
パスフレーズ	自由設定	全角3文字以上(半角6文字以上)で好きな文字を設定

“生成する”ボタンを押下すると”「{サーバーID}.key」のファイル名で秘密鍵ファイル“がダウンロードされるのでここまでやって設定完了

WordPress：WordPress簡単インストール画面にある項目など

「インストール済みWordPress一覧タブ」まで進めば既に設定されている各項目を取得するのみ

項目	値	備考
MySQLデータベース名	{サーバーID}_***	設定済みの値が表示されている
MySQLユーザー名	{サーバーID}_***	設定済みの値が表示されている
MySQLパスワード	********	右の目玉アイコンをクリックで値表示される

データベース：MySQL設定画面にある項目など

「ＭyＳQL情報タブ」に表示されているホスト名を取得する

項目	値	備考
{DB名}{バージョン}ホスト名	表示されている値	“localhost”が基本的な値？昔は”mysql****.xserver.jp”だった？

その他の項目

画面から確認できない（多分）けどプログラムの中で設定が必要な値

項目	値	備考
DB接続ポート	3306	MySQL、MariaDBともに接続用のデフォルト設定は3306
文字コード	UTF-8	特に意識していなければ”UTF-8″で設定されているはず

Pythonのプログラム

必要な値を取得出来たら実際にPythonでサンプルプログラムを作ります

サンプルと言いつつ、そのまま使えるのでモジュールとして使うと便利です

必要なパッケージをインストールする

Pythonなので例によってパッケージのインストールから

コネクターはいくつかあるみたいですが”mysql-connector-python“がOracle作成の純正っぽいのでこれを使います

pip install mysql-connector-python
pip install sshtunnel

実際のプログラムと簡単な説明

↓のプログラムにXserverで集めてきた値をセットしてあげればコネクターの完成

引数にするのもありですが、複数のサーバー契約してる人は少数派だと思うのでハードコーティングしちゃう

import mysql.connector
from sshtunnel import SSHTunnelForwarder


class xserver_class:
    def __init__(self):
        self.ssh_host = '{サーバー番号}.xserver.jp'
        self.ssh_port = {SSH接続ポート}
        self.ssh_user = '{サーバーID}'
        self.ssh_password = '{パスフレーズ}'
        self.ssh_pkey = R'{秘密鍵ファイルのフルパス}'

        self.db_name = '{MySQLデータベース名}'
        self.db_user = '{MySQLユーザー名}'
        self.db_password = '{MySQLパスワード}'
        self.db_host = '{ホスト名}'
        self.db_port = {DB接続ポート}
        self.db_charset = '{文字コード}'

    def __connect__(self):
        self.server = SSHTunnelForwarder(
            (self.ssh_host, self.ssh_port),
            ssh_username=self.ssh_user,
            ssh_password=self.ssh_password,
            ssh_pkey=self.ssh_pkey,
            remote_bind_address=(self.db_host, self.db_port),
        )
        self.server.start()
        # connectのリファレンスはコチラ
        # https://dev.mysql.com/doc/connector-python/en/connector-python-connectargs.html
        self.con = mysql.connector.connect(
            host=self.db_host,
            port=self.server.local_bind_port,
            user=self.db_user,
            passwd=self.db_password,
            db=self.db_name,
            charset=self.db_charset,
            use_pure=True,
        )
        # 扱いやすいdict型を指定しておく
        self.cur = self.con.cursor(dictionary=True)
        # コネクションの設定
        self.con.autocommit = False

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

    def fetch(self, sql):
        self.con.ping(reconnect=True)
        self.cur.execute(sql)
        result = self.cur.fetchall()
        return result

    def execute(self, sql):
        self.con.ping(reconnect=True)
        self.cur.execute(sql)
        self.con.commit()

    def open(self):
        self.__connect__()

    def close(self):
        self.__disconnect__()

「db_connector.py」をモジュールにした時のサンプル

try, exceptでエラーハンドリングした方がベターなのですが、サンプルなので最低限

from db_connector import xserver_class

db = xserver_class()
db.open()

# SELECT の場合はfetchを使ってdict型でレコード取得する
sql = """
    SELECT
        *
    FROM
        sample_table;
    """
ret = db.fetch(sql)
# for でループしながらカラム名を指定してレコード毎にデータ取得できる
for row in ret:
    print(row["{カラム名}"])

# SELECT 以外はexecuteでcommitまでする
sql = """
    INSERT INTO sample_table(id, col1, col2, create_date) 
    values (0, "test", "hoge", NOW());
    """
db.execute(sql)

db.close()