Help us understand the problem. What is going on with this article?

PostgreSQLのStored Procedureでkintone連携

More than 3 years have passed since last update.

社内のデータが外出先から見られたら便利なことってありますよね。
(それをやって良いかどうかは別にして)TRYしてみました。
PostgreSQLに登録された情報をkintoneアプリに登録するサンプルです。
もちろん、更新や削除もいけますよ!

今回はPostgreSQLのStored Procedureを使ってkintoneを操作します。

※kintoneはクラウドで動作するデータベースサービスです。
 ドラッグ&ドロップでパーツを配置するだけで、データベースと入力フォームが出来上がります。
 REST APIでのデータ操作が可能です。

前提

  • PostgreSQLのテーブルにserial型(unique)があること
  • serial値は更新されないこと

サンプルコードで動作するデータ構成

kintoneのフィールド名、フィールドタイプ、フィールドコード、PostgreSQLの列名、型は以下

フィールド名 フィールドタイプ フィールドコード 列名
PostgreSQLの管理番号 数値 serial_id serial_id serial
会社名 文字列1行 company_name company_name varchar
担当 ユーザー選択 account_executive account_executive varchar
訪問予定日 日付 next_visit_day next_visit_day date

PostgreSQLの環境構築

PostgreSQLのインストール
$ sudo yum install postgresql-server postgresql-plpython python-requests

PostgreSQLの起動
$ sudo postgresql-setup initdb
$ sudo systemctl start postgresql

データベースの作成
$ su - postgres
$ createdb withkintone

テーブルの作成
$ psql withkintone
withkintone=# create table customer_manage (serial_id serial, company_name varchar(100), account_executive varchar(50), next_visit_day date);

PL/Pythonの有効化
(本当はkintoneのカスタマイズと同じJavaScriptでやりたかったのですが、今はHTTPリクエストが不可なので)
withkintone=# create extension plpythonu;

kintoneの環境構築

前提にあるとおりフォームを作成

APIトークンを発行(アプリの設定完了を押し忘れないように!)
token.png

サンプルコード

sample.sql
CREATE OR REPLACE FUNCTION relay_to_kintone() RETURNS TRIGGER AS $$

import urllib
import requests
import json

# 連携するアプリID
app = 1983

# 連携するドメイン
domain = 'xxxx.cybozu.com'

# 連携するアプリのAPIトークン
token = 'xxxx'

# PostgreSQLserial列名
relay_column = 'serial_id'



class Kintone:
    # initialize
    def __init__(self, app, domain, token, relay_column):
        self.app = app
        self.domain = domain
        self.token = token
        self.relay_column = relay_column

    # 更新、削除のためにkintoneのレコード番号を取得
    def get_record_id(self):
        url = 'https://' + self.domain + '/k/v1/records.json'

        headers = {
            'X-Cybozu-API-Token': self.token,
            'Content-Type': 'application/json'
        }

        data = {
            'app': self.app,
            'query': self.relay_column + ' = "' + str(TD['old'][self.relay_column]) + '"',
            'fields': ['$id'],
            'totalCount': 'true'
        }

        res = requests.get(url, headers=headers, data=json.dumps(data))

        total_count = res.json()['totalCount']
        if total_count == '1':
            return res.json()['records'][0]['$id']['value']
        else:
            message = self.relay_column + ': ' + str(TD['old'][self.relay_column])
            message += 'が' + str(total_count) + '件あります'
            plpy.fatal(message)

    # レコードの登録
    def insert_record(self):
        url = 'https://' + self.domain + '/k/v1/record.json'

        headers = {
            'X-Cybozu-API-Token': self.token,
            'Content-Type': 'application/json'
        }

        # 列名やフィールドタイプなどが異なる場合は要書き換え
        data = {
            'app': app,
            'record': {
                self.relay_column: { 'value': TD['new'][self.relay_column] },
                'company_name': { 'value': TD['new']['company_name'] },
                'account_executive': { 'value': [{'code': TD['new']['account_executive']}]},
                'next_visit_day': { 'value': TD['new']['next_visit_day'] }
            }
        }

        res = requests.post(url, headers=headers, data=json.dumps(data))

        message = self.relay_column + ': ' + str(TD['new'][self.relay_column])
        if res.status_code == 200:
            message += 'を登録しました'
            plpy.info(message)
        else:
            message += 'の登録に失敗しました'
            plpy.error(message)

    # レコードの更新
    def update_record(self, update_id):
        url = 'https://' + self.domain + '/k/v1/record.json'

        headers = {
            'X-Cybozu-API-Token': self.token,
            'Content-Type': 'application/json'
        }


        # 列名やフィールドタイプなどが異なる場合は要書き換え
        data = {
            'app': self.app,
            'id': update_id,
            'record': {
                'company_name': { 'value': TD['new']['company_name'] },
                'account_executive': { 'value': [{'code': TD['new']['account_executive']}]},
                'next_visit_day': { 'value': TD['new']['next_visit_day'] }
            }
        }

        res = requests.put(url, headers=headers, data=json.dumps(data))

        message = self.relay_column + ': ' + str(TD['new'][self.relay_column])
        if res.status_code == 200:
            message += 'を更新しました'
            plpy.info(message)
        else:
            message += 'の更新に失敗しました'
            plpy.error(message)

    # レコードの削除
    def delete_record(self, delete_id):
        url = 'https://' + domain + '/k/v1/records.json'

        headers = {
            'X-Cybozu-API-Token': self.token,
            'Content-Type': 'application/json'
        }

        data = {
            'app': self.app,
            'ids': [delete_id]
        }

        res = requests.delete(url, headers=headers, data=json.dumps(data))

        message = self.relay_column + ': ' + str(TD['old'][self.relay_column])
        if res.status_code == 200:
            message += 'を削除しました'
            plpy.info(message)
        else:
            message += 'の削除に失敗しました'
            plpy.error(message)



kintone = Kintone(app, domain, token, relay_column)

if TD['event'] == 'INSERT':
    kintone.insert_record()
elif TD['event'] == 'UPDATE':
    update_id = kintone.get_record_id()
    kintone.update_record(update_id)
elif TD['event'] == 'DELETE':
    delete_id = kintone.get_record_id()
    kintone.delete_record(delete_id)

return 'OK'

$$ LANGUAGE plpythonu;

サンプルコードの読み込み

withkintone=# \i sample.sql

トリガーの作成

withkintone=# CREATE TRIGGER kintone_trigger AFTER INSERT OR UPDATE OR DELETE ON customer_manage FOR EACH ROW EXECUTE PROCEDURE relay_to_kintone();

動作確認

1件登録

どきどき・・・
withkintone=# insert into customer_manage (company_name, account_executive, next_visit_day) values ('○○株式会社', 'momota', '2016-01-01');

上手く行きました。
insert1.png

2件登録

withkintone=# insert into customer_manage (company_name, account_executive, next_visit_day) values ('××株式会社', 'momota', '2016-02-01'), ('△△株式会社', 'tamai', '2016-03-01');

いえあ!
insert2.png

2件更新

では調子に乗って2件更新してみます。
withkintone=# update customer_manage set account_executive = 'hayami' where next_visit_day in ('2016-02-01', '2016-03-01');

ひゅ~ひゅ~!
update1.png

2件削除

同じ調子で2件削除してみます。
withkintone=# delete from customer_manage where account_executive = 'hayami';

どんどんぱふぱふ!!!
delete1.png

まとめ

これで社内の情報をこっそり社外で見られますね!(違
kintoneはグラフ機能もすぐれてますので、kintoneで見える化するのもお勧めです。

kintone開発者ライセンス

kintone面白そうだな~と思った方は5ユーザー、1年間無償の開発者ライセンスを使ってみてください。

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away