'''
测试数据：
<增加>:
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("\n"+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]))+"\n")
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)]))+"\n")
# print("***",str(i),informationDictTemp4[str(i)])
# f.write(str(i)+","+(",".join(informationDictTemp4[str(i)]))+"\n")
f.write(str(start_num)+","+(",".join(v))+"\n")
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}的个人信息如下：\n{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}的个人信息如下：\n{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
浙公网安备 33010602011771号