#作业名称：员工信息的增删改查 #作业需求：

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/19 import 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(.+?)" %data1[-1].strip().strip('"'),initial_data[item][condition])                 if len(r)!=0:                     show_list[item]=initial_data[item]         if symbol=="=":             for item in initial_data:                 if initial_data[item][condition]==data1[-1].strip("\\\""):                     show_list[item]=initial_data[item]         return get_final_result(show_item,show_list) #判断添加sql def judge_add_sql(statements):     #insert into staff_table values ('name','age','phone','dept','enroll_date')     #截取输入的值     r=re.findall(r"insert into staff_table values (.+)",statements)[0].strip("(").strip(")").replace("'",'').replace('"',"")     r=r.split(",")     #没有截取到内容时     if r==None or len(r)!=5:         return False     else:         return("{'name':'%s','age':'%s','phone':'%s','dept':'%s','enroll_date':'%s'}" %(r[0],r[1],r[2],r[3],r[4])) #判断修改sql def judge_update_sql(statements):     #UPDATE staff_table SET dept="Market" WHERE dept = "IT"     change= re.findall(r"UPDATE staff_table SET (.+) WHERE *",statements)     condition = re.findall(r"UPDATE staff_table SET %s WHERE (.+)" %change[0], statements)     change = change[0].split("=")     condition=condition[0].split("=")     if change[0] and change[1] and condition[0] and condition[1]:         r=[change,condition]         return r     else:         return False #添加 def add(data):     #检索输入语句是否正确     judgment=judge_add_sql(data)     if judgment==False:         return ("输入的语句有误")     else:         judgment=eval(judgment)         #判断phone是否重复         old_file=load()         k_max=1         for k in old_file:             #获取序号的最大值             if k_max