Edited at

pandasのDataFrameがto_sqlで保存できないときの対処法

pandasのDataFrameをMySQLに保存しようとしたときに、pandas.io.sql.DatabaseError: Execution failed on sql WHERE type='table' AND name=?;: not all arguments converted during string formattingとエラーが出て詰まったので解決した方法を残しておきます。


サンプルソース


sanple.py

import pandas as pd

import pymysql

data = {
'red' : ["apple"],
'yellow': ["banana"],
'orange' : ["orange"]
}
df = pd.DataFrame(data)
print(df)
## データフレーム↓
# red yellow orange
# 0 apple banana orange


例になりますが、私は上記のようなDataFrameをpandasのto_sqlを使ってMySQLに保存しようとしていました。

下記がその時のコードです。


sample.py

import pandas as pd

import pymysql

data = {
'red' : ["apple"],
'yellow': ["banana"],
'orange' : ["orange"]
}

def mysql_connect():
connection = pymysql.connect(
host="localhost",
user="root",
db="sample_db",
password="password",
charset="utf8",
cursorclass=pymysql.cursors.DictCursor)
return connection

con = mysql_connect()
df = pd.DataFrame(data)
df.to_sql("table_name", con, index=None)


この方法でdbに保存しようとするとタイトルのエラーが発生してしまいました。原因を調べてみると、どうやら変数「con」にdbのコネクションを格納してto_sqlの引数に渡す方法はdbがsqliteの場合かsqlalchemy等を使ってマッピング(ORM)をしてあげないといけないらしいです。

こちらは参考にした記事になります

https://stackoverflow.com/questions/43136121/questions-about-pandas-to-sql


解決方法

今回はsqlalchemyを使うことで解決しました。


sample.py

import pandas as pd

import pymysql
import sqlalchemy as sqa

data = {
'red' : ["apple"],
'yellow': ["banana"],
'orange' : ["orange"]
}

# データベースの情報を変数urlに入れています。
url = 'mysql+pymysql://root:password@localhost/sample_db?charset=utf8'
engine = sqa.create_engine(url, echo=True)
df = pd.DataFrame(data)
df.to_sql("table_name", url, index=None)


ちなみにデータベースのurl指定の書き方は以下になります(pymysqlの場合)

mysql+pymysql://<username>:<password>@<host>:<port>/<database>?charset=<charset_type>

詳しい書き方はこちらの記事が参考になります

https://www.st-hakky-blog.com/entry/2017/08/13/130202