def alter_test(test, db_name, yh_db_name, project_id): """ 更改test 表中的position level_id level_status, user_book_id 字段值 :param test: :param db_name: :param yh_db_name: :param project_id: :return: """ level_id = test.get("level_id") + 1 if test.get("level_status") == 1 else test.get("level_id") test_level = test.get("level_id") test_position = test.get("position") position = (test.get("position") + 1) if test.get("position") > 5 else test.get("position") if test_level == 2 and test_position == 3: position = 2 elif test_level == 3 and test_position in (3, 8): position = 2 elif test_level >= 4: # position = 2 if test_position in (3, 4) else 8 if test_position in (8, 9) else test_position position = 2 if test_position in (3, 4, 8, 9) else test_position # 所有带批改的流程从错题订正重新走 if test.get("level_status") == 1 and test.get("position") == 1: position = 0 clear_time = test.get("update_time") if (test.get("c_type") == 3 and test.get("level_id") > 3) or ( test.get("c_type") == 1 and test.get("level_id") > 4) else 0 # 查询用户使用的教材对应的用户教材ID(user_book_id) yh_conn = lorm_pool_yh(yh_db_name) # 指定要连接的数据库(YH 库) result = yh_conn.default.yh_user_book.filter(user_id=test.get("user_id"), book_id=test.get("book_id"), project_id=project_id, status__in=[0, 1, 2]).select("id")[:] user_book_id = result[-1].get("id") if len(result) > 0 else 0 lore_conn = lorm_pool_57(db_name) # 指定要连接的数据库(逻辑库) lore_conn.default.test.filter(id=test.get("id")).update(level_status=0, level_id=level_id, position=position, clear_time=clear_time, user_book_id=user_book_id)
def alter_method(method, db_name): """ method_study 表中 q_json_data 添加字段result -1 不用温习 0 未温习 1 已温习 :param method: :return: """ q_json_data = method.get("q_json_data") q_json_data = json.loads(q_json_data) for one in q_json_data: one["result"] = 1 if one.get("status") == 2 else -1 q_json_data = json.dumps(q_json_data) lore_conn = lorm_pool_57(db_name) # 指定要连接的数据库(逻辑库) lore_conn.default.method_study.filter(id=method.get("id")).update(q_json_data=q_json_data)
def __get_test_id(user_id, catalog_id, book_id, db_name): """ 获取user_book_id :param user_id: :param catalog_id: :param book_id: :param yh_db_name: :return: """ # 查询用户使用的教材对应的用户教材ID(user_book_id) yh_conn = lorm_pool_57(db_name) # 指定要连接的数据库(YH 库) result = yh_conn.default.test.filter(user_id=user_id, book_id=book_id, catalog_id=catalog_id).select("id")[:] test_id = result[-1].get("id") if len(result) > 0 else 0 return test_id
def copy_date(conf): """ SLH 项目数据迁移 :return: """ import time print("start...") start = int(time.time()) db_name = conf.get("db_name") yh_db_name = conf.get("yh_db_name") project_id = conf.get("project_id") # 2 更改test 表中的position level_id level_status , user_book_id字段值 lore_conn = lorm_pool_57(db_name) # 指定要连接的数据库 result = lore_conn.default.test.filter(id__gt=0).select('id', 'user_id', 'book_id', 'level_id', 'level_status', 'c_type', 'position', 'update_time')[:] print("开始修改主测试表字段...") for test in result: alter_test(test, db_name, yh_db_name, project_id) # 3 第二关添加字段 knowledge_dictation 表中的json_data 字段值加 值对像( 合并数据) print("开始修改第二关知识默写表字段...") t_result = lore_conn.default.knowledge_dictation.filter(id__gt=0).select("id", "test_id", "test_num", "json_data") alter_knowledge(t_result, db_name) # 4 第三关q_json_data 添加字段result -1 不用温习 0 未温习 1 已温习 m_result = lore_conn.default.method_study.filter(id__gt=0).select("id", "q_json_data") print("开始修改第三关方法学习表字段...") for method in m_result: alter_method(method, db_name) # 5 四五六关 添加题号记录 q_result = lore_conn.default.apply_test.filter(id__gt=0).select("id", "json_data") print("开始修改四五六关 的测试提交记录 字段...") for apply in q_result: alter_apply(apply, db_name) # 6 update_user_book_id 更新添加user_book_id 字段 print("*******为以下表更新真实的user_book_id值*******") update_user_book_id(db_name, yh_db_name, project_id) totTime = time.time() - start print("数据迁移完成, 共用时:", totTime)
def alter_knowledge(knowledge, db_name): """ knowledge_dictation 表中的json_data 字段值加 值对像 :param knowledge: :return: """ test_ids = [one.get("test_id") for one in knowledge] test_ids = sorted(set(test_ids), key=test_ids.index) konwledge_dict = dict() for t_id in test_ids: json_list = list() for k in knowledge: if k.get("test_id") == t_id: temp_dict = dict(test_num=k.get("test_num"), json_data=k.get("json_data")) json_list.append(temp_dict) konwledge_dict[t_id] = json_list lore_conn = lorm_pool_57(db_name) # 指定要连接的数据库(逻辑库) lore_conn.default.knowledge_dictation.filter(test_num__gt=1).delete() # 查询出数据之后删除无用的数据 # 生成一组新的数据 new_list = list() for key, value in konwledge_dict.items(): new_dict = Struct() new_dict.test_id = key test_num_list = [obj.get("test_num") for obj in value] max_t_num = max(test_num_list) f_json = next((one.get("json_data") for one in value if one.get("test_num") == 1), None) f_json = json.loads(f_json) if max_t_num == 1 and not f_json: f_json = f_json else: for one in value: json_data = json.loads(one.get("json_data")) for k_json in json_data: k_dict = next(k for k in f_json if k.get("k_id") == k_json.get("k_id")) k_dict["result"] = k_json.get("result") k_dict["status"] = k_json.get("status") k_dict["dictation_num"] = one.get("test_num") new_dict.f_json = f_json new_list.append(new_dict) f_json = json.dumps(f_json) lore_conn.default.knowledge_dictation.filter(test_id=key, test_num=1).update(json_data=f_json, test_num=max_t_num)
def alter_apply(apply, db_name): """ apply_test 表中 json_data 添加字段q_no, ask_no :param method: :return: """ q_json_data = apply.get("json_data") q_json_data = json.loads(q_json_data) # 将试题提交记录到表里 apply_q_ids = [int(q.get("q_id")) for q in q_json_data] q_ids = sorted(set(apply_q_ids), key=apply_q_ids.index) # 大题id 去重 for i, one_q in enumerate(q_ids, start=1): ask_no = 1 for one_json in q_json_data: if one_json.get("q_id") == one_q: one_json["q_no"] = i one_json["ask_no"] = ask_no ask_no += 1 if one_json["type"] == 1 and one_json["result"] == 2: one_json["result"] = 0 # 待批改的状态改为未订正 q_json_data = json.dumps(q_json_data) lore_conn = lorm_pool_57(db_name) # 指定要连接的数据库(逻辑库) lore_conn.default.apply_test.filter(id=apply.get("id")).update(json_data=q_json_data)
def update_user_book_id(db_name, yh_db_name, project_id): """ 更新表中的user_book_id 字段 :param db_name: :param yh_db_name: :param project_id: :return: """ key = lambda d: (d["user_id"], d["book_id"]) lore_conn = lorm_pool_57(db_name) # 指定要连接的数据库 print("正在更新analysis 表的user_book_id 字段....") result1 = lore_conn.default.analysis.filter(id__gt=0).select('user_id', 'book_id')[:] result1 = list(dedupe(result1, key)) for obj in result1: user_book_id = __get_user_book_id(obj.get("user_id"), obj.get("book_id"), project_id, yh_db_name) lore_conn.default.analysis.filter(user_id=obj.get("user_id"), book_id=obj.get("book_id")).update( user_book_id=user_book_id) print("正在更新attendance_detail 表的user_book_id 字段....") result2 = lore_conn.default.attendance_detail.filter(id__gt=0).select('user_id', 'book_id')[:] result2 = list(dedupe(result2, key)) for obj in result2: user_book_id = __get_user_book_id(obj.get("user_id"), obj.get("book_id"), project_id, yh_db_name) lore_conn.default.attendance_detail.filter(user_id=obj.get("user_id"), book_id=obj.get("book_id")).update( user_book_id=user_book_id) print("正在更新personalise 表的user_book_id 字段....") result3 = lore_conn.default.personalise.filter(id__gt=0).select('user_id', 'book_id')[:] result3 = list(dedupe(result3, key)) for obj in result3: user_book_id = __get_user_book_id(obj.get("user_id"), obj.get("book_id"), project_id, yh_db_name) lore_conn.default.personalise.filter(user_id=obj.get("user_id"), book_id=obj.get("book_id")).update( user_book_id=user_book_id) print("正在更新mystic 表的user_book_id 字段....") result4 = lore_conn.default.mystic.filter(id__gt=0).select('user_id', 'book_id')[:] result4 = list(dedupe(result4, key)) for obj in result4: user_book_id = __get_user_book_id(obj.get("user_id"), obj.get("book_id"), project_id, yh_db_name) lore_conn.default.mystic.filter(user_id=obj.get("user_id"), book_id=obj.get("book_id")).update( user_book_id=user_book_id) print("正在更新user_jump_catalog 表的user_book_id 字段....") result5 = lore_conn.default.user_jump_catalog.filter(id__gt=0).select('user_id', 'book_id')[:] result5 = list(dedupe(result5, key)) for obj in result5: user_book_id = __get_user_book_id(obj.get("user_id"), obj.get("book_id"), project_id, yh_db_name) lore_conn.default.user_jump_catalog.filter(user_id=obj.get("user_id"), book_id=obj.get("book_id")).update( user_book_id=user_book_id) print("正在更新weak 表的user_book_id 字段....") result6 = lore_conn.default.weak.filter(id__gt=0).select('id', 'user_id', 'book_id', 'catalog_id')[:] for obj in result6: user_book_id = __get_user_book_id(obj.get("user_id"), obj.get("book_id"), project_id, yh_db_name) test_id = __get_test_id(obj.get("user_id"), obj.get("catalog_id"), obj.get("book_id"), db_name) lore_conn.default.weak.filter(id=obj.get("id")).update(user_book_id=user_book_id, test_id=test_id) print("删除weak 表中的book_id 字段") db_57.exec_sql(conf.get("db_name"), "alter table weak drop column book_id;") print("done...")