python3系でローカルのmysqlを操作する方法〜その１　mysqlへアクセス〜

いつもはdjangoからしかアクセスしないローカルのmysqlですが、今回はデータ分析で使うためのデータにpythonからアクセスしたかったので、記事にしてみました。

mysqlから取得したデータを最後はスプレッドシートにあげるところまでやるのが、今回のシリーズの流れです。

日頃mysql叩くだけなら、ターミナルから実行もしくはSequel proさんにお世話になるケースが多いですが、直接pythonコードでmysql叩けた方が便利なケースも増えると思いますので、暇な時にご参考に読んでみてください。

やりたいこと
環境・install packageなどの前提条件
1. mysqlclientのインストール
  1. mysqlclientがインストールできない時
実際のコード紹介
1. 実行結果
2. 簡易コード解説
  1. mysqlへ接続するためのconfigデータについて
まとめ

やりたいこと

ざっと、以下のようなことを実施していきたいと思います。

python(pycharm環境）でmysqlへ接続
sql文をpythonコード内に入れて、pythonでmysqlのデータを取得
取得したデータをcsvにしてsheetsAPIを使ってスプレッドシートへ書き込み

今回は１と２を実施していきます。３については、過去みてきたsheetsAPIの使い方も参考になりますので、過去記事リンクも以下に貼っておきます。

pythonとsheets API v4を使ってスプレッドシートにアクセスする〜④ sheets APIを利用してスプレッドシートへデータを書き込む〜

pythonとsheets API v4を使ってスプレッドシートにアクセスしてみましょう。スプレッドシートのデータの読み書きや、そもそも前提となる認証のパス方法などを説明していきます。スクレイピングで取得したデータをスプレッドシートにアップできるようになります。今回はデータの書き込み方法の解説です。

環境・install packageなどの前提条件

以下の前提で動作するようにしてあります。２系の記事は多くあったのですが、３系の記事って実はまだあんまり無いんですね。

OS：macOS Catalina
python: 3.7
mysqlclient: 1.4.6

mysqlclientのインストール

pip3にmysqlclientをインストしておいてください。pipでインストしてもらっても問題ありません。

//ターミナルで実行
pip3 install mysqlclient

mysqlclientがインストールできない時

たぶん、brew上にmysqlがインストールされていないケースかなぁと（初めてやられる方がエラーを起こすことをイメージしています。その他のケースもあるので悪しからず）。下記方法で直らない場合はググってください。

//ターミナルで実行
brew install mysql

実際のコード紹介

以下のコードでローカルmysqlにアクセスして、対象DB・テーブルからデータを５つ取得可能です。

#!/usr/bin/env/ python
# coding:utf-8

# CSV取り扱う関係
import io
# import csv

#表示関係
from pprint import pprint

# MySQL
import MySQLdb

def sql_to_csv():
    """MySQLでクエリー検索して、結果をもとに表示する
    """

    # クエリー結果の出力先ファイルオブジェクト->次回以降でCSVファイルに化けさせて、スプレッドシートに表示するためこの名表
    csvfile = io.StringIO()

    # MySQL に接続
    config = {
        'host': '127.0.0.1',
        'password': 'XXXXX', //mysqlへ接続するためのパスワード
        'user': 'root',
        'database': 'sale_data' //利用するDBの名前、今回は販売データを利用したのでDBName:sale_dataを利用
    }

    # configをアンパック
    cnx = MySQLdb.connect(**config)
    cursor = cnx.cursor()

    # SQL を投げる
　　# DB内にcoupon_logというテーブルがあるので、そこから全データ取得して５件だけ取得
    cursor.execute("""
  SELECT * FROM coupon_log
  LIMIT 5
    """)

   #fetchallでデータを取得して、データを一行ずつcsvfileに書き込む
    rows = cursor.fetchall()
    if rows:
        for r in rows:
            csvfile.write(str(r))

    # MySQL への接続を切る
    cursor.close()
    cnx.close()

    # csvfileに一時保存されてるデータを取得、csvfileを閉じる
    csv_data = csvfile.getvalue()
    csvfile.close()

    return csv_data

if __name__=="__main__":
    data = sql_to_csv()
    pprint(data)

実行結果

実行すると以下のように表示されるかと思います。データの中身はみなさんがアクセスしたDBによりけりですが。

簡易コード解説

さて、一部ではありますが、mysqlへ接続するための基本的なお作法について説明していきます。

mysqlへ接続するためのconfigデータについて

コードだとこの部分の説明です。

# MySQL に接続
    config = {
        'host': '127.0.0.1',
        'password': 'XXXXX', //mysqlへ接続するためのパスワード
        'user': 'root',
        'database': 'sale_data' //利用するDBの名前、今回は販売データを利用したのでDBName:sale_dataを利用
    }