PostgreSQLクエリを自動化：スクリプトファイルとメタコマンド
コマンドラインからPostgreSQLクエリを実行する方法
コマンドラインツール
PostgreSQLには、コマンドラインから操作するための公式ツール psql があります。
インストール
psql は、PostgreSQLと共にインストールされます。多くのLinuxディストリビューションでは、パッケージマネージャーを使ってインストールできます。
# Debian/Ubuntu
apt install postgresql-client
# CentOS/Red Hat
yum install postgresql-devel
# macOS
brew install postgresql
基本的な使い方
psql コマンドは、以下のように実行します。
psql [オプション] [データベース名]
オプションには、以下のようなものがあります。
-h: ホスト名-p: ポート番号-U: ユーザー名-W: パスワード-c: 実行するSQLクエリ
例えば、以下のコマンドは、postgres ユーザーで mydb データベースに接続し、SELECT * FROM users クエリを実行します。
psql -U postgres mydb -c "SELECT * FROM users"
クエリの実行
psql に接続すると、対話的なシェルが起動します。ここで、SQLクエリを入力して実行できます。
psql mydb
# ユーザー情報を表示
SELECT * FROM users;
# 新しいユーザーを追加
INSERT INTO users (name, email) VALUES ('John Doe', '[email protected]');
# データベースから切断
\q
スクリプトファイル
複数のクエリをまとめて実行したい場合は、SQLスクリプトファイルを作成できます。スクリプトファイルは、拡張子が .sql のテキストファイルです。
例えば、users.sql という名前のスクリプトファイルに以下の内容を記述します。
-- ユーザー情報を表示
SELECT * FROM users;
-- 新しいユーザーを追加
INSERT INTO users (name, email) VALUES ('John Doe', '[email protected]');
このスクリプトは、以下のコマンドで実行できます。
psql mydb -f users.sql
その他のツール
psql 以外にも、コマンドラインからPostgreSQLを操作するためのツールがあります。
pgcli: より使いやすいインターフェースを提供するツールsqlcmd: Windowsユーザー向けのツール
これらのツールは、psql と同じように使用できます。
コマンドラインからPostgreSQLクエリを実行する方法を学習することで、データベース操作をより効率的に行うことができます。
接続とクエリ実行
# postgresql ユーザーで mydb データベースに接続
psql mydb
# ユーザー情報を表示
SELECT * FROM users;
# 新しいユーザーを追加
INSERT INTO users (name, email) VALUES ('John Doe', '[email protected]');
# データベースから切断
\q
スクリプトファイル
-- ユーザー情報を表示
SELECT * FROM users;
-- 新しいユーザーを追加
INSERT INTO users (name, email) VALUES ('John Doe', '[email protected]');
psql mydb -f users.sql
パラメータ付きクエリ
-- ユーザー名で検索
SELECT * FROM users WHERE name = $1;
実行:
psql mydb -c "SELECT * FROM users WHERE name = 'John Doe'"
環境変数
# 環境変数に接続情報を設定
export PGHOST=localhost
export PGPORT=5432
export PGDATABASE=mydb
export PGUSER=postgres
export PGPASSWORD=password
# クエリ実行
psql -c "SELECT * FROM users"
コマンドラインから PostgreSQL クエリを実行するその他の方法
psql には、データベース操作を補助するメタコマンドと呼ばれる特殊なコマンドがあります。
\d: テーブルやデータベースなどの情報を表示\copy: データをファイルにコピー\set: 環境変数を設定
例:
# テーブル一覧を表示
\d
# users テーブルのデータを CSV ファイルにコピー
\copy users TO 'users.csv' WITH CSV
# 検索パスを設定
\set search_path = public, my_schema
シェルスクリプトを使って、複数のクエリをまとめて実行したり、条件分岐やループ処理などを組み込むことができます。
#!/bin/bash
# データベースに接続
psql mydb
# ユーザー情報を表示
SELECT * FROM users;
# 新しいユーザーを追加
if [[ "$1" ]]; then
INSERT INTO users (name) VALUES ('$1');
fi
# データベースから切断
\q
その他のツール
dbeaver: 複数のデータベースに対応したGUIツール
Python や Ruby などの言語
psycopg2 や pg gem などのライブラリを使って、PostgreSQL に接続し、クエリを実行することができます。
例 (Python):
import psycopg2
# データベースに接続
conn = psycopg2.connect(dbname="mydb", user="postgres", password="password")
# クエリを実行
cur = conn.cursor()
cur.execute("SELECT * FROM users")
rows = cur.fetchall()
# データベースを閉じる
conn.close()
Webブラウザ
pgAdmin や Adminer などのツールを使って、Webブラウザからデータベースを操作できます。
database postgresql
