import re import json ''' 员工表只设置了staff_id、staff_name、age、phone字段 其中phone是唯一的 1、查询支持select * from staff_table where age < 100；select staff_name,age from staff_table where staff_name = "diaochan";
select staff_name,age from staff_table where age > 10;select * from staff_table where staff_name like "xiang";
2、修改支持update staff_table set staff_name = "zhangsan" where staff_name = "xiangyu"；(只写了修改姓名) 3、因为切割的原因 age > 22 必须间隔空格 ''' select = re.compile(r'(select .+ from staff_table where .+\s*\=?\>?\ \*{1})|[^select]\s*(?P (\w+\,{1}\w+){1})') #匹配select后的内容 * 或者 字段 sn = re.compile(r'(\w+\s+\>+\s+\"?\'?\w+\"?\'?)|(\w+\s+\=+\s+\"?\'?\w+\"?\'?)|(\w+\s+\<+\s+\"?\'?\w+\"?\'?)|(\w+\s*(like)+\s*\"?\'?\w+\"?\'?)') #匹配<、>、=、like的前后内容 name like "zhang" up = re.compile(r'(?P (\w+\s*\=\s*\w+))') #匹配=的规则 rm = re.compile(r'[^"]\w*') #脱双引号的规则 def re_sql(sql): ''' 该函数实现sql语句的正则判断 :param sql: :return: ''' tf = select.search(sql) tf1 = modify.search(sql) if tf or tf1: return True else: return False def find_staff(sql): ''' 该函数实现了查询员工，支持select name,age from staff_table where age < 100 select * from staff_table where name = "yuji" select * from staff_table where name like xiang :param sql: :return: ''' staff = load_staff() #文件中加载所有用户信息 count = 0 you_need = seek.search(sql).groupdict() if you_need["key"] != None: #如果匹配到* you_sn = sn.search(sql).group() res = re.split(r'\s',you_sn) for temp in res: if "like" in temp: #如果匹配的是like for st in staff: if rm.search(res[2]).group() in st[res[0]]: print(st) count += 1 elif "=" in temp: #如果匹配的是 = for st in staff: if rm.search(res[2]).group() == st[res[0]]: print(st) count += 1 elif ">" in temp: #如果匹配的是 > for st in staff: # if int(re.search(r'[^\"?]\w+[^\"?]',res[2]).group()) > int(st[res[0]]): if int(re.search(r'[^\"?]\w*', res[2]).group()) < int(st[res[0]]): print(st) count += 1 elif "<" in temp: #如果匹配的是 < for st in staff: if rm.search(res[2]).group() > st[res[0]]: print(st) count += 1 elif you_need["key1"] != None: #如果匹配的是字段 age、staff_name you_sn = sn.search(sql).group() res = re.split(r'\s', you_sn) u_key = re.split(r'\,' ,you_need["key1"]) for temp in res: if "like" in temp: for st in staff: if rm.search(res[2]).group() in st[res[0]]: print(st[u_key[1]], st[u_key[0]]) count += 1 elif "=" in temp: for st in staff: if rm.search(res[2]).group() == st[res[0]]: print(st[u_key[1]], st[u_key[0]]) count += 1 elif ">" in temp: for st in staff: if int(re.search(r'[^\"?]\w*', res[2]).group()) > int(st[res[0]]): print(st[u_key[1]], st[u_key[0]]) count += 1 elif "<" in temp: for st in staff: if rm.search(res[2]).group() < st[res[0]]: print(st[u_key[1]],st[u_key[0]]) count += 1 print("共查询出%d条数据"%count) #打印查询出的条数 def add_staff(): ''' 该函数实现了添加员工，需要输入员工姓名，年龄，手机号码，其中手机号码是唯一，姓名输入英文，为了查询时like用 :return: ''' staff_list = [] staff_dict = {} name = input("请输入员工姓名 >>>>") age = input("请输入员工年龄 >>>>") phone = input("请输入员工手机号码 >>>>") staff = load_staff() for i in range(len(staff)): if staff[i]["phone"] == phone: #判断号码是否重复 print("手机号码不能重复") exit() staff_id = staff[i]["staff_id"] staff_list.append(staff[i]) staff_id = str(int(staff_id) + 1) #staff_id 自增加 staff_dict["staff_id"] = staff_id staff_dict["staff_name"] = name staff_dict["age"] = age staff_dict["phone"] = phone staff_list.append(staff_dict) #json序列化的时候只能一行，所以用列表保存信息 dump_staff(staff_list) print("添加成功") def remove_staff(id): ''' 该函数实现了删除员工，使用员工id :param id: :return: ''' staff_list = [] staff = load_staff() flag = 0 for i in range(len(staff)): if staff[i]["staff_id"] == id: #如果找到输入的id则跳出此次循环，不加入列表 flag = 1 continue staff_list.append(staff[i]) if flag == 0: #如果没有匹配到输出没有该id的员工 print("没有id：%s的员工"%id) else: dump_staff(staff_list) #否则json保存信息 def modify_staff(sql): ''' 该函数实现了修改名字 update staff_table set staff_name = "xiangyu" where staff_name="jack" :param sql: :return: ''' staff_list = [] staff = load_staff() s = re.findall(r'\w+\s*\=\s*\"?\w+\"?',sql) if s != None: ps = re.split(r'\s*\=?\s*',s[0]) rs = re.split(r'\s*\=?\s*',s[1]) for m in range(len(staff)): if staff[m][ps[0]] == rm.search(rs[1]).group(): staff[m][ps[0]] = rm.search(ps[1]).group() staff_list.append(staff[m]) dump_staff(staff_list) print("员工信息修改成功") def dump_staff(s): with open("员工信息表","w",encoding='utf-8') as f: json.dump(s,f) def load_staff(): with open("员工信息表",encoding='utf-8') as f: return json.load(f) def print_info(): info = ''' **********欢迎登陆员工信息中心*********** 1.查询员工 2.新增员工 3.修改员工 4.删除员工 5.退出 ''' print(info) def main(): print_info() choise = input("请输入操作 >>>>") if choise == "1": sql = input("请输入对应的sql >>>") result = re_sql(sql) if result: find_staff(sql) else: print("sql语法错误") elif choise == "2": add_staff() elif choise == "3": sql = input("请输入对应的sql >>>") result = re_sql(sql) if result: modify_staff(sql) else: print("sql语法错误") elif choise == "4": id = input("请输入需要删除的员工id >>>") remove_staff(id) elif choise == "5": exit("退出系统") else: print("输入有误，请重新输入") while True: main()