Excel や CSV の中身をデータベースに取り込むときは、Python の Pandas を使うと便利

Python のデータ分析ライブラリと言えば Pandas が定番かと思いますが、Pandas を使えば Excel や CSV ファイルの中身に簡単にデータベースに取り込むことができることをつい最近知ったので、ここにまとめてみたいと思います。

## 環境
- Python 3.8.10
- Python ライブラリ
  - Pandas 2.0.2
  - SQLAlchemy 2.0.16 
- PostgreSQL 13.8

## 下準備

今回は　PostgreSQL データベースにデータ取り込みを行ってみます。
クラウドでもローカル環境でもどこでも良いのですが、今回はローカル環境に PostgreSQL コンテナを立ててみました。

```Dockerfile:Dockerfile
FROM postgres:13-alpine
ENV LANG ja_JP.utf8
```

```yml:docker-compose.yml
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` を作成します。

```sql
CREATE TABLE hoge.test_tbl (id integer, name varchar(20), address  varchar(30));
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/759747/23823810-13cf-f80c-b2e9-1e699e8ae95c.png)

## データベースへのデータ取り込み（書き込み）

先ほど用意した `test_tbl` に合わせた Excel ファイルを作成します。
今回は `sample.xlsx` という名称で作成しました。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/759747/c5fb32b6-65f3-cbdd-eab8-10f373b0ff52.png)

取り込みのための Python スクリプトを書きます。
Pandas の `read_excel` メソッドで Excel ファイルを読んで、`to_sql` メソッドでデータベースに取り込みます。
`create_engine` メソッドの引数 `-csearch_path=hoge` でスキーマの指定ができ、これは `SET search_path TO schema_name` というクエリを記述することと同じ意味になります。
Excel でなく CSV ファイルを読む場合は、`read_csv` メソッドになります。

```python
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`（上記コードには書いていません）
  - スキーマの指定

その他パラメータについては、[公式ページ](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_sql.html)を参照してください。

## データベースからのデータ取得

データベースからのデータ取得は `read_sql` メソッドで行います。

```python
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

```python
  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 のプロファイル名を指定するようにします。

```bash
AWS_PROFILE=プロファイル名 python pandas_sample.py
```

## 補足

SQLAlchemy は PostgreSQL だけでなく、MySQL, SQLite, Oracle, Microsoft SQLServer などの一般的なデータベースもサポートしているため、筆者は検証できていませんが、PostgreSQL 以外のデータベースに対しても同じ要領でデータ取り込みができるかと思います。  

## 参考記事
- https://towardsdatascience.com/work-with-sql-in-python-using-sqlalchemy-and-pandas-cd7693def708