python练习——moudule02——员工信息增删改查

#作业名称：员工信息的增删改查#作业需求： 1.可进行模糊查询，语法至少支持下面3种:　　 select name,age from staff_table where age > 22　　 select * from staff_table where dept = "IT" select * from staff_table where enroll_date like "2013" 查到的信息，打印后，最后面还要显示查到的条数 2.可创建新员工纪录，以phone做唯一键，staff_id需自增 3.可删除指定员工信息纪录，输入员工id，即可删除 4.可修改员工信息，语法如下:　　 UPDATE staff_table SET dept="Market" WHERE where dept = "IT" #本次完成作业需求：用sql语句实现增删改查

代码如下：

#! /usr/bin/env python# -*- coding: utf-8 -*-# __author__ = "Q1mi"# Date: 2017/9/19import re,json# d={"1":{"name":"alex","age":'22','phone':'13651054608',"dept":"运维","enroll_data":"2013-04-01"}}# json.dump(d,sort_keys=True)#Json存储def store(data): with open('information.txt', 'w') as json_file: json_file.write(json.dumps(data))#Json加载def load(): with open('information.txt') as json_file: data = json.load(json_file) return data#判断phone是否重复def phone_repete(info1,info2): for item in info1: info1[item]["phone"]==info2["phone"] return True return False#将符合要求的查询结果转化为字符串#{'1': {'name': 'Mark', 'age': '22', 'phone': '13651054608', 'dept': 'IT', 'enroll_data': '2013-04-01'}}def get_final_result(show_item,show_list): if len(show_list)==0: return("没有符合条件的结果") show_final_list=[] show_final="" for item2 in show_list: for i in show_item: if i =="staff_id": show_final_list.append(item2) else: show_final_list.append(show_list[item2][i]) show_final_list.append("\n") show_final=",".join(show_final_list) return show_final#查询def fetch(data): #需要展示的项，如staff_id等 show_item=[] #筛选的初步结果，字典形式 show_list={} all_item=["staff_id","name","age","phone","dept","enroll_date"] data1=data.split(" ")#['select', '*', 'from', 'staff_table', 'where', 'dept', '=', '"IT"'] #判断是否是select语句 if data1[0]!="select": return("此语句不是select语句") #检索查询项及校验 if data1[1]=="*": show_item=all_item else: show_item=data1[1].split(",") for item in show_item: if item not in ["staff_id","name","age","phone","dept","enroll_date"]: return("输入的查询项%s有误" %item) #校验查询的表是否正确 if re.findall(r"from (.+?) where",data)[0]!="staff_table": return("查询的表不存在") #where条件 df=data1[-3].strip() if df not in all_item or data1[-2] not in [">","=","like"]: return("查询语句中的where条件输入有误") #读取Json文件中的原始数据 initial_data=load()#{'1': {'name': 'Mark', 'age': '22', 'phone': '13651054608', 'dept': 'IT', 'enroll_date': '2013-04-01'}} condition=data1[-3] symbol=data1[-2] if condition=="staff_id": if symbol==">": for item in initial_data: if item > int(data1[-1]): show_list[item] = initial_data[item] if symbol=="=": for item in initial_data: if item > int(data1[-1]): show_list[item] = initial_data[item] return get_final_result(show_item,show_list) if condition=="age": if symbol==">": for item in initial_data: if int(initial_data[item]["age"])>int(data1[-1]): show_list[item]=initial_data[item] if symbol=="=": for item in initial_data: if initial_data[item]["age"]==int(data1[-1]): show_list[item]=initial_data[item] return get_final_result(show_item,show_list) if condition in ["name","phone","dept","enroll_date"]: if symbol=="like": for item in initial_data: r=re.findall(r"%s(.+?)"