练习:员工信息表

要求：

  1 #_*_coding:utf-8_*_
  2 #第一部分:sql解析
  3 import os,time
  4 def sql_parse(sql):
  5     '''
  6     sql_parse--->insert_parse,delete_parse,update_parse,select_parse
  7     sql解析总控
  8     :param sql:用户输入的字符串
  9     :return: 返回字典格式sql解析结果
 10     '''''
 11     parse_func={
 12         'insert':insert_parse,
 13         'delete':delete_parse,
 14         'update':update_parse,
 15         'select':select_parse,
 16     }
 17     sql_l=sql.split(' ')
 18     func=sql_l[0]
 19     res=''
 20     if func in parse_func:
 21         res=parse_func[func](sql_l)
 22     return res
 23 
 24 def insert_parse(sql_l):
 25     # insert into db.t1 values 鲁海宝,35,13912015353,测试,2005-06-27
 26     '''
 27     sql解析分支:insert
 28     :param sql_l: sql按照空格分割的列表
 29     :return: 返回字典格式的sql解析结果
 30     '''''
 31     sql_dic={
 32         'func':insert, #函数名
 33         'insert':[],   #insert选项,留出扩展
 34         'into':[],     #表名
 35         'values':[],   #值
 36     }
 37     return handle_parse(sql_l,sql_dic)
 38 
 39 def delete_parse(sql_l):
 40     # delete from db.t1 where id=1
 41     '''
 42     sql解析分支:delete
 43     :param sql_l: sql按照空格分割的列表
 44     :return: 返回字典格式的sql解析结果
 45     '''''
 46     sql_dic={
 47         'func':delete,
 48         'delete':[], #delete选项,留出扩展
 49         'from':[],   #表名
 50         'where':[],  #filter条件
 51     }
 52     return handle_parse(sql_l,sql_dic)
 53 
 54 def update_parse(sql_l):
 55     # update db.t1 set id=2 where name='alex'
 56     '''
 57     sql解析分支:update
 58     :param sql_l: sql按照空格分割的列表
 59     :return: 返回字典格式的sql解析结果
 60     '''''
 61     sql_dic={
 62         'func':update,
 63         'update':[], #update选项,留出扩展
 64         'set':[],    #修改的值
 65         'where':[],  #filter条件
 66     }
 67     return handle_parse(sql_l,sql_dic)
 68 
 69 def select_parse(sql_l):
 70     # select * from db1.emp where not id= 1 and name = 'alex' or name= 'sb' limit 3
 71     '''
 72     sql解析分支:select
 73     :param sql_l: sql按照空格分割的列表
 74     :return: 返回字典格式的sql解析结果
 75     '''''
 76     sql_dic={
 77         'func':select,
 78         'select':[], #查询字段
 79         'from':[],   #表
 80         'where':[],  #filter条件
 81         'limit':[],  #limit条件
 82     }
 83     return handle_parse(sql_l,sql_dic)
 84 
 85 def handle_parse(sql_l,sql_dic):
 86     '''
 87     填充sql_dic
 88     :param sql_l: sql按照空格分割的列表
 89     :param sql_dic: 待填充的字典
 90     :return: 返回字典格式的sql解析结果
 91     '''''
 92     tag=False
 93     for item in sql_l:
 94         if tag and item in sql_dic:
 95             tag=False
 96         if not tag and item in sql_dic:
 97             tag=True
 98             key=item
 99             continue
100         if tag:
101             sql_dic[key].append(item)
102     # print('before \033[33;1m%s\033[0m' %sql_dic)
103     if sql_dic.get('where'):
104         sql_dic['where']=where_parse(sql_dic.get('where'))
105 
106     # print('after \033[33;1m%s\033[0m' %sql_dic)
107     return sql_dic
108 
109 def where_parse(where_l):
110     '''
111     对用户输入的where子句后的条件格式化,每个子条件都改成列表形式
112     :param where_l: 用户输入where后对应的过滤条件列表
113     :return:
114     '''''
115     res=[]
116     key=['and','or','not']
117     char=''
118     for i in where_l:
119         if len(i) == 0:continue
120         if i in key:
121             if len(char) != 0:
122                 char=three_parse(char) #将每一个小的过滤条件如,name>=1转换成['name','>=','1']
123                 res.append(char)
124             res.append(i)
125             char=''
126         else:
127           char+=i
128     else:
129         char=three_parse(char)
130         res.append(char)
131     return res
132 
133 def three_parse(exp_str):
134     '''
135     将每一个小的过滤条件如,name>=1转换成['name','>=','1']
136     :param exp_str:条件表达式的字符串形式,例如'name>=1'
137     :return:
138     '''''
139     # print('three_opt before is \033[34;1m%s\033[0m' %exp_str)
140     key=['>','=','<']
141     res=[]
142     char=''
143     opt=''
144     tag=False
145     for i in exp_str:
146         if i in key:
147             tag=True
148             if len(char) !=0:
149                 res.append(char)
150                 char=''
151             opt+=i
152         if not tag:
153             char+=i
154         if tag and i not in key:
155             tag=False
156             res.append(opt)
157             opt=''
158             char+=i
159     else:
160         res.append(char)
161     # print('res is %s ' %res)
162     #新增like功能
163     if len(res) == 1:#['namelike_ale5']
164         res=res[0].split('like')
165         res.insert(1,'like')
166     return res
167 
168 
169 #第二部分:sql执行
170 def sql_action(sql_dic):
171     '''
172     执行sql的统一接口,内部执行细节对用户完全透明
173     :param sql:
174     :return:
175     '''''
176     return sql_dic.get('func')(sql_dic)
177 
178 def insert(sql_dic):
179     # insert into db.emp values 李西昌,22,13822117767,运维,2012-01-10
180     print('insert %s' %sql_dic)
181     db,table=sql_dic.get('into')[0].split('.')
182     with open('%s/%s' %(db,table),'ab+') as fh:
183         offs = -100
184         while True:
185             fh.seek(offs,2)
186             lines = fh.readlines()
187             if len(lines)>1:
188                 last = lines[-1]
189                 break
190             offs *= 2
191         last=last.decode(encoding='utf-8')
192         last_id=int(last.split(',')[0])
193         print('insert_data',last.split(',')[-1])
194         new_id=last_id+1
195 
196         record=sql_dic.get('values')[0].split(',')
197 
198         record.insert(0,str(new_id))
199 
200         #['26', 'alex', '18', '13120378203', '运维', '2013-3-1\n']
201         record_str=','.join(record)+'\n'
202         fh.write(bytes(record_str,encoding='utf-8'))
203         fh.flush()
204     return [['insert successful']]
205 
206 def delete(sql_dic):
207     #delete from db.emp where id = 26
208     db,table=sql_dic.get('from')[0].split('.')
209     bak_file=table+'_bak'
210     with open("%s/%s" %(db,table),'r',encoding='utf-8') as r_file,\
211             open('%s/%s' %(db,bak_file),'w',encoding='utf-8') as w_file:
212         del_count=0
213         for line in r_file:
214             title="id,name,age,phone,dept,enroll_date"
215             dic=dict(zip(title.split(','),line.split(',')))
216             filter_res=logic_action(dic,sql_dic.get('where'))
217             if not filter_res:
218                 w_file.write(line)
219             else:
220                 del_count+=1
221         w_file.flush()
222     os.remove("%s/%s" % (db, table))
223     os.rename("%s/%s" %(db,bak_file),"%s/%s" %(db,table))
224     return [[del_count],['delete successful']]
225 
226 def update(sql_dic):
227     # update db.emp set age=24 where name like 李西昌
228     db,table=sql_dic.get('update')[0].split('.')
229     set=sql_dic.get('set')[0].split(',')
230     set_l=[]
231     for i in set:
232         set_l.append(i.split('='))
233     bak_file=table+'_bak'
234     with open("%s/%s" %(db,table),'r',encoding='utf-8') as r_file,\
235             open('%s/%s' %(db,bak_file),'w',encoding='utf-8') as w_file:
236         update_count=0
237         for line in r_file:
238             title="id,name,age,phone,dept,enroll_date"
239             dic=dict(zip(title.split(','),line.split(',')))
240             filter_res=logic_action(dic,sql_dic.get('where'))
241             if filter_res:
242                 for i in set_l:
243                     k=i[0]
244                     v=i[-1].strip("'")
245                     print('k v %s %s' %(k,v))
246                     dic[k]=v
247                 print('change dic is %s ' %dic)
248                 line=[]
249                 for i in title.split(','):
250                     line.append(dic[i])
251                 update_count+=1
252                 line=','.join(line)
253             w_file.write(line)
254 
255         w_file.flush()
256     os.remove("%s/%s" % (db, table))
257     os.rename("%s/%s" %(db,bak_file),"%s/%s" %(db,table))
258     return [[update_count],['update successful']]
259 
260 def select(sql_dic):
261 
262      db,table=sql_dic.get('from')[0].split('.')
263      fh = open("%s/%s" % (db, table), 'r', encoding='utf-8')
264 
265      filter_res=where_action(fh,sql_dic.get('where'))
266      fh.close()
267 
268      limit_res=limit_action(filter_res,sql_dic.get('limit'))
269      search_res=search_action(limit_res,sql_dic.get('select'))
270      return search_res
271 
272 
273 
274 def where_action(fh,where_l):
275     res=[]
276     logic_l=['and','or','not']
277     title="id,name,age,phone,dept,enroll_date"
278     if len(where_l) !=0:
279         for line in fh:
280             dic=dict(zip(title.split(','),line.split(',')))
281             logic_res=logic_action(dic,where_l)
282             if logic_res:
283                 res.append(line.split(','))
284     else:
285         res=fh.readlines()
286     return res
287 
288 def logic_action(dic,where_l):
289     res=[]
290     # print('==\033[45;1m%s\033[0m==\033[48;1m%s\033[0m' %(dic,where_l))
291     for exp in where_l:
292         if type(exp) is list:
293             exp_k,opt,exp_v=exp
294             if exp[1] == '=':
295                 opt='%s=' %exp[1]
296             if dic[exp_k].isdigit():
297                 dic_v=int(dic[exp_k])
298                 exp_v=int(exp_v)
299             else:
300                 dic_v="'%s'" %dic[exp_k]
301             if opt != 'like':
302                 exp=str(eval("%s%s%s" %(dic_v,opt,exp_v)))
303             else:
304                 if exp_v in dic_v:
305                     exp='True'
306                 else:
307                     exp='False'
308         res.append(exp)
309     res=eval(' '.join(res))
310     # print('==\033[45;1m%s\033[0m' %(res))
311     return res
312 
313 def limit_action(filter_res,limit_l):
314     res=[]
315     if len(limit_l) !=0:
316         index=int(limit_l[0])
317         res=filter_res[0:index]
318     else:
319         res=filter_res
320 
321     return res
322 
323 def search_action(limit_res,select_l):
324     res=[]
325     fileds_l=[]
326     title="id,name,age,phone,dept,enroll_date"
327     if select_l[0] == '*':
328         res=limit_res
329         fileds_l=title.split(',')
330     else:
331 
332         for record in limit_res:
333             dic=dict(zip(title.split(','),record))
334             # print("dic is %s " %dic)
335             fileds_l=select_l[0].split(',')
336             r_l=[]
337             for i in fileds_l:
338                 r_l.append(dic[i].strip())
339             res.append(r_l)
340 
341     return [fileds_l,res]
342 
343 def help():
344     msg = '''
345                        =_= 欢迎使用员工信息系统 =_=
346     本系统支持基本SQL语句查询，数据库为db，用户表名为user，必须使用列出的SQL语句操作，否则会出现未知错误
347     例：
348     查询： select * from db.user where name like 李 and id > 10
349            select name,id from db.user where id > 10 or age < 30
350     增加:  insert into db.user values Eric,22,17233786749,运维,2012-01-10
351     修改:  update db.user set age=24 where name like 李强
352            update db.user set dept=IT where name like 李强
353     删除:  delete from db.emp where id = 26
354     退出: exit()
355     帮助: help
356         '''
357     print(msg)
358 if __name__ == '__main__':
359     help()
360     while True:
361         sql=input("sql> ").strip()
362         if sql == 'exit':break
363         if sql == 'help': help()
364         if len(sql) == 0:continue
365 
366         sql_dic=sql_parse(sql)
367 
368         if len(sql_dic) == 0:continue
369         res=sql_action(sql_dic)
370 
371         for i in res[-1]:
372             print(i)

View Code

 

转载于:https://www.cnblogs.com/Xunmeng888/p/6385205.html