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])
