Flaskで複数条件での検索機能を実装したい

Question

前提

現在、Flaskを利用してウェブアプリケーションを作成しています。

そこにSQLを用いた検索機能を実装したいのですが、
現在のコードでは検索ワードを何も入力していない項目がある場合、検索結果にテーブル内の全レコードが表示されるなど正常に検索されません。

何か良い方法はありますでしょうか。

処理の流れとしては、

1. HTMLからPOST送信
2. Flaskで受け取り
3. localhost内のMariaDBにselect文で検索
4. render_templateでselect文の実行結果を送信

という流れにしたいです。

また、検索ワードは

• text1は部分一致検索
• text2～3は完全一致検索
• ラジオボタンは必ずどちらか選択されている

となっています。

実現したいこと

• 検索ワードが入力されていない項目を条件から外す（もしくは無視する）

ソースコード

検索画面

<!-- search.html -->

<form action="{{ url_for('search') }}" method="post">
    text1：
            <input type="text" name="text1">
    text2：
            <input type="text" name="text2">
    text3：
            <input type="text" name="text3">
    radio：
            <input type="radio" value="radio1">
           <input type="radio" value="radio2" >
    <input type="submit" value="検索">
</form>

検索結果

<!-- result.html -->

{% for res in result %}
    <tr>
        <td class="col-2">
            {{ res.text1' }}
        </td>
        <td class="col-2">
            {{ res.text2 }}
        </td>
        <td class="col-2">
            {{ res.text3 }}
        </td>
        <td class="col-2">
            {{ res.radio }}
        </td>
    </tr>
{% endfor %}

Flask

# app.py

@app.route("/search", methods=["POST"])
def search():
    text1 = '%' +  request.form.get("text1") + '%'
    text2 = request.form.get("text2")
    text3 = request.form.get("text3")
    radio = request.form.get("radio")
    dbmg = db_manager.db_manager()

    sql ="select * from db where text1 like %s or text2=%s or text3=%s or radio=%s"
    result = dbmg.exec_query(sql,(text1,text2,text3,radio))
    return render_template("result.html", result=result)

DB処理

# db_manager.py

import pymysql
import pymysql.cursors

class db_manager():
    def connect(self):
        return pymysql.connect(
            host="localhost", # DBサーバのアドレス
            user=***, # DBのユーザ名
            passwd=***,
            db="app", # 接続するDB名
            charset="utf8", # 文字コード
            cursorclass=pymysql.cursors.DictCursor # 検索結果のデータ型（辞書型）
        )

    def exec_query(self,sql,params=()):
        with self.connect() as conn :
            with conn.cursor() as cursor:
                cursor.execute(sql,params)
                results = cursor.fetchall()
            conn.commit()

        return results

試したこと

if文で検索ワードの有無を判定して条件分岐させる（現実的ではないと思い中止）

補足情報（ツールのバージョン）

Python3(Flask・PyMySQL)
MariaDB
Ubuntu
Apache

Comments

[meg_]

> if文で検索ワードの有無を判定して条件分岐させる（現実的ではないと思い中止）
上記はどういう理由で「現実的ではない」と判断されたのでしょうか？

[kou2014]

if文で書くとなると条件がかなり多くなってややこしくなり、現実的ではないと思って早々に諦めました。
でも、SQLで書く場合はそうするしかないのかなと思ってます。

Answer 1

SQLアンチパターンによると、kou2014さんの質問は、クエリのアンチパターンの内「プアマンズ・サーチエンジン(貧者のサーチエンジン)」にあたるものだと思いますので、今四苦八苦している実装はすべきではないというのが回答になると思います。

SQLアンチパターンに記載されている解決策は、「全文検索エンジンを使え」と書かれており、全文検索エンジンにはベンダー拡張のものや、サードパーティーのサーチエンジン(Sphinx Search, Apache Lucence)があると記載されています。

MariaDBにも全文検索エンジンあるようです。
MariaDBの全文検索を試したら感動した話

他にもElasticsearchなども有名で、色々な製品がありますので、使いやすいのを調べて見てはいかがですか？

現在のコードでは検索ワードを何も入力していない項目がある場合、検索結果にテーブル内の全レコードが表示されるなど正常に検索されません。

それでも無理矢理SQL(LIKE)で書きたい場合は、入力値が無い場合をパターン化して、if文で分岐するしかないと思います。

if文で検索ワードの有無を判定して条件分岐させる（現実的ではないと思い中止）

とありますが、どの辺に「現実的ではない感」を感じたのでしょうか？
text2,3とは完全一致するもの検索するなら、ここに何も入っていない物とマッチするものは存在しないとか、そういう結果になりそうなのですが、何か違う理由があるのでしょうか。

Comments

[kou2014]

回答ありがとうございます。全文検索エンジンというのがあることを初めて知りました。自分でも調べて検討してみます。

if文で書くとなるとかなり長くなってややこしくなると思い現実的ではないと感じたのですが、SQLで書く場合はやっぱりその方法しか無いですよね…

text1,2,3について補足させていただくと、１はキーワード検索のためFlaskで受け取った際に前後に％を付与して後々SQLに代入する際に部分一致になるようにして（Flask 行5）、２と３はドロップダウンリスト（ワードはDBのフィールドと合致する）にする予定だったのでそのまま受け取って（Flask 行6,7）、SQLに代入（Flask 行11）して検索をかけ、全ての条件に当てはまるレコードを返すという処理です。その際何も入力されていない項目があると検索結果がおかしくなります。（ORでつなげているのが原因だと推察）

[FiroProchainezo]

ややこしくなるのは仕方ないんじゃないかと思います。
パラメータが増えるとややこしく(複雑に)なるものじゃないですかね。

質問では聞かれていないですが、FlaskでSQLを使う場合、Flask-SQLAlchemyを使うのが一般的です。
Flask-SQLAlchemyを使えばSQLインジェクションなどを防ぐことができます。
とはいっても、入力値はescape処理をしないで使うべきではないです。
そのまま使うのではなく、escapeしてから使って下さい。