mysql 模拟_模拟MySQL命令

1 #! /usr/bin/env python

2 #-*- coding: utf-8 -*-

3 #__author__ = "Always"

4 #Date: 2017/6/16

5 importtime6

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 defadd_func_dic(keys):13 defdeco(func):14 func_dic[keys] =func15 returndeco16

17 defresolve_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() == "*" elsecmd_deal29 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 returncmd_dict34

35 defdeal_select_condetion(select_condetion):36 """

37 主要用来处理后面的条件有 like 的情况，为筛选的条件重新排序38 :param select_condetion:39 :return:40 """

41 select_condetion = select_condetion.split('and')42 for i inrange(len(select_condetion)):43 if 'like' inselect_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 defreturn_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 inall_tittle:59 if tittle inshow_tittle:60 print('%-14s' % d[tittle], end='')61 print()62 total_count += 1

63 returntotal_count64

65 @add_func_dic('select')66 defwhat_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 defselect(file_name, select_condetion, show_tittle):71 """

72 这个功能用来查找，并打印符合要求的字段73 :param file_name74 :param select_condetion75 :param show_tittle76 :return:77 """

78 file_info_list =get_file_info_to_list(file_name)79

80 for tittle in all_tittle: #打印表头

81 if tittle inshow_tittle:82 print('\033[44;1m%-14s' % tittle, end='')83 print('\033[0m')84 total_count =085

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 =False95 if eval(select_condetion): #如果满足输入的条件，那么打印信息

96

97 total_count =return_select_result(total_count,all_tittle,show_tittle, d)98 else: #如果用户没有输入筛选条件，执行这里

99 for d infile_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 inf]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 return

118 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 returncmd_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 notinsert_split(cmd):134 print('输入错误。。。')135 return

136 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 inkeys:141 print('哥，我也不想的，老师指定 phone 为唯一键，不输入不行啊。。。。')142 return

143

144 for i in file_info_list: #如果号码重复，重新输入

145 if i['phone'] == values[keys.index('phone')]:146 print('那啥，号码已注册，换一个吧。。亲。。。。')147 return

148 if 'age' in keys and not values[keys.index('age')].isdigit(): #判断年龄是不是数字

149 print('年龄需要为数字。。。')150 return

151

152 for i in all_tittle[1:]: #准备加入 新人物 的信息

153 if i inkeys: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 innew_people_info:177 for i inall_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 1

182

183

184 @add_func_dic('delete')185 defdelete(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 ifselect_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 return

201

202 file_info_list = get_file_info_to_list(file_name) #获取文件相关参数

203 new_file_list = [] #筛选出新信息后重新写入

204 total_count =0205 for d infile_info_list:206 globals().update(d)207 if noteval(select_condetion):208 new_file_list.append(d)209 else:210 total_count += 1

211 add_new_people(all_tittle,new_file_list,file_name,open_with='w')212 print('\033[1m删除了%s条内容。。。\033[0m' %total_count)213 return 1

214

215

216 defupdate_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 return

228 if 'where' not incmd:229 print('没有输入范围(where)。。。')230 return

231 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 return

236 return cmd_dic['file_name'], cmd_dic['change_thing'], cmd_dic['select_condetion']237

238 @add_func_dic('update')239 defupdate(cmd):240 """

241 这个程序用来修改数据库信息242 :param cmd: 用户输入的命令243 :return: 1244 """

245 cmd_dic =update_split(cmd)246 if notcmd_dic:247 return

248 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 inenumerate(file_info_list):255 globals().update(d)256 ifeval(select_condetion):257 file_info_list[i][change_thing[0].strip()] = change_thing[1].strip()258 total_count += 1

259

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 defmain():265 whileTrue:266 with open(readme_path,encoding='utf-8') as f:267 for i inf:268 print(i.strip())269 whileTrue:270 cmd = input("\033[31;1m>>>\033[0m").strip()271 if cmd.upper() == 'Q':272 exit('谢谢使用')273 elif cmd.upper() == 'R':274 break

275 try:276 func_dic[cmd.strip().split()[0]](cmd)277 exceptException:278 print('输入错误。。。')279 time.sleep(0.3)280

281 if __name__ == "__main__":282 main()