テンプレートエンジンを使ってSQLを書く環境を作ってみた
Web界隈ではaltjsやscssのファイル更新を監視して自動コンパイルするのが一般的になってきていると思いますが。
このエントリでは、そういった方法を真似て、SQLをテンプレートエンジンを利用して書いて、自動的に変換する環境を作ってみることにします。
データ界隈の人はpython使う人が多そうなので、pythonで以下のモジュールを使った環境を作ることにします。
- ファイル監視はwatchdog
- テンプレートエンジンはmako
- エディタはatomでlanguage-atomプラグインを利用
watchdog:
https://pypi.python.org/pypi/watchdog
mako:
http://www.makotemplates.org/
Mako 1.0.4 Documentation » Syntax:
http://docs.makotemplates.org/en/latest/syntax.html
language-mako for ATOM:
https://atom.io/packages/language-mako
このエントリで作成したgithubリポジトリ:
https://github.com/takemikami/sql-generated-by-mako-sample
言うまでも無いかもですが、
このエントリで紹介する方法は、SQLに限らずテンプレートから生成したいものであれば、何でも応用できます。
環境を作る流れ
watchdogとmakoをインストール
pythonとpipはセットアップ済みの前提として、
以下のコマンドで、watchdogとmakoをインストールします。
# この手順は、anaconda3-4.0.0で試しました。
$ pip install watchdog
$ pip install mako
SQLを変換するスクリプトを作成
SQLを変換するためのスクリプトを作成します。
gen.py
#!/usr/bin/env python
#!/bin/python3
# install:
# pip install watchdog
# pip install mako
#
# run(all):
# ./gen.py
#
# run(watchdog):
# watchmedo shell-command --patterns="*.mako" --recursive --command='./gen.py ${watch_src_path}'
import sys
import os
import re
import datetime
import glob
from mako.template import Template
from mako.lookup import TemplateLookup
def gen_template(fname_src, fname_dest):
print("[%s] %s -> %s" % (datetime.datetime.now(), fname_src, fname_dest))
tpldir = re.sub("/[^/]*$", "", fname_dest)
lookup = TemplateLookup(directories=[os.path.realpath('./')])
tpl = Template(filename=fname_src,input_encoding='utf-8',output_encoding='utf-8',lookup=lookup)
str = tpl.render_unicode()
dirpath = re.sub("/[^/]*$", "", 'gen/' + fname_dest)
if (not os.path.exists(dirpath)):
os.makedirs(dirpath)
f = open('gen/' + fname_dest, 'w')
f.write(str)
f.close()
if(len(sys.argv) > 1):
fname_src = sys.argv[1]
fname_dest = re.sub(".mako$", "", fname_src)
gen_template(fname_src, fname_dest)
else:
for root, dirs, files in os.walk(u'./'):
for file_ in files:
full_path = os.path.join(root, file_)
if(re.match(".*mako$", full_path)):
fname_dest = re.sub(".mako$", "", full_path)
gen_template(full_path, fname_dest)
ファイル更新を監視してスクリプトを実行
watchmedoを起動し、ファイル更新を監視してスクリプトを実行するようにします。
$ watchmedo shell-command --patterns="*.mako" --recursive --command='./gen.py ${watch_src_path}'
atomにlanguage-makoプラグインをインストール
Atomにlanguage-makoプラグインをインストールします。
Atom→PreferencesでSetting画面を開き、
Installの「Search Packages」で「language-mako」と入力して検索、
「language-mako」のInstallをclick。
実際にSQLを書いてみる
この例で作りたいSQLについて
ここでは、以下の入力に対する出力を作成するSQLを作るものとして説明します。
【入力】salesテーブル
年月・都市に対して売上が入っているテーブル
mysql> select * from sales;
+--------+------+-------+
| yyyymm | city | sales |
+--------+------+-------+
| 201604 | tyo | 100 |
| 201604 | tyo | 200 |
| 201604 | osa | 150 |
| 201604 | ngo | 300 |
| 201605 | tyo | 250 |
| 201605 | osa | 200 |
| 201605 | fuk | 100 |
+--------+------+-------+
7 rows in set (0.00 sec)
【出力】sales_by_cityテーブル
年月毎・都市毎の売上を合算したデータ
mysql> select * from sales_by_city;
+--------+-----------+-----------+-----------+-----------+
| yyyymm | sales_tyo | sales_ngo | sales_osa | sales_fuk |
+--------+-----------+-----------+-----------+-----------+
| 201604 | 300 | 300 | 150 | 0 |
| 201605 | 250 | 0 | 200 | 100 |
+--------+-----------+-----------+-----------+-----------+
2 rows in set (0.00 sec)
テンプレートを使ってSQLを書く
Atomを起動して、
以下のように、makoを使ったsqlを記載したファイルを作成します。
sample.sql.mako
<%
city_list = ['tyo', 'ngo', 'osa', 'fuk']
%>
create table sales_by_city(
yyyymm varchar(6)
% for city in city_list:
, sales_${city} integer
% endfor
);
insert into sales_by_city(
yyyymm
% for city in city_list:
, sales_${city}
% endfor
)
select
yyyymm
% for city in city_list:
, sum(case when city = '${city}' then sales else 0 end) sales_${city}
% endfor
from sales
group by yyyymm
;
生成されるSQLの確認
上記のようなファイルを作成すると、
gen/sample.sql
というファイルが作成されます。
このファイルのコンテキストメニューから「Split Right」を選ぶと、
以下のような画面になります。
この状態で、左側ペインで編集し保存すると、
右側ペインに変更が反映されるようになります。
これで冗長なSQLを機能的にかけるかなと。