作业要求及初步思路
员工信息表程序,实现增删改查操作:
① 可进行模糊查询,语法至少支持下面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)
sql语句执行过程
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"):
# 写入文件。默认文件\docs\staff_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("\033[1;33mWelcome to WONDERLAND'S staff information platform\033[0m".center(71, " "))
print("".center(71, "-"))
print("\033[1;30mOperation Menu:\033[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("\n\033[1;31mPlease write your sql sentence accordingly\033[0m \n>>>").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(["", "", "", "\033[1;31mSEARCH IN TOTAL\033[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(["\033[1;31mSEARCH IN TOTAL\033[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(["", "", "", "\033[1;31mSEARCH IN TOTAL\033[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(["\033[1;31mSEARCH IN TOTAL\033[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(["", "", "", "\033[1;31mSEARCH IN TOTAL\033[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(["\033[1;31mSEARCH IN TOTAL\033[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(["", "", "", "\033[1;31mSEARCH IN TOTAL\033[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(["\033[1;31mSEARCH IN TOTAL\033[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)
我的作业
示例
sample 1
sample 2