python制作表格的语句_初学 python 之 模拟sql语句实现对员工表格的增删改查

具体需求:

员工信息表程序，实现增删改查操作：

可进行模糊查询，语法支持下面3种:

select name,age from staff_data where age > 22                  多个查询参数name,age 用','分割

select * from staff_data where dept = 人事

select * from staff_data where enroll_date like 2013

查到的信息，打印后，最后面还要显示查到的条数

可创建新员工纪录，以phone做唯一键，phone存在即提示,staff_id需自增,添加多个记录record1/record2中间用'/'分割

insert into staff_data values record1/record2

可删除指定员工信息纪录，输入员工id，即可删除

delete from staff_data where staff_id>=5andstaff_id<=10

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

update staff_table set dept=Market,phone=13566677787  where dept = 运维   多个set值用','分割

使用re模块,os模块,充分使用函数精简代码,熟练使用 str.split()来解析格式化字符串

由于,sql命令中的几个关键字符串有一定规律,只出现一次,并且有顺序!!!

按照key_lis = ['select', 'insert', 'delete', 'update', 'from', 'into', 'set', 'values', 'where', 'limit']的元素顺序分割sql.

分割元素作为sql_dic字典的key放进字典中.分割后的列表为b,如果len(b)>1,说明sql字符串中含有分割元素,同时b[0]对应上一个分割元素的值,b[-1]为下一次分割对象!

这样不断迭代直到把sql按出现的所有分割元素分割完毕,但注意这里每次循环都是先分割后赋值!!!当前分割元素比如'select'对应的值,需要等到下一个分割元素

比如'from'执行分割后的列表b,其中b[0]的值才会赋值给sql_dic['select'],所以最后一个分割元素的值,不能通过上述循环来完成,必须先处理可能是最后一个分割元素,再正常循环!!

在这sql语句中,有可能成为最后一个分割元素的 'limit' ,'values', 'where',  按优先级别,先处理'limit' ,再处理'values'或 'where'.....

处理完得到sql_dic后,就是你按不同命令执行,对数据文件的增删改查,最后返回处理结果!!

1 #_*_coding:utf-8_*_

2 #Author:Jaye He

3 importre4 importos5

6

7 defsql_parse(sql, key_lis):8 '''

9 解析sql命令字符串,按照key_lis列表里的元素分割sql得到字典形式的命令sql_dic10 :param sql:11 :param key_lis:12 :return:13 '''

14 sql_list =[]15 sql_dic ={}16 for i inkey_lis:17 b = [j.strip() for j insql.split(i)]18 if len(b) > 1:19 if len(sql.split('limit')) > 1:20 sql_dic['limit'] = sql.split('limit')[-1]21 if i == 'where' or i == 'values':22 sql_dic[i] = b[-1]23 ifsql_list:24 sql_dic[sql_list[-1]] =b[0]25 sql_list.append(i)26 sql = b[-1]27 else:28 sql =b[0]29 if sql_dic.get('select'):30 if not sql_dic.get('from') and not sql_dic.get('where'):31 sql_dic['from'] = b[-1]32 if sql_dic.get('select'):33 sql_dic['select'] = sql_dic.get('select').split(',')34 if sql_dic.get('where'):35 sql_dic['where'] = where_parse(sql_dic.get('where'))36 returnsql_dic37

38

39 defwhere_parse(where):40 '''

41 格式化where字符串为列表where_list,用'and', 'or', 'not'分割字符串42 :param where:43 :return:44 '''

45 casual_l =[where]46 logic_key = ['and', 'or', 'not']47 for j inlogic_key:48 for i incasual_l:49 if i not inlogic_key:50 if len(i.split(j)) > 1:51 ele =i.split(j)52 index =casual_l.index(i)53 casual_l.pop(index)54 casual_l.insert(index, ele[0])55 casual_l.insert(index+1, j)56 casual_l.insert(index+2, ele[1])57 casual_l = [k for k in casual_l ifk]58 where_list =three_parse(casual_l, logic_key)59 returnwhere_list60

61

62 defthree_parse(casual_l, logic_key):63 '''

64 处理临时列表casual_l中具体的条件,'staff_id>5'-->['staff_id','>','5']65 :param casual_l:66 :param logic_key:67 :return:68 '''

69 where_list =[]70 for i incasual_l:71 if i not inlogic_key:72 b = i.split('like')73 if len(b) > 1:74 b.insert(1, 'like')75 where_list.append(b)76 else:77 key = ['']78 new_lis =[]79 opt = ''

80 lis = [j for j in re.split('([=<>])', i) ifj]81 for k inlis:82 if k inkey:83 opt +=k84 else:85 new_lis.append(k)86 new_lis.insert(1, opt)87 where_list.append(new_lis)88 else:89 where_list.append(i)90 returnwhere_list91

92

93 defsql_action(sql_dic, title):94 '''

95 把解析好的sql_dic分发给相应函数执行处理96 :param sql_dic:97 :param title:98 :return:99 '''

100 key = {'select': select,101 'insert': insert,102 'delete': delete,103 'update': update}104 res =[]105 for i insql_dic:106 if i inkey:107 res =key[i](sql_dic, title)108 returnres109

110

111 defselect(sql_dic, title):112 '''

113 处理select语句命令114 :param sql_dic:115 :param title:116 :return:117 '''

118 with open('staff_data', 'r', encoding='utf-8') as fh:119 filter_res = where_action(fh, sql_dic.get('where'), title)120 limit_res = limit_action(filter_res, sql_dic.get('limit'))121 search_res = search_action(limit_res, sql_dic.get('select'), title)122 returnsearch_res123

124

125 definsert(sql_dic, title):126 '''

127 处理insert语句命令128 :param sql_dic:129 :param title:130 :return:131 '''

132 with open('staff_data', 'r+', encoding='utf-8') as f:133 data =f.readlines()134 phone_list = [i.strip().split(',')[4] for i indata]135 ins_count =0136 if notdata:137 new_id = 1

138 else:139 last = data[-1]140 last_id = int(last.split(',')[0])141 new_id = last_id+1

142 record = sql_dic.get('values').split('/')143 for i inrecord:144 if i.split(',')[3] inphone_list:145 print('\033[1;31m%s 手机号已存在\033[0m' %i)146 else:147 new_record = '%s,%s\n' %(str(new_id), i)148 f.write(new_record)149 new_id += 1

150 ins_count += 1

151 f.flush()152 return ['insert successful'], [str(ins_count)]153

154

155 defdelete(sql_dic, title):156 '''

157 处理delete语句命令158 :param sql_dic:159 :param title:160 :return:161 '''

162 with open('staff_data', 'r', encoding='utf-8') as r_file,\163 open('staff_data_bak', 'w', encoding='utf-8') as w_file:164 del_count =0165 for line inr_file:166 dic = dict(zip(title.split(','), line.split(',')))167 filter_res = logic_action(dic, sql_dic.get('where'))168 if notfilter_res:169 w_file.write(line)170 else:171 del_count += 1

172 w_file.flush()173 os.remove('staff_data')174 os.rename('staff_data_bak', 'staff_data')175 return ['delete successful'], [str(del_count)]176

177

178 defupdate(sql_dic, title):179 '''

180 处理update语句命令181 :param sql_dic:182 :param title:183 :return:184 '''

185 set_l = sql_dic.get('set').strip().split(',')186 set_list = [i.split('=') for i inset_l]187 update_count =0188 with open('staff_data', 'r', encoding='utf-8') as r_file,\189 open('staff_data_bak', 'w', encoding='utf-8') as w_file:190 for line inr_file:191 dic = dict(zip(title.split(','), line.strip().split(',')))192 filter_res = logic_action(dic, sql_dic.get('where'))193 iffilter_res:194 for i inset_list:195 k =i[0]196 v = i[-1]197 dic[k] =v198 line = [dic[i] for i in title.split(',')]199 update_count += 1

200 line = ','.join(line)+'\n'

201 w_file.write(line)202 w_file.flush()203 os.remove('staff_data')204 os.rename('staff_data_bak', 'staff_data')205 return ['update successful'], [str(update_count)]206

207

208 defwhere_action(fh, where_list, title):209 '''

210 具体处理where_list里的所有条件211 :param fh:212 :param where_list:213 :param title:214 :return:215 '''

216 res =[]217 if len(where_list) !=0:218 for line infh:219 dic = dict(zip(title.split(','), line.strip().split(',')))220 if dic['name'] != 'name':221 logic_res =logic_action(dic, where_list)222 iflogic_res:223 res.append(line.strip().split(','))224 else:225 res = [i.split(',') for i infh.readlines()]226 returnres227 pass

228

229

230 deflogic_action(dic, where_list):231 '''

232 判断数据文件中每一条是否符合where_list条件233 :param dic:234 :param where_list:235 :return:236 '''

237 logic =[]238 for exp inwhere_list:239 if type(exp) islist:240 exp_k, opt, exp_v =exp241 if exp[1] == '=':242 opt = '=='

243 logical_char = "'%s'%s'%s'" %(dic[exp_k], opt, exp_v)244 if opt != 'like':245 exp =str(eval(logical_char))246 else:247 if exp_v indic[exp_k]:248 exp = 'True'

249 else:250 exp = 'False'

251 logic.append(exp)252 res = eval(' '.join(logic))253 returnres254

255

256 deflimit_action(filter_res, limit_l):257 '''

258 用列表切分处理显示符合条件的数量259 :param filter_res:260 :param limit_l:261 :return:262 '''

263 iflimit_l:264 index =int(limit_l[0])265 res =filter_res[:index]266 else:267 res =filter_res268 returnres269

270

271 defsearch_action(limit_res, select_list, title):272 '''

273 处理需要查询并显示的title和相应数据274 :param limit_res:275 :param select_list:276 :param title:277 :return:278 '''

279 res =[]280 fields_list = title.split(',')281 if select_list[0] == '*':282 res =limit_res283 else:284 fields_list =select_list285 for data inlimit_res:286 dic = dict(zip(title.split(','), data))287 r_l =[]288 for i infields_list:289 r_l.append((dic[i].strip()))290 res.append(r_l)291 returnfields_list, res292

293

294 if __name__ == '__main__':295 with open('staff_data', 'r', encoding='utf-8') as f:296 title =f.readline().strip()297 key_lis = ['select', 'insert', 'delete', 'update', 'from', 'into', 'set', 'values', 'where', 'limit']298 whileTrue:299 sql = input('请输入sql命令,退出请输入exit:').strip()300 sql = re.sub(' ', '', sql)301 if len(sql) == 0:continue

302 if sql == 'exit':break

303 sql_dict =sql_parse(sql, key_lis)304 fields_list, fields_data =sql_action(sql_dict, title)305 print('\033[1;33m结果如下:\033[0m')306 print('-'.join(fields_list))307 for data infields_data:308 print('-'.join(data))