pandas.DataFrame.to_sqlの落とし穴と対処法

バッチでデータフレーム型のデータを元に、DB上に仮テーブルを作ったものの
object型のカラムのデータの64文字目以降が勝手に消えていた。
エラーも警告も出なかったのに…なので対処法まとめ。

---

以下のようなデータを元にDBにテーブルを作るとする(nameは70文字)

sample.csv

name,age
AAAAAAAAAABBBBBBBBBBCCCCCCCCCCDDDDDDDDDDEEEEEEEEEEFFFFFFFFFFGGGGGGGGGG,20
AAAAAAAAAABBBBBBBBBBCCCCCCCCCCDDDDDDDDDDEEEEEEEEEEFFFFFFFFFFGGGGGGGGGG,18
AAAAAAAAAABBBBBBBBBBCCCCCCCCCCDDDDDDDDDDEEEEEEEEEEFFFFFFFFFFGGGGGGGGGG,23
AAAAAAAAAABBBBBBBBBBCCCCCCCCCCDDDDDDDDDDEEEEEEEEEEFFFFFFFFFFGGGGGGGGGG,21

pandasのto_sql関数とmysql.connectorを使った場合

サンプルソース

sample_to_sql.py

import pandas as pd
import mysql.connector
df = pd.read_csv('sample.csv')
table_name = "sample_to_sql"
db_settings = {
    "host": <ホスト名>,
    "database": <データベース名>,
    "user": <ユーザ名>,
    "password": <パスワード>,
    "port":3306
}
con = mysql.connector.connect(**db_settings)
df.to_sql(table_name, con, flavor='mysql', index=False)

結果

少しわかりにくいが、nameの64文字目以降が消えている。

テーブル情報を見ると

63文字制限になっている…
object型のカラムはvarchar(63)でテーブルを作るようにハードコーディングされていた。
(~~~/site-packages/pandas/io/sql.pyの1310行目あたり)

解決策

上のスクリプトを実行すると、df.to_sqlの部分でこんな警告が出る。

FutureWarning: The 'mysql' flavor with DBAPI connection is deprecated and will be removed in future versions. MySQL will be further supported with SQLAlchemy connectables.

ということで、sqlalchemyのインストール

sudo pip install sqlalchemy

サンプルコード

sample_sqlalchemy.py

import pandas as pd
from sqlalchemy import create_engine
df = pd.read_csv('sample.csv')
table_name = "sample_sqlalchemy"
db_settings = {
    "host": <ホスト名>,
    "database": <データベース名>,
    "user": <ユーザ名>,
    "password": <パスワード>,
    "port":3306
}
engine = create_engine('mysql://{user}:{password}@{host}:{port}/{database}'.format(**db_settings))
df.to_sql(table_name, engine, flavor='mysql', index=False)

この時点でデータフレームの中身にあったテーブルが作られる。
中身を見るとこんな感じ。

varcharだったのが、textになっていた。データはきちんと入っている。