はじめに
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 を構築する手順は下記の記事を参考にしてください。
https://poota.net/archives/685
ドライバーと 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)
おわりに
MySQL を pandas の DataFrame で操作する方法をまとました。この記事がどなたかの参考になれば幸いです。