day4

day4.py

作业：

有以下员工信息表

当然此表你在文件存储时可以这样表示

1	`1,Alex Li,22,13651054608,IT,2013-04-01`

现需要对这个员工信息文件，实现增删改查操作

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

注意：以上需求，要充分使用函数，请尽你的最大限度来减少重复代码！

下面是代码

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
#user:Felix
#date:2016-11-1
#mail:wudonghang@wudonghang.com
#If you have questions or suggestions, please contact the author.
import os
import time
import re
import shutil
def data(data):
    data=data.strip().replace(","," ").split(" ")
    # if data[0] == "select":
    #     print("select")
    # elif data[0] == "update":
    #     print("update")
    # elif data[0] == "insert":
    #     print("insert")
    # elif data[0] == "delete":
    #     print("delete")
    # else:
    #     print("输入语法有问题")
    tag=False
    for i in student_list[0]:
        if i in data:
            column.append(i)
    for i in data:
        if i == "where":
            tag=True
            continue
        if tag:
            condition.append(i)
    return column,condition


def select(data):
    select_list=[]
    data=data.replace("select","").replace("from","").replace("staff_table where","")
    #print(data) # *   age <= 22
    mark=student_list[0].index(if_information[1][0]) #select name,age from staff_table where occupation like I
    for i in student_list:
        if i == student_list[0]:continue #如果是列名就跳出
        #print("i[mark]  ",i[mark],"if_information[1][2]  ",if_information[1][2],"***",type(i[mark]),"  ",type(if_information[1][2]))
        #if i[mark].isdigit():i[mark]=int(i[mark])
        #if if_information[1][2].isdigit():if_information[1][2]=int(if_information[1][2])
        if if_information[1][2].isdigit():
            if if_information[1][1] == ">":
                if int(i[mark]) > int(if_information[1][2]):select_list.append(i)#print(i)
            elif if_information[1][1] == "=":
                if int(i[mark]) == int(if_information[1][2]):select_list.append(i)#print(i)
            elif if_information[1][1] == "<":
                if int(i[mark]) < int(if_information[1][2]):select_list.append(i)#print(i)
            elif if_information[1][1] == ">=":
                if int(i[mark]) >= int(if_information[1][2]):select_list.append(i)#print(i)
            elif if_information[1][1] == "<=":
                if int(i[mark]) <= int(if_information[1][2]):select_list.append(i)#print(i)
            elif if_information[1][1] == "!=":
                if int(i[mark]) != int(if_information[1][2]):select_list.append(i)#print(i)
            elif if_information[1][1] == "like":
                if if_information[1][2] in i[mark]:select_list.append(i)#print(i)
            else:print("您的输入有错")
        #以上是 age和id int类型查询
        else:
            #print(i[mark],if_information[1][1],if_information[1][2])
            if if_information[1][1] == "=":
                if i[mark] == if_information[1][2]:select_list.append(i)#print(i)
            elif if_information[1][1] == "like":
                if if_information[1][2] in i[mark]:select_list.append(i)#print(i)
            else:print("您的输入有错")

        #以上是字符串类型查询
    print_selcet_list=if_information[0]
    #print(print_selcet_list,if_information[0],student_list[0])
    count=[]
    if "*" in data:
        print(student_list[0])
        for line in select_list:
            print(line)
        print("您一空查询到\033[1;32;31m%s\033[0m条信息!"%(len(select_list)))
    else:
        for i in print_selcet_list:
            mark=student_list[0].index(i)
            count.append(int(mark))
        #print(count)
        print(print_selcet_list)
        for line in select_list:
            if len(count) == 0:print(line[count[0]],line[count[1]],line[count[2]],line[count[3]],line[count[4]],line[count[5]])
            elif len(count) == 1:print(line[count[0]])
            elif len(count) == 2:print(line[count[0]],line[count[1]])
            elif len(count) == 3:print(line[count[0]],line[count[1]],line[count[2]])
            elif len(count) == 4:print(line[count[0]],line[count[1]],line[count[2]],line[count[3]])
            elif len(count) == 5:print(line[count[0]],line[count[1]],line[count[2]],line[count[3]],line[count[4]])
            elif len(count) == 6:print(line[count[0]],line[count[1]],line[count[2]],line[count[3]],line[count[4]],line[count[5]])
        print("您一空查询到\033[1;32;31m%s\033[0m条信息!"%(len(select_list)))
        # for line in select_list:
        #     for i in count:
        #
        #         print(line[int(i)])
    return select_list


def update(data_update):
    #UPDATE staff_table SET dept="Market" WHERE dept = "IT"
    #update staff_table set dept=market where dept = it
    # print(data_update)
    # print(if_information)
    data1=data(data_update)[1]
    news_ids = []
    for id in data1:
        if id not in news_ids:
            news_ids.append(id)
    news_ids.insert(0,"*")
    # print(type(news_ids))
    # print(news_ids)
    select_list=select("".join(news_ids))
    awk=data_update.strip().replace(","," ").split(" ")[3:6]
    select_list_id=student_list[0].index(awk[0])
    with open("dbnew","w",encoding="utf-8") as write_file:
        #print(",".join(student_list[0]))
        print(student_list[0])
        write_file.write(",".join(student_list[0])+"\n")
        for i in range(len(select_list)):
            student_list.remove(select_list[i])
            select_list[i][select_list_id]=awk[2]
            print(select_list[i])
            string=",".join(select_list[i])
            #print(string)
            write_file.write(string+"\n")
        for s in student_list:
            if "staff_id" in s:continue
            #print(s)
            else:write_file.write(",".join(s)+"\n")
        print("您已经修改\033[1;32;31m%s\033[0m条信息!"%(len(select_list)))

        #现在已经查出来了 写入新文件并同时修改
        #print(awk)
    write_file.close()
    dbbak="dbbak"+time.strftime("%Y-%m-%d-%H-%M-%S",time.localtime(time.time()))
    shutil.copyfile("db",dbbak)
    os.remove("db")
    os.renames("dbnew","db")


    # with open("db","r",encoding="utf-8") as read_file,\
    #     open("dbnew","w",encoding="utf-8") as write_file:
    #     for r_line in read_file:
    #         for w_line in write_file:
    #             if

    #for line in student_list:
        #line


def insert(data):

    data = re.split('\(|\)',data)[-2].replace(" ","")
    data1= re.split('\(|\)',data)
    #print(data1)
    for line in student_list:
        if line[3] in data:
            print("新增用户手机号与老用户\033[1;32;31m%s\033[0m有冲突"%line)
            break
    else:
        sum=[]
        with open("db","r",encoding="utf-8") as read_file,\
            open("dbnew","w",encoding="utf-8") as write_file:
            for r_line in read_file:
                if "staff_id" not in  r_line:sum.append(r_line.split(",")[0])
                write_file.write(r_line)
            #print(type(",".join(data)))
            # print(str(int(sum[-1])+1))
            id=int(sum[-1])+1
            # print(type(id))
            # print(type(repr(id)))
            #write_file.write("\n"+repr(int(sum[-1])+1)+","+",".join(data))
            new_line="\n"+repr(id)+","+data
            write_file.write(new_line)
        # read_file.close()
        # write_file.close()
        dbbak="dbbak"+time.strftime("%Y-%m-%d-%H-%M-%S",time.localtime(time.time()))
        shutil.copyfile("db",dbbak)
        os.remove("db")
        os.renames("dbnew","db")

def delete(data_del):
    #DELETE FROM staff_table WHERE staff_id = 1
    print(data_del)
    delete=data(data_del)[1][3:6]
    delete.insert(0,"*")
    print(delete)
    select_list=select("".join(delete))
    awk=data(data_del)[1][3:6]
    print(awk)
    select_list_id=student_list[0].index(awk[0])
    with open("dbnew","w",encoding="utf-8") as write_file:
        #print(",".join(student_list[0]))
        print(student_list[0])
        write_file.write(",".join(student_list[0])+"\n")
        for i in range(len(select_list)):
            student_list.remove(select_list[i])
            # select_list[i][select_list_id]=awk[2]
            print(select_list[i])
            # string=",".join(select_list[i])
            # #print(string)
            # write_file.write(string+"\n")
        for s in student_list:
            if "staff_id" in s:continue
            #print(s)
            else:write_file.write(",".join(s)+"\n")
    print("您已经删除\033[1;32;31m%s\033[0m条信息!"%(len(select_list)))
    write_file.close()
    dbbak="dbbak"+time.strftime("%Y-%m-%d-%H-%M-%S",time.localtime(time.time()))
    shutil.copyfile("db",dbbak)
    os.remove("db")
    os.renames("dbnew","db")

if __name__ == "__main__":
    student_list=[]
    f=open("db","r",encoding="utf-8")
    for line in f:
        line=line.strip().split(",")
        student_list.append(line)

    #print(student_list)
    # msg='''
    # 1:查询
    # 2:添加
    # 3:删除
    # 4:修改
    # 5:退出
    # '''
    # menu_dic={
    #     '1':select,
    #     '2':insert,
    #     '3':delete,
    #     '4':update,
    #     '5':"",
    # }
    while True:
        column=[]
        condition=[]
        #print(msg)
        choice=input("数据>>: ").strip().lower().replace("\"","").replace("\'","").replace(";","")
        #choice="""INSERT INTO `staff_table`.`staff` VALUES ('刘耀', '20', '17777777777','运维',"2011-11-11");"""
        #choice=choice.strip().lower().replace("\"","").replace("\'","").replace(";","")
        #print(choice)
        str=choice
        if_information=list(data(str)) #[['name', 'age'], ['age', '>', '22']]
        #print("if_information==",if_information)
        choice_def=choice.strip().replace(","," ").split(" ")
        f.close()
        if choice_def[0] == "select":
            select(str)
        elif choice_def[0] == "update":
            update(str)
        elif choice_def[0] == "insert":
            insert(str)
        elif choice_def[0] == "delete":
            delete(str)
        else:
            print("输入语法有问题")
        # if len(choice) == 0 or choice not in menu_dic:continue
        # if choice == "5":break
        #str=input("数据>>: ").strip()
        # str="select name,age from staff_table where age > 22"
        # if_information=list(data(str))
        # menu_dic[choice](str)
        #print(if_information)
        # select name,age from staff_table where age > 22
        # select name,age from staff_table where dept like I

db文件因涉嫌隐私不往上放了（db文件所有应为为小写）

举例

staff_id,name,age,phone,dept,enroll_date
1,张三,25,15201525043,运维,2013-11-01
2,李四,26,18689346677,dba,2012-12-25

下面是README

博客园地址:http://www.cnblogs.com/wudonghang/p/457adc032f1678bbe2f0d7effce930f8.html


day4.py 使用说明
查找数据：
select name,age,enroll_date from staff_table where staff_id = 22; #按列输出，精确查找
select name,age,phone,enroll_date from staff_table where staff_id = 22;#按列输出，精确查找
select name,age,phone,enroll_date from staff_table where age <= 22; #按列输出，精确查找
select name,age,phone,enroll_date from staff_table where name like "李"; ##按列输出，模糊查找
select * from staff_table where name like "宋"; #输出整行信息，模糊匹配
select * from staff_table where name = "吴东杭"; #输出整行信息，精确匹配
select staff_id,name,age,enroll_date from staff_table where age > 33; #精确匹配
select name,age,phone,enroll_date from staff_table where dept = "it"; #精确匹配
select name,age,phone,enroll_date from staff_table where dept like "运"; 模糊查询
select * from staff_table where age <= 22; 精确查询
select 查询功能已经很完善了，可以根据我上述查询语句，举一反三进行检索（日期暂时查询有bug）

新增数据：INSERT INTO `staff_table`.`staff` VALUES ('刘耀', '20', '17777777777','运维',"2011-11-11");
（新增数据新增第一条然后添加第二条时不会马上报错手机号重复，因为文件我没有重新读取，算是一个小问题吧）

修改数据： UPDATE staff_table SET dept = "Market" WHERE dept = "IT"
（在这里会出现个问题 如果我修改地25个人的，他的id就会上浮至前几行，浙江就会导致连续添加数据id的不准确，因为我的逻辑是文件最后一个id+1）

删除数据：DELETE FROM staff_table WHERE staff_id = 1; #精确删除
                    DELETE FROM staff_table WHERE name like "刘" #模糊匹配删除
                    DELETE FROM staff_table WHERE age >= 30  #大量删除

注意：dbbak 是我为了修改db后快速回复db文件格式才留下的  真正的备份时dbbak加上时间戳才是备份

来自为知笔记(Wiz)

posted @ 2016-11-04 16:25 Anonymous-develop 阅读(614) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

Felix运维

day4

公告