Edited at

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年間無償の開発者ライセンスを使ってみてください。