哈工大计科数据库实验四

# coding=utf8 from Memory import Memory path_select1 = 'result/select1.txt' path_select2 = 'result/select2.txt' path_project = 'result/project.txt' path_join1 = 'result/join1.txt' path_join2 = 'result/join2.txt' path_join3 = 'result/join3.txt' path_bing = 'result/bing.txt' path_jiao = 'result/jiao.txt' path_cha = 'result/cha.txt' ##线性查找 def linear_search(): output = open(path_select1, 'w') output.write('R: \n') for i in range(100): mr.load(0, i) for t in mr.buffer[0]: if t[0] == 40: output.write(str(t[0]) + ' ' + str(t[1]) + '\n') output.write('S: \n') for i in range(200): ms.load(0, i) for t in ms.buffer[0]: if t[0] == 60: output.write(str(t[0]) + ' ' + str(t[1]) + '\n') output.close() ##二分查找 def binary_search(): m = [] output = open(path_select2, 'w') for i in range(100): mr.load(0, i) m.extend(mr.buffer[0]) sort_r = sorted(m, key=lambda t: t[0]) low = 0 high = len(sort_r) - 1 output.write('R: \n') while low <= high: mid = (low + high) / 2 if sort_r[mid][0] == 40: output.write(str(sort_r[mid][0]) + ' ' + str(sort_r[mid][1]) + '\n') tail = mid + 1 while tail < len(sort_r) and sort_r[tail][0] == 40: output.write(str(sort_r[tail][0]) + ' ' + str(sort_r[tail][1]) + '\n') tail += 1 head = mid - 1 while head > 0 and sort_r[head][0] == 40: output.write(str(sort_r[head][0]) + ' ' + str(sort_r[head][1]) + '\n') head -= 1 break elif sort_r[mid][0] < 40: low = mid + 1 else: high = mid - 1 m = [] for i in range(200): ms.load(0, i) m.extend(ms.buffer[0]) sort_s = sorted(m, key=lambda t: t[0]) low = 0 high = len(sort_s) - 1 output.write('S: \n') while low <= high: mid = (low + high) / 2 if sort_s[mid][0] == 60: output.write(str(sort_s[mid][0]) + ' ' + str(sort_s[mid][1]) + '\n') tail = mid + 1 while tail < len(sort_s) and sort_s[tail][0] == 60: output.write(str(sort_s[tail][0]) + ' ' + str(sort_s[tail][1]) + '\n') tail += 1 head = mid - 1 while head > 0 and sort_s[head][0] == 60: output.write(str(sort_s[head][0]) + ' ' + str(sort_s[head][1]) + '\n') head -= 1 break elif sort_s[mid][0] < 60: low = mid + 1 else: high = mid - 1 output.close() ##投影操作 def project(): output = open(path_project, 'w') output.write('R: \n') m=[] for i in range(100): mr.load(0, i) for t in mr.buffer[0]: m.append(t[0]) m = list(set(m)) for u in m: output.write(str(u)+'\n') output.close() ##循环连接 def nest_loop_join(): output = open(path_join1, 'w') for i in range(100): mr.load(0, i) for j in range(200): ms.load(0, j) for t_r in mr.buffer[0]: for t_s in ms.buffer[0]: if t_r[0] == t_s[0]: output.write(str(t_r[0]) + ' ' + str(t_r[1]) + ' ' + str(t_s[1]) + '\n') output.close() ##归并连接 def sort_merge_join(): output = open(path_join2, 'w') # 对关系R进行归并排序 queue_r = [] for i in range(100): mr.load(0, i) queue_r.append(sorted(mr.buffer[0], key=lambda t: t[0])) while len(queue_r) > 1: temp_queue = [] for i, t in enumerate(queue_r): if i % 2 == 1: continue elif i + 1 < len(queue_r): t.extend(queue_r[i + 1]) temp_queue.append(sorted(t, key=lambda x: x[0])) else: temp_queue.append(queue_r[i]) queue_r = temp_queue queue_r = queue_r[0] # 对关系S进行归并排序 queue_s = [] for i in range(200): ms.load(0, i) queue_s.append(sorted(ms.buffer[0], key=lambda t: t[0])) while len(queue_s) > 1: temp_queue = [] for i, t in enumerate(queue_s): if i % 2 == 1: continue elif i + 1 < len(queue_s): t.extend(queue_s[i + 1]) temp_queue.append(sorted(t, key=lambda x: x[0])) else: temp_queue.append(queue_s[i]) queue_s = temp_queue queue_s = queue_s[0] # 连接操作 for r in queue_r: for s in queue_s: if r[0] == s[0]: output.write(str(r[0]) + ' ' + str(r[1]) + ' ' + str(s[1]) + '\n') output.close() # hash连接 def hash_join(): output = open(path_join3, 'w') HR = {} HS = {} for i in range(100): mr.load(0, i) for t in mr.buffer[0]: if str(t[0]) in HR: HR[str(t[0])].append(t[1]) else: HR[str(t[0])] = [t[1]] for i in range(200): ms.load(0, i) for t in ms.buffer[0]: if str(t[0]) in HS: HS[str(t[0])].append(t[1]) else: HS[str(t[0])] = [t[1]] for i in range(20, 41): for r in HR[str(i)]: for s in HS[str(i)]: output.write(str(i) + ' ' + str(r) + ' ' + str(s) + '\n') output.close() def bing(): output = open(path_bing, 'w') R = [] S = [] for i in range(100): mr.load(0, i) R.extend(mr.buffer[0]) for i in range(200): ms.load(0, i) S.extend(ms.buffer[0]) for r in R: for s in S: if r[0] == s[0] and r[1] == s[1]: s[0] = -1 for r in R: output.write(str(r[0]) + ' ' + str(r[1]) + '\n') for s in S: if not s[0] == -1: output.write(str(s[0]) + ' ' + str(s[1]) + '\n') output.close() def jiao(): output = open(path_jiao, 'w') R = [] S = [] for i in range(100): mr.load(0, i) R.extend(mr.buffer[0]) for i in range(200): ms.load(0, i) S.extend(ms.buffer[0]) for r in R: for s in S: if r[0] == s[0] and r[1] == s[1]: output.write(str(r[0]) + ' ' + str(r[1]) + '\n') output.close() def cha(): # R - S output = open(path_cha, 'w') R = [] S = [] for i in range(100): mr.load(0, i) R.extend(mr.buffer[0]) for i in range(200): ms.load(0, i) S.extend(ms.buffer[0]) for r in R: for s in S: if r[0] == s[0] and r[1] == s[1]: r[0] == -1 for r in R: if not r[0] == -1: output.write(str(r[0]) + ' ' + str(r[1]) + '\n') output.close() if __name__ == '__main__': mr = Memory(1, 100, 10, 'disk_R') ms = Memory(1, 200, 10, 'disk_S') linear_search() binary_search() project() nest_loop_join() sort_merge_join() hash_join() bing() jiao() cha()