python作业---员工信息的增删改查

作业需求

： 
   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"

作业思路
   1.把需要查询的内容转成列表形式
   2.逐行读取文件内容，存成列表形式
   3.根据读取的内容与需求进行适配
   4.符合要求的进行打印或者重新写入文件

流程图

 

xinxi：

1,Alex,22,13651054608,运维,2013-04-01
2,Jack,23,13655818285,Web,2013-04-06
3,Mack,20,13651854708,HR,2013-05-06
4,Gao,22,18651054608,运维,2012-09-01
5,Rain,32,15125154608,CTO,2000-01-01

 

import os
# 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"
def query(data):
    data1 = data.split(" ")
    if data ==("select name,age from staff_table where age > %s"%(data1[7])):
        with open("xinxi","r", encoding="utf-8") as f:
            list = []
            for line in f:
                i = line.strip().split(",")
                if i[2] > data1[7]:
                    list.append(i)
                    # print(i)
            for k in list:
                print(k)
            print("查询到 %s 条符合的信息" % len(list))
    elif data == ("select * from staff_table where dept = %s" % (data1[7])):
            with open("xinxi","r", encoding="utf-8") as f:
                list1 = []
                for line in f:
                    i1 = line.strip().split(",")
                    if i1[4] == data1[7]:
                        list1.append(i1)
                for j in list1:
                    print(j)
                print("查询到 %s 条符合的信息" % len(list1))
    else:
        if data == ("select * from staff_table where enroll_date like %s" % (data1[7])):
            with open("xinxi","r", encoding="utf-8") as f:
                list = []
                for line in f:
                    i = line.strip().split(",")
                    if i[5][0:4]==data1[7]:
                        list.append(i)
                for k in list:
                    print(k)
                print("查询到 %s 条符合的信息" % len(list))
    return 0

#name,age,phone,dept,enroll-date
def add(data):
    data1=data.split(",")
    with open ("xinxi","r",encoding="utf-8") as f:
        list=[]
        phone_list=[]
        for line in f:
            i=line.strip().split(",")
            phone_list.append(i[3])
        if data1[2] in phone_list:
            print("手机号码已存在！")
        else:
            with open ("xinxi","r+",encoding="utf-8") as f1:
                for line in f1:
                    i1=line.strip().split(",")
                    list.append(i1)
                w=str(int(list[-1][0])+1)
                data1.insert(0,w)
                print(data1)
                data1=",".join(data1)
                f1.write("\n")
                f1.write(data1)
                print("添加成功！")
    return 0

#delete from staff_table where staff_id = 12
def delete(data):
    data1=data.split(" ")
    if data==("delete from staff_table where staff_id = %s"%(data1[6])):
        with open("xinxi","r",encoding="utf-8") as f:
            list=[]
            for line in f:
                i =line.strip().split(",")
                i1=line.splitlines()
                if data1[6]==i[0]:
                    i2=",".join(i1)
                    print(i2)
                    list.append(i)
        a=i2
        f=open ("xinxi","r",encoding="utf-8")
        f1=open ("xinxi_new","a+",encoding="utf-8")
        for i in f:
            if a in i:
                i=i.replace(a,"").strip()
            f1.write(i)
            f1.flush()
        f.close()
        f1.close()
    os.remove("xinxi")
    os.rename("xinxi_new","xinxi")
    print("删除成功")
    return 0

#UPDATE staff_table SET dept = IT where dept = 运维
def change(data):
    data1=data.split(" ")
    with open("xinxi","r",encoding="utf-8") as f,open("xinxi2","w",encoding="utf-8") as f1:
        for line in f:
            i = line.strip()
            print(i)
            if data1[5] in i:
                i = i.replace(data1[5],data1[9])
            f1.write(i)
            f1.write("\n")
            f1.flush()
    os.remove("xinxi")
    os.rename("xinxi2","xinxi")
    print("修改成功")
    return 0


#主程序
while True:
    print('''
            ============================
                欢迎来到员工信息系统
            ----------------------------
                    1.查询
                    2.添加
                    3.修改
                    4.删除
                    5.退出
            ===========================
    ''')
    choice=input("请输入序号：")
    if choice=="1":
        print("\t\t\t=====================语法实例=====================")
        print("""
            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
        """)
        data = input("请输入语法>>:").strip()
        query(data)

    if choice =="2":
        print("\t\t\t=====================语法实例=====================")
        print("""
              name,age,phone,dept,enroll-date
              """)
        data = input("请输入语法>>:").strip()
        add(data)

    if choice=="3":
        print("\t\t\t=====================语法实例=====================")
        print("""
            UPDATE staff_table SET dept = IT where dept = 运维
            """)
        data = input("请输入语法>>:").strip()
        change(data)

    if choice=="4":
        print("\t\t\t=====================语法实例=====================")
        print("""
            delete from staff_table where staff_id = 12
            """)
        data = input("请输入语法>>:").strip()
        delete(data)

    if choice=="5":
        break