1 from sqlalchemy importcreate_engine2 from sqlalchemy.ext.declarative importdeclarative_base3 from sqlalchemy importColumn,Integer,ForeignKey,CHAR,VARCHAR,UniqueConstraint4 from sqlalchemy.orm importsessionmaker5 #建立链接
6 engine = create_engine("mysql+pymysql://root:761379545@127.0.0.1:3306/test_db?charset=utf8", max_overflow=5)7 Base =declarative_base()8 Session = sessionmaker(bind=engine)9 session =Session()10
11 #创建用户信息表
12 classUser(Base):13 __tablename__ = 'user'
14 uid = Column(Integer,primary_key=True,autoincrement=True)15 name = Column(CHAR(20),nullable=False)16 pwd = Column(CHAR(20),nullable=False)17 roll_id = Column(Integer,ForeignKey('roll.rid'))18 #__table_args__的参数必须是元组的形式
19 __table_args__ =(20 UniqueConstraint(name,pwd,name='uni_np'), #用户名和密码不能重复!
21 )22
23 #创建权限表
24 classAuthority(Base):25 __tablename__ ='authority'
26 aid = Column(Integer,primary_key=True,autoincrement=True)27 auth = Column(VARCHAR(255),nullable=True)28
29 #创建角色表
30 classRoll(Base):31 __tablename__ ='roll'
32 rid = Column(Integer,primary_key=True,autoincrement=True)33 dep = Column(VARCHAR(255),nullable=False)34
35 #角色权限关系表
36 classRo_au(Base):37 __tablename__ ='ro_au'
38 id = Column(Integer,primary_key=True,autoincrement=True)39 rid = Column(Integer,ForeignKey('roll.rid'))40 aid = Column(Integer,ForeignKey('authority.aid'))41
42 #创建原始关系表
43 deforiginal_table():44 session.add_all([45 Roll(dep='项目经理'),46 Roll(dep='技术部'),47 Roll(dep='运营部'),48 Roll(dep='人事部'),49 Roll(dep='后勤部'),50 Roll(dep='普通员工'),51 Authority(auth='查看个人信息'),52 Authority(auth='更改用户信息'),53 Authority(auth='删除用户'),54 Authority(auth='设定项目整体方案'),55 Authority(auth='查看项目进度'),56 Authority(auth='查看项目经费'),57 Authority(auth='查看项目计划'),58 Authority(auth='项目技术方案'),59 Authority(auth='项目支出管理'),60 Authority(auth='项目人事安排'),61 Authority(auth='项目后勤保障'),62 ])63 session.commit()64 session.add_all([65 User(name='alex',pwd='3714',roll_id=1),66 User(name='egon',pwd='123456',roll_id=2),67 User(name='sl',pwd='761379545',roll_id=2),68 User(name='eric',pwd='123456',roll_id=3),69 User(name='mick',pwd='123456',roll_id=4),70 User(name='john',pwd='123456',roll_id=5),71 User(name='kaka',pwd='123456',roll_id=6),72 ])73 session.add_all([74 Ro_au(rid=1,aid=1),Ro_au(rid=1,aid=5),Ro_au(rid=1,aid=6),75 Ro_au(rid=2, aid=1),Ro_au(rid=2, aid=4),Ro_au(rid=2, aid=8),76 Ro_au(rid=3, aid=1),Ro_au(rid=3, aid=5),Ro_au(rid=3, aid=6),Ro_au(rid=3, aid=7),77 Ro_au(rid=4, aid=1),Ro_au(rid=4, aid=2),Ro_au(rid=4, aid=3),78 Ro_au(rid=5, aid=1),Ro_au(rid=5, aid=9),Ro_au(rid=5, aid=11),79 Ro_au(rid=6, aid=1),80 ])81 session.commit()82
83 #创建数据库所有表
84 defcreat_db():85 Base.metadata.create_all(engine)86
87 #删除数据库所用表
88 defdrop_db():89 Base.metadata.drop_all(engine)90
91 #显示个人权限
92 defshow_auth(user_name):93 user_roll_id = session.query(User.roll_id).filter(User.name ==user_name).subquery()94 user_dep_list = session.query(Roll.dep).filter(Roll.rid ==user_roll_id).all()95 for row inuser_dep_list:96 user_dep =row[0]97 print('您当前所属部门名称:', user_dep)98 print('\33[33m您具有的权限如下:\33[37m')99 auth_list = session.query(Authority.auth).join(Ro_au).filter(Ro_au.rid ==user_roll_id).all()100 count = 1
101 for auth inauth_list:102 print(count, auth[0])103 count += 1
104
105 #显示所有部门信息
106 defshow_department():107 print('公司现有部门如下:')108 dep_list =session.query(Roll).all()109 for dep indep_list:110 print(dep[0],dep[1])111
112
113 deflogin():114 whileTrue:115 user_name = input('请输入用户名【返回B】:\n>>>').strip()116 if user_name.lower() == 'b':117 break
118 user_pwd = input('请输入用户密码【返回B】:\n>>>').strip()119 if user_pwd.lower() == 'b':120 break
121 user_list =session.query(User.name,User.pwd).all()122
123 if (user_name,user_pwd) inuser_list:124 print('登陆成功!')125 choice = input('【1、进入个人权限界面】'
126 '\n【2、查看公司具有的所有部门】'
127 '\n【B、返回主界面】'
128 '\n>>>').strip()129 if choice == '1':130 show_auth(user_name)131 continue
132 if choice == '2':133 show_department()134 continue
135 if choice.lower() == 'b':136 break
137 else:138 print('用户名或者密码错误!')139 continue
140
141 #用户注册
142 defregister():143 whileTrue:144 user_name = input('请输入用户名【返回B】:\n>>>').strip()145 if not user_name:continue
146 if user_name.lower() == 'b':147 break
148 name_list =session.query(User.name).all()149 if (user_name,) inname_list:150 print('该用户名已经存在!')151 break
152 else:153 user_pwd = input('请输入用户密码:\n>>>').strip()154 if not user_pwd:continue
155 dep_list =session.query(Roll.rid,Roll.dep).all()156 for row indep_list:157 print(row.rid,row.dep)158 user_roll = input('请输入所属部门序号:\n>>>').strip()159 if notuser_roll:160 print('部门序号不能为空,请重新开始!')161 continue
162 ifuser_roll.isdigit():163 session.add(User(name=user_name,pwd=user_pwd,roll_id=int(user_roll))) #写入数据库
164 session.commit()165 print('新的员工信息已经注册成功!')166 break
167 else:168 print('您选择的部门有误,请重新选择!')169
170 #找回密码
171 deffind_pwd():172 whileTrue:173 user_name =input('请输入用户名【返回B】:').strip()174 if not user_name:continue
175 if user_name.lower() == 'b':176 break
177 name_list =session.query(User.name).all()178 if (user_name,) inname_list:179 pwd_list =session.query(User.pwd).filter(User.name==user_name).all()180 print('您的密码为:')181 for pwd inpwd_list:182 print(pwd[0])183 else:184 print('您输入的用户名不存在!')185
186 if __name__ == '__main__':187 whileTrue:188 creat_tab_choice = input('是否要在数据库中创建原始关系表?第一次请选择是!'
189 '**此操作会删除已添加的数据!**【是Y否N】:\n>>>').strip()190 if creat_tab_choice.lower() == 'y':191 drop_db()192 creat_db()193 original_table()194 print('基本权限管理原始表已创建!')195 elif creat_tab_choice.lower() == 'n':196 pass
197 else:198 print('您的操作有误,请重新选择!')199 continue
200 whileTrue:201 print('欢迎进入权限管理系统!'.center(21, '*'))202 choice = input('登录L\t注册R\t找回密码F\t退出Q:\n>>>').strip()203 if choice.lower() == 'q':204 print('成功退出权限管理系统,欢迎您再次使用!')205 break
206 elif choice.lower() == 'l':207 login()208 elif choice.lower() == 'r':209 register()210 elif choice.lower() == 'f':211 find_pwd()212 else:213 print('您输入的指令有误,请重新选择!')214 continue
215 break