员工信息表

作业需求
1.模糊查询，查询结果可显示条数，支持例如以下语法：
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.可创建新员工纪录，以phone做唯一键，staff_id需自增；
3.可删除指定员工信息纪录，输入员工id，即可删除；
4.可修改员工信息，语法如下:
update staff_table set dept = "Market" where dept = "IT";
5.充分使用函数，请尽你的最大限度来减少重复代码；  
 

import os,sys
def file_to_data(sql):
    '''将员工信息表的内容写入data_list'''
    n = 0
    data_list=[]
    with open('sql','r') as f:
        for lines in f:
            lines=lines.strip()
            if n == 0:
                struct_list=lines.split(',')
            else:
                data_list.append(lines.split(','))
            n+=1
    return struct_list,data_list

    # print(struct_list)
    # print(data_list)
def data_to_file(struct_list,data_list,sql2):
    '''将strcut_list 及 data_list里的信息写入文件'''
    with open('sql2','w') as f:
        f.write(','.join(struct_list)+'\n') # 通过指定字符串将序列中的元素连成字符串
        for i in range (len(data_list)):
            f.write(','.join(data_list[i])+'\n')
        print('done')
def print_help(action):
    '''打印语法提示'''
    print('the grammer of the {} support likes:\n'.format(action))
    if action == 'select':
        print("\tselect * from staff_table;")
        print("\tselect name,age from staff_table where age > 22;")
        print("\tselect * from staff_table where dept = \"IT\";")
        print("\tselect * from staff_table where enroll_date like \"2013\";")
    elif action == "add":
        print("\tadd [Alex Li,22,13651054608,IT,2013-04-01];")
    elif action == "update":
        print("\tupdate staff_table set dept = \"Market\" where dept = \"IT\";")
    elif action == "delete":
        print("\tdelete 5;")
def get_column_number(column,strct_list):
    '''返回指定列所在的位置'''
    column_number = strct_list.index(column)
    return column_number

def sql_input1():
    while True:
        print('-'.center(60,'-'))
        print('tip:input help select/update/add/delete to get help')
        print('-'.center(60,'-'))
        sql = input('please input sql:').strip().strip(';')
        if sql.startswith('help'):
            action = sql.split()[1]
            print_help(action)
            continue
        elif sql == 'q' or sql =='quit':
            exit('byebye'.center(60,'#'))
        break
    return sql

def sql_to_list(sql):
    tmp_sql = sql.split()
    sql_list=[]
    tmp = ''
    flag=1
    for l in tmp_sql:
        if l.startswith('"') and l.endswith('"'):
            flag=1
        elif l.startswith('"') and (not l.endswith('"')):
            flag = 0
            tmp = l+''
        elif (not l.startswith('"')) and (not l.endswith('"')):
            if flag == 0:
                l+=''
                tmp+=l
            else:
                flag =1
        elif (not l.startswith('"')) and (l.endswith('"')):
            if flag == 0:
                tmp+=l
                flag = 1
                sql_list.append(tmp)
                continue
        if flag==1:
            sql_list.append(l)
        # print(sql_list)
    return sql_list
def check_table(table):
    c_table_list=os.listdir(os.getcwd())
    if(table not in c_table_list):
        print("your input table \033[31m{}\033[0m is not exit".format(table))
        print('#'.center(60,'#'))
        return False
    return True

def select(sql_input,strcut_list,data_list,table):
    input_info = sql_to_list(sql_input)
    select_column = input_info[1].split(',')# 可能含有逗号
    continute_flag = check_table(input_info[3])
    if continute_flag:
        all_column = False
        all_line = False

        if '*' in select_column :
            all_column= True
        else:
            column_numbers=[]
            for i in select_column:
                c_numbers=get_column_number(i,struct_list)
                column_numbers.append(c_numbers)
        if 'where' in sql_input:
            where_flag = input_info[4]
            condition_column = input_info[5]
            condition_str = input_info[6]
            condition_value = input_info[7]
            condition_number= get_column_number(condition_column,struct_list)
            match_data_list=[]
            if condition_str == '=':
                for line in data_list:
                    if line[condition_number] == condition_value:
                        match_data_list.append(line)
            if condition_str == 'like':
                for line in data_list:
                    if line[condition_number] in condition_value:
                        match_data_list.append(line)# 符合选择条件的员工的信息
            if condition_str == '>':
                for line in data_list:
                    if line[condition_number] > condition_value:
                        match_data_list.append(line)
            elif condition_str == ">=":
                for line in data_list:
                    if line[condition_number] >= condition_value:
                        match_data_list.append(line)
            elif condition_str == "<":
                for line in data_list:
                    if line[condition_number] < condition_value:
                        match_data_list.append(line)
            elif condition_str == "<=":
                for line in data_list:
                    if line[condition_number] <= condition_value:
                        match_data_list.append(line)
        else:
            all_line==True
            match_data_list=data_list

        print('the select resault:')
        print('#'.center(60,'#'))
        print('\033[32m{}\033[0m row is set'.format(len(match_data_list)))
        if all_column is True:# 输出所有列
            print('{:>8}{:>8}{:>8}{:>8}{:>8}{:>8}'.format(*struct_list))
            for line in match_data_list:
                print('{:8}{:>8}{:>8}{:>8}{:>8}{:>8}'.format(*line))
        else:# 输出指定列
            len_num = len(column_numbers)
            format_str = '{:>8}'*len_num
            print(len_num)
            print(select_column)
            print(format_str.format(*select_column))
            for line in match_data_list:
                    line_list=[]
                    for i in column_numbers:
                        line_list.append(line[i])
                    print(format_str.format(*line_list))
        print('#'.center(60,'#'))
def auto_increment_id(data_list):
    max_id = int(data_list[-1][0])
    file = 'auto_increment_id'
    id=0
    with open(file,'r+') as f:
        for line in f:
            id = int(line)
        if max_id <= id:
            new_id = id+1
        else:
            new_id = max_id+1
        with open(file,'w+')as f2:
            f2.write(str(new_id))
        return new_id

def add(sql_input,struct_list,data_list,table):
    input_info = sql_input.strip().strip('add [').strip(']')
    add_list = input_info.split(',')
    phone=add_list[2]
    print(phone)
    for d_list in data_list:
        if phone == d_list:
            print('电话号码已经存在 不能再添加')
            return
        else:
            new_id = auto_increment_id(data_list)
            add_list.insert(0,str(new_id))
            data_list.append(add_list)
            break
    data_to_file(struct_list,data_list,'sql2')
def delete(sql_input,struct_list,data_list,sql):
    input_info = sql_to_list(sql_input)
    del_id = input_info[1]
    Flag = True

    for i in data_list:
        if del_id == i[0]:
            Flag=False
            data_list.remove(i)
            data_to_file(struct_list,data_list,'sql2')

    while Flag:
        print('所输入的员工id不存在')
    return
def update(sql_input,struct_list,data_list,sql):
    # update staff_table set dept = "Market" where dept = "IT";   只允许修改age,phone,dept,enroll_date
    input_info = sql_to_list(sql_input)
    if check_table(input_info[1]):
        modify_column = input_info[3].strip()
        modify_value=input_info[5].strip()
        condition_column = input_info[7].strip()
        condition_value = input_info[9].strip()
        condition_column_num=get_column_number(condition_column,struct_list)
        modify_column_num = get_column_number(modify_column,struct_list)
        for i in data_list:
            if i[condition_column_num]== condition_value:
                i[modify_column_num]=modify_value
                print('修改完成')
        data_to_file(struct_list,data_list,'sql2')

if __name__=='__main__':
    while True:
        sql_input = sql_input1()
        struct_list,data_list=file_to_data('sql')
        action=sql_input.split()[0]
        if action == "select":
            select(sql_input,struct_list,data_list,'sql')
            continue
        if action == 'add':
            add(sql_input,struct_list,data_list,'sql')
            continue
        if action == 'delete':
            delete(sql_input,struct_list,data_list,'sql')
            continue
        if action == 'update':
            update(sql_input,struct_list,data_list,'sql')
            continue
        else:
            print("输入错误")
            continue