''' 测试数据： <增加>: add staff_table Alex Li,25,134435344,IT,2015‐10‐29 <删除>： del from staff where serial = 3 <修改>： UPDATE staff_table SET dept="Market" WHERE dept = "IT" UPDATE staff_table SET age=25 WHERE name = "Alex Li" <查找>： find name,age from staff_table where age > 22 find * from staff_table where dept = "IT" find * from staff_table where enroll_date like "2013" https://blog.csdn.net/jt674106399/article/details/76516186 https://www.cnblogs.com/yuyutianxia/p/5106386.html https://www.jb51.net/article/84075.htm https://www.jb51.net/article/47990.htm ''' import sys import os import linecache informationDictTemp = {} UserName = "" staff_table = "information.txt" informationMap = {"0":"serial","1":"name","2":"age","3":"telephone","4":"dept","5":"enroll_date"} #登录 def login(): print(loadData("information.txt")) count =3 judgeDict = {} with open("user_information.txt","r",encoding="utf-8") as f: for i in f.readlines(): data = i.strip().split(",") judgeDict.setdefault(data[0],data[1]) while not count is None: global UserName print("欢迎进入个人信息修改系统".center(50,"*")) UserName = input("请输入用户名字：").strip().title() PassWord = input("请输入用户密码：").strip() if count >1: if UserName in judgeDict.keys() and PassWord == judgeDict[UserName]: return print("恭喜你，登录成功。。。") else: count-=1 print("sorry,你输入的用户名和密码有误，请重新输入！") continue else: print("连续登录三次错误，系统将自动退出！") sys.exit() #解析文件parse def loadData(file_name): with open(file_name,"r",encoding="utf-8") as f: for i in f.readlines(): data = i.strip().split(",") informationDictTemp.setdefault(data[1],data[2:]) return informationDictTemp #查询 def findFun(p1,p2,p3): logicSymbol = [">","<","=",">=","<=","like"] temp_list = [] # if para1[0] in informationMap.values() and para1[1] in logicSymbol: if p1=="*" and not p2.__contains__("like"): location = int(list(informationMap.values()).index(p2[0]))-2 if p2[1] == "=": result = {k:v for k,v in informationDictTemp.items() if v[location].__eq__(eval(p2[2]))} else: # print(informationDictTemp) result = {k:v for k,v in informationDictTemp.items() if eval(v[location] + "".join(p2[1:]))} printInfoFun(result) elif p1!="*" and not p2.__contains__("like"): location = int(list(informationMap.values()).index(p2[0]))-2 # print("******",location,p1,p2) if p2[1] == "=": result = {k:v for k,v in informationDictTemp.items() if v[location].__eq__(eval(p2[2]))} else: result = {k:v for k,v in informationDictTemp.items() if eval(v[location] + "".join(p2[1:]))} # print("****",p1.split(",")) for x in p1.split(","): temp_list.append(int(list(informationMap.values()).index(x))-1) print(x,end="".rjust(10)) print("") print("符合当前的条件记录为:{}条".format(len(result))) for i,j in result.items(): j.insert(0,i) for i in temp_list: print(j[i],end="".rjust(5)) print("") else: location = int(list(informationMap.values()).index(p2[0]))-2 result = {k:v for k,v in informationDictTemp.items() if v[location].__contains__(eval(p2[2]))} printInfoFun(result) # else: # print("表中不包含你过滤项目或条件语法有误") #打印信息 def printSelfInfoFun(): global UserName print(""" *************************** Name：{0} Age：{1[0]} PhoneNumber：{1[1]} Dept：{1[2]} EnrollDate：{1[3]} *************************** """.format(UserName,informationDictTemp[UserName])) def printInfoFun(a): print("serialno","name".rjust(10," "),"age".rjust(5," "),"telephone".rjust(5," "), "dept".rjust(5," "),"enroll_date".rjust(5," ")) count = 0 for index,con in a.items(): print(count,index.rjust(20," ")," ".join(con)) count+=1 print("符合当前的条件记录为:{}条".format(len(a))) # 创建 def createFun(p1,p2,p3): informationDictTemp2 = {} with open("information.txt","r",encoding="utf-8") as f: for i in f.readlines(): data = i.strip().split(",") informationDictTemp2.setdefault(data[0],data[1:]) for k,v in informationDictTemp2.items(): if informationDictTemp2[k][2] == p2: print("telephone已违反唯一键约束规则") return None with open("information.txt","a+",encoding="utf-8") as f: f.writelines(" "+str(len(informationDictTemp2)+1)+",") f.write("".join(p1)) f.flush() # f.seek(0) linenum = len(open("information.txt","r").readlines()) print("当前影响到行数为：{}".format(linenum-len(informationDictTemp2))) #修改 def modifyFun(p1,p2,p3): informationDictTemp3 = {} with open("information.txt","r",encoding="utf-8") as f: for i in f.readlines(): data = i.strip().split(",") informationDictTemp3.setdefault(data[0],data[1:]) location = int(list(informationMap.values()).index(p2[0]))-1 location2 = int(list(informationMap.values()).index(p3[0]))-1 # result = {k:v for k,v in informationDictTemp3.items() if eval(v[location] + "".join(p3[1:]))} # print(p3[2],location,location2) # print(informationDictTemp3) result = {k:v for k,v in informationDictTemp3.items() if v[location2].__eq__(eval(p3[2]))} # print(result,location) # informationDictTemp3.clear() for k,v in result.items(): # print(k,v,eval("".join(p2[2:]))) informationDictTemp3[k][location] = eval("".join(p2[2:])) # print(informationDictTemp3) with open("information2.txt","a+",encoding="utf-8") as f: for i in informationDictTemp3.keys(): f.write(str(i)+","+(",".join(informationDictTemp3[i]))+" ") f.flush() print("当前影响到行数为：{}".format(len(result))) #删除 def deleteFun(p1,p2): informationDictTemp4 = {} start_num = 1 with open("information.txt","r+",encoding="utf-8") as f: for i in f.readlines(): data = i.strip().split(",") informationDictTemp4.setdefault(data[0],data[1:]) location = int(list(informationMap.values()).index(p2[0])) # print(location) total = len(informationDictTemp4) # for k4,v4 in informationDictTemp4.items(): # print(k4,v4,v4[location-1],p2[2],p2[0]) # if "serial" == p2[0] or v4[location-1] == p2[2]: # print("delete ok") # del informationDictTemp4[k4] # continue for k4 in list(informationDictTemp4.keys()): # print(k4,p2[2],p2[0]) if "serial" == p2[0] and p2[2] == k4: del informationDictTemp4[k4] # print("delete ok--1") continue elif informationDictTemp4[k4][location-1] == p2[2]: del informationDictTemp4[k4] # print("delete ok--2") continue else: continue # print("无效的列名") # print(informationDictTemp4) f.seek(0) f.truncate() # for i in range(1,len(informationDictTemp4.keys())-1): for i,v in informationDictTemp4.items(): # informationDictTemp4[str(i)] = informationDictTemp4.pop(str(i)) # print(i,int(p2[2])) # print("***",informationDictTemp4) # if i >= int(p2[2]): # f.write(str(i)+","+(",".join(informationDictTemp4[str(i+1)]))+" ") # print("***",str(i),informationDictTemp4[str(i)]) # f.write(str(i)+","+(",".join(informationDictTemp4[str(i)]))+" ") f.write(str(start_num)+","+(",".join(v))+" ") start_num +=1 f.flush() print("当前影响到行数为：{}".format(total-len(informationDictTemp4))) login() print("欢迎进入个人信息查询系统，请输入合法语法指令".center(50,"-")) while 1: command = input(">>>>>>>").strip() # print(command) splitResult = command.split() if command.startswith("find"): para1 = splitResult[1] para2 = splitResult[(splitResult.index("where"))+1:] para3 = "" # print("find") # print("{0}的个人信息如下： {1}".format(UserName,informationDict[UserName])) findFun(para1,para2,para3) continue elif command.startswith("add"): splitResult1 = command.split(maxsplit=2) para1 = splitResult1[2:] para2 = para1[0].split(",")[2] para3 = splitResult1[1] createFun(para1,para2,para3) continue elif command.startswith("del"): splitResult2 = command.split(" ") para1 = splitResult2[splitResult2.index("from")+1:splitResult2.index("where")] para2 = splitResult2[splitResult2.index("where")+1:] deleteFun(para1,para2) continue elif command.startswith("update".upper()): # print("{0}的个人信息如下： {1}".format(UserName,informationDictTemp[UserName])) splitResult2 = command.split(" ") para1 = splitResult2[splitResult2.index("UPDATE")+1:splitResult2.index("SET")] para2 = splitResult2[splitResult2.index("SET")+1:splitResult2.index("WHERE")] para3 = splitResult2[splitResult2.index("WHERE")+1:] modifyFun(para1,para2,para3) continue else: print("输入指定有误，请重新输入！") continue