员工信息表实现增删改查操作

【项目要求】

可进行模糊查询，语法至少支持下面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命令修改指定员工信息。

方法一：利用数据库实现！

具体代码如下：

  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]()

员工信息表 实现增删改查操作

员工信息表实现增删改查操作