Python 模拟SQL对文件进行增删改查
#!/usr/bin/env python
2 # _*_ coding:UTF-8 _*_
3 # __auth__: Dalhhin
4 # Python 3.5.2,Pycharm 2016.3.2
5 # 2017/05/15
6
7 import sys,os
8
9 def where(dbfile,where_list): #条件是一个list
10
11 def and_or_where(sub_where_list):
12 ‘‘‘获取and或同时含有and、or关键字的条件‘‘‘
13 and_list = []
14 or_list = []
15 for i in sub_where_list:
16 if i == ‘and‘:
17 and_index = sub_where_list.index(i)
18 and_list_temp1 = sub_where_list[:and_index]
19 and_list_temp2 = sub_where_list[and_index + 1:]
20 if ‘or‘ in and_list_temp1:
21 and_list.append(and_list_temp2)
22 or_index = and_list_temp1.index(‘or‘)
23 or_list.append(and_list_temp1[:or_index])
24 and_list.append(and_list_temp1[or_index + 1:])
25 break
26 if ‘or‘ in and_list_temp2:
27 and_list.append(and_list_temp1)
28 or_index = and_list_temp2.index(‘or‘)
29 and_list.append(and_list_temp2[:or_index])
30 or_list.append(and_list_temp2[or_index + 1:])
31 break
32 and_list.append(and_list_temp1)
33 and_list.append(and_list_temp2)
34 return and_list,or_list
35
36 def or_where(sub_where_list):
37 ‘‘‘获取只有or条件下的关键字条件‘‘‘
38 if len(sub_where_list) > 4:
39 or_list = []
40 or_str_temp1 = ‘ ‘.join(sub_where_list)
41 or_list_temp2 = or_str_temp1.split(‘or‘)
42 for or_info in or_list_temp2:
43 or_list.append(or_info.split())
44 return ‘or‘,or_list
45 else:
46 return ‘or‘,sub_where_list
47
48 def and_where(sub_where_list):
49 ‘‘‘获取只有and条件下的关键字条件‘‘‘
50 and_list = []
51 and_str = ‘ ‘.join(sub_where_list)
52 and_list_temp = and_str.split(‘and‘)
53 for and_info in and_list_temp:
54 and_list.append(and_info.split())
55 return ‘and‘,and_list
56
57 def not_and_or_where(sub_where_list):
58 ‘‘‘获取无and和or关键字的时候的条件‘‘‘
59 par_list = []
60 par_list.append(sub_where_list)
61 return None,par_list
62
63 def not_and_or_data(where_list):
64 ‘‘‘获取没有and和or条件时匹配到的数据‘‘‘
65 where_tiaojian = not_and_or_where(where_list) # 交给函数处理，得到处理后的 条件元组
66 if ‘not‘ not in where_tiaojian[1][0]: # 当条件没有not关键字的时候
67 field_name, mark, value = where_tiaojian[1][0] # 不取反那么就获取条件的字段，运算符，值
68 match_data = [] # 用于存放匹配的数据，最后进行展示
69 with open(dbfile, encoding=‘UTF-8‘) as fd:
70 for user_info in fd:
71 user_id, user_name, user_age, user_phone, user_job, user_date = user_info.strip().split(‘,‘)
72 if field_name == ‘id‘: # 读取文件的每一行，判断条件的字段是否是id
73 if mark != ‘=‘: # 不取反
74 if eval(‘%s %s %s‘ % (user_id, mark, value)): # 针对条件字段，进行数据的过滤
75 match_data.append(user_info) # 存储匹配的数据
76 continue
77 else:
78 if user_id == value:
79 match_data.append(user_info)
80 continue
81 elif field_name == ‘age‘:
82 if mark != ‘=‘:
83 if eval(‘%s %s %s‘ % (user_age, mark, value)):
84 match_data.append(user_info)
85 continue
86 else:
87 if user_age == value:
88 match_data.append(user_info)
89 continue
90 else:
91 print(‘条件仅支持对id,age字段进行过滤！‘)
92 break
93 return match_data
94 else: # 当关键字有not的时候，对数据进行取反
95 not_str, field_name, mark, value = where_tiaojian[1][0]
96 match_data = []
97 with open(dbfile, encoding=‘UTF-8‘) as fd:
98 for user_info in fd:
99 user_id, user_name, user_age, user_phone, user_job, user_date = user_info.strip().split(‘,‘)
100 if field_name == ‘id‘:
101 if mark != ‘=‘:
102 if eval(‘%s %s %s‘ % (user_id, mark, value)):
103 match_data.append(user_info)
104 continue
105 else:
106 if user_id == value:
107 match_data.append(user_info)
108 continue
109 elif field_name == ‘age‘:
110 if mark != ‘=‘:
111 if eval(‘%s %s %s‘ % (user_age, mark, value)):
112 match_data.append(user_info)
113 continue
114 else:
115 if user_age == value:
116 match_data.append(user_info)
117 continue
118 else:
119 print(‘条件仅支持对id,age字段进行过滤！‘)
120 break
121 with open(dbfile, encoding=‘UTF-8‘) as old_fd:
122 data = old_fd.readlines()
123 data = set(data)
124 match_data = set(match_data) # 利用set求出not的集合，逻辑是先取出所有的数据，然后取出非not的匹配的数据，然后求差集
125 match_data_not = data.difference(match_data)
126 match_data_not = list(match_data_not)
127 match_data_not.sort()
128 return match_data_not
129
130 def or_data(where_list):
131 ‘‘‘获取or条件下匹配到的数据‘‘‘
132 if len(where_list) > 4:
133 where_tiaojian = or_where(where_list)
134 match_data_list = []
135 for or_where_list in where_tiaojian[1]:
136 field_name, mark, value = or_where_list
137 with open(dbfile, encoding=‘UTF-8‘) as fd:
138 for user_info in fd:
139 user_id, user_name, user_age, user_phone, user_job, user_date = user_info.strip().split(
140 ‘,‘)
141 if field_name == ‘id‘:
142 if mark != ‘=‘:
143 if eval(‘%s %s %s‘ % (user_id, mark, value)):
144 match_data_list.append(user_info)
145 continue
146 else:
147 if user_id == value:
148 match_data_list.append(user_info)
149 continue
150 elif field_name == ‘age‘:
151 if mark != ‘=‘:
152 if eval(‘%s %s %s‘ % (user_age, mark, value)):
153 match_data_list.append(user_info)
154 continue
155 else:
156 if user_age == value:
157 match_data_list.append(user_info)
158 continue
159 else:
160 print(‘条件仅支持对id,age字段进行过滤！‘)
161 break
162 match_data_set = set(match_data_list)
163 match_data_list = list(match_data_set)
164 match_data_list.sort()
165 return match_data_list
166 else:
167 where_tiaojian = or_where(where_list)
168 or_where_list = where_tiaojian[1][0]
169 match_data_list = []
170 field_name, mark, value = or_where_list
171 with open(dbfile, encoding=‘UTF-8‘) as fd:
172 for user_info in fd:
173 user_id, user_name, user_age, user_phone, user_job, user_date = user_info.strip().split(‘,‘)
174 if field_name == ‘id‘:
175 if mark != ‘=‘:
176 if eval(‘%s %s %s‘ % (user_id, mark, value)):
177 match_data_list.append(user_info)
178 continue
179 else:
180 if user_id == value:
181 match_data_list.append(user_info)
182 continue
183 elif field_name == ‘age‘:
184 if mark != ‘=‘:
185 if eval(‘%s %s %s‘ % (user_age, mark, value)):
186 match_data_list.append(user_info)
187 continue
188 else:
189 if user_age == value:
190 match_data_list.append(user_info)
191 continue
192 else:
193 print(‘条件仅支持对id,age字段进行过滤！‘)
194 break
195 return match_data_list
196
197 def and_data(where_list):
198 ‘‘‘获取and条件下匹配到的数据‘‘‘
199 where_tiaojian = and_where(where_list)
200 with open(dbfile, encoding=‘UTF-8‘) as fd:
201 match_data = fd.readlines()
202 match_data_set = set(match_data) # 获取所有数据
203 for and_where_list in where_tiaojian[1]:
204 match_data_list = []
205 field_name, mark, value = and_where_list
206 with open(dbfile, encoding=‘UTF-8‘) as fd:
207 for user_info in fd:
208 user_id, user_name, user_age, user_phone, user_job, user_date = user_info.strip().split(‘,‘)
209 if field_name == ‘id‘:
210 if mark != ‘=‘:
211 if eval(‘%s %s %s‘ % (user_id, mark, value)):
212 match_data_list.append(user_info)
213 continue
214 else:
215 if user_id == value:
216 match_data_list.append(user_info)
217 continue
218 elif field_name == ‘age‘:
219 if mark != ‘=‘:
220 if eval(‘%s %s %s‘ % (user_age, mark, value)):
221 match_data_list.append(user_info)
222 continue
223 else:
224 if user_age == value:
225 match_data_list.append(user_info)
226 continue
227 else:
228 print(‘条件仅支持对id,age字段进行过滤！‘)
229 break
230 match_data_temp = set(match_data_list)
231 match_data_set = match_data_set.intersection(match_data_temp) # 每次循环利用集合的并集来求出and的语句
232 match_data = list(match_data_set)
233 match_data.sort() # 排序
234 return match_data
235
236 #对条件进行and，or，not，等情况的综合判断，不同的条件组合调用不同的函数组合
237 if ‘where‘ in where_list: #首先判断带where关键字的条件
238 where_list.remove(‘where‘)
239 if ‘like‘ in where_list: #对like关键字进行判断
240 data = []
241 field, _, value = where_list
242 with open(dbfile, encoding=‘utf-8‘) as fd:
243 context = fd.readlines()
244 for ele in context:
245 _,name,_,_,_,_ = ele.split(‘,‘)
246 if name.find(value) != -1:
247 data.append(ele)
248 return data
249 elif ‘and‘ in where_list and ‘or‘ in where_list: #对and和or条件同时存在时进行判断
250 and_list, or_list = and_or_where(where_list)
251 and_where_str = ‘‘
252 for and_info in and_list:
253 and_str = ‘ ‘.join(and_info)
254 and_where_str += ‘ ‘ + and_str + ‘ ‘ + ‘and‘
255 if and_where_str.endswith(‘and‘):
256 and_list = and_where_str.split()[:-1]
257 and_data_list = and_data(and_list)
258 or_data_list = or_data(or_list)
259 and_data_set = set(and_data_list)
260 or_data_set = set(or_data_list)
261 show_data = and_data_set.union(or_data_set)
262 return show_data
263 elif ‘and‘ not in where_list and ‘or‘ not in where_list: # 不包含and和or关键字的条件
264 data = not_and_or_data(where_list)
265 return data
266 elif ‘or‘ in where_list and ‘and‘ not in where_list: # 仅包含or的条件
267 data = or_data(where_list)
268 return data
269 elif ‘and‘ in where_list and ‘or‘ not in where_list: #仅有and关键字的时候的条件
270 data = and_data(where_list)
271 return data
272
273 elif ‘limit‘ in where_list: #对limit条件进行判断
274 limits = where_list[-1]
275 data = []
276 with open(dbfile,encoding=‘UTF-8‘) as fd:
277 context = fd.readlines()
278 for i in range(int(limits)):
279 data.append(context[i])
280 return data
281
282 #增删改查逻辑处理
283 def insert(sqlcmd):
284 ‘‘‘插入操作‘‘‘
285 try:
286 cmd = sqlcmd.strip().split() #获取输入字符的list形式
287 # print(cmd)
288 into = cmd[1] #SQL关键字
289 dbfile = cmd[2].replace(‘.‘,‘/‘) #目标库/表文件
290 command = cmd[3] #SQL关键字
291 values = cmd[4] #插入的数据
292 if not into == ‘into‘ or not command == ‘values‘: #语法关键字判断
293 print(‘Syntax error,Please check‘)
294 else:
295 with open(dbfile,encoding=‘UTF-8‘) as fd: #读取目前文件的ID
296 old_id = len(fd.readlines())
297 with open(dbfile,‘a+‘,encoding=‘UTF-8‘) as fd: #拼成目标字符串然后追加至文件末尾
298 id = ( old_id + 1 )
299 name,age,phone,job,sqltime = values.split(‘,‘)
300 info = [‘{}‘.format(id),‘{}‘.format(name),‘{}‘.format(age),‘{}‘.format(phone),‘{}‘.format(job),‘{}‘.format(sqltime)]
301 info = ‘,‘.join(info)
302 fd.write(‘\n{}‘.format(info))
303 print("insert [ {} ] Successful!".format(info))
304 except (IndexError,ValueError) : #当插入的数据不满足需求，则进行异常抓取并提示
305 print(‘缺少数据：请按照如下格式插入：insert into table values 姓名,年龄,电话,工作,时间\n‘.expandtabs(20))
306 except FileNotFoundError: #当表不存在时，则进行异常抓取并提示
307 print(‘插入的数据库或表不存在，请检查！‘)
308
309 def delete(sqlcmd):
310 ‘‘‘删除操作‘‘‘
311 sql, where_list = sqlcmd.split()[:3], sqlcmd.split()[3:]
312 _, mark, dbfile = sql
313 if where_list:
314 if dbfile.find(‘.‘) == -1: # 判断数据库和表之间的分隔符是否是.
315 print(‘库名和表明之间请使用.分隔！‘)
316 else:
317 dbfile = dbfile.replace(‘.‘, ‘/‘) # 修改表名的故事 db1/emp
318 data = where(dbfile, where_list)
319 with open(dbfile,encoding=‘utf-8‘) as fd:
320 old_data = fd.readlines()
321 with open(dbfile,‘w‘,encoding=‘UTF-8‘) as fd :
322 for not_del in old_data:
323 if not_del in data:
324 continue
325 else:
326 fd.write(not_del)
327 else:
328 print(‘不允许不加条件的删除操作！‘)
329
330 def update(sqlcmd):
331 ‘‘‘更新操作‘‘‘
332 sql,where_list = sqlcmd.split()[:6],sqlcmd.split()[6:]
333 _,dbfile,key,field,mark,value = sql
334 if where_list:
335 if dbfile.find(‘.‘) == -1: # 判断数据库和表之间的分隔符是否是.
336 print(‘库名和表明之间请使用.分隔！‘)
337 else:
338 dbfile = dbfile.replace(‘.‘, ‘/‘) # 修改表名的故事 db1/emp
339 data = where(dbfile,where_list)
340 id,name,age,phone,job,date = data[0].split(‘,‘)
341 update_data = data[0].replace(name,value)
342 with open(dbfile,encoding=‘utf-8‘) as fd:
343 old_data = fd.readlines()
344 with open(‘{}.swap‘.format(dbfile),‘w‘,encoding=‘UTF-8‘) as fd :
345 for new_data in old_data:
346 if new_data == data[0]:
347 write_data = update_data
348 else:
349 write_data = new_data
350 fd.write(write_data)
351 os.remove(dbfile)
352 os.rename(‘{}.swap‘.format(dbfile), dbfile)
353
354 def select(sqlcmd):
355 sql,where_list = sqlcmd.split()[:4],sqlcmd.split()[4:]
356 _,field,mark,dbfile = sql
357