はじめに
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ファイルを使用する
Column1,Column2
a,hogehoge
b,hogehoge
セキュリティ対策として、Pythonのソースコード内で使用するpostgreSQLのユーザー名とパスワードは.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との接続を切る
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でサーバ起動
-