2.1 - 员工信息增删改查程序

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

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

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_table where  id=3

4.可修改员工信息，语法如下:

UPDATE staff_table SET dept="Market" WHERE  dept = "IT" 把所有dept=IT的纪录的dept改成Market
UPDATE staff_table SET age=25 WHERE  name = "Alex Li"  把name=Alex Li的纪录的年龄改成25

5.以上每条语名执行完毕后，要显示这条语句影响了多少条纪录。 比如查询语句 就显示 查询出了多少条、修改语句就显示修改了多少条等。

----------------------------------------------------------------

----------------------------------------------------------------

标准答案：

# _*_coding:utf-8_*_
# created by Alex Li on 10/15/17
from tabulate import tabulate
import os

STAFF_DB = "staff.db" #因为不会变，所以是常量
COLUMN_ORDERS = ['id','name','age','phone','dept','enrolled_date']



def load_db():
    """
    打开db文件，把文件里的数据的每列转换成一个列表
    1,Alex Li,22,13651054608,IT,2013-04-01

    :return: 
    """
    staff_data = { #把文件里的每列添加到下面这些列表里
        'id':[],
        'name':[],
        'age':[],
        'phone':[],
        'dept':[],
        'enrolled_date':[]
    }


    f = open(STAFF_DB,"r",encoding="utf-8")

    for line in f:
        staff_id,name,age,phone,dept,enrolled_date = line.strip().split(',')
        staff_data['id'].append(staff_id)
        staff_data['name'].append(name)
        staff_data['age'].append(age)
        staff_data['phone'].append(phone)
        staff_data['dept'].append(dept)
        staff_data['enrolled_date'].append(enrolled_date)

    #print(staff_data)
    f.close()
    return staff_data


def save_db():
    """sync data back to db each time after editing"""
    f = open("%s_tmp"%STAFF_DB, "w", encoding="utf-8")

    for index,val in enumerate(STAFF_DATA[COLUMN_ORDERS[0]]):
        row = [str(val)]
        for col in COLUMN_ORDERS[1:]:
            row.append(str(STAFF_DATA[col][index]) )

        raw_row = ",".join(row)
        f.write(raw_row+"\n")
    f.close()
    os.rename("%s_tmp"%STAFF_DB,STAFF_DB)


def print_log(msg,msg_type='info'):
    if msg_type == 'error':
        print("\033[31;1mError:%s\033[0m"%msg)
    else:
        print("\033[32;1mInfo:%s\033[0m"%msg)


def syntax_find(query_clause, matched_data):
    """
    
    :param query_clause: eg. find age,name from staff_table 
    :param matched_data: where方法匹配到的数据
    :return: 
    """

    filter_keys = query_clause.split('find')[1].split('from')[0]
    columns = [i.strip()  for i in filter_keys.split(',')] #要过滤出来的字段
    if "*" in columns:
        if len(columns) == 1: #只有find * from ...成立，*不能与其它字段同时出现
            columns = COLUMN_ORDERS
        else:
            print_log("*不能同时与其它字段出现","error")
            return False
    if len(columns) == 1:
        if not columns[0]:
            print_log("语法错误，find和from之间必须跟字段名或*","error")
            return False
    filtered_data = []
    for index,val in enumerate(matched_data[columns[0]]): #拿要查找的多列的第一个元素，[name,age,dept]，拿到name，到数据库匹配，然后按这一列的每个值 的索引到其它列表里依次找
        row = [val,]
        #if columns[1:]: #代表是多列过滤
        for col in columns[1:]:
            row.append(matched_data[col][index])
        #print("row",row)
        filtered_data.append(row)

    print(tabulate(filtered_data,headers=columns,tablefmt="grid"))
    print_log("匹配到%s条纪录"%len(filtered_data))


def syntax_add(query_clause, matched_data):
    """
    sample: add staff Alex Li,25,134435344,IT,2015-10-29
    :param query_clause: add staff Alex Li,25,134435344,IT,2015-10-29
    :param matched_data: 
    :return: 
    """
    column_vals = [ col.strip() for col in query_clause.split("values")[1].split(',')]
    #print('cols',column_vals)
    if len(column_vals) == len(COLUMN_ORDERS[1:]): #不包含id,id是自增

        #find max id first , and then plus one , becomes the  id of this new record
        init_staff_id = 0
        for i in STAFF_DATA['id']:
            if int(i) > init_staff_id:
                init_staff_id = int(i)

        init_staff_id += 1 #当前最大id再+1
        STAFF_DATA['id'].append(init_staff_id)
        for index,col in enumerate(COLUMN_ORDERS[1:]):
            STAFF_DATA[col].append( column_vals[index] )

    else:
        print_log("提供的字段数据不足，必须字段%s"%COLUMN_ORDERS[1:],'error')

    print(tabulate(STAFF_DATA,headers=COLUMN_ORDERS))
    save_db()
    print_log("成功添加1条纪录到staff_table表")


def syntax_update(query_clause, matched_data):
    pass


def syntax_delete(query_clause, matched_data):
    pass

def op_gt(q_name,q_condtion):
    """
    find records q_name great than q_condtion 
    :param q_name: 查找条件key
    :param q_condtion: 查找条件value
    :return: 
    """
    matched_data = {} #把符合条件的数据都放这
    for k in STAFF_DATA:
        matched_data[k] = []

    q_condtion = float(q_condtion)
    for index,i in enumerate(STAFF_DATA[q_name]):
        if float(i) > q_condtion :
            for k in matched_data:
                matched_data[k].append( STAFF_DATA[k][index] )  #把匹配的数据都 添加到matched_data里

    #print("matched:",matched_data)

    return matched_data

def op_lt():
    """
    less than
    :return: 
    """

def op_eq():
    """
    equal 
    :return: 
    """

def op_like(q_name,q_condtion):
    """
    find records where q_name like q_condition
    :param q_name: 查找条件key
    :param q_condtion: 查找条件value    
    :return: 
    """
    matched_data = {} #把符合条件的数据都放这
    for k in STAFF_DATA:
        matched_data[k] = []

    for index,i in enumerate(STAFF_DATA[q_name]):
        if  q_condtion  in i :
            for k in matched_data:
                matched_data[k].append( STAFF_DATA[k][index] )  #把匹配的数据都 添加到matched_data里

    #print("matched:",matched_data)

    return matched_data

def syntax_where(clause):
    """
    解析where条件，并查询数据
    :param clause: where条件 , eg. name=alex
    :return: False or matched data dict 
    """

    query_data = {} #存储查询出来的结果
    operators = {'>':op_gt,
                 '<':op_lt,
                 '=':op_eq,
                 'like':op_like}
    query_condtion_matched = False #如果匹配语句都没匹配上
    for op_key,op_func in operators.items():
        if op_key in clause:
            q_name,q_condition = clause.split(op_key)
            #print("query:",q_name,q_condition)
            if q_name.strip() in STAFF_DATA:
                matched_data = op_func(q_name.strip(),q_condition.strip()) #调用对应的方法
                return matched_data
            else:
                print_log("字段'%s' 不存在!"%q_name,'error')
                return False

    if not query_condtion_matched:
        print("\033[31;1mError:语句条件%s不支持\033[0m"%clause)
        return False







def syntax_parser(cmd):
    """
    解析语句
    :return: 
    """
    syntax_list = {
        'find':syntax_find,
        'add':syntax_add,
        'update':syntax_update,
        'delete':syntax_delete,
    }
    if cmd.split()[0] in ['find','add','update','delete'] and "staff_table" in cmd :

        if 'where' in cmd:
            query_cmd,where_clause = cmd.split("where")

            matched_data = syntax_where(where_clause.strip())
            if matched_data: #有匹配结果
                action_name = cmd.split()[0]
                syntax_list[action_name](query_cmd,matched_data) #调用对应的action方法
        else:
            syntax_list[cmd.split()[0]](cmd, STAFF_DATA) #没where,使用所有数据


    else:
        print_log('''语法错误!\nsample:[find/add/update/delete] name,age from [staff_table] where [id][>/</=/like][2]''','error')


def main():
    """
    程序主入口
    :return: 
    """

    while True:
        cmd = input("[staff db]:").strip()
        if not cmd:continue

        syntax_parser(cmd)



STAFF_DATA = load_db()

main()

我的答案：

# -*- coding:utf-8 -*-
import os

staff_db = 'staff_table.txt'
staff = 'staff_table'
staff_order = ['id', 'name', 'age', 'phone', 'dept', 'enroll_date']


def print_search(list_data):
    if len(list_data) != 0:
        print('影响了\033[1;31m%s\033[0m条记录'% len(list_data))
        for i in list_data:
            print(i)
    else:
        print("没有对应得数据！")


def syntax_update(con_sen,common_data):
    # update 修改语句
    if len(common_data) == 0:
        print('没有记录')
        return common_data
    else:
        index_num = 0
        id_data = []
        new_data = []
        for index, i in enumerate(staff_order):
            if i == con_sen[0].strip():
                index_num = index
                break
        for index, i in enumerate(common_data):
            id_data.append(i.split(',')[0])
        f = open(staff_db, 'r+', encoding='utf-8')
        f_new = open('staff_table_new.txt', 'w', encoding='utf-8')
        for line in f:
            temp_li =line.split(',')
            if temp_li[0] in id_data:
                if con_sen[1].strip().isdigit():
                    temp_li[index_num] = con_sen[1].strip()
                else:
                    temp_li[index_num] = eval(con_sen[1].strip())
                line = ','.join(temp_li)
                new_data.append(line.strip())
            f_new.write(line)
        f.close()
        f_new.close()

        os.replace(f_new.name, f.name)
        return new_data


def syntax_add(add_data):
    # add 语句增加
    f = open(staff_db, 'r+', encoding='utf-8')
    id_data, phone, new_list = [], [], []
    for index, line in enumerate(f):
        id_data.append(line.split(',')[0])
        phone.append(line.split(',')[3])
    if add_data[2] in phone:
        print('phone已存在')
    else:
        data = str(int(id_data[-1])+1) + ','+ ','.join(add_data)
        new_list.append(data)
        f.write(data+'\n')
    f.close()
    return new_list


def syntax_delete(common_data):
    # del 语句删除
    if len(common_data) == 0:
        print('没有该条记录'.center(20,'-'))
        return common_data
    else:
        str_data = ''
        f = open(staff_db, 'r+', encoding='utf-8')
        for line in f:
            if line.split(',')[0] == common_data[0].split(',')[0]:
                continue
            else:
                str_data += line
        f.seek(0)
        f.truncate()
        f.write(str_data)
        f.close()
        print('已从文件中删除'.center(20, '-'))
        return common_data


def syntax_find(con_sen,common_data):
    # find 语句查找
    if '*' in con_sen.split('find')[1]:
        return common_data
    sen = con_sen.split('find')[1].split(',')
    li = []
    for index, i in enumerate(staff_order):
        for j in sen:
            if j.strip() == i:
                li.append(index)
    new_data = []
    for i in common_data:
        str_data = ''
        for index, j in enumerate(i.split(',')):
            for k in li:
                if k == index:
                    str_data += j + ','
                    break
        new_data.append(str_data[:-1])
    return new_data


def syntax_where(common_data, con_sentence):
    # 查找语句分配
    if con_sentence == '':
        add_data = common_data.split(',')
        return syntax_add(add_data)
    else:
        if 'from' in con_sentence:
            con_sen = con_sentence.split('from')
            if con_sen[1].strip() == staff:
                if 'find' in con_sen[0]:
                    return syntax_find(con_sen[0],common_data)
                elif 'del' in con_sen[0]:
                    return syntax_delete(common_data)
            else:
                print('输入的语句有问题')
                return []
        elif 'set' in con_sentence and 'update' in con_sentence:
            con_sen = con_sentence.split('set')
            if 'staff_table' in con_sen[0]:
                return syntax_update(con_sen[1].split('='), common_data)
            else:
                print('输入的语句有问题')
                return []
        else:
            print('输入的语句有问题')
            return []


def common_op(where_data):
    # > < = like 公共方法
    list_where_data = []
    con_where = []
    index_num = 0
    op = ['>', '<', '=', 'like']
    for index, i in enumerate(op):
        if i in where_data:
            list_where_data = where_data.split(i)
            index_num = index
            break
    f = open(staff_db, 'r', encoding='utf-8')
    for index, i in enumerate(staff_order):
        if i == list_where_data[0].strip():
            for line in f:
                data = line.strip().split(',')
                if index_num == 0:
                    if eval(data[index].strip()) > eval(list_where_data[1].strip()):
                        con_where.append(line.strip())
                elif index_num == 1:
                    if eval(data[index].strip()) < eval(list_where_data[1].strip()):
                        con_where.append(line.strip())
                elif index_num == 2:
                    if data[index].strip().isdigit():  # 判断原有的内容是否是整数类型
                        if eval(data[index].strip()) == eval(list_where_data[1].strip()):
                            con_where.append(line.strip())
                    else:
                        if data[index].strip().lower() == eval(list_where_data[1].strip().lower()):
                            con_where.append(line.strip())
                elif index_num == 3:
                    if data[index].strip().split('-')[0].strip() == eval(list_where_data[1].strip()):
                        con_where.append(line.strip())
            break
    f.close()
    return con_where


def syntax_parser(cmd):
    if 'add' in cmd:
        print_search(syntax_where(cmd.split(staff)[1], ''))
    elif 'where' in cmd:
        con_sentence, data = cmd.split('where')
        print_search(syntax_where(common_op(data), con_sentence))


def main():
    while True:
        cmd = input('输入语句>>>:').strip().lower()
        if not cmd:
            continue
        syntax_parser(cmd)


if __name__ == '__main__':
    main()