第四章作业 员工信息增删改查
现要求你写一个简单的员工信息增删改查程序，需求如下：
当然此表你在文件存储时可以这样表示
1,Alex Li,22,13651054608,IT,2013‐04‐01
2,Jack Wang,28,13451024608,HR,2015‐01‐07
3,Rain Wang,21,13451054608,IT,2017‐04‐01
4,Mack Qiao,44,15653354208,Sales,2016‐02‐01
5,Rachel Chen,23,13351024606,IT,2013‐03‐16
6,Eric Liu,19,18531054602,Marketing,2012‐12‐01
7,Chao Zhang,21,13235324334,Administration,2011‐08‐08
8,Kevin Chen,22,13151054603,Sales,2013‐04‐01
9,Shit Wen,20,13351024602,IT,2017‐07‐03
10,Shanshan Du,26,13698424612,Operation,2017‐07‐02
1.可进行模糊查询，语法至少支持下面3种查询语法:
find name,age from staff_table where age > 22
find * from staff_table where dept = "IT"
find * from staff_table where enroll_date like "2013"
2.可创建新员工纪录，以phone做唯一键(即不允许表里有手机号重复的情况)，staff_id需自增
语法: add staff_table Alex Li,25,134435344,IT,2015‐10‐29
3.可删除指定员工信息纪录，输入员工id，即可删除
语法: del from staff where id=3
4.可修改员工信息，语法如下:
把所有dept=IT的纪录的dept改成Market：UPDATE staff_table SET dept="Market" WHERE dept = "IT"
把name=Alex Li的纪录的年龄改成25 5：UPDATE staff_table SET age=25 WHERE name = "Alex Li".
以上每条语名执行完毕后，要显示这条语句影响了多少条纪录。 比如查询语句 就显示 查询出了多少条、 修改语句就显示修改了多少条等。 * 注意：以上需求，要充分使用函数，请尽你的最大限度来减少重复代码！*
import os COLUMN = ["id", "name", "age", "phone", "dept", "enroll_date"] def open_file(): f = open(file="员工信息增删改查", mode="r+", encoding="utf-8") data = f.readlines() staff_table = {"id": [], "name": [], "age": [], "phone": [], "dept": [], "enroll_date": []} for line in data: line = line.strip() new_list = line.split(",") staff_table["id"].append(new_list[0]) staff_table["name"].append(new_list[1]) staff_table["age"].append(new_list[2]) staff_table["phone"].append(new_list[3]) staff_table["dept"].append(new_list[4]) staff_table["enroll_date"].append(new_list[5]) f.close() return staff_table STAFF_DATA = open_file() def save_file(): f = open(file="员工信息增删改查.new", mode="w", encoding="utf-8") for index, staff_id in enumerate(STAFF_DATA["id"]): row = [] for col in COLUMN: row.append(STAFF_DATA[col][index]) s = ",".join(row) f.write("%s\n" % s) f.close() os.replace("员工信息增删改查.new", "员工信息增删改查") def find_syntax(match_record, clause): """ :param match_record:eg. match_record :param clause: eg.clause = find name,age from staff_table where age >22 :return: """ filter = clause.split("from")[0][4:].split(",") # clause.split("from")=["find name,age","staff_table where age >22"] filter_cols = [i.strip() for i in filter] # filter_cols = ["name", "age"] if "*" in filter_cols[0]: print(match_record) else: reformate_match_record = [] for row in match_record: # 在match_record 里循环各列 filter_vals = [] for col in filter_cols: # col 在COLUMN的index和在match_record里一样的，先找到其在COLUMN的index col_index = COLUMN.index(col) filter_vals.append(row[col_index]) reformate_match_record.append(filter_vals) print(reformate_match_record) print("查询出%s条记录" % len(match_record)) def add_syntax(match_record, clause): """ :param match_record:eg.match_record :param clause:add staff_table Alex Li,25,134435344,IT,2015‐10‐29 :return: """ filter = clause.split("staff_table") match_record = filter[-1].split(",") if match_record[2] not in STAFF_DATA["phone"]: i = int(STAFF_DATA["id"][-1]) + 1 new_staff_data = [str(i)] for staff_id in match_record: new_staff_data.append(staff_id) for index, col in enumerate(COLUMN): STAFF_DATA[col].append(new_staff_data[index]) save_file() else: print("phone输入有误，请重新输入") def del_syntax(match_record, clause): """ :param match_record:eg.match_record,id=3的数据 :param clause:ed.del from staff_table :return: """ for match_row in match_record: staff_id = match_row[0] # id那一列的所有元素，通过元素找到在STAFF_DATA中对应的index值 staff_id_index = STAFF_DATA['id'].index(staff_id) for col in COLUMN: STAFF_DATA[col].pop(staff_id_index) save_file() print("删除了%s条数据" % len(match_record)) def update_syntax(match_record, clause): """ :param match_record:eg.match_record,所有dept=IT的匹配数据 :param clause:eg.UPDATE staff_table set dept= Market :return: """ filter = clause.split("set") # filter = ["update staff_table", "dept=Market"] if len(filter) > 1: # 代表set存在 col_name, new_val = filter[1].strip().split('=') # 将"dept=Market"以=转换成列表。col_name=dept，new_val=Market for match_row in match_record: staff_id = match_row[0] # id那一列的所有元素，通过元素找到在STAFF_DATA中对应的index值 staff_id_index = STAFF_DATA['id'].index(staff_id) STAFF_DATA[col_name][staff_id_index] = new_val save_file() print("修改成功了%s条数据" % len(match_record)) def op_gt(info, condition_val): """ :param info: age :param condition_val: 22 :return: match_record """ match_record = [] for index, val in enumerate(STAFF_DATA[info]): # 在STAFF_DATA里的info项里循环val if float(val) > float(condition_val): # 如果val比22大，找到它的index值，再设法找到对应的其他信息 record = [] for col in COLUMN: # 在COLUMN里循环各个参数 record.append(STAFF_DATA[col][index]) # 将STAFF_DATA中col里和val的index相同的参数提取出来 match_record.append(record) # 所有信息以列表形式记录到match_record里 return match_record def op_lt(info, condition_val): match_record = [] for index, val in enumerate(STAFF_DATA[info]): if float(val) < float(condition_val): record = [] for col in COLUMN: record.append(STAFF_DATA[col][index]) match_record.append(record) return match_record def op_eq(info, condition_val): match_record = [] for index, val in enumerate(STAFF_DATA[info]): if val == condition_val: print(val) record = [] for col in COLUMN: record.append(STAFF_DATA[col][index]) match_record.append(record) return match_record def op_like(info, condition_val): match_record = [] for index, val in enumerate(STAFF_DATA[info]): if condition_val in val: print(val) record = [] for col in COLUMN: record.append(STAFF_DATA[col][index]) match_record.append(record) return match_record def where(condition): """ :param condition: age>22 :return: match_data """ operators = {">": op_gt, "<": op_lt, "=": op_eq, "like": op_like} for op_key, op_func in operators.items(): if op_key in condition: # 如果符号在condition里，就跳到相对应的函数 info, val = condition.split(op_key) # info=age,val=22 match_data = op_func(info.strip(), val.strip()) # 跳到对应的函数 return match_data # print(match_data) else: print("输入有误,请输入find/add/del/update from staff_table where caulse >/</=/like condtion") def syntax_analyse(user_input): syntax_analyse = {"find": find_syntax, "add": add_syntax, "del": del_syntax, "update": update_syntax } if user_input.split()[0] in ('find', 'add', 'del', 'update'): if "where" in user_input: # where在输入的语句中，则以where为分割，将user_input转换成列表，判断where之后的语句 clause, condition = user_input.split("where") # clause = find name,age from staff_table,condition = age>22 match_record = where(condition) # 跳到where函数，并返回match_record的值 else: match_record = [] cmd_action = user_input.split()[0] # cmd_action = find,add,update,del clause = user_input cmd_action = user_input.split()[0] if cmd_action in syntax_analyse: syntax_analyse[cmd_action](match_record, clause) else: print("输入有误,请输入find/add/del/update from staff_table where caulse >/</=/like condtion") while True: user_input = input("staff_data").strip() if not user_input: # 如果用户输入的不为空就进入语法分析 continue syntax_analyse(user_input.strip()) # 跳转到语法分析函数
