この記事で行うこと
※（2019-11-20) pytestを使用して全面的に書き換えました。
- PostgreSQLのユーザ定義関数（ストアドプロシージャ）をデバッグする気の利いた方法が分からなかったのですが、PostgreSQLはPythonでユーザ定義関数を書けるので、「ローカルのPython IDEでデバッグ後にCREATE FUNCTION文を発行すればいいよね」ということで、やってみました。
- 開発したPostgreSQLユーザ定義関数のソースコードをJupyter Notebookで表示します。
※ ユーザ定義関数のソースコード表示で参考にしたページ（感謝します）
PostgreSQL の psql で関数のソースコードを取得する
ユーザ定義関数「foo」を作る
ローカルにて
まず、ローカルで開発し、後々PostgreSQLのユーザ定義関数にするfoo関数のソースコードを示します。プロジェクトの下にsrcサブディレクトリを作成して、その下にソースファイルを置きました。このfoo関数が今回のデバッグ＆テスト対象です。
def foo(num):
def bar(n):
return n * 2
return bar(num) + 1
次に、pytestのフィクスチャとテストファイルを用意します。
まずはフィクスチャです。プロジェクト配下にtestsサブディレクトリを作成して、その下にソースファイルを置きました。
import pytest
import psycopg2
import sys
import os
import configparser
from pathlib import Path
print("sys.path.append")
sys.path.append(os.path.join(os.path.dirname(__file__), '..'))
@pytest.fixture(scope="session")
def db_conn():
# 設定ファイル読み込み
conf = configparser.ConfigParser()
conf.read(Path.cwd() / "tests/tests.ini")
SECTION = "psycopg2"
host = conf.get(SECTION, "host")
port = conf.get(SECTION, "port")
dbname = conf.get(SECTION, "dbname")
user = conf.get(SECTION, "user")
password = conf.get(SECTION, "password")
# DB接続 ＆ テスト呼び出し
with psycopg2.connect(f"host={host} port={port} dbname={dbname} user={user} password={password}") as conn:
yield conn
フィクスチャで読み込んでいる設定ファイルは以下の通りです。
[psycopg2]
host = postgres
port = 5432
dbname = default
user = default
password = secret
次にテストファイルです。testsの下にunitサブディレクトリを作成して、その下にソースファイルを置きました。test_foo関数でやっていることは、SQLの結果表のデータをfoo関数に与えて、戻り値を表示＆assert検証しています。
from src import pgfunc
def test_foo(db_conn):
sql = '''\
WITH hoge(num) AS (
VALUES (1),
(2),
(3),
(5),
(7)
)
SELECT num
FROM hoge
ORDER BY num;
'''
print()
with db_conn.cursor() as cur:
cur.execute(sql)
for row in cur:
num = row[0]
result = pgfunc.foo(num)
print(f"{result} = foo({num})")
assert result == num * 2 + 1
ローカルのコマンドラインでpytestを使って実行すると、以下のように表示されてテストが通りました。
$ pipenv run pytest --capture=no
sys.path.append
============================= test session starts ==============================
platform darwin -- Python 3.7.4, pytest-5.3.0, py-1.8.0, pluggy-0.13.0
rootdir: /Users/username/PycharmProjects/project_foo
collected 1 item
tests/unit/test_foo.py
3 = foo(1)
5 = foo(2)
7 = foo(3)
11 = foo(5)
15 = foo(7)
.
============================== 1 passed in 0.05s ===============================
PyCharmでデバッグする方法
事前にPyCharmのPreferences画面でpytestを使うように設定します。以下のように、Default test runnerをpytestにします。
foo関数内にブレークポイントを仕掛けた後、以下のようにtest_foo関数からデバッガを起動します。
DB側にて
ローカルでfoo関数を開発したら、以下のようにfooの頭と尻尾を書き換えます。
CREATE OR REPLACE FUNCTION foo(IN num INTEGER)
RETURNS INTEGER
AS $$
def bar(n):
return n * 2
return bar(num) + 1
$$ LANGUAGE plpython3u;
上記CREATE文を発行し、ユーザ定義関数を作成後に使ってみます。
以下のSQLを実行します。
WITH hoge(num) AS (
VALUES (1),
(2),
(3),
(5),
(7)
)
SELECT num,
foo(num)
FROM hoge
ORDER BY num;
以下のように表示されました。意図通りにユーザ定義関数が動いています。
num | foo
-----+-----
1 | 3
2 | 5
3 | 7
5 | 11
7 | 15
(5 rows)
Jupyter NotebookでPostgreSQLユーザ定義関数のソースコード表示
開発したfooのソースコードをJupyter Notebookで表示します。以下、有償版PyCharmでJupyter Notebookを開いて、そこからコードをコピペしました。
# %% md
# PostgreSQLのユーザ定義関数（ストアドプロシージャ）のソースコードを表示する
# %%
import psycopg2
from IPython.display import display
# %% md
## ユーザ定義関数のソースコードを取得する関数
第二引数：ユーザ定義関数名
参考ページ：http://everything-you-do-is-practice.blogspot.com/2017/09/postgresql-psql.html
# %%
def get_pgproc_src(conn, proc_name):
with conn.cursor() as cur:
cur.execute("SELECT pg_get_functiondef((SELECT oid FROM pg_proc WHERE proname = %s));", (proc_name,))
src = cur.fetchone()
return src[0].split('\n')
# %% md
## fooという名前のユーザ定義関数のソースコードを取得するSQL問い合わせ実行
# %%
with psycopg2.connect("host=postgres port=5432 dbname=default user=default password=secret") as conn:
src_foo = get_pgproc_src(conn, 'foo')
# %% md
## fooのソースコード表示
# %%
display(src_foo)
このJupyter Notebookを実行した時のスクリーンショットは以下の通りです。
