MySQL を pandas の DataFrame で操作する

Contents

はじめに
環境
ドライバーと ORM について
サンプルコード
おわりに
参考

はじめに

MySQL を pandas の DataFrame で操作する方法をまとめます。

環境

以下が今回の環境です。

$ python -V
Python 3.7.10
$ pip list | grep -e PyMySQL -e SQLAlchemy -e pandas
pandas                1.3.5
PyMySQL               1.0.2
SQLAlchemy            1.4.36

$ mysql --version
mysql  Ver 8.0.29 for Linux on x86_64 (MySQL Community Server - GPL)

MySQL を構築する手順は下記の記事を参考にしてください。

ドライバーと ORM について

ドライバーとは
- アプリケーションが行うAPIコールを、データベースが処理可能なコマンドへと変換するものです。
- アプリケーションからデータベースを操作するためには、ドライバーが必要になります。
- 本記事では MySQL のドライバーとして PyMySQL を使用します。
ORMとは
- O/Rマッパーとも表記されることがあります。
- オブジェクト指向言語から、オブジェクトをそのまま扱う感覚でデータベース操作を行えるようにするものです。
- 本記事では MySQL のドライバーとして SQLAlchemy を使用します。

サンプルコード

データベース接続情報は.python-dotenvモジュールを利用して読み込みます。
.envファイルを作成し、必要な情報を記載します。

user=root
password=mysql_password
host=localhost
port=33060
database=pets

データベース接続

データベースに接続するためのエンジンを作成します。sqlalchemy.create_engineの引数に与える url は以下の通りです。
詳細は公式ドキュメント参照してください。

engine = sqlalchemy.create_engine("{dialect}+{driver}://{username}:{password}@{host}:{port}/{database}?charset={charset_type})

import os

from dotenv import load_dotenv
import sqlalchemy

# load environment variables
load_dotenv()

# connection parameters
user = os.environ.get('user')
password = os.environ.get('password')
host = os.environ.get('host')
port = os.environ.get('port')
database = os.environ.get('database')
url = f'mysql+pymysql://{user}:{password}@{host}:{port}/{database}'

engine = sqlalchemy.create_engine(url)

以下のようなエラーが出る場合、cryptographyライブラリをインストールする必要があります。

RuntimeError: 'cryptography' package is required for sha256_password or caching_sha2_password auth methods

DataFrame を MySQL に保存する

CSV ファイルを pandas で読み込み、MySQL に保存します。
pandas.DataFrame.to_sql を使用します。

df = pd.read_csv('./dogs.csv')
df.to_sql(
    name = 'dogs',
    con = engine,
    schema = 'pets', # 無指定の場合、DBエンジン作成時に指定したDB(スキーマ)が設定される
    if_exists='replace', 
    index = False,
    chunksize = 10000,
    method = "multi",
    )

データに登録できていることが確認できます。

mysql> select * from dogs;
+------+----------+---------+-------+
| id   | name     | owner   | birth |
+------+----------+---------+-------+
|    1 | poota    | masawai | 8/23  |
|    2 | pooko    | masawai | 8/23  |
|    3 | torapoo  | masawai | 8/23  |
|    4 | kachapoo | masawai | 8/23  |
|    5 | dospoo   | masawai | 8/23  |
+------+----------+---------+-------+
5 rows in set (0.00 sec)

MySQL を DataFrame に読み込む

pandas.read_sql を使用します。

sql = 'select * from dogs where id = 1'
df = pd.read_sql(
    sql = sql,
    con = engine,
    )
print(df)
#    id   name    owner birth
# 0   1  poota  masawai  8/23

（おまけ）pandas を使わず SQL を発行する

pandas を使わず、MySQL データベースに接続してテーブルを作成するコードです。

sql = """
    CREATE TABLE dogs
    (
      id              INT unsigned NOT NULL AUTO_INCREMENT,
      name            VARCHAR(150) NOT NULL,
      owner           VARCHAR(150) NOT NULL,
      birth           DATE NOT NULL,
      PRIMARY KEY     (id)
    );
    """

engine.execute(sql)