#!/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
