はじめに

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

psql -U ユーザー名
"psql: command not found"

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

環境変数の設定

Windowsの場合

setx PATH "PAYH%;C:\Program Files\PostgreSQL\version\bin"

versionは実際にインストールしたpostgreSQLのバージョンを指定

macOS/Linuxの場合

export PATH="/usr/local/pgsql/bin:$PATH"

パスを通したら、再度psqlコマンドを入力

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サーバの起動

pg_ctl start
pg_ctl: no database directory specified and environment variable PGDATA unset

環境変数PGDATAが設定されていなかったので、サーバが起動できないというエラー

環境変数`PGDATA`の設定

windowsのターミナルの場合

set PGDATA=C:\Program Files\PostgreSQL\data

PowerShellの場合

$env:PGDATA="C:\Program Files\PostgreSQL\data

macOS/Linuxの場合

export PGDATA="/usr/local/pgsql/data:$PATH"

環境変数が設定出来たら、サーバを起動

pg_ctl start
server starting

もし、サーバが正常に起動されなかったら、サーバの状態を確認

pg_ctl status

pg_ctlコマンドについてはpg_ctl --helpで確認できる
サーバを停止するにはpg_ctl stopと入力

やっとサーバを起動できたので、PostgreSQLがちゃんとインストールされているか確認

psql -U ユーザー名
postgres password:

パスワードを求められるので、インストール時に決めたパスワードを入力

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

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

データベースの作成

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

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

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

次に、作成したデータベースを編集するので、データベースに接続

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

データベース名は先ほど作成したデータベース

テーブルの作成

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

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

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

Column1,Column2
a,hogehoge
b,hogehoge

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

.env

SQL_USERNAME=ユーザー名
SQL_PASSWORD=パスワード

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

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

venvの仮想環境でインストールする場合

python3 -m venv 仮想環境名
\仮想環境名\Scripts\Activate

仮想環境を終了する時はdeactivate

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

pip install SQLAlchemy
pip install python-dotenv
pip install psycopg2

Pythonで行を追加

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

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サーバを起動

pg_ctl start

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

python3 pythonファイル名

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

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

SELECT * FROM テーブル名

\qで終了

まとめ

手順

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

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

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