def year_combo_activated(self, text):
self.query_year_name = text
self.set_SQLedit_content()
# 查询并设置major专业
if self.query_table_name == "admission_score_pro":
self.major_combo.clear()
self.major_combo.addItem("无此项数据")
else:
mysql_string = "select major from " + self.query_table_name + " where school='" + self.query_school_name \
+ "' and district='" + self.query_district_name + "' and year='" + self.query_year_name \
+ "' group by major;"
myresult = mysql_query_sentence(mysql_string)
temp = []
for item in myresult:
temp.append(item["major"])
major_names = temp
# 重新设置major_combo
self.major_combo.clear()
major_names = sorted(major_names,
key=lambda x: lazy_pinyin(x.lower())[0][0])
self.major_combo.addItems(major_names)
# 查询并设置batch批次
if self.query_table_name == "admission_score_pro":
mysql_string = "select batch from " + self.query_table_name + " where school='" + self.query_school_name \
+ "' and district='" + self.query_district_name + "' and year='" + self.query_year_name \
+ "' group by batch;"
myresult = mysql_query_sentence(mysql_string)
temp = []
for item in myresult:
temp.append(item["batch"])
batch_names = temp
# 重新设置classy_combo
self.batch_combo.clear()
batch_names = sorted(batch_names,
key=lambda x: lazy_pinyin(x.lower())[0][0])
self.batch_combo.addItems(batch_names)
else:
self.batch_combo.clear()
self.batch_combo.addItem("无此项数据")
# 查询并设置classy科别
mysql_string = "select classy from " + self.query_table_name + " where school='" + self.query_school_name \
+ "' and district='" + self.query_district_name + "' and year='" + self.query_year_name \
+ "' group by classy;"
myresult = mysql_query_sentence(mysql_string)
temp = []
for item in myresult:
temp.append(item["classy"])
classy_names = temp
# 重新设置classy_combo
self.classy_combo.clear()
classy_names = sorted(classy_names,
key=lambda x: lazy_pinyin(x.lower())[0][0])
self.classy_combo.addItems(classy_names)
def build_mysql_major_dict():
function_logger = MyLog(logger=sys._getframe().f_code.co_name).getlog()
function_logger.info("获取招生计划表中的专业字段...")
# 招生计划中的专业名称
plan_sql_string = "SELECT major FROM admission_plan GROUP BY major;"
myresult = mysql_query_sentence(plan_sql_string)
function_logger.debug("招生计划表中专业数%d:" % len(myresult))
pattern = re.compile(r"[(（[].*?[)）\]].*")
plan_major_set = set()
for major in myresult:
temp = re.sub(pattern, "", major[0])
plan_major_set.add(temp)
function_logger.debug("招生计划表中专业数(统计合并后):%d" % len(plan_major_set))
function_logger.debug(
str(
sorted(list(plan_major_set),
key=lambda x: lazy_pinyin(x.lower())[0][0])))
# 录取分数中的专业名称
score_sql_string = "SELECT major FROM admission_score_major GROUP BY major;"
myresult = mysql_query_sentence(score_sql_string)
function_logger.debug("录取分数表中专业数%d:" % len(myresult))
score_major_set = set()
for major in myresult:
temp = re.sub(pattern, "", major[0])
score_major_set.add(temp)
function_logger.debug("录取分数表中专业数(统计合并后):%d" % len(score_major_set))
function_logger.debug(
str(
sorted(list(score_major_set),
key=lambda x: lazy_pinyin(x.lower())[0][0])))
# 测定两者交集
function_logger.debug("以上两者的交集为：")
major_and_set = plan_major_set.intersection(score_major_set)
function_logger.debug("交集长度为：%d", len(major_and_set))
function_logger.debug(
str(
sorted(list(major_and_set),
key=lambda x: lazy_pinyin(x.lower())[0][0])))
# 测定两者并集
function_logger.debug("以上两者的并集为：")
major_or_set = plan_major_set.union(score_major_set)
function_logger.debug("并集长度为：%d", len(major_or_set))
function_logger.debug(
str(
sorted(list(major_or_set),
key=lambda x: lazy_pinyin(x.lower())[0][0])))
def answer_question_by_template(question: str,
school_flag: int = 0,
school_name: str = "") -> (dict, list):
"""
分词-》提取关键词、抽象问句-》抽象问句匹配模板-》模板构造sql语句-》sql语句返回查询结果-》查询结果构造模板输出
:param question: 问题
:param school_flag 学校标志位，是否指定默认学校
:param school_name 指定的默认学校名
:return: 中间结果词典，最终答案列表
"""
# 保存中间结果
mid_result = {}
segment_list = question_segment_hanlp(question)
mid_result["segment_list"] = segment_list
ab_question = question_abstract(segment_list)
mid_result["ab_question"] = ab_question
keyword = question_analysis_to_keyword(segment_list)
# 修改默认学校名
if school_flag:
keyword["search_school"] = school_name
mid_result["keyword"] = keyword
keyword_normalize = question_keyword_normalize(keyword)
mid_result["keyword_normalize"] = keyword_normalize
template_sentence_type = keyword_normalize["search_table"]
fq_condition, fq_target, match_template_question, match_template_answer \
= find_question_match_template(ab_question, template_sentence_type)
# 修改模板框
if school_flag:
if "(school)" not in match_template_question:
match_template_question = "(school)" + match_template_question
mid_result["match_template_question"] = match_template_question
mid_result["match_template_answer"] = match_template_answer
mysql_string = build_mysql_string_by_template_and_keymap(
match_template_question, template_sentence_type, keyword_normalize)
mid_result["mysql_string"] = mysql_string
# 数据库查询
result = []
result_edit = []
if mysql_string == "":
result_edit.append("问句条件词为空，无法构建查询语句！")
else:
# 若只有学校一个关键词
if "and" not in mysql_string:
result_edit.append("问句条件词只有学校，查询过宽！")
else:
result = mysql_query_sentence(mysql_string)
if len(result) == 0:
result_edit.append("查询结果为空！")
else:
mid_result["search_result"] = result
for item in result:
answer_string = build_mysql_answer_string_by_template(
match_template_answer, item)
result_edit.append(answer_string)
return mid_result, result_edit
def district_combo_activated(self, text):
self.query_district_name = text
# 查询当前选择下（表名、学校、地区）年份数据项
mysql_string = "select year from " + self.query_table_name + " where school='" + self.query_school_name \
+ "' and district='" + self.query_district_name + "' group by year;"
myresult = mysql_query_sentence(mysql_string)
temp = []
for item in myresult:
temp.append(str(item["year"]))
year_names = temp
# 重新设置year_combo
self.year_combo.clear()
year_names.sort()
self.year_combo.addItems(year_names)
self.set_SQLedit_content()
def mysql_query(self):
mysql_string = self.SQL_edit.text()
if mysql_string != "":
pass
else:
mysql_string = self.build_mysql_string()
self.SQL_edit.setText(mysql_string)
myresult = mysql_query_sentence(mysql_string)
if len(myresult) == 0:
self.result_edit.setText("查询结果为空！")
return
self.result_edit.clear()
for item in myresult:
self.result_edit.append(str(item))
return myresult
def school_combo_activated(self, text):
self.query_school_name = text
# 查询当前选择下（表名、学校）地区数据项
mysql_string = "select district from " + self.query_table_name + " where school='" + self.query_school_name \
+ "' group by district;"
myresult = mysql_query_sentence(mysql_string)
temp = []
for item in myresult:
temp.append(item["district"])
district_names = temp
# 重新设置major_combo
self.district_combo.clear()
district_names = sorted(district_names, key=lambda x: lazy_pinyin(x.lower())[0][0])
self.district_combo.addItems(district_names)
self.set_SQLedit_content()
def table_combo_activated(self, text):
table_names = ["计划招生", "专业分数", "地区分数"]
mysql_table_name = ["admission_plan", "admission_score_major", "admission_score_pro"]
self.query_table_name = mysql_table_name[table_names.index(text)]
# 查询当前选择下（表名）学校数据项
mysql_string = "select school from " + self.query_table_name + " group by school;"
myresult = mysql_query_sentence(mysql_string)
temp = []
for item in myresult:
temp.append(item["school"])
school_names = temp
# 重新设置school_combo
self.school_combo.clear()
school_names = sorted(school_names, key=lambda x: lazy_pinyin(x.lower())[0][0])
self.school_combo.addItems(school_names)
self.set_SQLedit_content()
def admission_score_major_table_query(keyword):
search_table = keyword["search_table"]
search_year = keyword["search_year"]
search_school = keyword["search_school"]
search_major = keyword["search_major"]
search_district = keyword["search_district"]
result_edit = []
# 只有年份
# 只有学校
# 只有专业
if search_year == "" and search_school == "" and search_major != "" and search_district == "":
# 查询各年各学校各地区该专业录取分数线
sql_string = "SELECT year FROM admission_score_major WHERE major = '" + search_major \
+ "' GROUP BY year;"
myresult = mysql_query_sentence(sql_string)
if len(myresult) == 0:
result_edit.append("查询结果为空！")
else:
for year_item in myresult:
result_edit.append(
str(year_item[0]) + "年" + search_major + "专业的录取分数如下：")
# 查询该年各校各地区该专业的录取分数
sub_sql_string = "SELECT school FROM admission_score_major WHERE major = '" + search_major \
+ "' and year = '" + str(year_item[0]) + "' GROUP BY school;"
# print(sub_sql_string)
sub_myresult = mysql_query_sentence(sub_sql_string)
if len(sub_myresult) == 0:
result_edit.append(
str(year_item[0]) + "年" + search_major +
"专业录取分数线查询结果为空！")
else:
for school_item in sub_myresult:
result_edit.append(
str(school_item[0]) + search_major + "专业的录取分数如下：")
# 查询该年该校各地区该专业的录取分数
sub_sub_sql_string = "SELECT * FROM admission_score_major WHERE major = '" + search_major \
+ "' and year = '" + str(year_item[0]) \
+ "' and school = '" + str(school_item[0]) + "';"
# print(sub_sql_string)
sub_sub_myresult = mysql_query_sentence(
sub_sub_sql_string)
if len(sub_sub_myresult) == 0:
result_edit.append(
str(year_item[0]) + "年" + str(school_item[0]) +
search_major + "专业录取分数线查询结果为空！")
else:
for item in sub_sub_myresult:
result_edit.append(
str(item[2]) + str(item[5]) + "类" +
"最高分：" + str(item[6]) + ";\t平均分：" +
str(item[7]) + ";\t最低分：" + str(item[8]))
result_edit.append("\n")
result_edit.append("\n")
result_edit.append("\n")
# 只有地区
# 只有年份、学校
# 只有年份、专业
elif search_year != "" and search_school == "" and search_major != "" and search_district == "":
# 查询该年各校各地区该专业的录取分数
sql_string = "SELECT school FROM admission_score_major WHERE major = '" + search_major \
+ "' and year = '" + search_year + "' GROUP BY school;"
# print(sql_string)
myresult = mysql_query_sentence(sql_string)
if len(myresult) == 0:
result_edit.append(search_year + "年" + search_major +
"专业录取分数线查询结果为空！")
else:
for school_item in myresult:
result_edit.append(search_year + str(school_item[0]) +
search_major + "专业的录取分数如下：")
# 查询该年该校各地区该专业的录取分数
sub_sql_string = "SELECT * FROM admission_score_major WHERE major = '" + search_major \
+ "' and year = '" + search_year \
+ "' and school = '" + str(school_item[0]) + "';"
# print(sub_sql_string)
sub_myresult = mysql_query_sentence(sub_sql_string)
if len(sub_myresult) == 0:
result_edit.append(search_year + "年" +
str(school_item[0]) + search_major +
"专业录取分数线查询结果为空！")
else:
for item in sub_myresult:
result_edit.append(
str(item[2]) + str(item[5]) + "类" + "最高分：" +
str(item[6]) + ";\t平均分：" + str(item[7]) +
";\t最低分：" + str(item[8]))
result_edit.append("\n")
result_edit.append("\n")
# 只有年份、地区
# 只有学校、专业
elif search_year == "" and search_school != "" and search_major != "" and search_district == "":
# 查询各年该学校各地区该专业录取分数线
sql_string = "SELECT year FROM admission_score_major WHERE major = '" + search_major \
+ "' and school = '" + search_school+ "' GROUP BY year;"
myresult = mysql_query_sentence(sql_string)
if len(myresult) == 0:
result_edit.append("查询结果为空！")
else:
for year_item in myresult:
result_edit.append(
str(year_item[0]) + "年" + search_major + "专业的录取分数如下：")
# 查询该年该校各地区该专业的录取分数
sub_sql_string = "SELECT * FROM admission_score_major WHERE major = '" + search_major \
+ "' and year = '" + str(year_item[0]) \
+ "' and school = '" + search_school + "';"
# print(sub_sql_string)
sub_myresult = mysql_query_sentence(sub_sql_string)
if len(sub_myresult) == 0:
result_edit.append(
str(year_item[0]) + "年" + search_school +
search_major + "专业录取分数线查询结果为空！")
else:
for item in sub_myresult:
result_edit.append(
str(item[2]) + str(item[5]) + "类" + "最高分：" +
str(item[6]) + ";\t平均分：" + str(item[7]) +
";\t最低分：" + str(item[8]))
result_edit.append("\n")
result_edit.append("\n")
result_edit.append("\n")
# 只有学校、地区
# 只有专业、地区
elif search_year == "" and search_school == "" and search_major != "" and search_district != "":
# 查询各年各学校该地区该专业录取分数线
sql_string = "SELECT year FROM admission_score_major WHERE major = '" + search_major \
+ "' and district = '"+ search_district + "' GROUP BY year;"
myresult = mysql_query_sentence(sql_string)
if len(myresult) == 0:
result_edit.append("查询结果为空！")
else:
for year_item in myresult:
result_edit.append(
str(year_item[0]) + "年" + search_major + "专业的录取分数如下：")
# 查询该年各校该地区该专业的录取分数
sub_sql_string = "SELECT * FROM admission_score_major WHERE major = '" + search_major \
+ "' and year = '" + str(year_item[0]) \
+ "' and district = '" + search_district + "';"
# print(sub_sql_string)
sub_myresult = mysql_query_sentence(sub_sql_string)
if len(sub_myresult) == 0:
result_edit.append(
str(year_item[0]) + "年" + search_major + "在" +
search_district + "的录取分数查询为空：")
else:
for item in sub_myresult:
result_edit.append(
str(item[1]) + str(item[5]) + "类" + "最高分：" +
str(item[6]) + ";\t平均分：" + str(item[7]) +
";\t最低分：" + str(item[8]))
result_edit.append("\n")
result_edit.append("\n")
result_edit.append("\n")
# 只有学校、专业、地区
elif search_year == "" and search_school != "" and search_major != "" and search_district != "":
# 查询各年该校该地区该专业的录取分数
result_edit.append(search_school + search_major + "在" +
search_district + "的录取分数如下：")
sql_string = "SELECT * FROM admission_score_major WHERE major = '" + search_major \
+ "' and school = '" + search_school \
+ "' and district = '" + search_district + "';"
# print(sql_string)
myresult = mysql_query_sentence(sql_string)
if len(myresult) == 0:
result_edit.append(search_school + search_major + "在" +
search_district + "的录取分数查询为空：")
else:
for item in myresult:
result_edit.append(
str(item[3]) + "年" + str(item[5]) + "类" + "最高分：" +
str(item[6]) + ";\t平均分：" + str(item[7]) + ";\t最低分：" +
str(item[8]))
result_edit.append("\n")
# 只有年份、专业、地区
elif search_year != "" and search_school == "" and search_major != "" and search_district != "":
# 查询各校该年该地区该专业的录取分数
result_edit.append(search_year + search_major + "在" + search_district +
"的录取分数如下：")
sql_string = "SELECT * FROM admission_score_major WHERE major = '" + search_major \
+ "' and year = '" + search_year \
+ "' and district = '" + search_district + "';"
# print(sql_string)
myresult = mysql_query_sentence(sql_string)
if len(myresult) == 0:
result_edit.append(search_year + search_major + "在" +
search_district + "的录取分数查询为空：")
else:
for item in myresult:
result_edit.append(
str(item[1]) + str(item[5]) + "类" + "最高分：" + str(item[6]) +
";\t平均分：" + str(item[7]) + ";\t最低分：" + str(item[8]))
result_edit.append("\n")
# 只有年份、学校、地区
# 只有年份、学校、专业
elif search_year != "" and search_school != "" and search_major != "" and search_district == "":
# 查询该校该年各地区该专业的录取分数
result_edit.append(search_school + search_year + search_major +
"的录取分数如下：")
sql_string = "SELECT * FROM admission_score_major WHERE major = '" + search_major \
+ "' and year = '" + search_year \
+ "' and school = '" + search_school + "';"
# print(sql_string)
myresult = mysql_query_sentence(sql_string)
if len(myresult) == 0:
result_edit.append(search_school + search_year + search_major +
"的录取分数查询为空：")
else:
for item in myresult:
result_edit.append(
str(item[2]) + str(item[5]) + "类" + "最高分：" + str(item[6]) +
";\t平均分：" + str(item[7]) + ";\t最低分：" + str(item[8]))
result_edit.append("\n")
# 只有年份、学校、专业、地区
elif search_year != "" and search_school != "" and search_major != "" and search_district != "":
# 查询该校该年该地区该专业的录取分数
result_edit.append(search_school + search_year + search_major + "在" +
search_district + "的录取分数如下：")
sql_string = "SELECT * FROM admission_score_major WHERE major = '" + search_major \
+ "' and year = '" + search_year + "' and district = '" + search_district\
+ "' and school = '" + search_school + "';"
# print(sql_string)
myresult = mysql_query_sentence(sql_string)
if len(myresult) == 0:
result_edit.append(search_school + search_year + search_major +
"在" + search_district + "的录取分数查询为空：")
else:
for item in myresult:
result_edit.append(
str(item[5]) + "类" + "最高分：" + str(item[6]) + ";\t平均分：" +
str(item[7]) + ";\t最低分：" + str(item[8]))
result_edit.append("\n")
return result_edit
def admission_score_pro_table_query(keyword):
search_table = keyword["search_table"]
search_year = keyword["search_year"]
search_school = keyword["search_school"]
search_major = keyword["search_major"]
search_district = keyword["search_district"]
result_edit = []
# 只有年份
if search_year != "" and search_school == "" and search_district == "":
# 查询各学校该年的各地区的录取分数线
sql_string = "SELECT school FROM admission_score_pro WHERE year = '" + search_year + "' GROUP BY school;"
myresult = mysql_query_sentence(sql_string)
if len(myresult) == 0:
result_edit.append("查询结果为空！")
else:
for item in myresult:
result_edit.append(search_year + "年" + str(item[0]) +
"的录取分数如下：")
# 查询该年各校的录取分数
sub_sql_string = "SELECT * FROM admission_score_pro WHERE year = '" + search_year \
+ "' and school = '" + str(item[0]) + "';"
# print(sub_sql_string)
sub_myresult = mysql_query_sentence(sub_sql_string)
if len(sub_myresult) == 0:
result_edit.append(str(item[0]) + "地区录取分数线查询结果为空！")
else:
for item in sub_myresult:
result_edit.append(
str(item[3]) + str(item[4]) + str(item[5]) +
"分数线为" + str(item[6]))
result_edit.append("\n")
# 只有学校
elif search_year == "" and search_school != "" and search_district == "":
# 查询该学校各年的各地区的录取分数线
sql_string = "SELECT year FROM admission_score_pro WHERE school = '" + search_school + "' GROUP BY year;"
myresult = mysql_query_sentence(sql_string)
if len(myresult) == 0:
result_edit.append("查询结果为空！")
else:
for item in myresult:
result_edit.append(search_school + str(item[0]) + "年的录取分数如下：")
# 查询各年该校的录取分数
sub_sql_string = "SELECT * FROM admission_score_pro WHERE school = '" + search_school \
+ "' and year = '" + str(item[0]) + "';"
# print(sub_sql_string)
sub_myresult = mysql_query_sentence(sub_sql_string)
if len(sub_myresult) == 0:
result_edit.append(str(item[0]) + "地区录取分数线查询结果为空！")
else:
for item in sub_myresult:
result_edit.append(
str(item[3]) + str(item[4]) + str(item[5]) +
"分数线为" + str(item[6]))
result_edit.append("\n")
# 只有地区
elif search_year == "" and search_school == "" and search_district != "":
# 查询该地区各年各校的录取分数线
sql_string = "SELECT year FROM admission_score_pro WHERE district = '" + search_district \
+ "' GROUP BY year ORDER BY year;"
myresult = mysql_query_sentence(sql_string)
if len(myresult) == 0:
result_edit.append("查询结果为空！")
else:
for item in myresult:
result_edit.append(search_district + str(item[0]) +
"年的录取分数如下：")
# 查询各年该校的录取分数
sub_sql_string = "SELECT * FROM admission_score_pro WHERE district = '" + search_district \
+ "' and year = '" + str(item[0]) + "';"
# print(sub_sql_string)
sub_myresult = mysql_query_sentence(sub_sql_string)
if len(sub_myresult) == 0:
result_edit.append(str(item[0]) + "地区录取分数线查询结果为空！")
else:
for item in sub_myresult:
result_edit.append(
str(item[1]) + str(item[4]) + str(item[5]) +
"分数线为" + str(item[6]))
result_edit.append("\n")
# 只有年份、学校
elif search_year != "" and search_school != "" and search_district == "":
# 查询该年该学校各地区的录取分数线
result_edit.append(search_school + search_year + "年的录取分数如下：")
sql_string = "SELECT * FROM admission_score_pro WHERE year = '" + search_year \
+ "' and school = '" + search_school + "';"
# print(sub_sql_string)
myresult = mysql_query_sentence(sql_string)
if len(myresult) == 0:
result_edit.append(search_school + search_year + "年地区录取分数线查询结果为空！")
else:
for item in myresult:
result_edit.append(
str(item[3]) + str(item[4]) + str(item[5]) + "分数线为" +
str(item[6]))
result_edit.append("\n")
# 只有年份、地区
elif search_year != "" and search_school == "" and search_district != "":
# 查询该年该地区各学校的录取分数线
result_edit.append(search_district + search_year + "年的录取分数如下：")
sql_string = "SELECT * FROM admission_score_pro WHERE year = '" + search_year \
+ "' and district = '" + search_district + "';"
# print(sub_sql_string)
myresult = mysql_query_sentence(sql_string)
if len(myresult) == 0:
result_edit.append(search_district + search_year + "年录取分数线查询结果为空！")
else:
for item in myresult:
result_edit.append(
str(item[1]) + str(item[4]) + str(item[5]) + "分数线为" +
str(item[6]))
result_edit.append("\n")
# 只有学校、地区
elif search_year == "" and search_school != "" and search_district != "":
# 查询该学校该地区各年的录取分数线
result_edit.append(search_school + "在" + search_district +
"的历年录取分数如下：")
sql_string = "SELECT * FROM admission_score_pro WHERE school = '" + search_school \
+ "' and district = '" + search_district + "';"
# print(sub_sql_string)
myresult = mysql_query_sentence(sql_string)
if len(myresult) == 0:
result_edit.append(search_school + "在" + search_district +
"的历年录取分数线查询结果为空！")
else:
for item in myresult:
result_edit.append(
str(item[2]) + str(item[4]) + str(item[5]) + "分数线为" +
str(item[6]))
result_edit.append("\n")
# 只有年份、学校、地区
elif search_year != "" and search_school != "" and search_district != "":
# 查询该学校该地区该年的录取分数线
result_edit.append(search_year + search_school + "在" +
search_district + "的录取分数如下：")
sql_string = "SELECT * FROM admission_score_pro WHERE year = '" + search_year \
+ "' and school = '" + search_school \
+ "' and district = '" + search_district + "';"
# print(sub_sql_string)
myresult = mysql_query_sentence(sql_string)
if len(myresult) == 0:
result_edit.append(search_year + search_school + "在" +
search_district + "的录取分数线查询结果为空！")
else:
for item in myresult:
result_edit.append(
str(item[4]) + str(item[5]) + "分数线为" + str(item[6]))
result_edit.append("\n")
return result_edit
def admission_plan_table_query(keyword):
search_table = keyword["search_table"]
search_year = keyword["search_year"]
search_school = keyword["search_school"]
search_major = keyword["search_major"]
search_district = keyword["search_district"]
result_edit = []
# 只有年份,统计各学校的招生情况（文科、理科）
if search_year != "" and search_school == "" and search_major == "" and search_district == "":
# 查询学校该年招生总人数
sql_string = "SELECT school,COUNT(*) FROM admission_plan WHERE year = '" + search_year \
+ "' GROUP BY school;"
myresult = mysql_query_sentence(sql_string)
if len(myresult) == 0:
result_edit.append("查询结果为空！")
else:
for item in myresult:
result_edit.append(
str(item[0]) + search_year + "年" + "招生计划为" + str(item[1]) +
"人")
# 查询该年该校招生的类别情况
sub_sql_string = "SELECT classy,COUNT(*) FROM admission_plan WHERE year = '" + search_year \
+ "' and school = '" + str(item[0]) + "' GROUP BY classy;"
# print(sub_sql_string)
sub_myresult = mysql_query_sentence(sub_sql_string)
if len(sub_myresult) == 0:
result_edit.append("招生类别查询结果为空！")
else:
for item in sub_myresult:
result_edit.append("其中" + str(item[0]) + str(item[1]) +
"人")
# 只有学校，统计该学校的招生情况（文科理科）
elif search_year == "" and search_school != "" and search_major == "" and search_district == "":
# 查询学校各年招生总人数
sql_string = "SELECT year,COUNT(*) FROM admission_plan WHERE school = '" + search_school \
+ "' GROUP BY year;"
myresult = mysql_query_sentence(sql_string)
if len(myresult) == 0:
result_edit.append("查询结果为空！")
else:
for item in myresult:
result_edit.append(search_school + str(item[0]) + "年" +
"招生计划为" + str(item[1]) + "人")
# 查询该年该校招生的类别情况
sub_sql_string = "SELECT classy,COUNT(*) FROM admission_plan WHERE year = '" + str(item[0]) \
+ "' and school = '" + search_school + "' GROUP BY classy;"
# print(sub_sql_string)
sub_myresult = mysql_query_sentence(sub_sql_string)
if len(sub_myresult) == 0:
result_edit.append("招生类别查询结果为空！")
else:
for item in sub_myresult:
result_edit.append("其中" + str(item[0]) + "招生" +
str(item[1]) + "人")
# 只有专业，统计每年招生人数
elif search_year == "" and search_school == "" and search_major != "" and search_district == "":
# 查询该专业各校各年招生总人数
sql_string = "SELECT year,COUNT(*) FROM admission_plan WHERE major = '" + search_major \
+ "' GROUP BY year;"
# print(sql_string)
myresult = mysql_query_sentence(sql_string)
if len(myresult) == 0:
result_edit.append("查询结果为空！")
else:
for item in myresult:
result_edit.append(search_major + str(item[0]) + "招生" +
str(item[1]) + "人")
sub_sql_string = "SELECT school,COUNT(*) FROM admission_plan WHERE major = '" + search_major \
+ "'and year = '" + str(item[0]) + "' GROUP BY school;"
sub_myresult = mysql_query_sentence(sub_sql_string)
if len(sub_myresult) == 0:
result_edit.append("学校具体招生人数查询结果为空！")
else:
for item in sub_myresult:
result_edit.append("其中" + str(item[0]) + "招生" +
str(item[1]) + "人")
# 只有地区，统计每年招生人数
elif search_year == "" and search_school == "" and search_major == "" and search_district != "":
# 查询该地区各年招生总人数
sql_string = "SELECT year,COUNT(*) FROM admission_plan WHERE district = '" + search_district \
+ "' GROUP BY year;"
myresult = mysql_query_sentence(sql_string)
if len(myresult) == 0:
result_edit.append("查询结果为空！")
else:
for item in myresult:
result_edit.append("C9高校在" + search_district + str(item[0]) +
"年" + "招生计划为" + str(item[1]) + "人")
# 查询该年某校招生的类别情况
sub_sql_string = "SELECT school,COUNT(*) FROM admission_plan WHERE district = '" \
+ search_district + "'and year = '" + str(item[0]) + "' GROUP BY school;"
# print(sub_sql_string)
sub_myresult = mysql_query_sentence(sub_sql_string)
if len(sub_myresult) == 0:
result_edit.append("学校具体招生人数查询结果为空！")
else:
for item in sub_myresult:
result_edit.append("其中" + str(item[0]) + "招生" +
str(item[1]) + "人")
# 只有年份和学校
elif search_year != "" and search_school != "" and search_major == "" and search_district == "":
# 查询学校该年招生总人数
sql_string = "SELECT COUNT(*) FROM admission_plan WHERE year = '" + search_year \
+ "' and school = '" + search_school + "' GROUP BY school;"
myresult = mysql_query_sentence(sql_string)
if len(myresult) == 0:
result_edit.append("查询结果为空！")
else:
result_edit.append(search_school + search_year + "年" + "招生计划为" +
str(myresult[0][0]) + "人")
sub_sql_string = "SELECT classy,COUNT(*) FROM admission_plan WHERE year = '" + search_year \
+ "' and school = '" + search_school + "' GROUP BY classy;"
# print(sub_sql_string)
sub_myresult = mysql_query_sentence(sub_sql_string)
if len(sub_myresult) == 0:
result_edit.append("学校具体招生人数查询结果为空！")
else:
for item in sub_myresult:
result_edit.append("其中" + str(item[0]) + str(item[1]) +
"人")
# 只有年份和专业
elif search_year != "" and search_school == "" and search_major != "" and search_district == "":
# 查询各学校该年该专业招生人数
sql_string = "SELECT school,COUNT(*) FROM admission_plan WHERE year = '" + search_year \
+ "' and major = '" + search_major + "' GROUP BY school;"
myresult = mysql_query_sentence(sql_string)
if len(myresult) == 0:
result_edit.append("查询结果为空！")
else:
for item in myresult:
result_edit.append(
str(item[0]) + search_year + "年" + search_major + "招生计划为" +
str(item[1]) + "人")
# 只有年份和地区
elif search_year != "" and search_school == "" and search_major == "" and search_district != "":
# 查询各学校该年该地区招生人数
sql_string = "SELECT school,COUNT(*) FROM admission_plan WHERE year = '" + search_year \
+ "' and district = '" + search_district + "' GROUP BY school;"
myresult = mysql_query_sentence(sql_string)
if len(myresult) == 0:
result_edit.append("查询结果为空！")
else:
for item in myresult:
result_edit.append(
str(item[0]) + search_year + "年" + "在" + search_district +
"招生计划为" + str(item[1]) + "人")
# 只有学校和专业
elif search_year == "" and search_school != "" and search_major != "" and search_district == "":
# 查询该学校各年该专业招生人数
sql_string = "SELECT year,COUNT(*) FROM admission_plan WHERE school = '" + search_school \
+ "' and major = '" + search_major + "' GROUP BY year;"
myresult = mysql_query_sentence(sql_string)
if len(myresult) == 0:
result_edit.append("查询结果为空！")
else:
for item in myresult:
result_edit.append(search_school + str(item[0]) + "年" +
search_major + "招生计划为" + str(item[1]) + "人")
# 只有学校和地区
elif search_year == "" and search_school != "" and search_major == "" and search_district != "":
# 查询该学校各年该地区招生人数
sql_string = "SELECT year,COUNT(*) FROM admission_plan WHERE school = '" + search_school \
+ "' and district = '" + search_district + "' GROUP BY year;"
myresult = mysql_query_sentence(sql_string)
if len(myresult) == 0:
result_edit.append("查询结果为空！")
else:
for item in myresult:
result_edit.append(search_school + str(item[0]) + "年" + "在" +
search_district + "招生计划为" + str(item[1]) +
"人")
# 只有专业和地区
elif search_year == "" and search_school == "" and search_major != "" and search_district != "":
# 查询各年该地区该专业招生人数
sql_string = "SELECT year,COUNT(*) FROM admission_plan WHERE major = '" + search_major \
+ "' and district = '" + search_district + "' GROUP BY year;"
myresult = mysql_query_sentence(sql_string)
if len(myresult) == 0:
result_edit.append("查询结果为空！")
else:
for item in myresult:
result_edit.append(
str(item[0]) + "年" + search_major + "在" + search_district +
"招生计划为" + str(item[1]) + "人")
# 查询该年各校的招生情况
sub_sql_string = "SELECT school,COUNT(*) FROM admission_plan WHERE major = '" + search_major \
+ "' and district = '" + search_district + "'and year = '" + str(item[0]) \
+ "' GROUP BY school;"
# print(sub_sql_string)
sub_myresult = mysql_query_sentence(sub_sql_string)
if len(sub_myresult) == 0:
result_edit.append("学校具体招生人数查询结果为空！")
else:
for item in sub_myresult:
result_edit.append("其中" + str(item[0]) + "招生" +
str(item[1]) + "人")
# 只有学校、专业、地区
elif search_year == "" and search_school != "" and search_major != "" and search_district != "":
# 查询该学校各年该专业该地区招生人数
sql_string = "SELECT year,COUNT(*) FROM admission_plan WHERE school = '" + search_school \
+ "' and major = '" + search_major + "' and district = '" + search_district \
+ "' GROUP BY year;"
myresult = mysql_query_sentence(sql_string)
if len(myresult) == 0:
result_edit.append("查询结果为空！")
else:
for item in myresult:
result_edit.append(search_school + str(item[0]) + "年" + "在" +
search_district + search_major + "招生计划为" +
str(item[1]) + "人")
# 只有年份、专业、地区
elif search_year != "" and search_school == "" and search_major != "" and search_district != "":
# 查询各学校该年该专业该地区招生人数
sql_string = "SELECT school,COUNT(*) FROM admission_plan WHERE year = '" + search_year \
+ "' and major = '" + search_major + "' and district = '" + search_district \
+ "' GROUP BY school;"
myresult = mysql_query_sentence(sql_string)
if len(myresult) == 0:
result_edit.append("查询结果为空！")
else:
for item in myresult:
result_edit.append(
str(item[0]) + search_year + "年" + "在" + search_district +
search_major + "招生计划为" + str(item[1]) + "人")
# 只有年份、学校、地区
elif search_year != "" and search_school != "" and search_major == "" and search_district != "":
# 查询该学校该年该地区招生人数
sql_string = "SELECT classy,COUNT(*) FROM admission_plan WHERE year = '" + search_year \
+ "' and school = '" + search_school + "' and district = '" + search_district \
+ "' GROUP BY classy;"
myresult = mysql_query_sentence(sql_string)
if len(myresult) == 0:
result_edit.append("查询结果为空！")
else:
for item in myresult:
result_edit.append(search_school + search_year + "年" + "在" +
search_district + "招生" + str(item[0]) +
"类" + str(item[1]) + "人")
# 只有年份、学校、专业
elif search_year != "" and search_school != "" and search_major != "" and search_district == "":
# 查询该学校该年该专业招生人数
sql_string = "SELECT district,COUNT(*) FROM admission_plan WHERE year = '" + search_year \
+ "' and school = '" + search_school + "' and major = '" + search_major \
+ "' GROUP BY district;"
myresult = mysql_query_sentence(sql_string)
if len(myresult) == 0:
result_edit.append("查询结果为空！")
else:
for item in myresult:
result_edit.append(search_school + search_year + "年" +
search_major + "在" + str(item[0]) + "招生" +
str(item[1]) + "人")
# 只有年份、学校、专业、地区
elif search_year != "" and search_school != "" and search_major != "" and search_district != "":
# 查询该学校该年该专业该地区招生情况
sql_string = "SELECT classy,COUNT(*) FROM admission_plan WHERE school = '" + search_school \
+ "' and year = '" + search_year + "' and major = '" + search_major \
+ "' and district = '" + search_district + "' GROUP BY classy;"
myresult = mysql_query_sentence(sql_string)
if len(myresult) == 0:
result_edit.append("查询结果为空！")
else:
for item in myresult:
result_edit.append(search_school + search_year + "年" +
search_major + "在" + search_district +
"招生" + str(item[0]) + "类" + str(item[1]) +
"人")
return result_edit