python全栈开发中级班全程笔记
第三章：员工信息增删改查作业代码
作业要求：
员工增删改查表
用代码实现一个简单的员工信息增删改查表
需求： 1、支持模糊查询，
（1、find name ,age form staff_table where age > 22
(查找 staff_fable （文件）内所有 age > 22 的name 和 age全部打印）
（2、find * from staff_table where dept = "IT"
(查找所有部门是 IT的所有列打印出来*代表所有)
（3、find * from staff_table where enroll_date like "2013"
(查找所有 enroll_date (登记日期）为 2013的序列并打印) 2、可创建员工信息记录，以 phone 做唯一键（既不允许表内有手机号重复的情况），staff_id 需自增
语法格式为：add staff Alex Li,25,134435344,IT,2015-10-29 3、可删除指定员工信息记录，输入员工id 即可删除
语法格式：del staff 3 4、可修改员工信息 语法如下：
UPDATE staff_table SET dept = "Market" WHERE dept = "IT"(把部门是 IT 的全部更改成 Market）
UPDATE staff_table SET age = 25 WHERE name = "Alex Li" (把 name = Alex Li 的年龄改成 25 )
以上每条语句执行完毕后，要显示影响了多少条记录，（比如查询，要显示查询出多少条、修改要显示修改多少条
整体思路图：
代码奉上：
# -*- coding:utf-8 -*-
from tabulate import tabulate
import os FILE_NAME = "staff_table"
OPTION = ["id", "name", "age", "phone", "dept", "enroll_date"] # 加载员工信息表并转成指定格式
def info(file_name):
data = {}
for i in OPTION:
data[i] = []
with open(file_name, "r", encoding="utf-8") as f:
for line in f:
staff_id, name, age, phone, dept, enroll_date = line.strip().split(",")
data["id"].append(staff_id)
data["name"].append(name)
data["age"].append(age)
data["phone"].append(phone)
data["dept"].append(dept)
data["enroll_date"].append(enroll_date)
return data INFO_DATA = info(FILE_NAME) # 程序启动后就执行 # 把修改的数据写入硬盘
def write_info():
f = open("%s" % FILE_NAME, "w", encoding="utf-8")
for index, staff_tb in enumerate(INFO_DATA["id"]):
res = []
for col in OPTION:
res.append(INFO_DATA[col][index])
f.write(",".join(res)+"\n") # 报错字体颜色
def print_log(calc, log_type = "info"):
if log_type == "info":
print("\033[33;1m%s\033[0m" % calc)
elif log_type == "error":
print("\033[31;1m%s\033[0m" % calc) # 自动删除函数
def del_info(del_name, del_term):
for index, del_loop in enumerate(INFO_DATA[del_name]):
if del_loop == del_term:
for del_key in OPTION:
del INFO_DATA[del_key][index]
write_info()
return
else:
print_log("没有此序号或未检测到关键字[id=NO.]", "error") # 解析条件并返回值
def less(field, term):
result = []
for index, cont in enumerate(INFO_DATA[field]):
if float(cont) > float(term):
whole_article = []
for cont_name in OPTION:
whole_article.append(INFO_DATA[cont_name][index])
result.append(whole_article)
return result def greater(field, term):
result = []
for index, cont in enumerate(INFO_DATA[field]):
if float(cont) < float(term):
whole_article = []
for cont_name in OPTION:
whole_article.append(INFO_DATA[cont_name][index])
result.append(whole_article)
return result def equal(field, term):
result = []
for index, cont in enumerate(INFO_DATA[field]):
if cont == term:
whole_article = []
for cont_name in OPTION:
whole_article.append(INFO_DATA[cont_name][index])
result.append(whole_article)
return result def like(field, term):
result = []
for index, cont in enumerate(INFO_DATA[field]):
if term in cont:
whole_article = []
for cont_name in OPTION:
whole_article.append(INFO_DATA[cont_name][index])
result.append(whole_article)
return result # 解析 where 条件并过滤数据
def syntax_where(offside):
handle = {">": less,
"<": greater,
"=": equal,
"like": like
}
for _key, func in handle.items():
if _key in offside:
field, term = offside.split(_key)
match_result = func(field.strip(), term.strip())
return match_result
else:
print_log("语法错误：where条件只支持[>,<,=,where]", "error") # 解析 find 语句并返回结果
def syntax_find(content, condition):
find_name = condition.split("from")[0].split("find")[1].split(",")
find_name = [i.strip() for i in find_name]
if "*" in find_name[0]:
print(tabulate(content, headers=INFO_DATA, tablefmt="grid"))
else:
title_list = []
for title in content:
item_list = []
for item in find_name:
item_index = OPTION.index(item)
item_list.append(title[item_index])
title_list.append(item_list)
print(tabulate(title_list, headers=find_name, tablefmt="grid"))
print_log("成功查询到%s条数据" % len(content), "info") # 增加条目函数
def syntax_add(content, codition):
codition = codition.split("staff_table")[1].split(",")
if codition[2] in INFO_DATA["phone"]:
print_log("手机号码重复", "error")
else:
id_id = []
for add_id in INFO_DATA["id"]:
id_id.append(int(add_id))
codition.insert(0, (id_id[-1] + 1))
for last_name, last_info in zip(OPTION, codition):
INFO_DATA[last_name].append(str(last_info))
write_info()
print_log("成功增加第%s条数据" % int(len(content)+1), "info") # 删除条目函数
def syntax_del(content, codition):
codition = [i.strip() for i in codition.split(" ")]
if "id" in codition[3]:
if "id" in codition[-1]:
del_name, del_term = codition[-1].split("=")
del_info(del_name, del_term)
elif "id" in codition[3]:
del_name = codition[-3]
del_term = codition[-1]
del_info(del_name, del_term)
print_log("成功删除第%s条数据" % del_term, "info")
else:
print_log("没有此序号或未检测到关键字[id=NO.]", "error")
elif "id"not in codition and len(content) > 0:
del_info(del_name="id", del_term=content[0][0])
print_log("成功删除第%s条数据" % content[0][0], "info")
else:
print_log("没有此序号或未检测到关键字[id=NO.]", "error") # 修改的函数
def syntax_update(content, codition):
codition = codition.split("set")
if len(codition) > 1:
index_own, number = codition[1].strip().split("=")
for index_value in content:
ind_index = index_value[0]
ind_index_id = INFO_DATA['id'].index(ind_index)
INFO_DATA[index_own.strip()][ind_index_id] = number.strip()
write_info() # 修改后的数据写进文件
print_log("成功修改%s条数据" % len(content), "info")
else:
print_log("语法格式错误，不存在 set 关键词", "error") # 解析语句并执行
def syntax_parser(order):
syntax_dict = {
"find": syntax_find,
"add": syntax_add,
"del": syntax_del,
"update": syntax_update
}
if order.split(" ")[0] in ("find", "add", "del", "update"):
if "where" in order:
left_side, offside = order.split("where")
final_result = syntax_where(offside)
else:
final_result = []
for index, info_id in enumerate(INFO_DATA["id"]):
info_set = []
for i in OPTION:
info_set.append(INFO_DATA[i][index])
final_result.append(info_set)
left_side = order
order_start = order.split()[0]
if order_start in syntax_dict:
syntax_dict[order_start](final_result, left_side)
else:
print_log("语法错误!\n[find/ add/ del/ update [size] from [staff_table] where [term][>,<,=,like][parameter]\n", "error") # 让用户输入语句并执行！
def main():
while True:
order = input("staff_db:"). strip()
if not order: continue
syntax_parser(order)
main()