作业要求及初步思路
员工信息表程序，实现增删改查操作：
① 可进行模糊查询，语法至少支持下面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"
- 解决方案： sql语句的python解析问题，即将用户输入的sql语句转为我们代码可以正确执行的动作。针对关键字where, values, limit, set 和 like等，新建字典作为key值。解析sql语句后，将相应信息赋值为value。 针对表字段name 和 age, 用for 循环打印字典获取key为name 和 age的value。
② 查到的信息，打印后，最后面还要显示查到的条数
- 解决方案： 读取json文件到字典，满足条件查询到后加入New_Dict, 运用len()计算key值。
③ 可创建新员工纪录，以phone做唯一键，staff_id需自增
- 解决方案： 以phone number作为唯一键，即在创建主要字典存入json的格式为： {1390000000: {'name': 'may Lol', 'age': 23, 'staff_id': xxxxxx, 'enroll_date': xxxxxxxx}}
- staff_id 自增， 需要建立在有序字典， 导入collection 模块， collections.OrderedDict
④ 可删除指定员工信息纪录，输入员工id，即可删除
- 删除字典的key和value
⑤ 可修改员工信息，语法如下:
UPDATE staff_table SET dept="Market" where dept = "IT"
- 同①
⑥ 以上需求，要充分使用函数，请尽你的最大限度来减少重复代码
大约设计的函数有：
- loaded_from_file: 读取json文件
- write_into_file: 写入json文件
- sql_parse: sql语句解析
- add, delete, update, search: 增撒改查
- main: 入库函数
知识点
1. 项目文件格式规范： 看这里
2. 不同目录文件读取
我的笨办法：
root = os.path.dirname(os.path.dirname(os.path.dirname(__file__))) file_path = root + r"/docs/" + filename
新潮写法：动态的运用绝对路径 os.path.abspath
Base_dir = os.path.dirname(os.path.dirname(os.path.abspath(__file__))) sys.path.append(Base_dir) # 添加环境变量 from core import main
3. 有序字典
from collections import OrderedDict
我们都知道，字典的key都是无序的。但在本项目中，staff_id是mutually execusive的。所以需要将key有序化，并每增加一个员工，staff_id自动添加1。
collections.OrderedDict的特点：
- OrderedDict的Key会按照插入的顺序排列，不是Key本身排序
- OrderedDict可以实现一个FIFO（先进先出）的dict，当容量超出限制时，先删除最早添加的Key
用法：
from collections import OrderedDict
Ordered_d = OrderedDict([("a",1),("b",2), ("c", 3)])
print(Ordered_d)
# 返回 OrderedDict([('a', 1), ('b', 2), ('c', 3)])
# OrderedDict的key会按照插入的顺序排列，而不是key本身排列
Ordered_d["z"] = 26
Ordered_d["y"] = 25
Ordered_d["x"] = 24
print(Ordered_d)
# 返回 OrderedDict([('a', 1), ('b', 2), ('c', 3), ('z', 26), ('y', 25), ('x', 24)])
详见 Module - collections
4. 表格打印： prettytable 模块
from prettytable import PrettyTable
from prettytable import PrettyTable
table = PrettyTable(["animal", "ferocity"])
table.add_row(["wolverine", 100])
table.add_row(["grizzly", 87])
table.add_row(["Rabbit of Caerbannog", 110])
table.add_row(["cat", -1])
table.add_row(["platypus", 23])
table.add_row(["dolphin", 63])
table.add_row(["albatross", 44])
table.sort_key("ferocity")
table.reversesort = True
print(table)
详见 Module - prettytable
4. python解析sql语言的执行过程： 这个是让人很头疼的问题，这篇文章写的简介明了。 代码如下：
#!usr/bin/env python #-*- coding:utf-8 -*- # 一张学生表 name,age,class_number = range(3) # 即 name = 0, age = 1, class_number = 2 zhangsan = ('zhangsan',18, 'Sfware class 1') lisi = ("lisi",19,'Sfware class 1') wangwu = ('wangwu', 19, 'Sfware class 2') Students = { zhangsan, lisi, wangwu } # 成绩表 name, course, score = range(3) grade1 = ('zhangsan','math',60) grade2 = ('zhangsan','C language', 70) grade3 = ('lisi', 'math', 61) grade4 = ('wangwu', 'math', 62) grade5 = ('wangwu','C language', 63) grades = {grade1, grade2, grade3,grade4,grade5} # 前奏：遍历“学生表”找到姓名为“张三”的人, 将打印出来 res = [ s for s in Students if s[name] == "zhangsan"] print(res) # [('zhangsan', 18, 'Sfware class 1')] # 高潮 - sql语句：select name,age,class from Students where name = "zhangsan" # select 学生.姓名， 成绩.学科， 成绩.分数 # from 学生 left join 成绩 on 学生.姓名 = 成绩.姓名 # where 学生.姓名 = "张三" # 以下解释如何sql语言的查询 # 第一步：笛卡尔积 Cartesian product Cartesian_product = {(x,y) for x in Students for y in grades} # print(Cartesian_product) # 第二步： 增加过滤条件 student_table, grade_table = range(2) '''写法一： for x in Cartesian_product: if x[student_table][name] == x[grade_table][name] and x[student_table][name] == "zhangsan": print(x) ''' # 写法二： final_res = { (x[student_table][name],x[grade_table][course], x[grade_table][score]) for x in Cartesian_product if x[student_table][name] == x[grade_table][name] and x[student_table][name] == "zhangsan"} print("最后结果", final_res)
My work
还存在问题：
1. 代码太长
2. 在select函数中，字段(e.g name 或 age) 只能选择两个字段，不能动态地根据要求变化搜索的字段。
#!usr/bin/env python #-*- coding:utf-8 -*- __author__ = "Jane Yue" import os, json from collections import OrderedDict from prettytable import PrettyTable def loaded_from_file(filename="staff_table.json"): # 切换到docs文件夹下读取文件，默认文件staff_table.json文件 root = os.path.dirname(os.path.dirname(__file__)) file_path = root + r"/docs/" + filename with open(file_path, "r+", encoding="utf-8") as file_object: staff_dict = OrderedDict(json.load(file_object)) # 有序字典 return staff_dict def write_into_file(staff_dict, filename="staff_table.json"): # 写入文件。默认文件docsstaff_table.json root = os.path.dirname(os.path.dirname(__file__)) file_path = root + r"/docs/" + filename with open(file_path, "w+", encoding="utf-8") as file_object: json.dump(staff_dict, file_object) def open_page(): # 首页,入库函数 print("".center(71, "-")) print(" 33[1;33mWelcome to WONDERLAND'S staff information platform 33[0m".center(71, " ")) print("".center(71, "-")) print(" 33[1;30mOperation Menu: 33[0m") operation_table = PrettyTable(["operation", "sql sentence"]) operation_table.add_row(["SEARCH", "select name,age from staff_table where age > 22"]) operation_table.add_row(["", "select * from staff_table where dept = "IT""]) operation_table.add_row(["", "select * from staff_table where enroll_date like "2013""]) operation_table.add_row(["CREATE", "create phone#,name,age,dept,enroll_date"]) operation_table.add_row(["DELETE", "delete staff_id"]) operation_table.add_row(["UPDATE", "update staff_table set dept = "Market" where dept = "IT""]) print(operation_table) operation_dict = {"select": search, "create": create, "delete": delete, "update": update} while True: sql = input(" 33[1;31mPlease write your sql sentence accordingly 33[0m >>>").strip() sql_list = sql.split(" ") if sql_list[0] in operation_dict.keys(): operation_dict[sql_list[0]](sql) # 调用函数 else: print("Sql language errors. Please try again!") continue def search(sql): # 查询 staff_dict = loaded_from_file() search_sql_list = sql.replace(""", "").split() selection_field_list = search_sql_list[1].split(",") if search_sql_list[4] == "where": if search_sql_list[6] == ">": if "*" in selection_field_list: res_all = [[k["name"], k["age"], k["staff_id"], k["dept"], k["enroll_date"]] for i, k in staff_dict.items() if int(k[search_sql_list[5]]) > int(search_sql_list[7])] print(res_all) y = PrettyTable(["name", "age", "staff_id", "dept", "enroll_date"]) for j in res_all: y.add_row(j) y.add_row(["", "", "", " 33[1;31mSEARCH IN TOTAL 33[0m", len(res_all)]) print(y) else: res = [[k[selection_field_list[0]], k[selection_field_list[1]]] for i, k in staff_dict.items() if int(k[search_sql_list[5]]) > int(search_sql_list[-1])] # 问题：这里只能按照要求答应下标为0和1，如何历遍字段列表search_sql_list答应所有 x = PrettyTable(selection_field_list) for i in res: x.add_row(i) x.add_row([" 33[1;31mSEARCH IN TOTAL 33[0m", len(res)]) print(x) elif search_sql_list[6] == "<": if "*" in selection_field_list: res_all = [[k["name"], k["age"], k["staff_id"], k["dept"], k["enroll_date"]] for i, k in staff_dict.items() if int(k[search_sql_list[5]]) < int(search_sql_list[7])] print(res_all) y = PrettyTable(["name", "age", "staff_id", "dept", "enroll_date"]) for j in res_all: y.add_row(j) y.add_row(["", "", "", " 33[1;31mSEARCH IN TOTAL 33[0m", len(res_all)]) print(y) else: res = [[k[selection_field_list[0]], k[selection_field_list[1]]] for i, k in staff_dict.items() if int(k[search_sql_list[5]]) < int(search_sql_list[-1])] x = PrettyTable(selection_field_list) for i in res: x.add_row(i) x.add_row([" 33[1;31mSEARCH IN TOTAL 33[0m", len(res)]) print(x) elif search_sql_list[6] == "=": if "*" in selection_field_list: res_all = [[k["name"], k["age"], k["staff_id"], k["dept"], k["enroll_date"]] for i, k in staff_dict.items() if k[search_sql_list[5]] == search_sql_list[7]] y = PrettyTable(["name", "age", "staff_id", "dept", "enroll_date"]) for j in res_all: y.add_row(j) y.add_row(["", "", "", " 33[1;31mSEARCH IN TOTAL 33[0m", len(res_all)]) print(y) else: res = [[k[selection_field_list[0]], k[selection_field_list[1]]] for i, k in staff_dict.items() if k[search_sql_list[5]] == search_sql_list[-1]] x = PrettyTable(selection_field_list) for i in res: x.add_row(i) x.add_row([" 33[1;31mSEARCH IN TOTAL 33[0m", len(res)]) print(x) elif search_sql_list[6] == "like": if "*" in selection_field_list: res_all = [[k["name"], k["age"], k["staff_id"], k["dept"], k["enroll_date"]] for i, k in staff_dict.items() if k[search_sql_list[5]][:4] == search_sql_list[7]] y = PrettyTable(["name", "age", "staff_id", "dept", "enroll_date"]) for j in res_all: y.add_row(j) y.add_row(["", "", "", " 33[1;31mSEARCH IN TOTAL 33[0m", len(res_all)]) print(y) else: res = [[k[selection_field_list[0]], k[selection_field_list[1]]] for i, k in staff_dict.items() if k[search_sql_list[5]][:4] == search_sql_list[7]] x = PrettyTable(selection_field_list) for i in res: x.add_row(i) x.add_row([" 33[1;31mSEARCH IN TOTAL 33[0m", len(res)]) print(x) else: print("Darling, something wrong with sql sentence") exit() else: print("Sql language errors. missing keyword "where"") def create(sql): # 增加新员工 staff_dict = loaded_from_file() create_sql_list = sql.split()[1].split(",") phone, name, age, dept, enroll_date = range(5) staff_dict.setdefault(create_sql_list[phone]) staff_dict[create_sql_list[phone]] = {"name": None, "age": None, "staff_id": None, "dept": None, "enroll_date": None} staff_dict[create_sql_list[phone]]["name"] = create_sql_list[name] staff_dict[create_sql_list[phone]]["age"] = create_sql_list[age] staff_dict[create_sql_list[phone]]["dept"] = create_sql_list[dept] staff_dict[create_sql_list[phone]]["enroll_date"] = create_sql_list[enroll_date] temp_list = [v["staff_id"] for k, v in staff_dict.items()] temp_list.remove(None) staff_dict[create_sql_list[phone]]["staff_id"] = max(temp_list) + 1 write_into_file(staff_dict) print("New account is created successfully.") def delete(sql): # 删除；因为staff_id和phone都具有唯一性，所以都可以做key staff_dict = loaded_from_file() sql_list = sql.split() phone_dict = {} # 创建以staff_id为key, phone#为value的字典 for k in staff_dict: phone_dict[staff_dict[k]["staff_id"]] = k del staff_dict[phone_dict[int(sql_list[1])]] write_into_file(staff_dict) print("Account is removed from system.") def update(sql): staff_dict = loaded_from_file() set_list = sql.split("set")[1].split("where")[0].replace(""", " ").split() # 返回：['dept', '=', 'Market'] where_list = sql.split("set")[1].split("where")[1].replace(""", " ").split() # 返回：['dept', '=', 'IT'] if set_list[0] == where_list[0]: for k, v in staff_dict.items(): if v[where_list[0]] == where_list[-1]: v[where_list[0]] = set_list[-1] print("Information updated") else: print("the category you want to change is unmatched.") write_into_file(staff_dict)
示例
详细描述参考http://www.cnblogs.com/alex3714/articles/5740985.html