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...")