クエリログの中から特定のクエリを取り出したいという状況になったことがあったのでその際のメモ
今回はsqlparseというライブラリを使った
sqlparse
Python製のSQLパース用のライブラリ、パースだけでなくフォーマットなども行える
andialbrecht/sqlparse: A non-validating SQL parser module for Python
パースしたSQLについての操作に関するドキュメントは下記
Analyzing the Parsed Statement — python-sqlparse 0.4.2.dev0 documentation
- install
pip install sqlparse
試してみる
>>> import sqlparse >>> str = 'SELECT * FROM hoge WHERE column1 = 1 and column2 = "fuga"' # SQL文字列を解析 >>> parsed = sqlparse.parse(str)[0] >>> parsed <Statement 'SELECT...' at 0x7FFB0ACB1930> >>> parsed.tokens [<DML 'SELECT' at 0x7FFB0AA57948>, <Whitespace ' ' at 0x7FFB0AA579A8>, <Wildcard '*' at 0x7FFB0AA57A08>, <Whitespace ' ' at 0x7FFB0AA57A68>, <Keyword 'FROM' at 0x7FFB0AA57AC8>, <Whitespace ' ' at 0x7FFB0AA57B28>, <Identifier 'hoge' at 0x7FFB0ACB1E58>, <Whitespace ' ' at 0x7FFB0AA57BE8>, <Where 'WHERE ...' at 0x7FFB0ACB1C78>] >>> list(parsed.tokens[8].flatten()) [<Keyword 'WHERE' at 0x7FFB0AA57C48>, <Whitespace ' ' at 0x7FFB0AA57CA8>, <Name 'column1' at 0x7FFB0AA57D08>, <Whitespace ' ' at 0x7FFB0AA57D68>, <Comparison '=' at 0x7FFB0AA57DC8>, <Whitespace ' ' at 0x7FFB0AA57E28>, <Integer '1' at 0x7FFB0AA57E88>, <Whitespace ' ' at 0x7FFB0AA57EE8>, <Keyword 'and' at 0x7FFB0AA57F48>, <Whitespace ' ' at 0x7FFB0AA57FA8>, <Name 'column2' at 0x7FFB0AA5D048>, <Whitespace ' ' at 0x7FFB0AA5D0A8>, <Comparison '=' at 0x7FFB0AA5D108>, <Whitespace ' ' at 0x7FFB0AA5D168>, <Symbol '"fuga"' at 0x7FFB0AA5D1C8>] >>> parsed.tokens[8].value 'WHERE column1 = 1 and column2 = "fuga"' >>> parsed.tokens[8].ttype >>> parsed.tokens[0].value 'SELECT' >>> parsed.tokens[0].ttype Token.Keyword.DML >>> parsed.tokens[0].match(sqlparse.tokens.DML, "SELECT") True >>> parsed.tokens[0].match(sqlparse.tokens.DML, "INSERT") False
- sqlparse.parse()でSQL文字列を解析
- SQL文字列の配列で渡してもOKだが文字列で渡してもパースしてくれる
- 返却値はどちらにしても配列で返ってくる
parsed.tokens
でTokenのリストを取得できる- トークンの中には入れ子になっているものもある
parsed.flatten()
で入れ子になっているトークンなどもすべて展開した状態にできる- Tokenオブジェクトは
ttype
でトークンのタイプ、value
で分割された文字列を取得できる - Tokenオブジェクトは
match
というメソッドがありトークンのタイプと文字列を渡すとマッチしているかどうかの真偽値を返す- マッチに正規表現を使う場合は
regex=True
を渡す(デフォルトはFalse) - 文字列の大小を区別しないのは特定のトークンタイプ(
sqlparse.tokens.Kyeword
)のみ
- マッチに正規表現を使う場合は
特定のクエリを抜き出す
なんとなく使い方がわかったのでもう少し使ってみる
今回はGROUP BY
やDISTINCT
を使っているクエリを抜き出すスクリプトを書いてみる
改行区切りのSQLのリストを一行ずつ精査してGROUP BY
やDISTINCT
を用いたクエリの場合は出力させる
- query_list.txt
SELECT c1,SUM(v1) FROM sample GROUP BY c1; SELECT v1 FROM sample WHERE c2 = 'hoge'; SELECT DISTINCT(c1) FROM sample WHERE c2 = 'hoge';
1,3行目が抽出対象のクエリ
- filter.py
import sqlparse import sys filepath = sys.argv[1] f = open(filepath) lines = f.readlines() f.close() for line in lines: parsed = sqlparse.parse(line)[0] tokens = list(parsed.flatten()) is_grouped = filter(lambda t: t.match(sqlparse.tokens.Keyword, "GROUP\s+BY", regex=True), tokens) is_distinct = filter(lambda t: t.match(sqlparse.tokens.Name, "DISTINCT"), tokens) if len(list(is_grouped)) > 0 or len(list(is_distinct )) > 0: print(line)
$ python filter.py query_list.txt > grouped_query.txt $ cat grouped_query.txt SELECT c1,SUM(v1) FROM sample GROUP BY c1; SELECT DISTINCT(c1) FROM sample WHERE c2 = 'hoge';
こんな感じで目的のクエリを抽出できた
所感
- ある程度の条件であれば簡単にクエリの種類の判別が行えるので調査など色々活用できそう
- matchで正規表現を使えるので良い
- matchで文字列の大小比較を含めて行えるのはKeywordのみのようなので実際に使う場合は注意したい
- 今回使ったmatch以外にもネストの中身に対してチェックするみたいなメソッドもあったので覚えておきたい