def addr_validate_and_verify(addr_type, sql_sentence, area_dict): cursor = employ_conn.cursor() cursor.execute(sql_sentence) results = cursor.fetchall() output_file = '../log' + addr_type with open(output_file, "a") as w: for res in results: student_code = res[0] addr = res[1] addr_code = res[2] if addr is None or addr == '': print(student_code + '--未填写') elif addr in area_dict and addr_code == area_dict[addr]: print(student_code + '--校验通过!') elif addr + '省' in area_dict and addr_code == area_dict[addr + '省']: print(student_code + '--校验通过!') addr_update(addr_type, student_code, addr + '省', area_dict[addr + '省']) w.write(student_code + '--修改成功!\n') elif addr in area_dict and addr_code != area_dict[addr]: print(student_code + '--代码不正确') # TODO: 更新 addr_update(addr_type, student_code, addr, area_dict[addr]) w.write(student_code + '--修改成功!\n') elif addr + '省' in area_dict and addr_code != area_dict[addr + '省']: # TODO: 更新 print(student_code + '--代码不正确') addr_update(addr_type, student_code, addr + '省', area_dict[addr + '省']) w.write(student_code + '--修改成功!\n')
def get_student_list_by_category(student_category): cursor = employ_conn.cursor() select_sql = '''select id from student where category = %s''' cursor.execute(select_sql, (student_category, )) results = cursor.fetchall() student_list = [] for res in results: student_list.append(res[0]) return student_list
def get_special_job_dict(): cursor = employ_conn.cursor() select_sql = '''select * from basic where type = 'specialEmployType' and status = 1''' cursor.execute(select_sql) results = cursor.fetchall() special_job_dict = {} for res in results: code = res[1] name = res[2] special_job_dict.update({name: code}) # print(special_job_dict) return special_job_dict
def get_industry_dict(): cursor = employ_conn.cursor() select_sql = '''select * from basic where type = 'industry' and status = 1''' cursor.execute(select_sql) results = cursor.fetchall() industry_dict = {} for res in results: code = res[1] name = res[2] industry_dict.update({name: code}) # print(industry_dict) return industry_dict
def get_property_dict(): cursor = employ_conn.cursor() select_sql = '''select * from basic where type = 'property' and status = 1''' cursor.execute(select_sql) results = cursor.fetchall() property_dict = {} for res in results: code = res[1] name = res[2] property_dict.update({name: code}) print(property_dict) return property_dict
def get_ethnicity_dict(): cursor = employ_conn.cursor() select_sql = '''select * from basic where type = 'ethnicity' and status = 1''' cursor.execute(select_sql) results = cursor.fetchall() ethnicity_dict = {} for res in results: code = res[1] name = res[2] ethnicity_dict.update({name: code}) return ethnicity_dict
def text_export_func(paper_id): cursor = employ_conn.cursor() questions_query = '''select * from question where paper_id = %s and question_type = 4''' answers_query = '''select * from anwser where question_id = %s and student_id in %s''' cursor.execute(questions_query, paper_id) questions = cursor.fetchall() index = 1 for ques in questions: question_id = ques[0] question_title = ques[4] output_file = 'D:\\jianda\\' + str(index) + '.txt' with open(output_file, "a") as w: student_list_7 = get_student_list_by_category('7') student_list_3 = get_student_list_by_category('3') student_list_2 = get_student_list_by_category('2') student_list_5 = get_student_list_by_category('5') cursor.execute(answers_query, (question_id, student_list_7)) answers_7 = cursor.fetchall() cursor.execute(answers_query, (question_id, student_list_3)) answers_3 = cursor.fetchall() cursor.execute(answers_query, (question_id, student_list_2)) answers_2 = cursor.fetchall() cursor.execute(answers_query, (question_id, student_list_5)) answers_5 = cursor.fetchall() print("===============" + question_title + "===============") w.write( "\n====================================================================\n" ) w.write("题目:" + question_title + "\n") w.write("\n-----------------------专硕答案-------------------------\n") for a in answers_7: w.write(a[5] + '\n') w.write("\n-----------------------学硕答案-------------------------\n") for a in answers_3: w.write(a[5] + '\n') w.write("\n-----------------------博士答案-------------------------\n") for a in answers_2: w.write(a[5] + '\n') w.write("\n-----------------------非全答案-------------------------\n") for a in answers_5: w.write(a[5] + '\n') index = index + 1
def addr_update(addr_type, student_code, addr, addr_code): cursor = employ_conn.cursor() if addr_type == 'enrollment_addr': update_sql = '''update student set enrollment_addr = %s, enrollment_code = %s where username = %s''' cursor.execute(update_sql, (addr, addr_code, student_code)) employ_conn.commit() # '''select account, implement_comp_addr, implement_comp_code from record''' elif addr_type == 'implement_comp_addr': update_sql = '''update record set implement_comp_code = %s where account = %s and implement_comp_addr = %s''' cursor.execute(update_sql, (addr_code, student_code, addr)) employ_conn.commit() # company_addr, company_addr_code elif addr_type == 'company_addr': update_sql = '''update employ set company_addr_code = %s where account = %s and company_addr = %s''' cursor.execute(update_sql, (addr_code, student_code, addr)) employ_conn.commit()
def special_job_validate_and_verify(special_job_dict): select_sql = '''select account, special_job, special_job_code from employ''' update_sql = '''update employ set special_job_code = %s where account = %s and special_job = %s''' cursor = employ_conn.cursor() cursor.execute(select_sql) results = cursor.fetchall() with open("../log/special_job", "a") as w: for res in results: account = res[0] special_job = res[1] special_job_code = res[2] if special_job is not None and special_job != '': if special_job in special_job_dict and str(special_job_code) == special_job_dict[special_job]: print(account + '--校验通过!') elif special_job in special_job_dict and str(special_job_code) != special_job_dict[special_job]: print(account + '--代码不匹配!') cursor.execute(update_sql, (special_job_dict[special_job], account, special_job)) employ_conn.commit() w.write(account + '修改成功!\n')
def get_area_code_dict(): print('开始获取地区原始数据') cursor = employ_conn.cursor() select_province_sql = '''select * from area where type = 1''' cursor.execute(select_province_sql) province_results = cursor.fetchall() area_dict = {} for province in province_results: province_code = province[1] province_name = province[2] area_dict.update({province_name: province_code}) select_city_sql = '''select * from area where type = 2 and citycode = %s''' cursor.execute(select_city_sql, (province_code,)) city_results = cursor.fetchall() for city in city_results: city_code = city[1] city_name = city[2] area_dict.update({province_name + city_name: city_code}) print('加载地区原始数据完毕') return area_dict
def ethnicity_validate_and_verify(ethnicity_dict): select_sql = '''select username, ethnicity, ethnicity_code from student''' update_sql = '''update student set ethnicity_code = %s where username = %s and ethnicity = %s''' cursor = employ_conn.cursor() cursor.execute(select_sql) results = cursor.fetchall() # output_file = r"G:\ethnicity.txt" with open("../log/ethnicity", "a") as w: for res in results: username = res[0] ethnicity = res[1] ethnicity_code = res[2] if ethnicity is not None and ethnicity != '': if ethnicity in ethnicity_dict and str(ethnicity_code) == ethnicity_dict[ethnicity]: print(username + '--校验通过!') w.write(username + '--校验通过!\n') elif ethnicity in ethnicity_dict and str(ethnicity_code) != ethnicity_dict[ethnicity]: print(username + '--代码不匹配!') cursor.execute(update_sql, (ethnicity_dict[ethnicity], username, ethnicity)) employ_conn.commit() w.write(username + '修改成功!\n')
def industry_validate_and_verify(industry_dict): select_sql = '''select account, industry, industry_code from employ''' update_sql = '''update employ set industry_code = %s where account = %s and industry = %s''' cursor = employ_conn.cursor() cursor.execute(select_sql) results = cursor.fetchall() with open("../log/industry", "a") as w: for res in results: account = res[0] industry = res[1] industry_code = res[2] if industry is not None and industry != '': if industry in industry_dict and str( industry_code) == industry_dict[industry]: print(account + '--校验通过!') elif industry in industry_dict and str( industry_code) != industry_dict[industry]: print(account + '--代码不匹配!') cursor.execute( update_sql, (industry_dict[industry], account, industry)) employ_conn.commit() w.write(account + '修改成功!\n')
def property_validate_and_verify(property_dict): select_sql = '''select account, property, property_code from employ''' update_sql = '''update employ set property_code = %s where account = %s and property = %s''' cursor = employ_conn.cursor() cursor.execute(select_sql) results = cursor.fetchall() with open("../log/property", "a") as w: for res in results: account = res[0] property = res[1] property_code = res[2] if property is not None and property != '': if property in property_dict and str( property_code) == property_dict[property]: print(account + '--校验通过!') elif property in property_dict and str( property_code) != property_dict[property]: print(account + '--代码不匹配!') cursor.execute( update_sql, (property_dict[property], account, property)) employ_conn.commit() w.write(account + '修改成功!\n')
def political_validate_and_verify(political_dict): select_sql = '''select username, political, political_code from student''' update_sql = '''update student set political_code = %s where username = %s and political = %s''' cursor = employ_conn.cursor() cursor.execute(select_sql) results = cursor.fetchall() with open("../log/political", "a") as w: for res in results: username = res[0] political = res[1] political_code = res[2] if political is not None and political != '': if political in political_dict and str( political_code) == political_dict[political]: print(username + '--校验通过!') elif political in political_dict and str( political_code) != political_dict[political]: print(username + '--代码不匹配!') cursor.execute( update_sql, (political_dict[political], username, political)) employ_conn.commit() w.write(username + '修改成功!\n')
#!/usr/bin/env python # coding=utf-8 from data_resource import employ_conn if __name__ == '__main__': cursor = employ_conn.cursor() select_sql = '''select * from student where username = %s''' cursor.execute(select_sql, ('7180278', )) res = cursor.fetchall() print(res[0])