创建于:2020.03.28
修改于:2020.03.30
1. 背景
用户在前端输入内容,按照空格后其他字符隔开关键字。在数据库中多个字段查询,若有匹配的则返回查询结果。
涉及到:字符串正则匹配、sql循环搜索多个关键字、数据库模糊查找。@python3.5环境。
2. python实现
2.1 代码片段
# 输入
user_input = 'Odds Better better 5G 区块链?我的!nnnT%%% t'
# 模糊搜索
fuzzy_search(10, 0, user_input)
def fuzzy_search(limit, offset, user_input):
import re
# regex = re.compile('\s+')
input_list = set(re.sub('[^\w\u4e00-\u9fff]+', ' ', user_input.lower()).split(' '))
print("input list is ", input_list)
db_sessioin_query = db.session.query(DataTable.data_id,
DataTable.con_title, DataTable.con_author, DataTable.con_time, DataTable.data_address,
DataTable.data_spidername, DataTable.category)
rule = or_(*[DataTable.con_title.like("%"+input+"%") for input in input_list],
*[DataTable.con_author.like("%"+input+"%") for input in input_list],
*[DataTable.data_spidername.like("%"+input+"%") for input in input_list],
*[DataTable.category.like("%"+input+"%") for input in input_list])
print("rule is:", rule)
filter_result = db_sessioin_query.filter(or_(rule)).\
order_by(desc(DataTable.data_id)).limit(limit).offset(offset).all()
print("filter_result:", filter_result)
return filter_result
2.2 结果查看
input list is {'区块链', '5g', 'better', '我的', 't', 'odds', 'nnnt'}
rule is: t_data.con_title LIKE :con_title_1 OR t_data.con_title LIKE :con_title_2 OR t_data.con_title LIKE :con_title_3 OR t_data.con_title LIKE :con_title_4 OR t_data.con_title LIKE :con_title_5 OR t_data.con_title LIKE :con_title_6 OR t_data.con_title LIKE :con_title_7 OR t_data.con_author LIKE :con_author_1 OR t_data.con_author LIKE :con_author_2 OR t_data.con_author LIKE :con_author_3 OR t_data.con_author LIKE :con_author_4 OR t_data.con_author LIKE :con_author_5 OR t_data.con_author LIKE :con_author_6 OR t_data.con_author LIKE :con_author_7 OR t_data.data_spidername LIKE :data_spidername_1 OR t_data.data_spidername LIKE :data_spidername_2 OR t_data.data_spidername LIKE :data_spidername_3 OR t_data.data_spidername LIKE :data_spidername_4 OR t_data.data_spidername LIKE :data_spidername_5 OR t_data.data_spidername LIKE :data_spidername_6 OR t_data.data_spidername LIKE :data_spidername_7 OR t_data.category LIKE :category_1 OR t_data.category LIKE :category_2 OR t_data.category LIKE :category_3 OR t_data.category LIKE :category_4 OR t_data.category LIKE :category_5 OR t_data.category LIKE :category_6 OR t_data.category LIKE :category_7