def validatecsv(file_path, unique_id): needed_column = ['電話', '教授姓名', 'email', '研究領域', '簡介', '教授id'] record_status = 1 validate_flag = True df1 = pd.read_csv(file_path, dtype=str) csv_column = df1.keys().tolist() all_include = set(needed_column).issubset(csv_column) if all_include == False: record_status = 0 err_col = str(set(needed_column) - set(csv_column)) message = "錯誤:名稱有誤:" + err_col validate_flag = False checkFile.recordLog(unique_id, record_status, message, mycursor, connection) return validate_flag df1['是否退休'] = 0 os.remove(file_path) df1.to_csv(file_path, index=False, encoding='utf-8') df2 = pd.DataFrame(columns=['教授id', '教授姓名']) df2['教授id'] = df1['教授id'] df2['教授姓名'] = df1['教授姓名'] output_path = os.getcwd() + "/temp_teacher.csv" df2.to_csv(output_path, index=False, encoding='utf-8') return output_path, validate_flag
def validateCSV(file_path, unique_id): needed_column = ['學號', '姓名', '班級', '性別', '入學身份', '在學身份', '在學狀況', 'eMail'] record_status = 1 validate_flag = True df = pd.read_csv(file_path, dtype={'學號': object}) csv_column = df.keys().tolist() all_include = set(needed_column).issubset(csv_column) if all_include == False: record_status = 0 error_column = str(set(needed_column) - set(csv_column)) message = "錯誤:名稱有誤 : " + error_column validate_flag = False checkFile.recordLog(unique_id, record_status, message, mycursor, connection) return validate_flag #check student length == 7 or 9 student_len = [7, 9] df['student_id_len'] = df['學號'].str.len() student_id_check = all(_ in student_len for _ in list(df['student_id_len'])) if student_id_check == False: error_count = len(df[~df['student_id_len'].isin(student_len)]) message = "錯誤:學號長度有誤 (可能為開頭缺少0) 共 : " + str(error_count) + "筆" validate_flag = False checkFile.recordLog(unique_id, record_status, message, mycursor, connection) return validate_flag return validate_flag
def validatecsv(file_path, unique_id): needed_column = ['學號', '身分', '考試年度', '考試年級', '英檢狀態代碼'] record_status = 1 validate_flag = True df = pd.read_csv(file_path) csv_column = df.keys().tolist() all_include = set(needed_column).issubset(csv_column) if all_include == False: record_status = 0 err_col = str(set(needed_column) - set(csv_column)) message = "錯誤:名稱有誤:" + err_col validate_flag = False checkFile.recordLog(unique_id, record_status, message, mycursor, connection) return validate_flag return validate_flag
def ValidateCSV(file_path, unique_id, mycursor, connection): needed_column = ['學號', '學期', '專題一或二'] record_status = 1 validate_flag = True df = pd.read_csv(file_path, encoding='utf-8') csv_column = df.keys().tolist() all_include = set(needed_column).issubset(csv_column) if all_include == False: record_status = 0 err_col = str(set(needed_column) - set(csv_column)) message = "錯誤:名稱有誤:" + err_col validate_flag = False checkFile.recordLog(unique_id, record_status, message, mycursor, connection) return validate_flag return validate_flag
def preValidateCSV(file_path, unique_id, mycursor, connection): #since score has NaN, so its type will be float64 needed_column = ['學號', '班別', '姓名', '學期', '當期課號', '永久課號', '課名',\ '學分數', '開課單位', '選別', '摘要', '評分方式', '成績', '備註', 'GP'] record_status = 1 validate_flag = True df = pd.read_csv(file_path, dtype={'學號': object, '當期課號': object}) csv_column = df.keys().tolist() all_include = set(needed_column).issubset(csv_column) if all_include == False: record_status = 0 error_column = str(set(needed_column) - set(csv_column)) message = "錯誤:名稱有誤 : " + error_column validate_flag = False checkFile.recordLog(unique_id, record_status, message, mycursor, connection) return validate_flag
def validateCSV(file_path, unique_id): needed_column = [ '學號', '申請年度', '申請學期', '修課年度', '修課學期', '修課當期課號', '修課課名', '欲抵免免修永久課號', '欲抵免免修課名', '學分數', '抵免或免修', '原課程向度', '學生選別' ] record_status = 1 validate_flag = True df = pd.read_csv(file_path, encoding='utf-8') csv_column = df.keys().tolist() all_include = set(needed_column).issubset(csv_column) if all_include == False: record_status = 0 err_col = str(set(needed_column) - set(csv_column)) message = "錯誤:名稱有誤:" + err_col validate_flag = False checkFile.recordLog(unique_id, record_status, message, mycursor, connection) return validate_flag return validate_flag
def validateCSV(file_path, unique_id, mycursor, connection): #since score has NaN, so its type will be float64 needed_column = [ '學號', '學期', '當期課號', '永久課號', '課名', '學分數', '開課單位', '選別', '評分方式', '評分狀態', '成績', 'GP' ] record_status = 1 validate_flag = True df = pd.read_csv(file_path, dtype={'學號': object, '當期課號': object}) csv_column = df.keys().tolist() all_include = set(needed_column).issubset(csv_column) if all_include == False: record_status = 0 error_column = str(set(needed_column) - set(csv_column)) message = "錯誤:名稱有誤 : " + error_column validate_flag = False checkFile.recordLog(unique_id, record_status, message, mycursor, connection) return validate_flag #check cos_id length == 4 cos_len = 4 df['cos_id_len'] = df['當期課號'].str.len() cos_id_check = list(df['cos_id_len'] != cos_len) if True in cos_id_check: record_status = 0 error_cos_id_count = len( df[df['cos_id_len'] != cos_len]['當期課號'].values) message = "錯誤:當期課號長度有誤 (可能為開頭缺少0) 共 : " + str(error_cos_id_count) + "筆" validate_flag = False checkFile.recordLog(unique_id, record_status, message, mycursor, connection) return validate_flag #check student length == 7 or 9 student_len = [7, 9] df['student_id_len'] = df['學號'].str.len() student_id_check = all(_ in student_len for _ in list(df['student_id_len'])) if student_id_check == False: error_count = len(df[~df['student_id_len'].isin(student_len)]) message = "錯誤:學號長度有誤 (可能為開頭缺少0) 共 : " + str(error_count) + "筆" validate_flag = False checkFile.recordLog(unique_id, record_status, message, mycursor, connection) return validate_flag return validate_flag
validate_flag = preValidateCSV(file_path, unique_id, mycursor, connection) if validate_flag: # Parse csv to fit format convertData(file_path, csv_path, year, semester) #Check csv file validate_flag = validateCSV(csv_path, unique_id, mycursor, connection) if validate_flag == True: #Delete last semester's on cos data record_status, code, message, affect_count = deleteLastCourse( mycursor, connection) if record_status == 0: message = "刪除上學期課程錯誤:" + message checkFile.recordLog(unique_id, record_status, message, mycursor, connection) elif record_status == 1: #Import this semester's on cos data record_status, code, message, affect_count = insertDB( csv_path, mycursor, connection) if record_status == 0: message = "匯入當期課程錯誤:" + message checkFile.recordLog(unique_id, record_status, message, mycursor, connection) if record_status == 1: message = "已匯入本學期當期課程共 " + str(affect_count) + ' 筆' checkFile.recordLog(unique_id, record_status, message, mycursor, connection) mycursor.close() ## here all loops done connection.close() ## close db connection
def convert_coscode(file_path, mycursor, connection, unique_id): df = pd.read_csv(file_path, encoding='utf-8', dtype={ '學號': object, '修課年度': object, '修課學期': object, '修課當期課號': object }) # insert "修課永久課號" col_name = df.columns.tolist() col_name.insert(6, "修課永久課號") df = df.reindex(columns=col_name) year = df['修課年度'].tolist() semester = df['修課學期'].tolist() code = df['修課當期課號'].tolist() offset_type = df['抵免或免修'].tolist() cos_code_old = df['修課永久課號'].tolist() for i in range(len(year)): if (offset_type[i] == '抵免'): continue elif (offset_type[i] == '免修'): year[i] = str(year[i]).replace('.0', '') # 以防裡面有轉換失敗產生的小數 item = str(year[i]) + '-' + str(semester[i]) + '-' + str(code[i]) mycursor.execute( "select cos_code from cos_name where unique_id = '%s';" % item) tmp = mycursor.fetchall() if tmp != (): cos_code_old[i] = tmp[0][0] else: msg = item + " 沒有找到修課永久課號" print(msg) df['修課永久課號'] = cos_code_old df['修課年度'] = year try: df['欲抵免免修課名'] = df['欲抵免免修課名'].astype(str).str.replace( '院基本素養', '外院基本能力') df['欲抵免免修課名'] = df['欲抵免免修課名'].astype(str).str.replace('校基本素養', '校基本能力') df['原課程向度'] = df['原課程向度'].astype(str).str.replace('跨院基本素養', '外院基本能力') df['原課程向度'] = df['原課程向度'].astype(str).str.replace('校基本素養', '校基本能力') df['原課程向度'] = df['原課程向度'].astype(str).str.replace('nan', '') col = [ '學號', '申請年度', '申請學期', '修課年度', '修課學期', '修課當期課號', '修課永久課號', '修課課名', '欲抵免免修永久課號', '欲抵免免修課名', '學分數', '抵免或免修', '原課程向度', '學生選別' ] df = df[col] except Exception as e: record_status = 0 msg = '錯誤:欄位資料可能有錯,' msg += str(e) checkFile.recordLog(unique_id, record_status, msg, mycursor, connection) exit(0) output_path = os.getcwd() + "/temp_offset.csv" df.to_csv(output_path, index=False, encoding='utf-8') return output_path
def convertData(file_path, output_path, selected_year, selected_semester, unique_id, mycursor, connection): validate_flag = True record_status = 1 df = pd.read_csv(file_path, dtype={'學號': object, '當期課號': object}) concat_year_semester = str(selected_year) + str(selected_semester) df = df[df['學期'] != int(concat_year_semester)].reset_index(drop=True) df_parse = df.copy() df_parse = df_parse.drop(columns=['班別', '姓名', '摘要', '備註']) # Processing to fit table columns year = [] semester = [] brief = [] pass_fail = [] score_level = [] for i in range(len(df)): year.append(df['學期'][i] // 10) semester.append(df['學期'][i] % 10) brief.append(df['摘要'][i]) # Parse score level by score if df['成績'][i].isnumeric(): if int(df['成績'][i]) >= 90 and int(df['成績'][i]) <= 100: score_level.append('A+') elif int(df['成績'][i]) >= 85 and int(df['成績'][i]) <= 89: score_level.append('A') elif int(df['成績'][i]) >= 80 and int(df['成績'][i]) <= 84: score_level.append('A-') elif int(df['成績'][i]) >= 77 and int(df['成績'][i]) <= 79: score_level.append('B+') elif int(df['成績'][i]) >= 73 and int(df['成績'][i]) <= 76: score_level.append('B') elif int(df['成績'][i]) >= 70 and int(df['成績'][i]) <= 72: score_level.append('B-') elif int(df['成績'][i]) >= 67 and int(df['成績'][i]) <= 69: score_level.append('C+') elif int(df['成績'][i]) >= 63 and int(df['成績'][i]) <= 66: score_level.append('C') elif int(df['成績'][i]) >= 60 and int(df['成績'][i]) <= 62: score_level.append('C-') elif int(df['成績'][i]) >= 50 and int(df['成績'][i]) <= 59: score_level.append('D') elif int(df['成績'][i]) >= 1 and int(df['成績'][i]) <= 49: score_level.append('E') elif int(df['成績'][i]) == 0: score_level.append('X') else: message = "錯誤:成績為數字但不在0~100分,第 : " + str(i + 1) + "筆" validate_flag = False record_status = 0 checkFile.recordLog(unique_id, record_status, message, mycursor, connection) return validate_flag else: if df['評分方式'][i] == '通過不通過' or df['評分方式'][i] == '等級' or ( df['評分方式'][i] == '百分'): score_level.append(None) else: message = "錯誤:成績不為數字且評分方式不為通過不通過、等級或百分,第 : " + str(i + 1) + "筆" validate_flag = False record_status = 0 checkFile.recordLog(unique_id, record_status, message, mycursor, connection) return validate_flag # Parse pass fail by score if df['成績'][i].isnumeric(): if int(df['成績'][i]) >= 60: pass_fail.append('通過') else: pass_fail.append('不通過') elif df['成績'][i] == 'A+' or df['成績'][i] == 'A' or df['成績'][i] == 'A-' or \ df['成績'][i] == 'B+' or df['成績'][i] == 'B' or df['成績'][i] == 'B-' or \ df['成績'][i] == 'C+' or df['成績'][i] == 'C' or df['成績'][i] == 'C-': pass_fail.append('通過') df_parse['成績'][i] = None elif df['成績'][i] == 'D' or df['成績'][i] == 'E' or df['成績'][i] == 'X': pass_fail.append('不通過') df_parse['成績'][i] = None elif df['成績'][i] == 'P': pass_fail.append('通過') df_parse['成績'][i] = None elif df['成績'][i] == 'F': pass_fail.append('不通過') df_parse['成績'][i] = None elif df['成績'][i] == 'W': pass_fail.append('W') df_parse['成績'][i] = None elif df['成績'][i] == 'Y': pass_fail.append('Y') else: message = "錯誤:成績不為數字且不為訂定標準(A~F、W、Y等),第 : " + str(i + 1) + "筆" validate_flag = False record_status = 0 checkFile.recordLog(unique_id, record_status, message, mycursor, connection) return validate_flag df_parse['學年度'] = year df_parse['學期'] = semester df_parse['課程向度'] = brief df_parse['評分狀態'] = pass_fail df_parse['等級成績'] = score_level # Swap columns columns_order = [ '學號', '學年度', '學期', '當期課號', '開課單位', '課名', '永久課號', '課程向度', '選別', '學分數', '評分方式', '評分狀態', '成績', '等級成績', 'GP' ] cols = list(df.columns) df_parse = df_parse[columns_order] df_parse.to_csv(output_path, index=False, encoding='utf-8') return validate_flag