第四章作业 员工信息增删改查

现要求你写一个简单的员工信息增删改查程序，需求如下：

当然此表你在文件存储时可以这样表示

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
" % 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())       # 跳转到语法分析函数