SQLをローカルでフォーマットするツールを探していてsqlparseというツールを見つけたので試してみました。
インストール
sqlparseはPython製でpipを使ってインストールすることができます。
$ pip install sqlparse
ウェブ上で使用する
sqlparseはSQLFormatというオンラインサービスで使用されており、ブラウザやAPIでSQLを渡してフォーマットすることができます。
SQLFormat - Online SQL Formatter
コマンドラインで使用する
sqlparseをインストールすると使用可能になるsqlformatというコマンドに
ファイルか標準入力でSQLを入力するとフォーマットされたSQLが出力されます。
-r で改行・インデント、-k upper でキーワードを大文字にすることができます。その他のオプションについては-h オプションで確認することができます。
$ SQL='select t1.c1 A, t2.c2 B from t1 join t2 on t1.id = t2.id where t1.c1 = "HOGE";'
$ echo "$SQL" | sqlformat -r -k upper -
SELECT t1.c1 A,
t2.c2 B
FROM t1
JOIN t2 ON t1.id = t2.id
WHERE t1.c1 = "HOGE";
ライブラリとして使用する
sqlparseはライブラリとして使用することもできます。
フォーマット
sqlparse.formatによりsqlformatコマンドと同様にフォーマットを行うことができます。
>>> import sqlparse
>>> sql = 'select t1.c1 A, t2.c2 B from t1 join t2 on t1.id = t2.id where t1.c1 = "HOGE";'
>>> print sqlparse.format(sql, reindent=True, keyword_case='upper')
SELECT t1.c1 A,
t2.c2 B
FROM t1
JOIN t2 ON t1.id = t2.id
WHERE t1.c1 = "HOGE";
パース
sqlparse.parseでパース結果を取得することができます。sqlparse.parseはSQL文を表すStatementのタプルを返します。
>>> parsed = sqlparse.parse(sql)
>>> parsed
(<Statement 'select...' at 0x1077c6160>,)
sqlparse.parseはの入力には次のように複数のSQL文を含めることもできます。この場合、タプルの要素が複数になります。
>>> sqlparse.parse("select 1; select 2;")
(<Statement 'select...' at 0x1077c6958>,
<Statement 'select...' at 0x1077c6848>)
Statement.tokensによってトークンのリストを取得することができます。
>>> stmt = parsed[0]
>>> for t in stmt.tokens:
... print type(t), t
...
<class 'sqlparse.sql.Token'> select
<class 'sqlparse.sql.Token'>
<class 'sqlparse.sql.IdentifierList'> t1.c1 A, t2.c2 B
<class 'sqlparse.sql.Token'>
<class 'sqlparse.sql.Token'> from
<class 'sqlparse.sql.Token'>
<class 'sqlparse.sql.Identifier'> t1
<class 'sqlparse.sql.Token'>
<class 'sqlparse.sql.Token'> join
<class 'sqlparse.sql.Token'>
<class 'sqlparse.sql.Identifier'> t2
<class 'sqlparse.sql.Token'>
<class 'sqlparse.sql.Token'> on
<class 'sqlparse.sql.Token'>
<class 'sqlparse.sql.Comparison'> t1.id = t2.id
<class 'sqlparse.sql.Token'>
<class 'sqlparse.sql.Where'> where t1.c1 = "HOGE";
フォーマット方法のカスタマイズ
SQLのフォーマットの仕方には色々とスタイルがあります。例えば、SELECT句の列名はsqlformatの出力として得られるように行末にカンマをつけて並べられることもあれば、行の先頭にカンマをつけて並べられることもあります。
formatメソッドのオプションである程度フォーマット方法を変更することはできますが、カンマの位置などオプションだけでは対応できないものもあります。
sqlparseがフォーマット方法を制御しているフィルタを変更しSELECT句の列名をカンマを先頭にして並べるよう変更してみます。
まず、sqlparse.formatの処理の流れを理解するため実装をみると以下のようになっています。
def format(sql, **options):
"""Format *sql* according to *options*.
Available options are documented in :ref:`formatting`.
In addition to the formatting options this function accepts the
keyword "encoding" which determines the encoding of the statement.
:returns: The formatted SQL statement as string.
"""
encoding = options.pop('encoding', None)
stack = engine.FilterStack()
options = formatter.validate_options(options)
stack = formatter.build_filter_stack(stack, options)
stack.postprocess.append(filters.SerializerUnicode())
return ''.join(stack.run(sql, encoding))
stack = engine.FilterStack()でパーサーを作成、stack.run(sql, encoding)で与えられたSQLのパースを実行しています。
FilterStackにはフォーマット方法を制御するフィルタを追加することができます。sqlparse.formatではformatter.validate_options、formatter.build_filter_stackでオプションの値に基づいてセットしています。
stack.postprocess.append(filters.SerializerUnicode()) はトークンのリストを文字列に変換するためのフィルタです。
今回は簡単のためオプションを取らない例を考えます。手始めに入力をそのまま出力してみます。
>>> from sqlparse import engine
>>> stack = engine.FilterStack()
>>> stack.postprocess.append(filters.SerializerUnicode())
>>> print stack.run(sql).next()
select t1.c1 A, t2.c2 B from t1 join t2 on t1.id = t2.id where t1.c1 = "HOGE";
sqlparse.filters.ReindentFilter を追加すると結果がインデントされるようになります。
ReindentFilter などのSQLを処理するフィルタを使用する場合には、stack.enable_grouping() でトークンのグルーピングを有効にしておく必要があります。
>>> from sqlparse.filters import ReindentFilter
>>> stack = engine.FilterStack()
>>> stack.enable_grouping()
>>> stack.stmtprocess.append(ReindentFilter())
>>> stack.postprocess.append(filters.SerializerUnicode())
>>> print stack.run(sql).next()
select t1.c1 A,
t2.c2 B
from t1
join t2 on t1.id = t2.id
where t1.c1 = "HOGE";
カンマを先頭にもってくるよう動作を変えるために、ReindentFilter の_process_identifierlist をオーバーライドしたクラスを作成します。
from sqlparse.sql import Function
class MyReindentFilter(ReindentFilter):
def _process_identifierlist(self, tlist):
identifiers = list(tlist.get_identifiers())
if len(identifiers) > 1 and not tlist.within(Function):
first = identifiers[0]
self.indent += 1
tlist.insert_before(first, self.nl())
self.offset -= 1
tlist.insert_after(first, self.nl())
for token in identifiers[1:len(identifiers)-1]:
prev = tlist.token_prev(tlist.token_index(token), False)
if prev and prev.is_whitespace():
prev.value = ''
tlist.insert_after(token, self.nl())
last = identifiers[-1]
prev = tlist.token_prev(tlist.token_index(last), False)
if prev and prev.is_whitespace():
prev.value = ''
self.offset += 1
self.indent -= 1
self._process_default(tlist)
ReindentFilter の代わりにMyReindentFilter を使用するとカンマが先頭に来るようフォーマットされていることがわかります。
>>> stack = engine.FilterStack()
>>> stack.enable_grouping()
>>> stack.stmtprocess.append(MyReindentFilter())
>>> stack.postprocess.append(filters.SerializerUnicode())
>>> print stack.run(sql).next()
select
t1.c1 A
,t2.c2 B
from t1
join t2 on t1.id = t2.id
where t1.c1 = "HOGE"
and t2.c2 = 1;
サブクエリでも次のようにインデントの深さに合わせて正しく列名を並べられています。
>>> print stack.run('select a, b, c FROM (select a, b, c FROM t1) t2;').next()
select
a
,b
,c
FROM
(select
a
,b
,c
FROM t1) t2;
ReindentFilter の他のメソッドや他のフィルタによって、WHERE句のインデント方法やキーワードの大文字・小文字などについても制御することが可能です。