雑記

pandasで繋がるDockerの上のMySQL

DockerでMySQLサーバを構築する

こちらの記事を参考に、DockerでMySQLを構築します。

yuki-PC:temp yuki$ docker pull mysql

イメージを取得しました。次にコンテナを立てます。

yuki-PC:temp yuki$ docker run --name mysql -e MYSQL_ROOT_PASSWORD=mysql -d -p 3306:3306 mysql

コンテナを起動できました。

docker psでCONTAINE_IDを取得しました。

yuki-PC:temp yuki$ docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                               NAMES
5fbb32afb6c8        mysql               "docker-entrypoint.s…"   5 minutes ago       Up 5 minutes        0.0.0.0:3306->3306/tcp, 33060/tcp   mysql

取得したIDでコンテナ接続します。

yuki-PC:temp yuki$ docker exec -it 5fbb32afb6c8 /bin/bash

接続したら、MySQLに接続します。

root@5fbb32afb6c8:/# mysql -uroot -pmysql

接続できたら、データベースを作成し、移動します。

mysql> CREATE DATABASE SAMPLE01;
Query OK, 1 row affected (0.02 sec)

mysql> USE SAMPLE01;
Database changed

そしてテスト用のテーブルを2つ作ります。

mysql> CREATE TABLE TEST1 (A INT, B INT, C INT);
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE TEST2 (A INT, B INT, C INT);
Query OK, 0 rows affected (0.05 sec)

データも入れておきます。

mysql> INSERT INTO TEST1 (A, B, C) VALUES(1, 2, 3);
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO TEST1 (A, B, C) VALUES(4, 5, 6);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO TEST1 (A, B, C) VALUES(7, 8, 9);
Query OK, 1 row affected (0.02 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)

それでは続いて、PythonからDockerのMySQLにアクセスしたいと思います。

PandasからDockerのMySQLにアクセスする

PandasでMySQLのデータを読み込む

こちらの記事を参考に疎通確認してみたところ、Trueで返ってきたの、無事疎通しているようです。

import mysql.connector

# コネクションの作成
conn = mysql.connector.connect(
  host='localhost',
  port='3306',
  user='root',
  password='mysql',
  database='SAMPLE01'
)

# コネクションが切れた時に再接続してくれるよう設定
conn.ping(reconnect=True)

# 接続できているかどうか確認
print(conn.is_connected())

それではpandasでデータを読み取ってみます。

import pandas as pd
sql = """
        SELECT *
            FROM 
        TEST1
"""
df = pd.read_sql(sql, conn)  # connは上のコードで生成したconn
df

PandasでMySQLにデータを入れる

先に結論

sqlalchemyというライブラリを新しく利用する必要があります。ここにコードを貼ってしまってもいいのですが、つまづきポイントが設定周りで多くあるので、順番に書いていきます。

怒涛のエラー

上と同様のクエリでTEST2テーブルにはまだデータがないことを確認します。

pandas mysql docker

ただしここで失敗しました。エラー内容は以下の通りです。

色々調べていくと、sqlalchemyというものが必要になったようです。

こちらの記事を参考に進めていきます。

まずをsqlalchemy利用するにはMySQLDBを使えるようにする必要があるようです。なのでおとなしく入れることにします、

$pip install mysqlclient
もしクライアントにMySQLがはいっていなければ、入れる必要があります。(わたしもインストール時にエラーになりましたが、macでbrew install mysqlでいけました。)

次にまたエラーが起きました。どうやらhostをlocalhostに指定していると、docker上のMySQLではなく、クライアント側に直に置いてあるMySQLを参照しに行ってしまうようです。

これを回避するための方法はlocalhostを127.0.0.1に置き換えることです。(こちらの記事にお世話になりました。)

そして以下を実行することで、データを挿入することができました。

from sqlalchemy import create_engine

#engine = create_engine('database://testuser:testpass@localhost/sample_db?charset=utf8')
engine = create_engine('mysql://root:mysql@127.0.0.1:3306/SAMPLE01?charset=utf8')
df1.to_sql('TEST2',engine,if_exists='replace',index=False)
conn.commit

確認のために以下のクエリを実行すると、df1(TEST1から取得したDataFrame)のデータが入っていました。

sql = """
        SELECT *
            FROM 
        TEST2
"""
df2 = pd.read_sql(sql, engine)
df2

また、pd.read_sqlの2番目の引数はengineに置き換えました。これはmysql.connectorと同様の役割を果たしています。もしデータの挿入から行いたい時は、最初からsqlalchemyの create_engineを使うとよいです。

結論

import pandas as pd
from sqlalchemy import create_engine

# sqlalchemyを利用して接続する
engine = create_engine('mysql://root:mysql@127.0.0.1:3306/SAMPLE01?charset=utf8')

# 1つ目のテーブルからデータを取得する
sql1 = """
        SELECT *
            FROM 
        TEST1
"""
df1 = pd.read_sql(sql1, engine)
print(df1)
print("====")

# 任意の計算処理を行う(不要ならスキップ)
df1*=3
print(df1)
print("====")

# 2つ目のテーブルにデータを挿入する
df1.to_sql('TEST2',engine,if_exists='replace',index=False)
conn.commit

# データを確認する
sql2 = """
        SELECT *
            FROM 
        TEST2
"""
df2 = pd.read_sql(sql2, engine)
print(df2)
print("====")

となります!

お読みいただきありがとうございました!
ABOUT ME
hirayuki
今年で社会人3年目になります。 日々体当たりで仕事を覚えています。 テーマはIT・教育です。 少しでも技術に親しんでもらえるよう、noteで4コマ漫画も書いています。 https://note.mu/hirayuki