Python SQLAlchemy + MySQL で 複数フィールド に対する 全文検索：MATCH AGAINST 文 を実行する

概要

MySQL に構築した 全文検索インデックス に対して、SQLAlchemy で全文検索を実行する 方法です
やりたいことはシンプルですが、やってみると意外とつまづくためその解消法を書きます

環境

MySQL：Ver. 5.7
SQLAlchemy：Ver. 1.2

• MySQL FULLTEXTインデックス：https://dev.mysql.com/doc/refman/5.6/ja/innodb-fulltext-index.html
• MySQL 全文検索：https://dev.mysql.com/doc/refman/5.6/ja/fulltext-search.html
• SQLAlchemy：http://docs.sqlalchemy.org/

スキーマ

複数の文字列カラムに対して全文検索インデックスを付与したテーブルを定義
インデックスに対して、任意文字列の全文検索を実行したい

mysql> show create table articles

| Table    | Create Table |

| articles | CREATE TABLE `articles` (
      `id` INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
      `title` VARCHAR(200),
      `body` TEXT,
      FULLTEXT (`title`,`body`) WITH PARSER ngram
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

いくつかの方法／発生する問題

1. ORM で問い合わせる

まず SQLAlchemy らしい方法で全文検索しようと考えます が、Ver. 1.2 時点のドキュメント ¹ をみると、MySQLの MATCH AGAINST 構文は 単独フィールドに対してのみ 問い合わせできる仕様になっています（複数フィールドに問い合わせできない）．
対応しているのは単独フィールドへの検索のみで、複数フィールドにまたがった検索インデックスでは、問い合わせできないようです

customers = session.query(Customer).filter(Customer.name.match("anomaly")).all()

MySQL - renders MATCH (x) AGAINST (y IN BOOLEAN MODE)

2. SQL構文を自前で組み立てて connection.execute() に渡す

ORM が使えないので、次に MATCH AGAINST を含むSQL構文を文字列で組み立て、connection.execute() へ渡して問い合わせる方法を考えます ² ．自由にクエリ生成できるので複数フィールドに対する検索構文も書けますし、その他構文も自由に組み立てられてむしろ便利かもしれません

sample.py

search_phrase = "一般的なひらがなカタカナ漢字文字列"
sql = text("""
    SELECT * FROM articles 
    WHERE
      MATCH( articles.title, articles.body ) 
        AGAINST ({search_phrase}) 
    """.format( search_phrase = search_phrase ))

connection = engine.connect()
result = connection.execute(sql)

しかし全文検索を行うという性質上、検索文字列 search_phrase に入ってくる文字に配慮する必要がでてきます．例えば検索文字列に %, {} のような文字を含んだ場合、クエリを組み立てる際に文字列中の %, {} が placeholder と認識されて引数を要求して死んでしまったりします

ProgrammingError('(_mysql_exceptions.ProgrammingError) not enough arguments for format string',)

3. connection.execute() の bind を使って検索文字列を引き渡す

特殊な扱いの文字をサニタイズしていくのは面倒なので、connection.execute() にある bind 機構を使います．connection.execute() の bind 機構で文字列を渡すと %, {} 等が含まれていてもおかしな挙動がないよう自動的に無害化してくれるため、SQL構文を文字列のまま操作せず connection.execute() へ渡します

sample.py

search_phrase = "不正な挙動になる文字列 %, {}"
sql = text("""
    SELECT * FROM articles 
    WHERE
      MATCH( articles.title, articles.body ) 
        AGAINST (':search_phrase' IN BOOLEAN MODE) 
    """)

connection = engine.connect()
search_result = connection.execute(sql, search_phrase=search_phrase)

これで問題なく動きそうですが、Boolean Mode らしいクエリを試すと意図しない検索結果になったりします（自然言語全文検索 IN NATURAL LANGUAGE MODE で検索実行した場合には概ね大丈夫そうでした ）

ブール全文検索 ³ の検索文字列は +(phrase1 phrase2) +phrase3 （ (phrase1 OR phrase2）AND phrase3 の意） のように、論理式を引き渡す検索方式です

この際クエリに含まれる +,-,*,() 等の文字列が connection.execute() のbindパラメータのサニタイズ対象となってエスケープされ、意図と違う検索文字列になるようです

4. SQL構文を text( text, bindparams ) で組み立てる

意図しないエスケープ処理を避けるため、text( text, bindparams ) を使って text を組み立てます（検索文字列を [ bindparam() ] リストで引き渡します）．

これで、引き渡すSQL構文と検索文字列がリテラルとして扱われ、不正な挙動を避けつつ意図通りの問い合わせが実行できました．

sample.py

from sqlalchemy import text, bindparam

search_phrase = "+(不正な挙動 になる 文字列) +(%とか) +({}とか)"
sql = text("""
    SELECT * FROM articles 
    WHERE
      MATCH( articles.title, articles.body ) 
        AGAINST (:search_phrase IN BOOLEAN MODE) 
    """, bindparams=[bindparam('search_phrase', search_phrase)])

connection = engine.connect()
search_result = connection.execute(sql)

まとめ

MySQL に構築した 全文検索インデックス に対して、SQLAlchemy で全文検索実行（BOOLEAN MODE） するには

• 単独フィールドへの検索であれば match() を使い、
  複数フィールドへの検索であれば SQL構文を生成して connection.execute() へ渡す
• text( text, bindparams ) を使って、検索文字列をリテラルとして引き渡す

で実装しましょう

余談ですが、将来的には SQLAlchemy の ORM で複数フィールドによる全文検索インデックスへの MATCH AGAINST 構文をサポートする予定 のようです．将来的には、リテラル文字列を渡すよりずっとスマートに実装できるようになるかもしれないですね

参考資料

• MySQL (Ver.5.7)

  • FULLTEXTインデックス
  • 全文検索関数

• SQLAlchemy (Ver.1.2)

  • match 構文
  • sqlalchemy.sql.expression.text

• MySQL Full-text Searches and SQLAlchemy; the Present and a Proposed Future

• Proper use of MySQL full text search with SQLAlchemy

1. http://docs.sqlalchemy.org/en/latest/orm/internals.html#sqlalchemy.orm.properties.ColumnProperty.Comparator.match ↩

2. http://docs.sqlalchemy.org/en/latest/core/tutorial.html?highlight=match%20against#using-textual-sql ↩

3. https://dev.mysql.com/doc/refman/5.6/ja/fulltext-boolean.html ↩