Python自动化开发—EX.6(员工信息表查询)
员工信息表程序,实现增删改查操作
可进行模糊查询,语法至少支持下面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"
查到的信息,打印后,最后面还要显示查到的条数
可创建新员工纪录,以phone做唯一键,staff_id需自增
可删除指定员工信息纪录,输入员工id,即可删除
可修改员工信息,语法如下:
UPDATE staff_table SET dept="Market" WHERE where dept = "IT"
注意:以上需求,要充分使用函数,请尽你的最大限度来减少重复代码
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
import sys,os #打印初始界面 def menu(): info = ''' ==========员工信息表========== 1、增加员工信息 2、删除员工信息 3、修改员工信息 4、查询员工信息 5、退出 ============================= ''' print(info) #读取员工信息列表 def view_file(): staff_list = [] with open('staff_table', 'r', encoding='utf-8') as staff_f: for i in staff_f: i = i.strip('\n').split(',') staff_list.append(i) #print(staff_list) return staff_list #最新的员工信息列表写入文件 def write_file(staff_list): with open('staff_table','w',encoding='utf-8') as staff_f: for i in staff_list: i = ','.join(i) + '\n' staff_f.write(i) def add(): print('=*=' * 20) print("添加的内容格式请按照: Alex Li,25,13651054608,IT,2015-10-29") print('=*=' * 20) staff_list = view_file() addInfo = input("add staff_table ").strip() addInfo_list = addInfo.split(',') if len(addInfo_list) == 5: phone_list = [] for i in staff_list: phone_list.append(i[3]) if addInfo_list[2] in phone_list: print("phone 号码已存在,此次添加无效!") else: max_id = int(staff_list[-1][0]) add_id = max_id+1 addInfo_list.insert(0,add_id) addInfo_list[0] = str(addInfo_list[0]) staff_list.append(addInfo_list) write_file(staff_list) print("以下员工信息已添加成功!") print(','.join(addInfo_list)) else: print("%s为无效的输入!" % addInfo) def delete(): print('=*=' * 20) print("请输入您想要删除员工的staff_id") staff_list = view_file() id = input('del from staff where id = ').strip() if id.isdigit(): id = int(id) delete_list = [] max_id = int(staff_list[-1][0])#员工信息表最长ID if id <= max_id: for i,k in enumerate(staff_list): if int(k[0]) == id: delete_list.append(k) del staff_list[i] if len(delete_list) == 0: print("staff_ID为%d的员工不存在..." % id) else: print("以下员工已被删除成功!") for i in delete_list: #print(i) print(','.join(i)) write_file(staff_list) else: print("staff_id:%d 不存在!" % id) else: print("%s为无效的输入..." % id) def update(): pass update_info = ''' =*==*==*==*==*==*==*==*==*==*==*==*==*==*==*==*==*==*==*==*= 只有age和dept能被修改,修改语句如下(请任选以下修改语句): 1、UPDATE staff_table SET dept="Market" WHERE dept = "IT" 2、UPDATE staff_table SET age=25 WHERE name = "Alex Li" ''' print(update_info) staff_list = view_file() update_choice = input("您想根据什么修改信息?(name or dept):").strip() if update_choice == 'dept': old_dept = input("请输入修改条件dept:").strip() new_dept = input("请输入修改之后dept的值:").strip() updateDept_list = [] for i in staff_list: if i[4] == old_dept: i[4] = new_dept updateDept_list.append(i) if len(updateDept_list) > 0: print("以下信息已经修改成功!") for i in updateDept_list: print(','.join(i)) write_file(staff_list) else: print("%s不存在!" % old_dept) elif update_choice == 'name': Name = input("请输入修改条件name的值:").strip() new_age = input("请输入修改后的age:").strip() updateAge_list = [] for i in staff_list: if i[1] == Name: i[2] = new_age updateAge_list.append(i) if len(updateAge_list) > 0: print("以下信息已经修改成功!") for i in updateAge_list: print(','.join(i)) write_file(staff_list) else: print("%s不存在!" % Name) def find(): find_info = ''' =*==*==*==*==*==*==*==*==*==*==*==*==*==*==*==*==*==*==*==*= 本系统支持以下三种查询方法(查询条件分别为:age、dept、enroll_date): 1、find name,age from staff_table where age > 22 2、find * from staff_table where dept = "IT" 3、find * from staff_table where enroll_date like "2013" ''' print(find_info) staff_list = view_file() find_choice = input("您想使用哪种方法查询员工信息(请填写查询条件):").strip() if find_choice == 'dept': find_dept = input("find * from staff_table where dept = ") findDept_list = [] for i in staff_list: if i[4] == find_dept: findDept_list.append(i) if len(findDept_list) > 0: print("符合条件的信息如下:") for i in findDept_list: print(','.join(i)) else: print("无dept为%s的员工信息!" % find_dept) elif find_choice == 'age': find_age = input("find * from staff_table where age > ") findAge_list = [] for i in staff_list: if int(i[2]) > int(find_age): findAge_list.append(i) if len(findAge_list) > 0: print("符合条件的信息如下:") for i in findAge_list: print(','.join(i)) else: print("无age大于%s的员工信息!" % find_age) elif find_choice == 'enroll_date': findEnroll_date = input("find * from staff_table where enroll_date like ") findEnroll_date_list = [] for i in staff_list: i_date = i[5] i_date = i_date.split('-')[0] if i_date == findEnroll_date: findEnroll_date_list.append(i) if len(findEnroll_date_list) > 0: print("符合条件的信息如下:") for i in findEnroll_date_list: print(','.join(i)) else: print("Enroll_date为%s 的信息不存在!" % findEnroll_date) else: print("%s为无效的输入!" % find_choice) def main(): while True: menu() choice = input("请选择:").strip() if choice == '1': add() elif choice == '2': delete() elif choice == '3': update() elif choice == '4': find() elif choice == '5': sys.exit("logout......") else: print("无效的输入!") #write_file(view_file()) staff_list = view_file() print(staff_list) main()