エックスサーバーのMySQLにPythonで接続できるの?
こんな疑問にお答えします。
- エックスサーバーの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を使ったデータ分析をやる際は有効活用していこうと思います。