参考文档:
https://www.cnblogs.com/huxi/archive/2010/07/04/1771073.html
https://docs.python.org/3/library/re.html
https://mozillazg.github.io/2013/08/ansi-escape-sequences.html#
import os
import re
"""
员工信息表:
当然此表你在文件存储时可以这样表示
staff_id,name,age,phone,dept,enroll_date
1,Alex Li,22,13651054608,IT,2013-04-01
2,Jack Wang,30,13304320533,HR,2015-05-03
3,Rain Liu,25,1383235322,Sales,2016-04-22
4,Mack Cao,40,1356145343,HR,2009-03-01
现需要对这个员工信息文件,实现增删改查操作
1. 可进行模糊查询,语法至少支持下面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"
2. 查到的信息,打印后,最后面还要显示查到的条数
3. 可创建新员工纪录,以phone做唯一键,staff_id需自增
4. 可删除指定员工信息纪录,输入员工id,即可删除
5. 可修改员工信息,语法如下:
UPDATE staff_table SET dept="Market" WHERE where dept = "IT"
注意:以上需求,要充分使用函数,请尽你的最大限度来减少重复代码!
"""
default_t = """
1,Alex Li,22,13651054608,IT,2013-04-01
2,Jack Wang,30,13304320533,HR,2015-05-03
3,Rain Liu,25,1383235322,Sales,2016-04-22
4,Mack Cao,40,1356145343,HR,2009-03-01
"""
# 首先实现列表和文件的转换
file_path = "staff_info.txt"
# 生成默认表
if not os.path.exists(file_path):
with open(file_path, "w", encoding="utf-8") as f:
f.write(default_t.strip())
# 表头
column = ["staff_id", "name", "age", "phone", "dept", "enroll_date"]
# 读出文件生成表
def load(file):
temp = []
with open(file, "r", encoding="utf-8") as f:
for i in f:
temp.append(i.strip().split(","))
return temp
# 保存列表到文件
def save(table_list):
table_str = ''
for i in table_list:
table_str = table_str + ','.join(i) + "\n"
with open(file_path, "w", encoding="utf-8") as f:
f.write(table_str.strip())
# 实现添加员工
def add(table_list, user_add):
# 验证电话号码
for i in table_list:
if i[3] == user_add[2]:
print("电话号码不唯一")
return
# 实现id自动增加
table_list.append(list(str(int(table_list[-1][0]) + 1)) + user_add)
save(table_list)
# 实现删除员工
def delete(table_list, user_del):
for i in table_list:
if i[0] == user_del:
table_list.remove(i)
save(table_list)
# 修改员工信息
def modify(table_list, user_mod, column=column):
con_index = column.index(user_mod[1][0])
set_index = column.index(user_mod[0][0])
for i in table_list:
print(i)
if i[con_index] == user_mod[1][1]:
i[set_index] = user_mod[0][1]
save(table_list)
def show(result_list, column=column):
print(column)
for i in result_list:
print(i)
print("共查询到 {} 条数据".format(len(result_list)))
def sql_select(table_list, column=column):
sql = input("\033[32m请输入您的查询sql语句: \033[0m")
p1 = r"select (.*) from staff_list where (.*);"
p2 = r"select (.*) from staff_list;"
rt1 = re.fullmatch(p1, sql)
rt2 = re.fullmatch(p2, sql)
if rt1:
print(rt1.group(1), rt.group(2))
elif rt2:
if rt2.group(1).strip() == "*":
show(table_list)
else:
sl = rt2.group(1).strip().split(",")
rt_l = []
if set(sl).issubset(set(column)):
for i in range(len(table_list)):
row = []
for j in sl:
row.append(table_list[i][column.index(j)])
rt_l.append(row)
show(rt_l)
else:
print("查询的列不存在...")
else:
print("\033[31m语法不正确\033[0m")
def sql_delete(table_list):
user_delete = input("请输入你想删除的员工id: ")
delete(table_list, user_delete)
def sql_update(table_list, column=column):
sql_input = input("\033[32m请输入您的更新sql语句: \033[0m")
p = "update staff_list set (.*) where (.*);"
rt = re.fullmatch(p, sql_input)
if rt:
set_l = [i.strip().strip("\"").strip("\'") for i in rt.group(1).split("=")]
con_l = [i.strip().strip("\"").strip("\'") for i in rt.group(2).split("=")]
modify(table_list, [set_l, con_l], column)
else:
print("请输入正确的sql语句...")
def sql_insert(table_list):
sql_input = input("\033[32m请输入您的插入sql语句: \033[0m")
p = "insert into staff_list values\((.*)\);"
rt = re.fullmatch(p, sql_input)
if rt:
user_add = [x.strip().strip("\"").strip("\'") for x in rt.group(1).split(",")]
if len(user_add) == 6:
user_add = user_add[1:]
add(table_list, user_add)
elif len(user_add) == 5:
add(table_list, user_add)
else:
print("员工参数列数不对!!!")
else:
print("请输入正确的sql语句...")
# 欢迎菜单
def menu():
print("Welcome".center(80, "="))
print("""
\033[34m请选择您想进行的操作:\033[0m
1. 查询员工:
2. 删除员工
3. 修改员工
4. 增加员工
q. 退出程序
""")
def main():
# add(staff_table, ['fbo', '30', "111111111", 'Sales', "2017-12-19"])
# delete(staff_table, input())
# modify(staff_table, (("dept", "HEHE"), ("name", "fbo")))
while True:
staff_table = load(file_path)
menu()
user_input = input(">>>")
if user_input == 'q' or user_input == "Q":
break
elif user_input == "1":
sql_select(staff_table)
elif user_input == "2":
sql_delete(staff_table)
elif user_input == "3":
sql_update(staff_table)
elif user_input == "4":
sql_insert(staff_table)
else:
print("\033[31m输入错误, 请重新输入!!!\033[0m")
if __name__ == "__main__":
main()
网友评论