#!/usr/bin/env python
# _*_ coding:UTF-8 _*_
# __auth__: Dalhhin
# Python 3.5.2,Pycharm 2016.3.2
# 2017/05/15
import sys,os
def where(dbfile,where_list): #条件是一个list
def and_or_where(sub_where_list):
'''获取and或同时含有and、or关键字的条件'''
and_list = []
or_list = []
for i in sub_where_list:
if i == 'and':
and_index = sub_where_list.index(i)
and_list_temp1 = sub_where_list[:and_index]
and_list_temp2 = sub_where_list[and_index + 1:]
if 'or' in and_list_temp1:
and_list.append(and_list_temp2)
or_index = and_list_temp1.index('or')
or_list.append(and_list_temp1[:or_index])
and_list.append(and_list_temp1[or_index + 1:])
break
if 'or' in and_list_temp2:
and_list.append(and_list_temp1)
or_index = and_list_temp2.index('or')
and_list.append(and_list_temp2[:or_index])
or_list.append(and_list_temp2[or_index + 1:])
break
and_list.append(and_list_temp1)
and_list.append(and_list_temp2)
return and_list,or_list
def or_where(sub_where_list):
'''获取只有or条件下的关键字条件'''
if len(sub_where_list) > 4:
or_list = []
or_str_temp1 = ' '.join(sub_where_list)
or_list_temp2 = or_str_temp1.split('or')
for or_info in or_list_temp2:
or_list.append(or_info.split())
return 'or',or_list
else:
return 'or',sub_where_list
def and_where(sub_where_list):
'''获取只有and条件下的关键字条件'''
and_list = []
and_str = ' '.join(sub_where_list)
and_list_temp = and_str.split('and')
for and_info in and_list_temp:
and_list.append(and_info.split())
return 'and',and_list
def not_and_or_where(sub_where_list):
'''获取无and和or关键字的时候的条件'''
par_list = []
par_list.append(sub_where_list)
return None,par_list
def not_and_or_data(where_list):
'''获取没有and和or条件时匹配到的数据'''
where_tiaojian = not_and_or_where(where_list) # 交给函数处理，得到处理后的 条件元组
if 'not' not in where_tiaojian[1][0]: # 当条件没有not关键字的时候
field_name, mark, value = where_tiaojian[1][0] # 不取反那么就获取条件的字段，运算符，值
match_data = [] # 用于存放匹配的数据，最后进行展示
with open(dbfile, encoding='UTF-8') as fd:
for user_info in fd:
user_id, user_name, user_age, user_phone, user_job, user_date = user_info.strip().split(',')
if field_name == 'id': # 读取文件的每一行，判断条件的字段是否是id
if mark != '=': # 不取反
if eval('%s %s %s' % (user_id, mark, value)): # 针对条件字段，进行数据的过滤
match_data.append(user_info) # 存储匹配的数据
continue
else:
if user_id == value:
match_data.append(user_info)
continue
elif field_name == 'age':
if mark != '=':
if eval('%s %s %s' % (user_age, mark, value)):
match_data.append(user_info)
continue
else:
if user_age == value:
match_data.append(user_info)
continue
else:
print('条件仅支持对id,age字段进行过滤！')
break
return match_data
else: # 当关键字有not的时候，对数据进行取反
not_str, field_name, mark, value = where_tiaojian[1][0]
match_data = []
with open(dbfile, encoding='UTF-8') as fd:
for user_info in fd:
user_id, user_name, user_age, user_phone, user_job, user_date = user_info.strip().split(',')
if field_name == 'id':
if mark != '=':
if eval('%s %s %s' % (user_id, mark, value)):
match_data.append(user_info)
continue
else:
if user_id == value:
match_data.append(user_info)
continue
elif field_name == 'age':
if mark != '=':
if eval('%s %s %s' % (user_age, mark, value)):
match_data.append(user_info)
continue
else:
if user_age == value:
match_data.append(user_info)
continue
else:
print('条件仅支持对id,age字段进行过滤！')
break
with open(dbfile, encoding='UTF-8') as old_fd:
data = old_fd.readlines()
data = set(data)
match_data = set(match_data) # 利用set求出not的集合，逻辑是先取出所有的数据，然后取出非not的匹配的数据，然后求差集
match_data_not = data.difference(match_data)
match_data_not = list(match_data_not)
match_data_not.sort()
return match_data_not
def or_data(where_list):
'''获取or条件下匹配到的数据'''
if len(where_list) > 4:
where_tiaojian = or_where(where_list)
match_data_list = []
for or_where_list in where_tiaojian[1]:
field_name, mark, value = or_where_list
with open(dbfile, encoding='UTF-8') as fd:
for user_info in fd:
user_id, user_name, user_age, user_phone, user_job, user_date = user_info.strip().split(
',')
if field_name == 'id':
if mark != '=':
if eval('%s %s %s' % (user_id, mark, value)):
match_data_list.append(user_info)
continue
else:
if user_id == value:
match_data_list.append(user_info)
continue
elif field_name == 'age':
if mark != '=':
if eval('%s %s %s' % (user_age, mark, value)):
match_data_list.append(user_info)
continue
else:
if user_age == value:
match_data_list.append(user_info)
continue
else:
print('条件仅支持对id,age字段进行过滤！')
break
match_data_set = set(match_data_list)
match_data_list = list(match_data_set)
match_data_list.sort()
return match_data_list
else:
where_tiaojian = or_where(where_list)
or_where_list = where_tiaojian[1][0]
match_data_list = []
field_name, mark, value = or_where_list
with open(dbfile, encoding='UTF-8') as fd:
for user_info in fd:
user_id, user_name, user_age, user_phone, user_job, user_date = user_info.strip().split(',')
if field_name == 'id':
if mark != '=':
if eval('%s %s %s' % (user_id, mark, value)):
match_data_list.append(user_info)
continue
else:
if user_id == value:
match_data_list.append(user_info)
continue
elif field_name == 'age':
if mark != '=':
if eval('%s %s %s' % (user_age, mark, value)):
match_data_list.append(user_info)
continue
else:
if user_age == value:
match_data_list.append(user_info)
continue
else:
print('条件仅支持对id,age字段进行过滤！')
break
return match_data_list
def and_data(where_list):
'''获取and条件下匹配到的数据'''
where_tiaojian = and_where(where_list)
with open(dbfile, encoding='UTF-8') as fd:
match_data = fd.readlines()
match_data_set = set(match_data) # 获取所有数据
for and_where_list in where_tiaojian[1]:
match_data_list = []
field_name, mark, value = and_where_list
with open(dbfile, encoding='UTF-8') as fd:
for user_info in fd:
user_id, user_name, user_age, user_phone, user_job, user_date = user_info.strip().split(',')
if field_name == 'id':
if mark != '=':
if eval('%s %s %s' % (user_id, mark, value)):
match_data_list.append(user_info)
continue
else:
if user_id == value:
match_data_list.append(user_info)
continue
elif field_name == 'age':
if mark != '=':
if eval('%s %s %s' % (user_age, mark, value)):
match_data_list.append(user_info)
continue
else:
if user_age == value:
match_data_list.append(user_info)
continue
else:
print('条件仅支持对id,age字段进行过滤！')
break
match_data_temp = set(match_data_list)
match_data_set = match_data_set.intersection(match_data_temp) # 每次循环利用集合的并集来求出and的语句
match_data = list(match_data_set)
match_data.sort() # 排序
return match_data
#对条件进行and，or，not，等情况的综合判断，不同的条件组合调用不同的函数组合
if 'where' in where_list: #首先判断带where关键字的条件
where_list.remove('where')
if 'like' in where_list: #对like关键字进行判断
data = []
field, _, value = where_list
with open(dbfile, encoding='utf-8') as fd:
context = fd.readlines()
for ele in context:
_,name,_,_,_,_ = ele.split(',')
if name.find(value) != -1:
data.append(ele)
return data
elif 'and' in where_list and 'or' in where_list: #对and和or条件同时存在时进行判断
and_list, or_list = and_or_where(where_list)
and_where_str = ''
for and_info in and_list:
and_str = ' '.join(and_info)
and_where_str += ' ' + and_str + ' ' + 'and'
if and_where_str.endswith('and'):
and_list = and_where_str.split()[:-1]
and_data_list = and_data(and_list)
or_data_list = or_data(or_list)
and_data_set = set(and_data_list)
or_data_set = set(or_data_list)
show_data = and_data_set.union(or_data_set)
return show_data
elif 'and' not in where_list and 'or' not in where_list: # 不包含and和or关键字的条件
data = not_and_or_data(where_list)
return data
elif 'or' in where_list and 'and' not in where_list: # 仅包含or的条件
data = or_data(where_list)
return data
elif 'and' in where_list and 'or' not in where_list: #仅有and关键字的时候的条件
data = and_data(where_list)
return data
elif 'limit' in where_list: #对limit条件进行判断
limits = where_list[-1]
data = []
with open(dbfile,encoding='UTF-8') as fd:
context = fd.readlines()
for i in range(int(limits)):
data.append(context[i])
return data
#增删改查逻辑处理
def insert(sqlcmd):
'''插入操作'''
try:
cmd = sqlcmd.strip().split() #获取输入字符的list形式
# print(cmd)
into = cmd[1] #SQL关键字
dbfile = cmd[2].replace('.','/') #目标库/表文件
command = cmd[3] #SQL关键字
values = cmd[4] #插入的数据
if not into == 'into' or not command == 'values': #语法关键字判断
print('Syntax error,Please check')
else:
with open(dbfile,encoding='UTF-8') as fd: #读取目前文件的ID
old_id = len(fd.readlines())
with open(dbfile,'a+',encoding='UTF-8') as fd: #拼成目标字符串然后追加至文件末尾
id = ( old_id + 1 )
name,age,phone,job,sqltime = values.split(',')
info = ['{}'.format(id),'{}'.format(name),'{}'.format(age),'{}'.format(phone),'{}'.format(job),'{}'.format(sqltime)]
info = ','.join(info)
fd.write('\n{}'.format(info))
print("insert [ {} ] Successful!".format(info))
except (IndexError,ValueError) : #当插入的数据不满足需求，则进行异常抓取并提示
print('缺少数据：请按照如下格式插入：insert into table values 姓名,年龄,电话,工作,时间\n'.expandtabs(20))
except FileNotFoundError: #当表不存在时，则进行异常抓取并提示
print('插入的数据库或表不存在，请检查！')
def delete(sqlcmd):
'''删除操作'''
sql, where_list = sqlcmd.split()[:3], sqlcmd.split()[3:]
_, mark, dbfile = sql
if where_list:
if dbfile.find('.') == -1: # 判断数据库和表之间的分隔符是否是.
print('库名和表明之间请使用.分隔！')
else:
dbfile = dbfile.replace('.', '/') # 修改表名的故事 db1/emp
data = where(dbfile, where_list)
with open(dbfile,encoding='utf-8') as fd:
old_data = fd.readlines()
with open(dbfile,'w',encoding='UTF-8') as fd :
for not_del in old_data:
if not_del in data:
continue
else:
fd.write(not_del)
else:
print('不允许不加条件的删除操作！')
def update(sqlcmd):
'''更新操作'''
sql,where_list = sqlcmd.split()[:6],sqlcmd.split()[6:]
_,dbfile,key,field,mark,value = sql
if where_list:
if dbfile.find('.') == -1: # 判断数据库和表之间的分隔符是否是.
print('库名和表明之间请使用.分隔！')
else:
dbfile = dbfile.replace('.', '/') # 修改表名的故事 db1/emp
data = where(dbfile,where_list)
id,name,age,phone,job,date = data[0].split(',')
update_data = data[0].replace(name,value)
with open(dbfile,encoding='utf-8') as fd:
old_data = fd.readlines()
with open('{}.swap'.format(dbfile),'w',encoding='UTF-8') as fd :
for new_data in old_data:
if new_data == data[0]:
write_data = update_data
else:
write_data = new_data
fd.write(write_data)
os.remove(dbfile)
os.rename('{}.swap'.format(dbfile), dbfile)
def select(sqlcmd):
sql,where_list = sqlcmd.split()[:4],sqlcmd.split()[4:]
_,field,mark,dbfile = sql
if where_list:
if dbfile.find('.') == -1: # 判断数据库和表之间的分隔符是否是.
print('库名和表明之间请使用.分隔！')
else:
dbfile = dbfile.replace('.', '/') # 修改表名的故事 db1/emp
data = where(dbfile,where_list)
if data:
for ele in data:
if field == '*':
print(ele, end='')
else:
id, name, age, phone, job, date = ele.strip().split(',') # 获取展示数据的每一个字段内容，进行匹配
src_field_list = field.split(',')
field_check = set(['id', 'age', 'phone', 'job', 'date', 'name'])
src_file_check = set(src_field_list)
if not src_file_check.issubset(field_check): # 字段检查，利用set的子集实现
print('文件中未包含指定的字段名，请输入:id,age,phone,job,data,name')
break
show_match_data = [] # 存放最终经过用户指定的展示字段匹配后的的数据
show_match_temp = [] # 展示数据临时列表
for show_field in src_field_list:
if show_field == 'id': # 符合展示字段的要求
show_match_temp.append(id) # 加入最终要显示的数据列表中
elif show_field == 'age':
show_match_temp.append(age)
elif show_field == 'name':
show_match_temp.append(name)
elif show_field == 'phone':
show_match_temp.append(phone)
elif show_field == 'job':
show_match_temp.append(job)
elif show_field == 'date':
show_match_temp.append(date)
show_match_data.append(show_match_temp) # 每循环一次，把符合的数据添加到 展示数据临时列表
for ele in show_match_data: # 展示数据
print(','.join(ele))
else:
print()
else:
if dbfile.find('.') == -1: # 判断数据库和表之间的分隔符是否是.
print('库名和表明之间请使用.分隔！')
else:
dbfile = dbfile.replace('.', '/') # 修改表名的故事 db1/emp
with open(dbfile,encoding='UTF-8') as fd:
for ele in fd:
print(ele,end='')
if __name__ == '__main__':
while True:
sqlcmd = input('SQL> ').strip()
if sqlcmd == '':
continue
sqltype = sqlcmd.split() #截取sql语句类型，根据类型调用不同模块
if sqltype[0] == 'select':
select(sqlcmd)
elif sqltype[0] == 'delete':
delete(sqlcmd)
elif sqltype[0] == 'update':
update(sqlcmd)
elif sqltype[0] == 'insert':
insert(sqlcmd)
elif sqltype[0] == 'exit':
sys.exit('欢迎下次登陆')
else:
print('用法：\n\t1、查询：select field from tablename where 条件\n\t' \
'2、增加：insert into tablename values name,age,phone,job,date\n\t' \
'3、更新：update tablename set field = value where 条件\n\t' \
'4、删除：delete from tablename where 条件\n\t' \
'5、退出：exit')
