15-作业：员工信息表查询
1.作业说明
现要求你写一个简单的员工信息增删改查程序
文件存储时可以这样表示
1,Alex Li,22,13651054608,IT,2013-04-01
2,Jack Wang,28,13451024608,HR,2015-01-07
3,Rain Wang,21,13451054608,IT,2017-04-01
4,Mack Qiao,44,15653354208,Sales,2016-02-01
5,Rachel Chen,23,13351024606,IT,2013-03-16
6,Eric Liu,19,18531054602,Marketing,2012-12-01
7,Chao Zhang,21,13235324334,Administration,2011-08-08
8,Kevin Chen,22,13151054603,Sales,2013-04-01
9,Shit Wen,20,13351024602,IT,2017-07-03
10,Shanshan Du,26,13698424612,Operation,2017-07-02
1.可进行模糊查询，语法至少支持下面3种查询语法:
find name,age from staff_table where age > 22
find * from staff_table where dept = "IT"
find * from staff_table where enroll_date like "2013"
2.可创建新员工纪录，以phone做唯一键(即不允许表里有手机号重复的情况)，staff_id需自增
语法: add staff_table Alex Li,25,134435344,IT,2015-10-29
3.可删除指定员工信息纪录，输入员工id，即可删除
语法: del from staff where id=3
4.可修改员工信息，语法如下:
UPDATE staff_table SET dept="Market" WHERE dept = "IT" 把所有dept=IT的纪录的dept改成Market
UPDATE staff_table SET age=25 WHERE name = "Alex Li" 把name=Alex Li的纪录的年龄改成25
5.以上每条语名执行完毕后，要显示这条语句影响了多少条纪录。 比如查询语句 就显示 查询出了多少条、修改语句就显示修改了多少条等。
2.我的版本
# # -*- coding:utf-8 -*- """ 需求基本完成，程序还存在bug，感觉代码写的有点混乱！不太清楚思路 难度有点大，思考了好几天，睡觉都在想。。。 重要知识点：read readline join split """ import os def find(): msg = """ 查询格式：1> find * from staff_table where dept = "IT" 2> find * from staff_table where enroll_date like "2013" 3> find name,age from staff_table where age > 22 你要查询的语句格式序号》》""" choice = input(msg) all_list = [] list1 = [] list2 = [] with open('staff', 'r') as f: for i in f: list1.append(i) i = i.split(',') all_list.append(i) if choice.isdigit(): choice = int(choice) if choice == 1: select = input('find * from staff_table where dept = ').strip() select = select[1:-1] for index,item in enumerate(all_list): #print(item[4]) if select == item[4]: print(item) print(item[4]) print(item[4][0:5]) print(list1[index],end='') list2.append(list1[index]) print('\n查询到了%s条数据'%len(list2)) elif choice == 2: select = input('find * from staff_table where enroll_date like ').strip() select = select[1:-1] if select.isdigit(): for index,item in enumerate(all_list): if int(select) == int(item[-1][0:4]): print(list1[index],end='') list2.append(list1[index]) print('\n查询到了%s条数据'%len(list2)) else: print('你输入的有误') elif choice == 3: select = input('find name,age from staff_table where age > ').strip() if select.isdigit(): for index,item in enumerate(all_list): if int(select) < int(item[2]): list2.append(list1[index]) for i in list2: line = i.split(',') print(line[1],line[2]) print('\n查询到了%s条数据'%len(list2)) else: print('你输入的有误') else: print('你输入的有误') else: print('你输入的有误') def add(): add_data = ''' 请输入新员工信息: [格式:Alex Li,25,134435344,IT,2015-10-29] >>''' add_data = input(add_data) # 如何判断输入不是Alex Li,25,134435344,IT,2015-10-29这种格式？ if len(add_data.split(',')) == 5: phone = add_data.split(',')[2] data_list = [] phone_list = [] with open('staff', 'r+') as f: for line in f: line = line.split(',') data_list.append(line) phone_list.append(line[3]) if phone in phone_list: print('该手机号已经存在！') else: new_index = int(data_list[-1][0])+1 add_data = str(new_index)+','+add_data f.write('\n'+add_data) print('添加成功') else: print('你输入的有误') def delete(): del_data = ''' 请输入要删除的员工信息: 格式: del from staff where id=3 del from staff where id=''' del_data = input(del_data) if del_data.isdigit(): del_data = int(del_data) f_name = "staff" f_new_name = "%s.bak" % f_name data_list = [] f1 = open(f_name, 'r') f2 = open(f_new_name, 'w') for line in f1: line = line.split(',') if del_data == int(line[0]): line = ','.join(line) data_list.append(line) if len(data_list) > 0: f1.seek(0) for line in f1: if data_list[0] in line: new_line = '' else: new_line = line f2.write(new_line) print("删除成功") f1.close() f2.close() os.replace(f_new_name, f_name) else: print('你要删除的用户不存在') else: print("输入有误请重新输入") def update(): update_data = ''' 请输入要修改的员工信息: 格式：1> UPDATE staff_table SET dept="Market" WHERE dept = "IT" 2> UPDATE staff_table SET age=25 WHERE name = "Alex Li" >>>''' choice = input(update_data).strip() if choice.isdigit(): choice = int(choice) if choice == 1: new_dept = input('UPDATE staff_table SET dept=').strip() new_dept = new_dept[1:-1] old_dept = input('WHERE dept = ').strip() old_dept = old_dept[1:-1] f_name = "staff" f_new_name = "%s.bak" % f_name data_list = [] list2 = [] f1 = open(f_name, 'r') f2 = open(f_new_name, 'w') for line in f1: line = line.split(',') data_list.append(line) for item in data_list: if old_dept == item[4]: item[4] = new_dept list2.append(item) new_item = (',').join(item) else: new_item = (',').join(item) f2.write(new_item) if len(list2) > 0 and new_dept: f1.close() f2.close() os.replace(f_new_name, f_name) print("修改%s条数据成功" % len(list2)) else: print("你要修改的数据不存在") elif choice == 2: sql_age = input('UPDATE staff_table SET age=') sql_name = input('WHERE name = ') sql_name = sql_name[1:-1] f_name = "staff" f_new_name = "%s.bak" % f_name data_list = [] f1 = open(f_name, 'r') f2 = open(f_new_name, 'w') for line in f1: line = line.split(',') if sql_name == line[1]: data_list.append(line) if len(data_list) > 0 and sql_age.isdigit(): old_line = (',').join(data_list[0]) data_list[0][2] = sql_age new_list = (',').join(data_list[0]) f1.seek(0) for line in f1: if old_line in line: new_line = new_list else: new_line = line f2.write(new_line) print("修改%s条数据成功"%len(data_list)) f1.close() f2.close() os.replace(f_new_name, f_name) else: print("你要修改的数据不存在") else: print("你输入的有误！") else: print("你输入的有误！") def main(): menu = ''' --------员工信息管理系统------ 1.模糊查询 2.创建新员工记录 3.删除员工记录 4.修改员工信息 --------------------------- ''' while True: print(menu) choice = input('q 退出| 输入你的选择>') if choice.isdigit(): choice = int(choice) if choice == 1: find() elif choice == 2: add() elif choice == 3: delete() elif choice == 4: update() else: print("11输入错误，请重新输入") elif choice == 'q': exit() else: print("输入错误，请重新输入") if __name__ == '__main__': main()
3.修改意见
- 100个elif 是否写100个函数？
- 变量名命名不规范
- 先判断用户输入的非法性，再进行下一步
- read 、readlines 和for i in f :read 的区别
- join 和 split
4.修改程序
# # -*- coding:utf-8 -*- """ 需求基本完成，程序还存在bug，感觉代码写的有点混乱！不太清楚思路 难度有点大，思考了好几天，睡觉都在想。。。 重要知识点：read readline join split """ import os def find(): msg = """ 查询格式：1> find * from staff_table where dept = "IT" 2> find * from staff_table where enroll_date like "2013" 3> find name,age from staff_table where age > 22 你要查询的语句格式序号》》""" choice = input(msg) if choice.isdigit(): choice = int(choice) all_list = [] all_list2 = [] find_list = [] with open('staff', 'r') as f: for i in f: all_list2.append(i) i = i.split(',') all_list.append(i) if choice == 1: select = input('find * from staff_table where dept = ').strip() select = select[1:-1] for index,item in enumerate(all_list): if select == item[4]: print(item) print(item[4]) print(item[4][0:5]) print(all_list2[index],end='') find_list.append(all_list2[index]) print('\n查询到了%s条数据'%len(find_list)) elif choice == 2: select = input('find * from staff_table where enroll_date like ').strip() select = select[1:-1] if select.isdigit(): for index,item in enumerate(all_list): if int(select) == int(item[-1][0:4]): print(all_list2[index],end='') find_list.append(all_list2[index]) print('\n查询到了%s条数据'%len(find_list)) else: print('你输入的有误') elif choice == 3: select = input('find name,age from staff_table where age > ').strip() if select.isdigit(): for index,item in enumerate(all_list): if int(select) < int(item[2]): find_list.append(all_list2[index]) for i in find_list: line = i.split(',') print(line[1],line[2]) print('\n查询到了%s条数据'%len(find_list)) else: print('你输入的有误') else: print('你输入的有误') else: print('你输入的有误') def add(): add_data = ''' 请输入新员工信息: [格式:Alex Li,25,134435344,IT,2015-10-29] >>''' add_data = input(add_data) # 如何判断输入不是Alex Li,25,134435344,IT,2015-10-29这种格式？ if len(add_data.split(',')) == 5: phone = add_data.split(',')[2] data_list = [] phone_list = [] with open('staff', 'r+') as f: for line in f: line = line.split(',') data_list.append(line) phone_list.append(line[3]) if phone in phone_list: print('该手机号已经存在！') else: new_index = int(data_list[-1][0])+1 add_data = str(new_index)+','+add_data f.write('\n'+add_data) print('添加成功') else: print('你输入的有误') def delete(): del_data = ''' 请输入要删除的员工信息: 格式: del from staff where id=3 del from staff where id=''' del_data = input(del_data) if del_data.isdigit(): del_data = int(del_data) f_name = "staff" f_new_name = "%s.bak" % f_name data_list = [] f1 = open(f_name, 'r') f2 = open(f_new_name, 'w') for line in f1: line = line.split(',') if del_data == int(line[0]): line = ','.join(line) data_list.append(line) if len(data_list) > 0: f1.seek(0) for line in f1: if data_list[0] in line: new_line = '' else: new_line = line f2.write(new_line) print("删除成功") f1.close() f2.close() os.replace(f_new_name, f_name) else: print('你要删除的用户不存在') else: print("输入有误请重新输入") def update(): update_data = ''' 请输入要修改的员工信息: 格式：1> UPDATE staff_table SET dept="Market" WHERE dept = "IT" 2> UPDATE staff_table SET age=25 WHERE name = "Alex Li" >>>''' choice = input(update_data).strip() if choice.isdigit(): choice = int(choice) if choice == 1: new_dept = input('UPDATE staff_table SET dept=').strip() new_dept = new_dept[1:-1] old_dept = input('WHERE dept = ').strip() old_dept = old_dept[1:-1] f_name = "staff" f_new_name = "%s.bak" % f_name data_list = [] list2 = [] f1 = open(f_name, 'r') f2 = open(f_new_name, 'w') for line in f1: line = line.split(',') data_list.append(line) for item in data_list: if old_dept == item[4]: item[4] = new_dept list2.append(item) new_item = (',').join(item) else: new_item = (',').join(item) f2.write(new_item) if len(list2) > 0 and new_dept: f1.close() f2.close() os.replace(f_new_name, f_name) print("修改%s条数据成功" % len(list2)) else: print("你要修改的数据不存在") elif choice == 2: sql_age = input('UPDATE staff_table SET age=') sql_name = input('WHERE name = ') sql_name = sql_name[1:-1] f_name = "staff" f_new_name = "%s.bak" % f_name data_list = [] f1 = open(f_name, 'r') f2 = open(f_new_name, 'w') for line in f1: line = line.split(',') if sql_name == line[1]: data_list.append(line) if len(data_list) > 0 and sql_age.isdigit(): old_line = (',').join(data_list[0]) data_list[0][2] = sql_age new_list = (',').join(data_list[0]) f1.seek(0) for line in f1: if old_line in line: new_line = new_list else: new_line = line f2.write(new_line) print("修改%s条数据成功"%len(data_list)) f1.close() f2.close() os.replace(f_new_name, f_name) else: print("你要修改的数据不存在") else: print("你输入的有误！") else: print("你输入的有误！") def main(): menu = ''' --------员工信息管理系统------ 1.模糊查询 2.创建新员工记录 3.删除员工记录 4.修改员工信息 --------------------------- ''' func_dict = { '1': find, '2': add, '3': delete, '4': update } while True: print(menu) choice = input('q 退出| 输入你的选择>') if choice in func_dict: func_dict[choice]() elif choice == 'q': exit() else: print("输入错误，请重新输入") if __name__ == '__main__': main()
5.文件读取
# 几种不同的读取和遍历文件的方法比较：
#
# 如果文件很小，read()一次性读取最方便；
# 如果不能确定文件大小，反复调用read(size)比较保险；
# 如果是配置文件，调用readlines()最方便。
# 普通情况，使用for循环更好，速度更快。
with open('aaa.txt', 'r') as f: data = f.read() print(data) print(type(data)) # 1,Alex Li,22,13651054608,IT,2013-04-01 # 2,Jack Wang,28,13451024608,HR,2015-01-07 # 3,Rain Liu,25,1383235322,Sales,2016-04-22 all_list=list(data) print(all_list) # ['1', ',', 'A', 'l', 'e', 'x', ' ', 'L', 'i', ',', '2', '2', ',', '1', ] msg_list = [] line = data.split(',') # ['1', 'Alex Li', '22', '13651054608', 'IT', '2013-04-01\n2',] print(line) msg_list.append(line) print(msg_list) # [['1', 'Alex Li', '22', '13651054608', 'IT', '2013-04-01\n2',]]
with open('aaa.txt', 'r') as f: data = f.readlines() print(data) # ['1,Alex Li,22,13651054608,IT,2013-04-01\n', '2,Jack Wang,28,13451024608,HR,2015-01-07\n'] print(type(data)) # <class 'list'> split_list = [] for line in data: item = line.split(',') # ['1', 'Alex Li', '22', '13651054608', 'IT', '2013-04-01\n2',] split_list.append(item) print(split_list) # [['1', 'Alex Li', '22', '13651054608', 'IT', '2013-04-01\n2',]]
all_list = [] with open('aaa.txt', 'r') as f: for line in f: all_list.append(line) # 1,Alex Li,22,13651054608,IT,2013-04-01\n print(all_list) # ['1,Alex Li,22,13651054608,IT,2013-04-01\n', '2,Jack Wang,28,13451024608,HR,2015-01-07\n']
6.join split方法
# join方法 split方法
# list---> str
data = ('\n').join(all_list)
print(data)
# str --> list
li = data.split(',')
print(li)
