Python Tech

Python NaNを含むデータをMySQLに保存したいとき(コードとエラー対処)

2021年6月1日

Pythonでデータ処理した後にMySQLなどのデータベースに保存する操作をすることはよくあります。

Pythonにまだ慣れていないという人でも、Pandasなどで処理したデータをMySQLに保存したことは何度もあるのでは?

今回の記事では、Pandasのデータ(DataFrameやSeries)にNaNやNoneが含まれているときに、MySQLからエラーが返ってきたときの対処法をまとめています。

実行環境

Python 3.9
Pandas 1.2.3
SQLAlchemy 1.4.5
PyMySQL 1.0.2

 

nan can not be used with MySQLエラーに対処する

このようなエラーが出ましたか?

sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) nan can not be used with MySQL

これは、NaNを含むデータをMySQLにUPDATEまたはINSERTしようとしたときに発生します。

エラーを再現してみます。

 

まずは適当なデータ(カンマ区切りのテキストファイル形式)を用意し、読み込ませます。

csv = ("tag,name\n"
    + "A,Alpha\n"
    + "B,Bravo\n"
    + "C,\n"
    + "D,Delta\n")
stream = io.StringIO(csv)
df = pd.read_csv(stream)

1行目はヘッダーとして扱います。
今回、4行目(tag='C')のname列を空欄としました。

pandas.read_csv で読み込むと、空欄(空文字)のところは自動でNaN(np.nan)になります。

printして見てみます。

print(df)

#   tag   name
# 0   A  Alpha
# 1   B  Bravo
# 2   C    NaN    # 空欄の部分はNaNに
# 3   D  Delta

 

次に、このdf をそのままSQLAlchemyのORMでaddしようとすると…

for _, d in df.iterrows():
    tag = TagName(tag=d['tag'], name=d['name'])
    session.add(tag)
session.commit()

こんな感じのエラーとなります。

sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) nan can not be used with MySQL
[SQL: INSERT INTO tag_names (tag, name) VALUES (%(tag)s, %(name)s)]
[parameters: {'tag': 'C', 'name': nan}]
(Background on this error at: http://sqlalche.me/e/14/f405)

nan は MySQLでは扱えないよ!と怒られちゃいましたね。

そのままだと保存できないので、None などに変換してあげましょう

import numpy as np

df = df.replace({np.nan: None})

これで、もう一度 add / commitを実行してみると、正常に挿入が完了します。

MySQL側をみれば、空欄だったところがnullで保存されているはずです。

 

pandasa.to_sqlでNaNをnullとしてINSERT

ちなみに別の方法もあります。

SQLAlchemy ORMではなくCoreでinsertすればnanのままでも更新操作ができます。

engine = get_engine()
df.to_sql(
    'tag_names',  # テーブル名
    con=engine,
    schema='playground',
    if_exists='append',
    index=False
)

DataFrameの中身をデータベースに書き込む pandas.to_sqlメソッドを使っています。

この場合も、nanだった部分がMySQLにはnullとして保存されます。

 

雑記

ちなみにPythonの世界ではNone、NaNはそれぞれ違うものです。

これらの違いはこちらのまとめで。

 

  • この記事を書いた人

次世代ペンギン

長いのでペンギンとお呼びください。システム開発・プログラミングのお仕事をしています。甘味とコーヒーは生命線。日常での学びを記事にしています。

人気の記事

1

キャリアアップのため、または高収入を目指して、しっかりプログラミングを学びたいという人が増えてきましたね。 この記事では現役のエンジニアである私が、実際に仕事で稼げるようになるためのスクール選びで失敗 ...

2

先日の記事では、初心者からフリーランスプログラマーになる難しさと、それでもなんとか稼げるようになるにはどうしたらいいか解説しました。 今回の記事では、稼げるフリーランサーになるために必要な要素を、もう ...

3

気休めだけの甘い言葉は書きません。 最近は多くの企業やサイトで、使い捨てられるプログラマーが欲しいがために、甘い言葉で初心者プログラマを誘い出しています。 この記事では、まずは「現実」をちゃんと理解し ...

4

Vuetifyの v-progress-circular コンポーネントは、数値データや処理状況を環状(円状)のデザインで教えてくれるUIデザインです。 ローディングのスピナー(処理中のマーク)として ...

5

※画像はずとまよの新曲とは一切関係ありません   ずっと真夜中でいいのに。(以下、ずとまよ)の新曲『勘ぐれい』のMVが12月1日に公開されましたね。 MV前に公開されていた原曲を聴き、神曲な ...

6

Vuexのstore(ストア)を使うと、各コンポーネント間で個別にデータのやり取りすることなく、データを一元的に管理できます。Vueでは欠かせない機能といえるでしょう。 state(変数、コンポーネン ...

-Python, Tech
-,

© 2021 ペンギンのーと