要求： 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 ...
要求：
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
