notebook

都内でWEB系エンジニアやってます。

sqlparseを使い特定のSQLを抽出する

クエリログの中から特定のクエリを取り出したいという状況になったことがあったのでその際のメモ

今回はsqlparseというライブラリを使った

sqlparse

Python製のSQLパース用のライブラリ、パースだけでなくフォーマットなども行える

andialbrecht/sqlparse: A non-validating SQL parser module for Python

github.com

パースしたSQLについての操作に関するドキュメントは下記

Analyzing the Parsed Statement — python-sqlparse 0.4.2.dev0 documentation

sqlparse.readthedocs.io

  • 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 BYDISTINCTを使っているクエリを抜き出すスクリプトを書いてみる

改行区切りのSQLのリストを一行ずつ精査してGROUP BYDISTINCTを用いたクエリの場合は出力させる

  • 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以外にもネストの中身に対してチェックするみたいなメソッドもあったので覚えておきたい