前提条件

本記事の前提条件は以下のとおりです．

• macOS
• Homebrewがインストール済み
• Pythonの環境構築済み
• Docker Desktopがインストール済み

PostgreSQLのインストール

PostgreSQLをHomebrewでインストールします．

brew install postgresql

Python（Jupyter）側の準備

ipython-sql のインストール

JupyterでSQLを動かすためにipython-sqlを以下のコマンドでインストールします．

pip install ipython-sql

psycopg2のインストール

続いてPythonのPostgreSQLドライバーpsycopg2を以下のコマンドでインストールします．

pip install psycopg2

Docker側の設定

PostgreSQLの公式イメージを使用してDocker上に環境を作ります．

docker-compose.ymlの作成

以下の内容をコピーしてdocker-compose.ymlというファイル名で保存します．

version: '3'

services:
  db:
    container_name: postgres_j
    image: postgres:14.0
    ports:
      - 5432:5432
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: pass
      TZ: "Asia/Tokyo"
    tty: true
    volumes:
      - ./db:/var/lib/postgresql/data
      - ./init_db:/docker-entrypoint-initdb.d

構築時に実行するSQLの準備

docker-compose時に併せて実行するSQLファイルを作成します．
以下の内容をコピーして00_init.sqlというファイル名で保存します．

--ユーザーの作成
CREATE USER tst_user;

--DBの作成
CREATE DATABASE tst_db;

--ユーザーにDBの権限をまとめて付与
GRANT ALL PRIVILEGES ON DATABASE tst_db TO tst_user;

-- DBを切り替え
\c tst_db tst_user

--テーブルを作成
CREATE TABLE tst_tb (
    id SERIAL PRIMARY KEY, 
    c_name VARCHAR(10),
    age INTEGER,
    check_date DATE
);

--テーブルにデータを挿入
INSERT INTO
    tst_tb
    (c_name, age, check_date)
VALUES
    ('maguro', 18, CURRENT_DATE),
    ('hokke', 19, CURRENT_DATE),
    ('iwashi', 20, CURRENT_DATE);

階層の整理

以下のようなディレクトリ構成にしましょう．

postgres_j
├── docker-compose.yml
└── init_db
    └── 00_init.sql

docker-compose

dockerアプリを起動しdocker-compose.ymlがある階層で以下のコマンドを実行します．

docker-compose up -d

実行後，しばらく待つとpostgres_jというコンテナができます．

psqlで接続確認

ターミナルからDockerのPostgreSQLに接続します．
以下のコマンドを実行するとパスワードを求められるのでdocker-compose.ymlで設定したパスワード²を入れてreturnを押します．
最終的に以下のような出力になっていればOKです．

psql -h localhost -p 5432 -U postgres tst_db

Password for user postgres:
psql (14.5 (Homebrew), server 14.0 (Debian 14.0-1.pgdg110+1))
Type "help" for help.

tst_db=#

クエリでテーブルの存在を確認

00_init.sqlで作成したtst_tbを確認しましょう．
以下のクエリを実行してテーブルが表示されればOKです．

SELECT * FROM tst_tb;

 id | c_name | age | check_date
----+--------+-----+------------
  1 | maguro |  18 | 2022-09-01
  2 | hokke  |  19 | 2022-09-01
  3 | iwashi |  20 | 2022-09-01
(3 rows)

上記の確認が取れたらexitで抜けておきましょう．

exit

JupyterでSQLを実行する

これまでの手順でJupyterでSQLを実行する準備が整いました．
Jupyterのセルで以下のマジックコマンドを実行します．

%load_ext sql
%sql postgresql://postgres:pass@localhost:5432/tst_db

２行目は以下のような構成になっています．
postgresql://{user}:{password}@{host}:{port}/{db_name}

エラーなく実行が完了すればDocker内のPostgreSQLに接続できています．

あとは，以下のように%sqlと共にクエリをセルで実行すればテーブルを取得できます！

%sql SELECT * FROM tst_tb;

なお，クエリを複数行書く場合は%%sqlを使います．

%%sql
SELECT
    *
FROM
    tst_tb
WHERE
    age >= 20;

以下のように;で区切れば複数のクエリも使えます．

%%sql
INSERT INTO
    tst_tb
    (c_name, age, check_date)
VALUES
    ('saba', 22, CURRENT_DATE);

SELECT * FROM tst_tb;

ひとこと

Jupyterを使ってクエリを気軽に叩けると楽でいいですね．
当ブログを書く際にもこの機能を使っています．