我们前端设计了一个界面,以实现,用户可以选择公司,输入关键词,来查询相应的高频考题。
于是这里,我们使用到了三个数据库表,一个是根据jieba分词得到的各公司的高频关键词,一个是大模型生成的各公司可能会提问的问题,一个是八股信息表。
需要实现的逻辑是,前端传递一个公司列表,和一个关键词字符串,如果用户没有选择公司,则在数据库两个表中查询相应的与关键词匹配的问题,返回前端。如果传递了公司列表,则选择和公司列表中相匹配的公司进行查询。
代码如下:
# 首先查询相应公司前10个关键词
keywords_query = sql.execute(
f"SELECT keyword FROM keyword WHERE company='{company}' ORDER BY frequence DESC LIMIT 10")
keywords = [keyword[0] for keyword in keywords_query]
keyword_conditions = " OR ".join([f"content LIKE '%{keyword}%'" for keyword in keywords])
result = sql.execute(f"SELECT content FROM technical_question WHERE {keyword_conditions}")
print(result) # 调试信息:打印查询结果
然后选择得到的结果中10个问题作为输出
# 返回查询结果,根据需要进行处理
random_index1 = random.randint(0,len(result) - 1)
random_index2 = random.randint(0,len(result) - 1)
while random_index2 == random_index1:
random_index2 = random.randint(0,len(result) - 1)
return {'status': 'success', 'question1': result[random_index1],'question2': result[random_index2]}
完整代码:
@app.route('/api/getTechnicalQuestion',methods=['POST'])
def showTechnicalQuestion():
if request.method == "POST":
company = request.json['company']
if request.method == "GET":
company = request.args.get['company']
# 创建Database类的对象sql,test为需要访问的数据库名字 具体可见Database类的构造函数
sql = Database("xmsx")
try:
# 首先查询相应公司前10个关键词
keywords_query = sql.execute(
f"SELECT keyword FROM keyword WHERE company='{company}' ORDER BY frequence DESC LIMIT 10")
keywords = [keyword[0] for keyword in keywords_query]
keyword_conditions = " OR ".join([f"content LIKE '%{keyword}%'" for keyword in keywords])
result = sql.execute(f"SELECT content FROM technical_question WHERE {keyword_conditions}")
print(result) # 调试信息:打印查询结果
except Exception as e:
print(e) # 调试信息:打印异常信息
return {'status': "error", 'message': "code error"}
else:
print(result)
if not len(result) == 0:
# 返回查询结果,根据需要进行处理
random_index1 = random.randint(0,len(result) - 1)
random_index2 = random.randint(0,len(result) - 1)
while random_index2 == random_index1:
random_index2 = random.randint(0,len(result) - 1)
# return {'status': 'success', 'question1': result[random_index1],'question2': result[random_index2]}
return {'questions':[result[random_index1],result[random_index2]]}
else:
return {'status': 'success', 'question1': '无','question2':'无'}
# 前端传递关键词和公司查询LLM 问题,从而显示在前端
@app.route('/getLLMQuestion', methods=['POST'])
def showLLMQuestion():
if request.method == "POST":
company_list = request.json['data']['company']
keyword = request.json['data']['keyword']
sql = Database("xmsx")
try:
# 首如果前端没有选择公司:则默认只匹配关键词
if company_list:
# 如果前端传递了公司名,并且是一个列表
company_conditions = " OR ".join([f"company LIKE '%{company}%'" for company in company_list])
# 从词频表中选择前10个高频关键词
keyword_list = sql.execute(
f"SELECT keyword FROM keyword WHERE {company_conditions} ORDER BY frequence DESC LIMIT 10")
# 模糊匹配高频关键字
keywords = [keyword[0] for keyword in keyword_list]
if keyword=="":
sql_query = f"SELECT question FROM llmquestion WHERE ({company_conditions}) AND {keyword_conditions}"
else:
keyword_conditions = " OR ".join([f"question LIKE '%{keyword}%'" for keyword in keywords])
sql_query = f"SELECT question FROM llmquestion WHERE ({company_conditions}) AND question LIKE '%{keyword}%'"
else:
sql_query = f"SELECT question FROM llmquestion WHERE question LIKE '%{keyword}%'"
result = sql.execute(sql_query)
print(result) # 调试信息:打印查询结果
except Exception as e:
print(e) # 调试信息:打印异常信息
return jsonify({'status': "error", 'message': "code error"})
else:
if not len(result) == 0:
return jsonify({'questions': [result[0], result[1],result[2],result[3],result[4],result[5],result[6],result[7],result[8],result[9]]});
else:
return {'status': 'success', 'questions': '无'}