staff_table

1,Alex Li,22,13651054608,IT,2013-04-012,Jack Wang,30,13304320533,HR,2015-05-033,Rain Liu,25,1383235322,Saies,2016-04-224,Mmck Cao,40,1356145343,HR,2009-03-01

+---------------------------------------READ ME-------------------------------------------------+|   注：命令及关键字请使用小写|       本代码为模拟简单的 MySQL 增删改查的功能，可参考给的例子，自行输入+-----------------------------------------------------------------------------------------------+|   * 的位置表示显示范围，也可以输入表头名字，只打印这几列|  范围 的位置，表示查找（修改）的条件，可以用and连接多个参数|   (field1,field2) 表示你增加时需要写入的列的名字， phone 必选|   values(value1,value2) 表示你增加时需要写入的列的数值+-----------------------------------------------------------------------------------------------+|   查找：select * from staff_table where 范围|        :select * from staff_table where age > 22 and dept = "HR" and enroll_date like "2009"|        :select name,age from staff_table where age > 22+-----------------------------------------------------------------------------------------------+|   插入：insert into staff_table (field1,field2) values(value1,value2)|        :insert into staff_table (name,age,phone,dept) values ('jia',22,15246512541,'IT')+-----------------------------------------------------------------------------------------------+|   删除：delete from staff_table where 范围|        :delete from staff_table where staff_id = 3+-----------------------------------------------------------------------------------------------+|   更新：update staff_table set field1=value1 where 范围|        :update staff_table set age = 23 where staff_id = 1+-----------------------------------------------------------------------------------------------+  退出：q    再看一遍说明：r

low_B的代码

1 #! /usr/bin/env python  2 # -*- coding: utf-8 -*-  3 # __author__ = "Always"  4 # Date: 2017/6/16  5 import time  6   7 file_path = r'staff_table'  8 readme_path = r'readme.txt'  9 all_tittle = ['staff_id', 'name', 'age','phone', 'dept', 'enroll_date'] 10 func_dic = {} 11  12 def add_func_dic(keys): 13     def deco(func): 14         func_dic[keys] = func 15     return deco 16  17 def resolve_cmd(cmd): 18     """ 19     分解命令 20     这个函数，主要是用来处理接收的 select 命令，将各个参数提取出来，放到一个字典中 21     :param cmd:  这个参数是用户输入的，命令，格式为字符串形式 22     :return: 23     """ 24     cmd_dict = {
    'show_tittle':None, 'file_name':None, 'select_condetion':None, 'cmd':None} 25     cmd_deal = cmd.split('from')[0].split() 26     cmd_dict['cmd'] = cmd_deal.pop(0).strip() 27     cmd_deal = ''.join(cmd_deal).split(',') 28     cmd_dict['show_tittle'] = all_tittle if cmd_deal[0].strip() == "*" else cmd_deal 29     filename_select = cmd.split('from')[1].strip().split('where') 30     cmd_dict['file_name'] = filename_select[0].strip() 31     if len(filename_select) > 1: 32         cmd_dict['select_condetion'] = filename_select[1].strip() 33     return cmd_dict 34  35 def deal_select_condetion(select_condetion): 36     """ 37     主要用来处理后面的条件有 like 的情况，为筛选的条件重新排序 38     :param select_condetion: 39     :return: 40     """ 41     select_condetion = select_condetion.split('and') 42     for i in range(len(select_condetion)): 43         if 'like' in select_condetion[i]: 44             select_condetion[i] = select_condetion[i].split() 45             select_condetion[i].reverse() 46             select_condetion[i] = ' '.join(select_condetion[i]) 47     return ' and '.join(select_condetion).replace('like', 'in') 48  49  50 def return_select_result(total_count, all_tittle, show_tittle, d): 51     """ 52     这个函数主要用来处理打印满足条件的行数和列数 53     :param total_count: 用来计算一共打印了多少行 54     :param all_tittle: 所有的表头，用来固定打印顺序 55     :param show_tittle: 代表需要打印的列 56     :return: 用来计算一共打印了多少行 57     """ 58     for tittle in all_tittle: 59         if tittle in show_tittle: 60             print('%-14s' % d[tittle], end='') 61     print() 62     total_count += 1 63     return total_count 64  65 @add_func_dic('select') 66 def what_a_funck(cmd): 67     cmd_dict = resolve_cmd(cmd) 68     select(cmd_dict['file_name'], cmd_dict['select_condetion'], cmd_dict['show_tittle']) 69  70 def select(file_name, select_condetion, show_tittle): 71     """ 72     这个功能用来查找，并打印符合要求的字段 73     :param file_name 74     :param select_condetion 75     :param show_tittle 76     :return: 77     """ 78     file_info_list = get_file_info_to_list(file_name) 79  80     for tittle in all_tittle:                         # 打印表头 81         if tittle in show_tittle: 82             print('\033[44;1m%-14s' % tittle, end='') 83     print('\033[0m') 84     total_count = 0 85  86     if select_condetion:                   # 如果有筛选条件的话，按条件打印，没有的话，全部打印 87         select_condetion = select_condetion.replace('=', '==')  # 如果判断条件有一个 ’=‘ 就把他变成两个，方便后面 eval 计算 88         if 'like' in select_condetion:  # 如果判断条件有 like 就把   enroll_date like "2013"  改成 “2013” in enroll_date ，方便后面 eval 计算 89             select_condetion = deal_select_condetion(select_condetion) 90  91  92         for d in file_info_list:                          # 遍历 用户信息 列表 93             globals().update(d)                           # 将字典中所有内容变为变量，用来处理条件 94             show_table_tittle = False 95             if eval(select_condetion):                    # 如果满足输入的条件，那么打印信息 96  97                 total_count = return_select_result(total_count,all_tittle,show_tittle, d) 98     else:                                                 # 如果用户没有输入筛选条件，执行这里 99         for d in file_info_list:100             total_count = return_select_result(total_count, all_tittle, show_tittle, d)101     print('\033[1mtotal count: %s\033[0m'%total_count)102 103 def get_file_info_to_list(file_path):               # 将文件内容取出来，放入 [{},{}] 中104     with open (file_path,encoding='utf-8') as f:105         return [{
    'staff_id':int(line.strip().split(',')[0]), 'name':line.strip().split(',')[1], 'age':int(line.strip().split(',')[2]), 'phone':line.strip().split(',')[3], 'dept':line.strip().split(',')[4], 'enroll_date':line.strip().split(',')[5]} for line in f]106 107 108 def insert_split(cmd):                                 # 插入功能的前戏109     """110     插入功能的命令分解。。。111     :param cmd: 用户输入的命令112     :return: 包含分解的命令的字典113     """114     cmd_dict = {}115     cmd_split = cmd.split()116     if cmd_split[1] != 'into':117         return118     cmd_dict['file_name'] = cmd_split[2]119     cmd_split = ''.join(cmd_split[3:]).split('values')120     cmd_dict['keys'] = cmd_split[0].strip('(').strip(')').split(',')121     cmd_dict['values'] = cmd_split[1].strip('(').strip(')').split(',')122     return cmd_dict123 124 @add_func_dic('insert')125 def insert(cmd, all_tittle = all_tittle, file_path = file_path):               # 插入功能的开始126     """127     插入功能，新增一个用户128     :param cmd: 传入命令行的输入，进行分解调用129     :param all_tittle: 默认为全局变量，用来判断需要写入那些内容，以及位置排序130     :param file_path: 默认为全局变量，用来标记需要修改的文件名131     :return:132     """133     if not insert_split(cmd):134         print('输入错误。。。')135         return136     cmd_dict = insert_split(cmd)137     globals().update(cmd_dict)138     file_info_list = get_file_info_to_list(file_path)139     new_people_info = {
    'staff_id':file_info_list[-1]['staff_id'] + 1}140     if 'phone' not in keys:141         print('哥，我也不想的，老师指定 phone 为唯一键，不输入不行啊。。。。')142         return143 144     for i in file_info_list:                                            # 如果号码重复，重新输入145         if i['phone'] == values[keys.index('phone')]:146             print('那啥，号码已注册，换一个吧。。亲。。。。')147             return148     if 'age' in keys and not values[keys.index('age')].isdigit():               # 判断年龄是不是数字149         print('年龄需要为数字。。。')150         return151 152     for i in all_tittle[1:]:                  # 准备加入 新人物 的信息153         if i in keys:154             new_people_info[i] = values[keys.index(i)].strip("'").strip('"')155         else:156             new_people_info[i] = None157     new_people_info = [new_people_info]158     add_new_people(all_tittle, new_people_info, file_path, open_with='a')159     print('\033[1m加入成功。。。\033[0m')160 161 162 def add_new_people(all_tittle,new_people_info, file_path, open_with = 'r'):163     """164     主要用来写入或增加 staff_table 的内容165     :param all_tittle: 存放表头信息166     :param new_people_info: 需要加入，或重新写入的任务信息167     :param file_path: 文件路径168     :param open_with: 打开方式169     :return: 1170     """171     with open(file_path, open_with, encoding='utf-8') as f:172         new_people_info_str = ''173         if new_people_info[0]['enroll_date'] == None:174             new_people_info[0]['enroll_date'] = time.strftime("%Y-%m-%d")175 176         for d in new_people_info:177             for i in all_tittle:178                 new_people_info_str += '%s,'%d[i]179             new_people_info_str = new_people_info_str.strip(',') + '\n'180         f.write(new_people_info_str)181         return 1182 183 184 @add_func_dic('delete')185 def delete(cmd1):186     """187     处理删除命令，通过匹配符合条件的方法，不符合的加入新的列表中，等待重新写入188     :param cmd1: 用户输入的命令189     :return: 1190     """191     cmd_dic = resolve_cmd(cmd1)192     cmd, file_name, select_condetion = cmd_dic['cmd'], cmd_dic['file_name'], cmd_dic['select_condetion']193 194     if select_condetion:195         select_condetion = select_condetion.replace('=', '==')196         if 'like' in select_condetion:               # 如果判断条件有 like 就把   enroll_date like "2013"  改成 “2013” in enroll_date ，方便后面 eval 计算197             select_condetion = deal_select_condetion(select_condetion)198     else:199         print("请输入条件。。。。")200         return201 202     file_info_list = get_file_info_to_list(file_name)   # 获取文件相关参数203     new_file_list = []            # 筛选出新信息后重新写入204     total_count = 0205     for d in file_info_list:206         globals().update(d)207         if not eval(select_condetion):208             new_file_list.append(d)209         else:210             total_count += 1211     add_new_people(all_tittle,new_file_list,file_name,open_with='w')212     print('\033[1m删除了%s条内容。。。\033[0m' % total_count)213     return 1214 215 216 def update_split(cmd):217     """218     处理用户更新信息时候的命令，将它分割开219     :param cmd: 用户输入的信息220     :return: 分割好的内容 file_name, change_thing, select_condetion221     """222     cmd_dic={}223     cmd_dic['file_name'] = cmd.split('set')[0].split('update')[1].strip()224     cmd_dic['change_thing'] = cmd.split('where')[0].split('set')[1].strip().split('=')225     if 'staff_id' in cmd_dic['change_thing'][1]:226         print('哥，为了方便程序运行，我禁止了修改 ID 。。。。')227         return228     if 'where' not in cmd:229         print('没有输入范围(where)。。。')230         return231     cmd_dic['select_condetion'] = cmd.split('where')[1].strip()232     if 'age' in cmd_dic['change_thing']:233         if not cmd_dic['change_thing'][1].isdigit():234             print('年龄要是个数字。。。。')235             return236     return cmd_dic['file_name'], cmd_dic['change_thing'], cmd_dic['select_condetion']237 238 @add_func_dic('update')239 def update(cmd):240     """241     这个程序用来修改数据库信息242     :param cmd: 用户输入的命令243     :return: 1244     """245     cmd_dic = update_split(cmd)246     if not cmd_dic:247         return248     file_name, change_thing, select_condetion = update_split(cmd)249     select_condetion = select_condetion.replace('=','==')250     if 'like' in select_condetion:  # 如果判断条件有 like 就把   enroll_date like "2013"  改成 “2013” in enroll_date ，方便后面 eval 计算251         select_condetion = deal_select_condetion(select_condetion)252     file_info_list = get_file_info_to_list(file_name)253     total_count = 0254     for i,d in enumerate(file_info_list):255         globals().update(d)256         if eval(select_condetion):257             file_info_list[i][change_thing[0].strip()] = change_thing[1].strip()258             total_count += 1259 260     add_new_people(all_tittle,file_info_list, file_name, open_with = 'w')261     print('\033[1m修改了%s条内容。。。\033[0m' % total_count)262 263 264 def main():265     while True:266         with open(readme_path,encoding='utf-8') as f:267             for i in f:268                 print(i.strip())269         while True:270             cmd = input("\033[31;1m>>>\033[0m").strip()271             if cmd.upper() == 'Q':272                 exit('谢谢使用')273             elif cmd.upper() == 'R':274                 break275             try:276                 func_dic[cmd.strip().split()[0]](cmd)277             except Exception:278                 print('输入错误。。。')279             time.sleep(0.3)280 281 if __name__ == "__main__":282     main()