def insert_separate(num, clean=True, average_iteration_num=1):
sum_time = 0.0
for i in range(0, average_iteration_num):
old_session = EngineFactory.create_session_to_databackup_so(echo=False)
test_session = EngineFactory.create_session_to_test_so(echo=False)
old_post_list = old_session.query(PostsRecord).limit(num)
starttime = datetime.datetime.now()
for post in old_post_list:
test_session.add(post.make_copy())
## 每插入一条就commit写入数据库
test_session.commit()
endtime = datetime.datetime.now()
time = (endtime - starttime).total_seconds()
print("test_insert_separate num={num} time={time}".format(num=num,
time=time))
if clean:
PostsRecord.delete_all(test_session)
sum_time = sum_time + time
return {
"type": "insert separate",
"num": num,
"time": sum_time / average_iteration_num
}
def insert_batch(num, clean=True, average_iteration_num=1):
sum_time = 0.0
for i in range(0, average_iteration_num):
## 接下来的三行代码从旧的总表中获取想要的数目的数据，作为之后插入的数据源，其实也可以读文件得到，但是那就太麻烦了
old_session = EngineFactory.create_session_to_databackup_so(echo=False)
test_session = EngineFactory.create_session_to_test_so(echo=False)
old_post_list = old_session.query(PostsRecord).limit(num)
starttime = datetime.datetime.now()
for post in old_post_list:
test_session.add(post.make_copy())
## 全部写入缓存再一次性commit写入数据库
test_session.commit()
endtime = datetime.datetime.now()
time = (endtime - starttime).total_seconds()
print("test_insert_batch num={num} time={time}".format(num=num,
time=time))
if clean:
PostsRecord.delete_all(test_session)
sum_time = sum_time + time
return {
"type": "insert batch",
"num": num,
"time": sum_time / average_iteration_num
}
def search_aggregate(num, average_iteration_num=1):
sum_time = 0.0
for i in range(0, average_iteration_num):
starttime = datetime.datetime.now()
session = EngineFactory.create_session_to_test_so(echo=False)
res = session.query(
func.sum(PostsRecord.favorite_count), UsersRecord.display_name,
UsersRecord.reputation).filter(
UsersRecord.id < num,
PostsRecord.owner_user_id == UsersRecord.id).all()
if len(res) > 0:
print("search_aggregate_result:", len(res), ":", res[0])
else:
print("search_aggregate_result: null")
endtime = datetime.datetime.now()
time = (endtime - starttime).total_seconds()
print("test_search_aggregate num={num} time={time}".format(num=num,
time=time))
sum_time = sum_time + time
return {
"type": "search_aggregate",
"num": num,
"time": sum_time / average_iteration_num
}
def search_one_table_mul_filter(num, average_iteration_num=1):
sum_time = 0.0
for i in range(0, average_iteration_num):
starttime = datetime.datetime.now()
session = EngineFactory.create_session_to_test_so(echo=False)
res = session.query(PostsRecord).filter(
PostsRecord.owner_user_id < num,
PostsRecord.view_count > 1000).all()
if len(res) > 0:
print("search_one_table_mul_filter_result:", len(res), ":", res[0])
else:
print("search_one_table_mul_filter_result: null")
endtime = datetime.datetime.now()
time = (endtime - starttime).total_seconds()
print("test_search_one_table_mul_filter num={num} time={time}".format(
num=num, time=time))
sum_time = sum_time + time
return {
"type": "search_one_table_mul_filter",
"num": num,
"time": sum_time / average_iteration_num
}
def delete_post_test_data_in_test_db():
"""
删除测试服务器得帖子表数据
:param num: 要取得测试数据得数目
:return:
"""
test_session = EngineFactory.create_session_to_test_so(echo=False)
PostsRecord.delete_all(test_session)
def create_post_test_data_in_test_db(num):
"""
利用全表服务器在测试服务器上创建新的数据,这个创建得是用户表得测试数据
:param num: 要取得测试数据得数目
:return:
"""
## 接下来的三行代码从旧的总表中获取想要的数目的数据，作为之后插入的数据源，其实也可以读文件得到，但是那就太麻烦了
old_session = EngineFactory.create_session_to_databackup_so(echo=False)
new_session = EngineFactory.create_session_to_test_so(echo=False)
old_post_list = old_session.query(PostsRecord).limit(num)
count = 0
for post in old_post_list:
new_session.add(post.make_copy())
count = count + 1
if count >= 100000:
new_session.commit()
count = 0
new_session.commit()