我正在创建一个web应用程序(使用flask),用户可以在其中选择和输入变量,然后根据选择的变量从数据集生成输出。这些可能的变量可以在下面的htmlpost表单中看到,并被传递到后端以允许在另一个代码块中看到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')
1条答案
按热度按时间0yycz8jy1#
在您的视图中,指定通配符
%
作为缺少或为空的任何变量的默认值:此外,这也变得不必要:
你可以用
attribs[1]
,因为LIKE
已进行子字符串匹配。CompanyName LIKE apple
与…完全相同CompanyName LIKE %apple%
.