【项目要求】
可进行模糊查询，语法至少支持下面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%%"
可修改员工信息，语法如下:
update staff_table set dept="Market" where dept = "IT"
1. 支持至少三种方法的select查询，并在最后显示查询到的条数。
2. 创建新员工记录，以phone为唯一键，staff_id自增
3. 输入员工id可删除指定员工信息记录
4. 可以使用update命令修改指定员工信息。
2. 创建新员工记录，以phone为唯一键，staff_id自增
3. 输入员工id可删除指定员工信息记录
4. 可以使用update命令修改指定员工信息。
方法一：利用数据库实现！
具体代码如下：
1 from sqlalchemy import create_engine 2 from sqlalchemy.ext.declarative import declarative_base 3 from sqlalchemy import Column,Integer,CHAR,VARCHAR,DATE 4 from sqlalchemy.orm import sessionmaker 5 6 #建立链接 7 engine = create_engine("mysql+pymysql://root:761379545@127.0.0.1:3306/test_db?charset=utf8", max_overflow=5) 8 Session = sessionmaker(bind=engine) 9 session = Session() 10 Base = declarative_base() 11 class Emp(Base): 12 __tablename__ = 'staff_table' 13 id = Column(Integer,primary_key=True,autoincrement=True) 14 name = Column(VARCHAR(20),nullable=False) 15 age = Column(Integer,nullable=True) 16 phone = Column(CHAR(11),unique=True) 17 dept = Column(VARCHAR(255)) 18 enroll_date = Column(CHAR(20)) 19 # #创建表格 20 # Base.metadata.create_all(engine) 21 22 def search_user(): 23 msg =''' 24 可进行模糊查询，语法至少支持下面3种: 25 select name,age from staff_table where age > 22 26 select * from staff_table where dept = "IT" 27 select * from staff_table where enroll_date like('2013%%') 28 ''' 29 while 1: 30 print(msg) 31 cmd = input('请输入要进行操作的语句【返回B】： >>>').strip() 32 if not cmd:continue 33 if cmd.lower() == 'b': 34 break 35 try: 36 data = engine.execute(cmd) 37 res= data.fetchall() 38 if not res: 39 print('没有满足条件的员工！') 40 else: 41 for row in res: 42 print(row) 43 except Exception: 44 print('请输入正确的查询语句！') 45 continue 46 47 def add_user(): 48 ''' 49 新增员工信息 50 ''' 51 while 1: 52 user_name = input('请输入员工名【返回B】：').strip() 53 if user_name.lower() == 'b': 54 break 55 user_age = input('请输入年龄：').strip() 56 user_age_int = int(user_age) 57 user_phone = input('请写入手机号：').strip() 58 user_dept = input('所属部门').strip() 59 user_enroll_date = input('入职年月日【年-月-日】：').strip() 60 try: 61 session.add(Emp(name=user_name,age=user_age_int,phone=user_phone,dept=user_dept,enroll_date=user_enroll_date)) 62 session.commit() 63 print('员工添加成功！') 64 except Exception: 65 print('员工添加失败！') 66 continue 67 68 def modify_user(): 69 msg = ''' 70 可修改员工信息，语法如下: 71 update staff_table set dept="Market" where dept = "IT" 72 ''' 73 while 1: 74 print(msg) 75 cmd = input('请输入要进行操作的语句【返回B】： >>>').strip() 76 if not cmd: continue 77 if cmd.lower() == 'b': 78 break 79 try: 80 engine.execute(cmd) 81 print('修改成功！') 82 except Exception: 83 print('请输入正确的查询语句！') 84 continue 85 86 def delete_user(): 87 ''' 88 删除员工信息 89 ''' 90 while 1: 91 del_id = input('请输入您要删除的员工ID【返回B】:').strip() 92 if not del_id:continue 93 if del_id.lower() == 'b': 94 break 95 if del_id.isdigit(): 96 del_id_int = int(del_id) 97 try: 98 session.query(Emp).filter(Emp.id==del_id_int).delete() 99 session.commit() 100 print('删除成功！') 101 except Exception: 102 print('删除失败！') 103 continue 104 105 #主程序 106 if __name__ == '__main__': 107 print('欢迎进入员工信息操作界面！'.center(23,'*')) 108 while 1: 109 choice = input('请输入想要进行的操作代码：' 110 ' 【1】查询员工信息' 111 ' 【2】新增员工信息' 112 ' 【3】修改员工信息' 113 ' 【4】删除员工信息' 114 ' 【Q】退出操作' 115 ' >>>').strip() 116 if choice == '1': 117 search_user() 118 elif choice == '2': 119 add_user() 120 elif choice == '3': 121 modify_user() 122 elif choice == '4': 123 delete_user() 124 elif choice.lower() == 'q': 125 print('退出成功，感谢您的使用！'.center(22,'*')) 126 break 127 else: 128 print('您的操作有误，请重新选择！')
方法二：函数加文件读写实现（有待完善）！
1 import os 2 def inquiry_system(): 3 welcome =''' 4 -----------员工信息查询系统------------ 5 操作说明： 6 增加ADD:1 7 删除DELETE:2 8 修改CHANGE:3 9 查询SEARCH:4 10 退出QUIT:5 11 --------------------------------------- 12 ''' 13 print(welcome) 14 15 def add(): 16 # 添加语法： name,age,phone,dept,enroll-date 17 add_staff =input('请输入要添加的员工信息：(添加格式： name,age,phone,dept(大写）,enroll-date) ') 18 list_data =add_staff.split(',') 19 list_all =[] 20 f = open('staff_table.txt','r+',encoding='utf8') 21 for line in f: 22 list_all.append(line.strip().split(',')[3]) 23 if list_data[2] in list_all: 24 print('用户已存在！') 25 f.close() 26 else: 27 for line in f: 28 f.write(line) 29 staff_id = str(len(list_all) + 1) 30 list_data.insert(0, str(staff_id)) 31 f.write(' ') 32 f.write(','.join(list_data)) 33 f.close() 34 print('添加成功') 35 36 def delete(): 37 del_staff_id =input('请输入要删除的员工ID：') 38 f = open('staff_table.txt','r',encoding='utf8') 39 f1 = open('new_staff_table.txt','w',encoding='utf8') 40 for line in f: 41 each_line = line.split(',') 42 if int(each_line[0]) != int(del_staff_id): 43 f1.write(line) 44 #若想实现删除后员工ID顺序排列使用下三行代码！ 45 # elif int(each_line[0]) > int(del_staff_id): 46 # each_line[0] =str(int(each_line[0])-1) 47 # f1.write(','.join(each_line)) 48 else: 49 continue 50 f.close() 51 f1.close() 52 os.remove('staff_table.txt') #删除指定目录 53 os.rename('new_staff_table.txt', 'staff_table.txt') #重命名 54 55 def change(): 56 # 修改请输入（注意空格和没有引号）：UPDATE staff_table SET dept = IT where dept = MARKET 57 data = input("请输入您要修改的信息：(修改格式：UPDATE staff_table SET dept = IT where dept = MARKET) ") 58 old = data.split(' ')[5] 59 new = data.split(' ')[9] 60 f = open('staff_table.txt', 'r', encoding='utf-8') 61 f1 = open('new_staff_table.txt', 'w', encoding="utf-8") 62 for line in f: 63 if old in line: 64 line = line.replace(old, new) 65 f1.write(line) 66 f.close() 67 f1.close() 68 os.remove('staff_table.txt') 69 os.rename('new_staff_table.txt', 'staff_table.txt') 70 print('修改成功') 71 72 def search(): 73 formation=''' 74 查询方式1：（按年龄查询）select * from staff_table where age >= 22 75 查询方式2：（按职业查询）select * from staff_table where dept = "IT" 76 查询方式3：（按年份查询）select * from staff_table where enroll_date like "2013" 77 ''' 78 print(formation) 79 80 while True: 81 search_way = input('请输入您的查询方式(退出Q)：') 82 res = [] 83 count = 0 84 if search_way.lower() =='q': 85 exit() 86 if search_way =='1': 87 age_search = input('请输入要查询的最小年龄：') 88 with open('staff_table.txt','r',encoding='utf8') as f: 89 for line in f: 90 each_line =line.split(',') 91 if int(each_line[2]) >=int(age_search): 92 res.append(line) 93 count += 1 94 print("查询的结果为：") 95 for per in res: 96 print(per) 97 print('共有%s个员工满足查询信息条件!'%count) 98 break 99 if search_way =='2': 100 dept_search = input('请输入要查询的职业：') 101 with open('staff_table.txt', 'r', encoding='utf8') as f: 102 for line in f: 103 each_line = line.split(',') 104 res.append(each_line) 105 result =filter(lambda l:l[4]==dept_search.upper(),res) 106 for i in result: 107 count+=1 108 print(i) 109 print('共有%s个员工满足查询信息条件!'%count) 110 break 111 if search_way =='3': 112 enroll_date = input('请输入要查询的年份：') 113 with open('staff_table.txt', 'r', encoding='utf8') as f: 114 for line in f: 115 each_line = line.split(',') 116 res.append(each_line) 117 result = filter(lambda l:enroll_date in l[5],res) 118 for i in result: 119 count += 1 120 print(i) 121 print('共有%s个员工满足查询信息条件!' %count) 122 break 123 else: 124 print('请重新输入！') 125 126 my_choice = { 127 '1':add, 128 '2':delete, 129 '3':change, 130 '4':search, 131 '5':'quit' 132 } 133 while True: 134 inquiry_system() 135 choice = input('请输入操作序号：') 136 if choice not in my_choice: 137 print("输入错误，请重新输入！") 138 continue 139 if int(choice) == 5: 140 exit() 141 else: 142 my_choice[choice]()