第四周作业-员工信息表程序
需求
员工信息表程序,实现增删改查操作:
可进行模糊查询,语法至少支持下面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
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)
3.程序运行