はじめまして。
キャスレーコンサルティングLS(リーディング・サービス)部の田村です。
データベースにストアドプロシージャを作成する際、皆さんは何を使っていますか？
私は今まで、PL/pgSQL(PostgresSQL)等のDB固有の言語を利用していましたが、
カーソル等DB固有言語独特の書き方に違和感がありました。
何かないか探していたところ、その他のスクリプト言語でも
直接ストアドプロシージャの作成ができることを知りました。
（詳しくは、こちらの公式マニュアルを参照してください。）
今回は、業務で使用しているスクリプト言語のひとつ、PL/Pythonの使い方をご紹介いたします。
目次
・動作環境
・環境構築
・統計情報の取得
・おまけ
・終わりに
動作環境
・PostgreSQL-10.4-1
・Python-3.4.4
・Windows10
・QlikView12（おまけ）
環境構築
まずは、PL/Pythonが利用できる環境を構築していきます。
１．Postgresのインストール
配布先：https://www.enterprisedb.com/software-downloads-postgres/
※設定は、デフォルトのままでインストールしてください。
２．Pythonのインストール
配布先：https://www.python.org/downloads/
※設定は、デフォルトのままでインストールしてください。
インストールバージョンは3.4.4を選択してください。
PATHが通っているか確認
３．PythonのdllをPostgresへコピー
Postgresには、Pythonをストアドプロシージャとして利用できる、インターフェースが用意されていますが
実際の処理を行うには、Pythonのdllが必要になります。
コピー元：(Postgresインストール先)\Python34\Python34.dll
コピー先：(Postgresインストール先)\PostgreSQL\10\lib
４．pgAdminを立ち上げ、ExtensionsにPL/Pythonを追加
pgAdminはPostgresSQLをインストールするとついてくる、データベース管理ツールです。
５．PL/Pythonの動作確認
試験用のストアドプロシージャを作成して、PL/Pythonが利用可能かチェックします。
今回は、pgAdminのQueryToolから実施しました。
①メニューのToolsからQueryToolを起動、SourceCodeを記載して実行します
（F5キーで実行できます）
--SourceCode-- CREATE FUNCTION py_test(x text) returns text AS $$ return x $$ LANGUAGE plPython3u;
②検証用のSourceCodeを実行して、下記の結果が戻ってくれば成功です。
--SourceCode--
select py_test('成功!');
これで準備完了です。
統計情報の取得
今回はe-StatのAPI機能を利用して、政府統計情報から東京の老年人口割合を取得します。
（e-Stat:https://www.e-stat.go.jp/)
■事前準備
１．pipを使って、PythonのAPI通信用モジュールをインストール
コマンドプロンプトからCommandを実行して、インストールします。
--Command-- pip install requests
２．e-Statにログインして、アプリケーションIDを取得
※WebAPIへの連携には、アプリケーションIDが必要になります。
e-Statのアカウントを作成して、アプリケーションIDを発行してください。
３．統計情報格納用のテーブル作成
QueryToolにてSouceCodeを実行して、格納用テーブルを作成してください。
--SourceCode-- CREATE TABLE population_ratio ( year char(4), ratio numeric )
■PL/Pythonの実装
１．ストアドプロシージャを作成
QueryToolにて、統計情報取得用のストアドプロシージャを作成します。
--SourceCode--
CREATE FUNCTION py_create_population_ratio ()
RETURNS integer
AS $$
import requests
import urllib.parse
import json
# リクエストurl
api = "http://api.e-stat.go.jp/rest/2.0/app/json/getStatsData?appId={appid}&statsDataId={statsDataId}&cdCat01={cdCat01}"
# アプリケーションID
api_key = "6a886e502671da6536be5abb254d244c56c07185"
# 統計表ID
statsdata_id = "C0020050213000"
# コード
cd_cat ="#A03503"
# urlencode
url = api.format(appid=urllib.parse.quote_plus(api_key, encoding='utf-8'), statsDataId=urllib.parse.quote_plus(statsdata_id, encoding='utf-8'), cdCat01=urllib.parse.quote_plus(cd_cat, encoding='utf-8'))
# リクエスト
r = requests.get(url)
# decode
data = json.loads(r.text)
# データ登録
plpy.execute("truncate table population_ratio", 0)
for rowdata in data["GET_STATS_DATA"]["STATISTICAL_DATA"]["DATA_INF"]["VALUE"]:
plpy.execute("insert into population_ratio values('" + rowdata["@time"] + "', " + rowdata["$"] + ")", 0)
return 0
$$ LANGUAGE plpython3u;
２．動作確認
検証用のSourceCodeを実行して、下記の結果が戻ってくれば成功です！
--SourceCode-- select py_create_population_ratio(); select * from population_ratio;
おまけ（統計情報の利用）
せっかく取得してきた統計情報なので、BIツールであるQlikViewで視覚化してみました。
（QlikViewの使用方法については、こちらのキャスレー技術ブログをご覧ください。）
終わりに
今回ストアドプロシージャを、PL/Pythonで作成してみましたが、下記の効果を感じました。
・直感的に処理が作成できる。（作業短縮になる）
・SourceCodeが読みやすい！
・今回のAPIやファイル操作など、スクリプトの便利な機能がそのまま使える。
以上、最後までご覧頂き、ありがとうございました。
