第四周作业-员工信息表程序

需求

员工信息表程序,实现增删改查操作:

 

可进行模糊查询,语法至少支持下面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"

查到的信息,打印后,最后面还要显示查到的条数

可创建新员工纪录,以phone做唯一键,staff_id需自增

可删除指定员工信息纪录,输入员工id,即可删除

可修改员工信息,语法如下:

  UPDATE staff_table SET dept="Market" where dept = "IT"

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

程序实现

1.逻辑图

2.主程序

博客地址:http://www.cnblogs.com/Mr-hu/

程序运行步骤:
1.输入以下语句:
    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"

    insert maria,32,1876542390,IT,2016-06-21

    update staff_table set dept = "Market" where dept = "IT"

    delete x
readme.txt
import os


def info_display():  # 打印程序启动信息
    print("-".center(40, "-"))
    print("欢迎来到员工信息系统".center(40, " "))
    print("【输入\033[1;31mhelp\033[0m显示帮助信息】".center(52, " "))
    print("-".center(40, "-"))
    sql = input("请输入\033[1;31mSQL查询语句\033[0m信息>>>>").strip("")
    if sql == "q" or sql == "quit":  # 用户如果输入q,程序退出
        exit("再见!".center(40, "-"))
    elif sql == "help":  # 用户输入help,打印help信息,help信息为打印的格式
        print("查询 输入格式:\n\t\033[1;34mselect name,age from staff_table where age > 22\n\tselect  * from staff_table where dept = \"IT\"\n\tselect  * from staff_table where enroll_date like \"2013\"\033[0m")
        print("创建 输入格式:\n\t\033[1;34minsert maria,32,1876542390,IT,2016-06-21\033[0m")
        print("修改 输入格式:\n\t\033[1;34mupdate staff_table set dept = \"Market\" where dept = \"IT\"\033[0m")
        print("删除 输入格式:\n\t\033[1;34mdelete 5\033[0m")
        print("退出程序:\033[1;34mq\033[0m")
    else:
        sql_parse(sql)  # 用户输入正确的sql语法,调用sql解析函数


def sql_parse(sql):
    func_choice = {"insert": insert, "delete": delete, "update": update, "select": select}
    sql_list = sql.replace(",", " ").split()
    if sql_list[0] == "select":
        sql_dict = {"from": [], "where": [], "limit": []}
        Flag = False
        for item in sql_list:
            if Flag and item in sql_dict:
                Flag = False
            if not Flag and item in sql_dict:
                Flag = True
                key = item
                continue
            if Flag:
                sql_dict[key].append(item.strip('\"'))
        func_choice.get(sql_list[0])(sql_dict)
    elif sql_list[0] == "insert":
        sql_dict = {}
        sql_list.remove("insert")
        sql_dict.setdefault("values", sql_list)
        insert(sql_dict)
    elif sql_list[0] == "delete":
        delete_id = int(sql_list[1])
        delete(delete_id)
    elif sql_list[0] == "update":
        sql_dict = {"set": [], "where": [], "update": []}
        Flag = False
        for items in sql_list:
            if Flag and items in sql_dict:
                Flag = False
            if not Flag and items in sql_dict:
                Flag = True
                key = items
                continue
            if Flag:
                sql_dict[key].append(items.strip('\"'))
        update(sql_dict)
    return sql_list

def insert(sql_dict):
    with open("staff_table", "ab+") as f:
        offs = -100
        while True:
            f.seek(offs, 2)
            lines = f.readlines()
            if len(lines) > 1:
                last = lines[-1]
                break
            offs *= 2
        last = last.decode(encoding="utf-8")
        last_id = int(last.split(",")[0])
        new_id = last_id + 1
        record = sql_dict.get("values")
        record.insert(0, str(new_id))
        record_str = ",".join(record) + "\n"
        f.write(bytes(record_str, encoding="utf-8"))
        f.flush()
        print("\033[1;31m创建成功 \033[0m")


def delete(delete_id):
    with open("staff_table", "r", encoding="utf-8") as f1, open("staff_table_bak", "w", encoding='utf-8') as f2:
        del_count = 1
        for line in f1:
            if del_count != delete_id:
                f2.write(line)
            elif del_count == delete_id:
                pass
            del_count += 1
    print("\033[1;31m删除成功 \033[0m")
    os.remove("staff_table")
    os.rename("staff_table_bak", "staff_table")


def update(sql_dict):
    set_list = sql_dict.get("set")
    set_key = set_list[0]
    where_list = sql_dict.get("where")
    with open("staff_table", "r", encoding="utf-8") as f1, open("staff_table_bak", "w", encoding='utf-8') as f2:
        for line in f1:
            title = "id,name,age,phone,dept,enroll_date"
            dic = dict(zip(title.split(","), line.split(",")))
            if logic_action(dic, where_list):
                line = line.replace(dic[set_key], set_list[2])
            f2.write(line)
    print("\033[1;31m修改成功 \033[0m")
    os.remove("staff_table")
    os.rename("staff_table_bak", "staff_table")


def select(sql_dict):
    title = "id,name,age,phone,dept,enroll_date"
    f = open("staff_table", "r", encoding="utf-8")
    res = []
    for line in f:
        dict1 = dict(zip(title.split(","), line.strip().split(",")))
        where_list = sql_dict.get("where")
        if len(where_list) != 0:
            if logic_action(dict1, where_list):
                res.append(line.strip())
        else:
            res = f.readlines()
    print("查询到的信息有 \033[1;35m %s \033[0m 条" % len(res))
    for i in res:
        print("分别是\033[1;35m [%s]\033[0m " % i)
    return res


def logic_action(dict1, where_list):
    Flag = False
    if where_list[1] == "<":
        if dict1[where_list[0]] < where_list[2]:
            Flag = True
    elif where_list[1] == "=":
        if dict1[where_list[0]] == where_list[2]:
            Flag = True
        else:
            pass
    elif where_list[1] == ">":
        if dict1[where_list[0]] > where_list[2]:
            Flag = True
        else:
            pass
    elif where_list[1] == "like":
        if where_list[2] in dict1[where_list[0]]:
            Flag = True
    return Flag


while True:
    try:
        info_display()
    except IndexError as e:
        print("【%s】,请输入正确的格式" % e)
main.py

3.程序运行

 

posted @ 2017-08-24 14:24  Mr.hu  阅读(188)  评论(0编辑  收藏  举报