美文网首页
Python3 - 员工表

Python3 - 员工表

作者: drfung | 来源:发表于2017-12-20 15:59 被阅读37次

    参考文档:
    https://www.cnblogs.com/huxi/archive/2010/07/04/1771073.html
    https://docs.python.org/3/library/re.html
    https://mozillazg.github.io/2013/08/ansi-escape-sequences.html#

    import os
    import re
    
    """
    员工信息表:
    
    当然此表你在文件存储时可以这样表示
    
    staff_id,name,age,phone,dept,enroll_date
    1,Alex Li,22,13651054608,IT,2013-04-01
    2,Jack Wang,30,13304320533,HR,2015-05-03
    3,Rain Liu,25,1383235322,Sales,2016-04-22
    4,Mack Cao,40,1356145343,HR,2009-03-01
    
    现需要对这个员工信息文件,实现增删改查操作
    
    1. 可进行模糊查询,语法至少支持下面3种:
        select name,age from staff_table where age > 22
        select  * from staff_table where dept = "IT"
        select  * from staff_table where enroll_date like "2013"
    2. 查到的信息,打印后,最后面还要显示查到的条数
    3. 可创建新员工纪录,以phone做唯一键,staff_id需自增
    4. 可删除指定员工信息纪录,输入员工id,即可删除
    5. 可修改员工信息,语法如下:
      UPDATE staff_table SET dept="Market" WHERE where dept = "IT"
    注意:以上需求,要充分使用函数,请尽你的最大限度来减少重复代码!
    """
    
    default_t = """
    1,Alex Li,22,13651054608,IT,2013-04-01
    2,Jack Wang,30,13304320533,HR,2015-05-03
    3,Rain Liu,25,1383235322,Sales,2016-04-22
    4,Mack Cao,40,1356145343,HR,2009-03-01
    """
    
    # 首先实现列表和文件的转换
    file_path = "staff_info.txt"
    # 生成默认表
    if not os.path.exists(file_path):
        with open(file_path, "w", encoding="utf-8") as f:
            f.write(default_t.strip())
    
    # 表头
    column = ["staff_id", "name", "age", "phone", "dept", "enroll_date"]
    
    
    # 读出文件生成表
    def load(file):
        temp = []
        with open(file, "r", encoding="utf-8") as f:
            for i in f:
                temp.append(i.strip().split(","))
        return temp
    
    
    # 保存列表到文件
    def save(table_list):
        table_str = ''
        for i in table_list:
            table_str = table_str + ','.join(i) + "\n"
        with open(file_path, "w", encoding="utf-8") as f:
            f.write(table_str.strip())
    
    
    # 实现添加员工
    def add(table_list, user_add):
        # 验证电话号码
        for i in table_list:
            if i[3] == user_add[2]:
                print("电话号码不唯一")
                return
        # 实现id自动增加
        table_list.append(list(str(int(table_list[-1][0]) + 1)) + user_add)
        save(table_list)
    
    
    # 实现删除员工
    def delete(table_list, user_del):
        for i in table_list:
            if i[0] == user_del:
                table_list.remove(i)
        save(table_list)
    
    
    # 修改员工信息
    def modify(table_list, user_mod, column=column):
        con_index = column.index(user_mod[1][0])
        set_index = column.index(user_mod[0][0])
        for i in table_list:
            print(i)
            if i[con_index] == user_mod[1][1]:
                i[set_index] = user_mod[0][1]
        save(table_list)
    
    
    def show(result_list, column=column):
        print(column)
        for i in result_list:
            print(i)
        print("共查询到 {} 条数据".format(len(result_list)))
    
    
    def sql_select(table_list, column=column):
        sql = input("\033[32m请输入您的查询sql语句: \033[0m")
        p1 = r"select (.*) from staff_list where (.*);"
        p2 = r"select (.*) from staff_list;"
        rt1 = re.fullmatch(p1, sql)
        rt2 = re.fullmatch(p2, sql)
        if rt1:
            print(rt1.group(1), rt.group(2))
        elif rt2:
            if rt2.group(1).strip() == "*":
                show(table_list)
            else:
                sl = rt2.group(1).strip().split(",")
                rt_l = []
                if set(sl).issubset(set(column)):
                    for i in range(len(table_list)):
                        row = []
                        for j in sl:
                            row.append(table_list[i][column.index(j)])
                        rt_l.append(row)
                    show(rt_l)
                else:
                    print("查询的列不存在...")
        else:
            print("\033[31m语法不正确\033[0m")
    
    
    def sql_delete(table_list):
        user_delete = input("请输入你想删除的员工id: ")
        delete(table_list, user_delete)
    
    
    def sql_update(table_list, column=column):
        sql_input = input("\033[32m请输入您的更新sql语句: \033[0m")
        p = "update staff_list set (.*) where (.*);"
        rt = re.fullmatch(p, sql_input)
        if rt:
            set_l = [i.strip().strip("\"").strip("\'") for i in rt.group(1).split("=")]
            con_l = [i.strip().strip("\"").strip("\'") for i in rt.group(2).split("=")]
            modify(table_list, [set_l, con_l], column)
        else:
            print("请输入正确的sql语句...")
    
    
    def sql_insert(table_list):
        sql_input = input("\033[32m请输入您的插入sql语句: \033[0m")
        p = "insert into staff_list values\((.*)\);"
        rt = re.fullmatch(p, sql_input)
        if rt:
            user_add = [x.strip().strip("\"").strip("\'") for x in rt.group(1).split(",")]
            if len(user_add) == 6:
                user_add = user_add[1:]
                add(table_list, user_add)
            elif len(user_add) == 5:
                add(table_list, user_add)
            else:
                print("员工参数列数不对!!!")
        else:
            print("请输入正确的sql语句...")
    
    
    # 欢迎菜单
    def menu():
        print("Welcome".center(80, "="))
        print("""
        \033[34m请选择您想进行的操作:\033[0m
        1. 查询员工:
        2. 删除员工
        3. 修改员工
        4. 增加员工
        q. 退出程序
        """)
    
    
    def main():
        # add(staff_table, ['fbo', '30', "111111111", 'Sales', "2017-12-19"])
        # delete(staff_table, input())
        # modify(staff_table, (("dept", "HEHE"), ("name", "fbo")))
        while True:
            staff_table = load(file_path)
            menu()
            user_input = input(">>>")
            if user_input == 'q' or user_input == "Q":
                break
            elif user_input == "1":
                sql_select(staff_table)
            elif user_input == "2":
                sql_delete(staff_table)
            elif user_input == "3":
                sql_update(staff_table)
            elif user_input == "4":
                sql_insert(staff_table)
            else:
                print("\033[31m输入错误, 请重新输入!!!\033[0m")
    
    
    if __name__ == "__main__":
        main()
    

    相关文章

      网友评论

          本文标题:Python3 - 员工表

          本文链接:https://www.haomeiwen.com/subject/apigwxtx.html