SQLAlchemyを使ってpostgreSQLにデータを追加する

# はじめに

PythonのパッケージSQLAlchemyで、postgreSQLにデータを追加する。
エラーで詰まったところが多かったので、その対処方法も記載。

# 開発環境

- エディタ
    - VSCode
- ターミナル
    - gitbash
    - Windows PowerShell
- データベース
    - postgreSQL 15.3
- 言語
    - Python 3.11.3
- Pythonのパッケージ
    - psycopg2 2.9.6
    - python-dotenv 1.0.0
    - SQLAlchemy 2.0.16

# postgreSQLのインストール

公式サイトからpostgreSQLをダウンロード
https://www.postgresql.org/download/
パスワード、オプションなどを設定(今回はデフォルトで進めた)

## インストールの確認

インストールできたか確認する

ターミナルを起動

ユーザー名はデフォルトなら`postgres`
```Bash
psql -U ユーザー名
"psql: command not found"
```

psqlというコマンドはないよと言われた
環境変数にパスが通っていなかったのが原因だったので、パスを通す

### 環境変数の設定

Windowsの場合
```Bash
setx PATH "PAYH%;C:\Program Files\PostgreSQL\version\bin"
```
versionは実際にインストールしたpostgreSQLのバージョンを指定

macOS/Linuxの場合
```Bash
export PATH="/usr/local/pgsql/bin:$PATH"
```

パスを通したら、再度`psql`コマンドを入力
```Bash
psql -U ユーザー名
psql: could not connect to server: Connection refused
        Is the server running on host "localhost" (127.0.0.1) and accepting
        TCP/IP connections on port 5432?
```

次は、PostgreSQLサーバに接続されてないと言われた
サーバの設定はデフォルトにしたので、ホスト名もポート番号も間違っていないはず…

PostgreSQLサーバが動いていないことが原因だったので、サーバを起動させる

### postgreSQLサーバの起動
```Bash
pg_ctl start
pg_ctl: no database directory specified and environment variable PGDATA unset
```
環境変数`PGDATA`が設定されていなかったので、サーバが起動できないというエラー

### 環境変数`PGDATA`の設定

windowsのターミナルの場合
```Bash
set PGDATA=C:\Program Files\PostgreSQL\data
```

PowerShellの場合
```PowerShell
$env:PGDATA="C:\Program Files\PostgreSQL\data
```

macOS/Linuxの場合
```Bash
export PGDATA="/usr/local/pgsql/data:$PATH"
```

環境変数が設定出来たら、サーバを起動
```Bash
pg_ctl start
server starting
```

もし、サーバが正常に起動されなかったら、サーバの状態を確認
```Bash
pg_ctl status
```
`pg_ctl`コマンドについては`pg_ctl --help`で確認できる
サーバを停止するには`pg_ctl stop`と入力

やっとサーバを起動できたので、PostgreSQLがちゃんとインストールされているか確認
```Bash
psql -U ユーザー名
postgres password:
```
パスワードを求められるので、インストール時に決めたパスワードを入力

SQLの入力に切り替われば成功！

終了する時は`\q`か`Ctrl`+`C`

# データベースの作成

```Bash
createdb -U ユーザー名 データベース名
```

ユーザー名はデフォルトなら`postgres`
データベース名は作成するデータベースの名前

パスワードを求められた場合は、パスワードを入力

次に、作成したデータベースを編集するので、データベースに接続
```Bash
psql -U ユーザー名　データベース名
```
データベース名は先ほど作成したデータベース

# テーブルの作成

データベースに接続したら、SQLでテーブルを作成する

今回は、テーブルと列の作成をSQLで行い、行の追加をPythonで行った

```sql
CREATE TABLE テーブル名 (id INT SERIAL PRIMARY KEY,Column1 VARCHAR[50],Column2 TEXT);
```
テーブル名は作成するテーブルの名前

idは主キー(`PRIMARY KEY`)に設定する

Column1,Column2は列名
列名の後にその列に入る値の型を記述

## SQLのデータ型

- 整数型（Integer）: 整数値を格納するためのデータ型 例: INT, INTEGER, SMALLINT, BIGINTなど。

- 浮動小数点型（Floating-Point）: 浮動小数点数を格納するためのデータ型 例: FLOAT, REAL, DOUBLE PRECISIONなど。

- 文字列型（String）: 文字列を格納するためのデータ型 例: CHAR, VARCHAR, TEXTなど。

- 日付型（Date）: 日付を格納するためのデータ型 例: DATE, TIMESTAMPなど。

- 真偽値型（Boolean）: 真偽値（True/False）を格納するためのデータ型 例: BOOLEANなど。

- その他の型: 上記以外にも、バイナリデータを格納するためのバイナリ型、JSONデータを格納するためのJSON型、配列データを格納するための配列型など。

## 作成したテーブルの確認

`\d`:そのデータベースのテーブル一覧を表示

`\d テーブル名':テーブル名の列を表示

`SELECT * FROM テーブル名`:テーブルの内容を表示

# 行を追加

PythonのSQLAlchemyでデータベースに接続

行の追加は、追加したいデータを記述したcsvファイルを使用する

```csv:csv
Column1,Column2
a,hogehoge
b,hogehoge
```

セキュリティ対策として、Pythonのソースコード内で使用するpostgreSQLのユーザー名とパスワードは.envファイルに記述

```env:.env
SQL_USERNAME=ユーザー名
SQL_PASSWORD=パスワード
```

## パッケージのインストール

Pythonで必要なパッケージをインストール

venvの仮想環境でインストールする場合
```Bash
python3 -m venv 仮想環境名
\仮想環境名\Scripts\Activate
```
仮想環境を終了する時は`deactivate`

パッケージのインストール
```Bash
pip install SQLAlchemy
pip install python-dotenv
pip install psycopg2
```

## Pythonで行を追加

1. .envからpostgreSQLに接続するためのユーザー名とパスワードを取得
1. postgreSQLに接続
1. csvファイルの読み込み
1. SQLの記述
1. postgreSQLにデータを追加
1. postgreSQLとの接続を切る

```Python:Python
import csv
from sqlalchemy import create_engine,text
import os
from dotenv import load_dotenv

#.envファイルの読み込み
load_dotenv()

#.envファイルからpostgreSQLのユーザー名とパスワードを取得
username=os.getenv("SQL_USERNAME")
password=os.getenv("SQL_PASSWORD")

#postgreSQLに接続
engine=create_engine(f"postgresql://{username}:{password}@localhost:5432/データベース名")

with open("csvファイルのパス","r",encoding="utf-8") as csvfile:
    reader=csv.reader(csvfile)
    next(reader) #csvファイルの1行目(列名)を除く
    for row in reader:
        sql=text(f"INSERT INTO テーブル名 (Column1,Column2) VALUES ('{row[0]}','{row[1]}');")
        with engine.begin() as connection:
            connection.execute(sql)

engine.dispose() #postgreSQLの接続を切る
```

# 実行

postgreSQLサーバを起動
```Bash
pg_ctl start
```

Pythonファイルがあるルートディレクトリに移動して、Pythonファイルを実行
```Bash
python3 pythonファイル名
```

# データが追加されているか確認

```Bash
psql -U ユーザー名 データベース名
```

```sql
SELECT * FROM テーブル名
```

`\q`で終了

# まとめ

## 手順

1. postgreSQLのインストール
1. データベースの作成
1. `psql`でデータベースに接続
1. SQLでテーブルと列の作成
1. Pythonで行の追加
1. テーブルの確認

## エラーの主な原因と対処方法

- コマンドが実行できない
    - 環境変数の設定
- postgreSQLサーバが起動されていない
    - `pg_ctl status`でサーバ状態の確認
    - `pg_ctl start`でサーバ起動