Python3 - 员工表

参考文档：
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()