はじめまして。
キャスレーコンサルティングLS(リーディング・サービス)部の田村です。

データベースにストアドプロシージャを作成する際、皆さんは何を使っていますか?

私は今まで、PL/pgSQL(PostgresSQL)等のDB固有の言語を利用していましたが、
カーソル等DB固有言語独特の書き方に違和感がありました。

何かないか探していたところ、その他のスクリプト言語でも
直接ストアドプロシージャの作成ができることを知りました。
(詳しくは、こちらの公式マニュアルを参照してください。)

今回は、業務で使用しているスクリプト言語のひとつ、PL/Pythonの使い方をご紹介いたします。

目次

・動作環境
・環境構築
・統計情報の取得
・おまけ
・終わりに

動作環境

・PostgreSQL-10.4-1
・Python-3.4.4
・Windows10
・QlikView12(おまけ)

環境構築

まずは、PL/Pythonが利用できる環境を構築していきます。

1.Postgresのインストール

配布先:https://www.enterprisedb.com/software-downloads-postgres/
※設定は、デフォルトのままでインストールしてください。

 

2.Pythonのインストール

配布先:https://www.python.org/downloads/
※設定は、デフォルトのままでインストールしてください。
インストールバージョンは3.4.4を選択してください。

PATHが通っているか確認

pasted image 0

 

3.PythonのdllをPostgresへコピー

Postgresには、Pythonをストアドプロシージャとして利用できる、インターフェースが用意されていますが
実際の処理を行うには、Pythonのdllが必要になります。
コピー元:(Postgresインストール先)\Python34\Python34.dll
コピー先:(Postgresインストール先)\PostgreSQL\10\lib

 

4.pgAdminを立ち上げ、ExtensionsにPL/Pythonを追加

pgAdminはPostgresSQLをインストールするとついてくる、データベース管理ツールです。

pasted image 1

 

5.PL/Pythonの動作確認

試験用のストアドプロシージャを作成して、PL/Pythonが利用可能かチェックします。
今回は、pgAdminのQueryToolから実施しました。

①メニューのToolsからQueryToolを起動、SourceCodeを記載して実行します
(F5キーで実行できます)

--SourceCode--
CREATE FUNCTION py_test(x text)
returns text
AS $$
return x
$$ LANGUAGE plPython3u;

pasted image 2

②検証用のSourceCodeを実行して、下記の結果が戻ってくれば成功です。

--SourceCode--
select py_test('成功!');

pasted image 3

これで準備完了です。

統計情報の取得

今回はe-StatのAPI機能を利用して、政府統計情報から東京の老年人口割合を取得します。

(e-Stat:https://www.e-stat.go.jp/)

■事前準備

1.pipを使って、PythonのAPI通信用モジュールをインストール

コマンドプロンプトからCommandを実行して、インストールします。

--Command--
pip install requests

2.e-Statにログインして、アプリケーションIDを取得

※WebAPIへの連携には、アプリケーションIDが必要になります。
e-Statのアカウントを作成して、アプリケーションIDを発行してください。

3.統計情報格納用のテーブル作成

QueryToolにてSouceCodeを実行して、格納用テーブルを作成してください。

--SourceCode--
CREATE TABLE population_ratio
(
year char(4),
ratio numeric
)

■PL/Pythonの実装

1.ストアドプロシージャを作成

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;

2.動作確認

検証用のSourceCodeを実行して、下記の結果が戻ってくれば成功です!

--SourceCode--
select py_create_population_ratio();
select * from population_ratio;

pasted image 4

おまけ(統計情報の利用)

せっかく取得してきた統計情報なので、BIツールであるQlikViewで視覚化してみました。
(QlikViewの使用方法については、こちらのキャスレー技術ブログをご覧ください。)

pasted image 5

終わりに

今回ストアドプロシージャを、PL/Pythonで作成してみましたが、下記の効果を感じました。

・直感的に処理が作成できる。(作業短縮になる)
・SourceCodeが読みやすい!
・今回のAPIやファイル操作など、スクリプトの便利な機能がそのまま使える。

以上、最後までご覧頂き、ありがとうございました。