Python のデータ分析ライブラリと言えば Pandas が定番かと思いますが、Pandas を使えば Excel や CSV ファイルの中身に簡単にデータベースに取り込むことができることをつい最近知ったので、ここにまとめてみたいと思います。
環境
- Python 3.8.10
- Python ライブラリ
- Pandas 2.0.2
- SQLAlchemy 2.0.16
- PostgreSQL 13.8
下準備
今回は PostgreSQL データベースにデータ取り込みを行ってみます。
クラウドでもローカル環境でもどこでも良いのですが、今回はローカル環境に PostgreSQL コンテナを立ててみました。
FROM postgres:13-alpine
ENV LANG ja_JP.utf8
version: '3.6'
services:
db:
container_name: postgres
build: .
ports:
- 15432:5432
volumes:
- postgres_volume:/var/lib/postgresql/data
environment:
POSTGRES_USER: root
POSTGRES_PASSWORD: root
volumes:
postgres_volume:
コンテナを立ち上げたら、CREATE DATABASE で test_db を作成し、CREATE TABLE で hoge スキーマに test_tbl を作成します。
CREATE TABLE hoge.test_tbl (id integer, name varchar(20), address varchar(30));
データベースへのデータ取り込み（書き込み）
先ほど用意した test_tbl に合わせた Excel ファイルを作成します。
今回は sample.xlsx という名称で作成しました。
取り込みのための Python スクリプトを書きます。
Pandas の read_excel メソッドで Excel ファイルを読んで、to_sql メソッドでデータベースに取り込みます。
create_engine メソッドの引数 -csearch_path=hoge でスキーマの指定ができ、これは SET search_path TO schema_name というクエリを記述することと同じ意味になります。
Excel でなく CSV ファイルを読む場合は、read_csv メソッドになります。
import pandas as pd
from sqlalchemy import create_engine
connection_config = {
'user': 'root',
'password': 'root',
'host': 'localhost',
'port': '15432',
'database': 'test_db'
}
if __name__ == '__main__':
url = 'postgresql://{user}:{password}@{host}:{port}/{database}'.format(**connection_config)
engine = create_engine(url, connect_args={'options': '-csearch_path=hoge'})
with engine.connect() as conn:
df = pd.read_excel('sample.xlsx')
df.to_sql(name='test_tbl', con=conn, if_exists='append', index=False)
-
name- テーブル名
-
con- SQL接続エンジン
-
if_exists- append: データが追加される
- replace: 既存データが置き換えられる
-
index- DataFrame のインデックスをテーブルに追加するか否か
-
schema（上記コードには書いていません）- スキーマの指定
その他パラメータについては、公式ページを参照してください。
データベースからのデータ取得
データベースからのデータ取得は read_sql メソッドで行います。
import pandas as pd
from sqlalchemy import create_engine
connection_config = {
'user': 'root',
'password': 'root',
'host': 'localhost',
'port': '15432',
'database': 'test_db'
}
if __name__ == '__main__':
url = 'postgresql://{user}:{password}@{host}:{port}/{database}'.format(**connection_config)
engine = create_engine(url, connect_args={'options': '-csearch_path=hoge'})
with engine.connect() as conn:
df = pd.read_sql('SELECT * FROM test_tbl', con=conn)
print(df)
# 実行結果
id name address
0 100 Taro Tokyo
1 101 Hanako Chiba
2 102 Ichiro Kanagawa
-
sql- SQL クエリ
- テーブル名を指定することもでき、その場合は全カラムの値を取得できる
-
con- SQL接続エンジン
おまけ
read_excel メソッドで、AWS S3 バケット内に格納した Excel(CSV)ファイルを読むこともできます。
fsspec と s3fs のインストールが必要です。
- Python ライブラリ
- fsspec 2023.6.0
- s3fs 2023.6.0
with engine.connect() as conn:
df = pd.read_excel('s3://your_bucket_name/sample.xlsx')
df.to_sql(name='test_tbl', con=engine, if_exists='append', index=False)
Python スクリプト実行時は、AWS CLI のプロファイル名を指定するようにします。
AWS_PROFILE=プロファイル名 python pandas_sample.py
補足
SQLAlchemy は PostgreSQL だけでなく、MySQL, SQLite, Oracle, Microsoft SQLServer などの一般的なデータベースもサポートしているため、筆者は検証できていませんが、PostgreSQL 以外のデータベースに対しても同じ要領でデータ取り込みができるかと思います。