如何在不重写多个SQL语句的情况下查询（SQL）可变数量的用户输入

Question

我正在创建一个web应用程序（使用Flask），用户可以在其中选择和输入变量，然后根据选择的变量从数据集生成输出。这些可能的变量可以在下面的HTML POST表单中看到，并通过反馈到后端，以允许在其他代码块中看到SQL查询

我遇到的问题是当用户选择一些输入变量但将其他变量留空时，查询将返回所有数据

例如，如果从表单中的一个下拉列表中选择了值“Financial Crime”，但没有填写其他下拉列表/文本输入，则查询将返回所有可能的结果。 而我想要的结果是查询返回Category=Financial Crime的所有行

我知道我可以编写一个if语句来排除任何值为''的输入变量，但这需要为每个可能的结果重新编写查询，我确信必须有一个更简单、更优化的方法来实现这一点

HTML:

<form method="POST">

                <div class="row">
                    <div class="col">
                        <p>Enter Company/Product name:</p>
                        <input type="text" name="name" class="form-control">
                    </div>

                    <br><br>

                    <div class="col">
                        <p>Enter Keywords (delimited by comma):</p>
                        <input type="text" name="keywords" class="form-control">
                    </div>

                </div>

                <br>

                <div class="row">
                    <div class="col-sm text-left">
                        <label for="category">Choose a category: </label>
                    <br>
                        <select name="category" id="category">
                            <option value="">Select Category</option>
                            <option value="Financial Crime">Financial Crime</option>
                            <option value="Regulatory Change">Regulatory Change</option>
                        </select>
                    </div>

                    <div class="col-sm text-center">
                        <label for="maturity">Choose maturity: </label>
                    <br>
                        <select name="maturity" id="maturity">
                            <option value="">Select Maturity</option>
                            <option value="Incumbent">Incumbent</option>
                            <option value="Challenger">Challenger</option>
                            <option value="New kid">New kid</option>
                        </select>
                    </div>

                    <div class="col-sm text-right">
                        <label for="under_tech">Choose underlying tech: </label>
                            <br>
                            <select name="under_tech" id="under_tech">
                                <option value="">Select Underlying Tech</option>
                                <option value="AI/ML">AI/ML</option>
                                <option value="Cloud">Cloud</option>
                                <option value="Blockchain">Blockchain</option>
                            </select>
                    </div>
                </div>

                <br><br>
                <div class="row float-right">
                    <input class="btn btn-primary" type="submit" value="Search">
                </div>
            </form>

Python（Flask）/SQL：

@app.route('/advancedsearch', methods=['GET', 'POST'])
def advancedsearch():
    if request.method == 'POST':
        category = request.form.get('category')
        maturity = request.form.get('maturity')
        under_tech = request.form.get('under_tech')
        keywords = request.form.get('keywords')
        name = request.form.get('name')
        attribs = [name,keywords,category,maturity,under_tech]

        with db.connect() as conn:
            # Query to find products with selected attributes
            qry = """SELECT CompanyName,ProductName,Category,CompanyWebsite,Logo
                    FROM directory_data.full_dataset
                    WHERE Company_description LIKE %s
                    AND Underlying_Tech LIKE %s
                    AND Company_Maturity LIKE %s
                    AND Category LIKE %s
                    AND CompanyName LIKE %s OR ProductName LIKE %s"""
            results = conn.execute(qry, ("%"+attribs[1]+"%","%"+attribs[4]+"%","%"+attribs[3]+"%","%"+attribs[2]+"%","%"+attribs[0]+"%","%"+attribs[0]+"%")).fetchall()

        if results:
            return render_template('advancedsearch.html', results=results, attribs=attribs)
        else:
            error = 'Results not found'
            return render_template('advancedsearch.html', error=error, attribs=attribs)
    return render_template('advancedsearch.html')

Answer 1

在您的视图中，为缺少或为空的任何变量指定通配符%作为默认值：

category = request.form.get('category') or '%'
maturity = request.form.get('maturity') or '%'
etc.

此外，这也变得不必要：

"%"+attribs[1]+"%"

您可以只使用attribs[1]，因为LIKE已经进行了子字符串匹配。 CompanyName LIKE apple与CompanyName LIKE %apple%完全相同