from sqlalchemy import create_engine
2 from sqlalchemy.ext.declarative import declarative_base
3 from sqlalchemy import Column,Integer,ForeignKey,CHAR,VARCHAR,UniqueConstraint
4 from sqlalchemy.orm import sessionmaker
5 #建立链接
6 engine = create_engine(
"mysql+pymysql://root:@127.0.0.1:3306/s作业db?charset=utf8", max_overflow=
5)
7 Base =
declarative_base()
8 Session = sessionmaker(bind=
engine)
9 session =
Session()
10 #创建用户信息表
11 class User(Base):
12 __tablename__ =
‘user‘
13 uid = Column(Integer,primary_key=True,autoincrement=
True)
14 name = Column(CHAR(
20),nullable=
False)
15 pwd = Column(CHAR(
20),nullable=
False)
16 roll_id = Column(Integer,ForeignKey(
‘roll.rid‘))
17 #__table_args__的参数必须是元组的形式
18 __table_args__ =
(
19 UniqueConstraint(name,pwd,name=
‘uni_np‘), #用户名和密码不能重复！
20 )
21 #创建权限表
22 class Authority(Base):
23 __tablename__ =
‘authority‘
24 aid = Column(Integer,primary_key=True,autoincrement=
True)
25 auth = Column(VARCHAR(
255),nullable=
True)
26
27 #创建角色表
28 class Roll(Base):
29 __tablename__ =
‘roll‘
30 rid = Column(Integer,primary_key=True,autoincrement=
True)
31 dep = Column(VARCHAR(
255),nullable=
False)
32
33 #角色权限关系表
34 class Ro_au(Base):
35 __tablename__ =
‘ro_au‘
36 id = Column(Integer,primary_key=True,autoincrement=
True)
37 rid = Column(Integer,ForeignKey(
‘roll.rid‘))
38 aid = Column(Integer,ForeignKey(
‘authority.aid‘))
39 #创建原始关系表
40 def original_table():
41 session.add_all([
42 Roll(dep=
‘项目经理‘),
43 Roll(dep=
‘实验室‘),
44 Roll(dep=
‘办公室‘),
45 Roll(dep=
‘人事部‘),
46 Roll(dep=
‘工程部‘),
47 Roll(dep=
‘普通员工‘),
48 Authority(auth=
‘查看个人信息‘),
49 Authority(auth=
‘更改用户信息‘),
50 Authority(auth=
‘删除用户‘),
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 ])
60 session.commit()
61 session.add_all([
62 User(name=
‘李一‘,pwd=
‘123‘,roll_id=
1),
63 User(name=
‘李二‘,pwd=
‘456‘,roll_id=
2),
64 User(name=
‘李三‘,pwd=
‘789‘,roll_id=
2),
65 User(name=
‘李四‘,pwd=
‘1234‘,roll_id=
3),
66 User(name=
‘李五‘,pwd=
‘2345‘,roll_id=
4),
67 User(name=
‘李六‘,pwd=
‘3456‘,roll_id=
5),
68 User(name=
‘李七‘,pwd=
‘4567‘,roll_id=
6),
69 ])
70 session.add_all([
71 Ro_au(rid=
1,aid=
1),Ro_au(rid=
1,aid=
5),Ro_au(rid=
1,aid=
6),
72 Ro_au(rid=
2, aid=
1),Ro_au(rid=
2, aid=
4),Ro_au(rid=
2, aid=
8),
73 Ro_au(rid=
3, aid=
1),Ro_au(rid=
3, aid=
5),Ro_au(rid=
3, aid=
6),Ro_au(rid=
3, aid=
7),
74 Ro_au(rid=
4, aid=
1),Ro_au(rid=
4, aid=
2),Ro_au(rid=
4, aid=
3),
75 Ro_au(rid=
5, aid=
1),Ro_au(rid=
5, aid=
9),Ro_au(rid=
5, aid=
11),
76 Ro_au(rid=
6, aid=
1),
77 ])
78 session.commit()
79
80 #创建数据库所有表
81 def creat_db():
82 Base.metadata.create_all(engine)
83
84 #删除数据库所用表
85 def drop_db():
86 Base.metadata.drop_all(engine)
87 #显示个人权限
88 def show_auth(user_name):
89 user_roll_id = session.query(User.roll_id).filter(User.name ==
user_name).subquery()
90 user_dep_list = session.query(Roll.dep).filter(Roll.rid ==
user_roll_id).all()
91 for row
in user_dep_list:
92 user_dep = row[
0]
93 print(
‘您当前所属部门名称：‘, user_dep)
94 print(
‘\33[33m您具有的权限如下:\33[37m‘)
95 auth_list = session.query(Authority.auth).join(Ro_au).filter(Ro_au.rid ==
user_roll_id).all()
96 count =
1
97 for auth
in auth_list:
98 print(count, auth[
0])
99 count +=
1
100
101 #显示所有部门信息
102 def show_department():
103 print(
‘公司现有部门如下：‘)
104 dep_list =
session.query(Roll).all()
105 for dep
in dep_list:
106 print(dep[
0],dep[
1])
107
108
109 def login():
110 while True:
111 user_name = input(
‘请输入用户名【返回3】：\n>>>‘).strip()
112 if user_name.lower() ==
‘3‘:
113 break
114 user_pwd = input(
‘请输入用户密码【返回3】：\n>>>‘).strip()
115 if user_pwd.lower() ==
‘3‘:
116 break
117 user_list =
session.query(User.name,User.pwd).all()
118
119 if (user_name,user_pwd)
in user_list:
120 print(
‘登陆成功！‘)
121 choice = input(
‘》》》1、进入个人权限界面‘
122 ‘\n》》》2、查看公司具有的所有部门‘
123 ‘\n》》》3、返回主界面‘
124 ‘\n>>>‘).strip()
125 if choice ==
‘1‘:
126 show_auth(user_name)
127 continue
128 if choice ==
‘2‘:
129 show_department()
130 continue
131 if choice.lower() ==
‘3‘:
132 break
133 else:
134 print(
‘用户名或者密码错误！‘)
135 continue
136
137 #用户注册
138 def register():
139 while True:
140 user_name = input(
‘请输入用户名【返回B】：\n>>>‘).strip()
141 if not user_name:
continue
142 if user_name.lower() ==
‘3‘:
143 break
144 name_list =
session.query(User.name).all()
145 if (user_name,)
in name_list:
146 print(
‘该用户名已经存在！‘)
147 break
148 else:
149 user_pwd = input(
‘请输入用户密码：\n>>>‘).strip()
150 if not user_pwd:
continue
151 dep_list =
session.query(Roll.rid,Roll.dep).all()
152 for row
in dep_list:
153 print(row.rid,row.dep)
154 user_roll = input(
‘请输入所属部门序号：\n>>>‘).strip()
155 if not user_roll:
156 print(
‘部门序号不能为空，请重新开始！‘)
157 continue
158 if user_roll.isdigit():
159 session.add(User(name=user_name,pwd=user_pwd,roll_id=
int(user_roll))) #写入数据库
160 session.commit()
161 print(
‘新的员工信息已经注册成功！‘)
162 break
163 else:
164 print(
‘您选择的部门有误，请重新选择！‘)
165
166 #找回密码
167 def find_pwd():
168 while True:
169 user_name =input(
‘请输入用户名【返回B】：‘).strip()
170 if not user_name:
continue
171 if user_name.lower() ==
‘3‘:
172 break
173 name_list =
session.query(User.name).all()
174 if (user_name,)
in name_list:
175 pwd_list =session.query(User.pwd).filter(User.name==
user_name).all()
176 print(
‘您的密码为：‘)
177 for pwd
in pwd_list:
178 print(pwd[
0])
179 else:
180 print(
‘您输入的用户名不存在！‘)
181
182 if __name__ ==
‘__main__‘:
183 while True:
184 creat_tab_choice = input(
‘是否要在数据库中创建原始关系表？第一次请选择是！‘
185 ‘**此操作会删除已添加的数据！**【是Y否N】：\n>>>‘).strip()
186 if creat_tab_choice.lower() ==
‘y‘:
187 drop_db()
188 creat_db()
189 original_table()
190 print(
‘基本权限管理原始表已创建！‘)
191 elif creat_tab_choice.lower() ==
‘n‘:
192 pass
193 else:
194 print(
‘您的操作有误，请重新选择！‘)
195 continue
196 while True:
197 print(
‘欢迎进入权限管理系统！‘.center(
21,
‘*‘))
198 choice = input(
‘登录D\t注册Z\t找回密码M\t退出Q:\n>>>‘).strip()
199 if choice.lower() ==
‘q‘:
200 print(
‘成功退出权限管理系统，欢迎您再次使用！‘)
201 break
202 elif choice.lower() ==
‘d‘:
203 login()
204 elif choice.lower() ==
‘z‘:
205 register()
206 elif choice.lower() ==
‘m‘:
207 find_pwd()
208 else:
209 print(
‘您输入的指令有误，请重新选择！‘)
210 continue
211 break
MySQL权限管理小程序
标签：style pac 管理系统 subquery 结合 count 进度 utf8 index
