ExcelのデータをDataFrame経由でMySQLに登録する
複数個あるエクセルのデータをMySQLに登録した時のメモ
エクセルをDataFrameに読み込み、DataFrameからMySQLのテーブルにデータを登録する。
ライブラリインストール
python用のORM、sqlalchemcyをインストール。Jupyter Notebookから以下のコマンドを実行
!pip install sqlalchemy
MySQL接続設定
url中の括弧内を接続環境に合わせて変更する。
import glob import pandas as pd import sqlalchemy as sa url = 'mysql+pymysql://(user):(password)@(host):(ip)/(database_name)?charset=utf8' engine = sa.create_engine(url, echo=False)
エクセル読み込み&DB登録
- 特定のディレクトリ配下に、同じフォーマットのエクセルデータが複数存在している前提
- ファイルを一つ一つ読み込み、DBに流し込んでいく。
# 指定したディレクトリ配下のファイルをすべて読み込み files = glob.glob('/(file_path_to)/*') for i, file in enumerate(files): print(i, file) # エクセルをDataFrameに読み込み。(列名は2行目に存在している想定) df = pd.read_excel(file, header=1) # 指定したテーブルにデータを登録 df.to_sql('xxxxx_tbl', engine, index=True, if_exists='append')
DataFrameのto_sql関数の引数if_existsをappendにすることで、テーブルが存在しない場合は、テーブルを新規作成しデータ登録、存在する場合はデータの追加登録。
pandas.DataFrame.to_sql — pandas 1.2.1 documentation
登録データの件数確認
登録完了後、以下を実行することでテーブルの件数を確認
engine.execute("SELECT count(1) FROM xxxxx_tbl").fetchall()
以上
