def get_menus_by_permission(permission_ids: list):
"""
根据权限获取菜单
:param permission_id:
:return:
"""
with db_engine.connect() as conn:
sql = select([
t_menu_permission.c.menu_id,
]).where(
t_menu_permission.c.permission_id.in_(permission_ids)
).where(
t_user_group.c.status == TABLE_STATUS_VALID
).order_by('sort', 'id')
menu_permission_list = conn.execute(sql).fetchall()
if not menu_permission_list:
return []
sql = select([
t_menu.c.id,
t_menu.c.pid,
t_menu.c.code,
t_menu.c.name,
t_menu.c.uri,
t_menu.c.intro,
]).where(t_menu.c.id.in_([item.menu_id for item in menu_permission_list])).where(t_menu.c.status == TABLE_STATUS_VALID)
menu_list = conn.execute(sql).fetchall()
return menu_list
async def del_user(permission_id: int, userinfo: dict = Depends(tool.get_userinfo_from_token)):
"""
删除权限\n
:param permission_id:\n
:param userinfo:\n
:return:
"""
# 鉴权
tool.check_operation_permission(userinfo['id'], PERMISSION_PERMISSION_DEL)
conn = db_engine.connect()
try:
# 查找权限
permission_sql = t_permission.select().where(t_permission.c.id == permission_id).limit(1).with_for_update()
conn.execute(permission_sql).fetchone()
# 修改权限状态为无效（软删除）
update_permission_sql = t_permission.update().where(and_(
t_permission.c.id == permission_id,
t_permission.c.sub_status != TABLE_SUB_STATUS_INVALID_DEL,
)).values({
'status': TABLE_STATUS_INVALID,
'sub_status': TABLE_SUB_STATUS_INVALID_DEL
})
conn.execute(update_permission_sql)
return ItemOutOperateSuccess()
except:
raise MyException(status_code=HTTP_500_INTERNAL_SERVER_ERROR, detail=ItemOutOperateFailed(code=HTTP_500_INTERNAL_SERVER_ERROR, msg='inter server error'))
finally:
conn.close()
def get_user_permission(user_id: int):
"""
获取用户权限（用户的权限 + 用户所在组的权限）
可继续优化，不借用第三变量，通过pop或者remove直接在本地操作
:param user_id: 用户id
:return: [RowProxy, RowProxy,]
"""
# 角色去重
is_super = 0
user_roles = get_user_roles(user_id)
group_roles = get_group_roles(user_id)
target_roles = user_roles + group_roles
roles_length = len(target_roles)
for index, item in enumerate(target_roles[-1::-1]):
if item.is_super:
# 是超管，直接中断，取所有权限
is_super = 1
break
if (roles_length - (index + 2)) >= 0 and item.code in [item2.code for item2 in target_roles[roles_length - (index + 2)::-1]]:
target_roles.pop(roles_length - index - 1)
with db_engine.connect() as conn:
if is_super:
# 有超管角色，直接从权限表中获取所有权限
sql = select([t_permission.c.id, t_permission.c.code]).where(t_permission.c.status == TABLE_STATUS_VALID)
permission_list = conn.execute(sql).fetchall()
else:
# 没有超管角色，从角色权限绑定表中获取权限
sql = select([t_role_permission.c.permission_id.label('id')]).where(t_role_permission.c.role_id.in_([role.id for role in target_roles])).where(t_role_permission.c.status == TABLE_STATUS_VALID)
permission_list = conn.execute(sql).fetchall()
if permission_list:
sql = select([t_permission.c.id, t_permission.c.code]).where(t_permission.c.id.in_([permission.id for permission in permission_list])).where(t_permission.c.status == TABLE_STATUS_VALID)
permission_list = conn.execute(sql).fetchall()
return permission_list
async def edit_group(group_id: int,
item_in: ItemInEditGroup,
userinfo: dict = Depends(tool.get_userinfo_from_token)):
"""
修改用户组\n
:param group_id:\n
:param item_in:\n
:param userinfo:\n
:return:
"""
# 鉴权
tool.check_operation_permission(userinfo['id'], PERMISSION_GROUP_EDIT)
conn = db_engine.connect()
trans = conn.begin()
try:
# 查找用户组
group_sql = t_group.select().where(
t_group.c.id == group_id).limit(1).with_for_update()
group_obj = conn.execute(group_sql).fetchone()
if not group_obj:
raise MyException(status_code=HTTP_404_NOT_FOUND,
detail={
'code': HTTP_404_NOT_FOUND,
'msg': 'group not exists'
})
# 修改用户组
group_val = {'editor': userinfo['name']}
if item_in.pid:
group_val['pid'] = item_in.pid
if item_in.name:
group_val['name'] = item_in.name
if item_in.intro:
group_val['intro'] = item_in.intro
update_group_sql = t_group.update().where(
t_group.c.id == group_id).values(group_val)
conn.execute(update_group_sql)
if item_in.role_id:
# 指定了角色，绑定用户组 - 角色关系
tool.bind_group_role(group_id, item_in.role_id, userinfo, conn)
# 提交事务
trans.commit()
return ItemOutOperateSuccess()
except MyException as mex:
raise mex
except:
trans.rollback()
raise MyException(status_code=HTTP_500_INTERNAL_SERVER_ERROR,
detail=ItemOutOperateFailed(
code=HTTP_500_INTERNAL_SERVER_ERROR,
msg='inter server error'))
finally:
conn.close()
def get_user_roles(user_id: int):
"""
获取用户角色，用户与角色是多对多的关系
:param user_id: 用户id
:return:
"""
if not user_id:
return
with db_engine.connect() as conn:
# 取用户所拥有的角色
sql = select([
t_user_role.c.role_id,
]).where(t_user_role.c.user_id == user_id).where(t_user_role.c.status == TABLE_STATUS_VALID)
user_roles = conn.execute(sql).fetchall()
if not user_roles:
return []
# 取具体角色信息
sql = select([
t_role.c.id,
t_role.c.pid,
t_role.c.code,
t_role.c.name,
t_role.c.intro,
t_role.c.is_super,
]).where(t_role.c.id.in_([item.role_id for item in user_roles])).where(t_role.c.status == TABLE_STATUS_VALID)
roles = conn.execute(sql).fetchall()
if not roles:
return []
return roles
async def add_permission(item_in: ItemInAddPermission, userinfo: dict = Depends(tool.get_userinfo_from_token)):
"""
添加权限\n
:param item_in:\n
:param userinfo:\n
:return:
"""
# 鉴权
tool.check_operation_permission(userinfo['id'], PERMISSION_PERMISSION_ADD)
conn = db_engine.connect()
try:
# 查看是否已经有该code的权限
if not tool.is_code_unique(t_permission, item_in.code, conn):
raise MyException(status_code=HTTP_400_BAD_REQUEST, detail={'code': MULTI_DATA, 'msg': 'code repeat'})
# 新增权限
permission_sql = t_permission.insert().values({
'pid': item_in.pid,
'name': item_in.name,
'code': item_in.code,
'intro': item_in.intro,
'category': item_in.category,
'creator': userinfo['name']
})
conn.execute(permission_sql)
return ItemOutOperateSuccess()
except MyException as mex:
raise mex
except Exception as ex:
raise MyException(status_code=HTTP_500_INTERNAL_SERVER_ERROR, detail=ItemOutOperateFailed(code=HTTP_500_INTERNAL_SERVER_ERROR, msg=str(ex)))
finally:
conn.close()
async def edit_menu(menu_id: int, item_in: ItemInEditMenu, userinfo: dict = Depends(tool.get_userinfo_from_token)):
"""
修改菜单\n
:param menu_id:\n
:param item_in:\n
:param userinfo:\n
:return:
"""
# 鉴权
tool.check_operation_permission(userinfo['id'], PERMISSION_MENU_EDIT)
conn = db_engine.connect()
trans = conn.begin()
try:
# 查找菜单
menu_sql = t_menu.select().where(t_menu.c.id == menu_id).limit(1).with_for_update()
menu_obj = conn.execute(menu_sql).fetchone()
if not menu_obj:
raise MyException(status_code=HTTP_404_NOT_FOUND, detail={'code': HTTP_404_NOT_FOUND, 'msg': 'menu not exists'})
# 修改菜单
# 过滤掉空字段
item_dict = dict((k, v) for k, v in item_in.dict().items() if v is not None)
item_dict['editor'] = userinfo['name']
update_menu_sql = t_menu.update().where(t_menu.c.id == menu_id).values(item_dict)
conn.execute(update_menu_sql)
permission_dict = {}
if item_in.name and menu_obj.name != item_in.name:
permission_dict['name'] = '{}菜单访问'.format(item_in.name)
permission_dict['intro'] = '[{}菜单访问]权限'.format(item_in.name)
if item_in.code and menu_obj.code != item_in.code:
permission_dict['code'] = 'PERMISSION_{}_QUERY'.format(item_in.code)
if permission_dict:
# 因修改菜单而导致权限名称无法人眼识别，故同步修改权限
# 从菜单-权限绑定关系中，获取权限信息
menu_permission_sql = t_menu_permission.select().where(t_menu_permission.c.menu_id == menu_id).limit(1)
menu_permission_obj = conn.execute(menu_permission_sql).fetchone()
permission_dict['editor'] = userinfo['name']
permission_sql = t_permission.update().where(t_permission.c.id == menu_permission_obj.permission_id).values(permission_dict)
conn.execute(permission_sql)
# 提交事务
trans.commit()
return ItemOutOperateSuccess()
except MyException as mex:
raise mex
except:
trans.rollback()
raise MyException(status_code=HTTP_500_INTERNAL_SERVER_ERROR, detail=ItemOutOperateFailed(code=HTTP_500_INTERNAL_SERVER_ERROR, msg='inter server error'))
finally:
conn.close()
async def add_menu(item_in: ItemInAddMenu, userinfo: dict = Depends(tool.get_userinfo_from_token)):
"""
添加菜单\n
:param item_in:\n
:param userinfo:\n
:return:
"""
# 鉴权
tool.check_operation_permission(userinfo['id'], PERMISSION_MENU_ADD)
conn = db_engine.connect()
trans = conn.begin()
try:
# 查看是否已经有该code的用户组
if not tool.is_code_unique(t_menu, item_in.code, conn):
raise MyException(status_code=HTTP_400_BAD_REQUEST, detail={'code': MULTI_DATA, 'msg': 'code repeat'})
# 新增菜单
menu_val = {
'creator': userinfo['name']
}
menu_val.update(item_in.dict())
menu_sql = t_menu.insert().values(menu_val)
menu_res = conn.execute(menu_sql)
# 新增该菜单的可见权限
permission_sql = t_permission.insert().values({
'pid': 0,
'code': 'PERMISSION_{}_QUERY'.format(item_in.code),
'name': '{}菜单访问'.format(item_in.name),
'intro': '[{}菜单访问]权限'.format(item_in.name),
'category': 1,
'creator': userinfo['name']
})
permission_res = conn.execute(permission_sql)
# 绑定菜单与可见权限关系
menu_permission_sql = t_menu_permission.insert().values({
'menu_id': menu_res.lastrowid,
'permission_id': permission_res.lastrowid,
'creator': userinfo['name']
})
conn.execute(menu_permission_sql)
# 提交事务
trans.commit()
return ItemOutOperateSuccess()
except MyException as mex:
raise mex
except:
trans.rollback()
raise MyException(status_code=HTTP_500_INTERNAL_SERVER_ERROR, detail=ItemOutOperateFailed(code=HTTP_500_INTERNAL_SERVER_ERROR, msg='inter server error'))
finally:
conn.close()
async def add_group(item_in: ItemInAddGroup,
userinfo: dict = Depends(tool.get_userinfo_from_token)):
"""
添加用户组\n
:param item_in:\n
:param userinfo:\n
:return:
"""
# 鉴权
tool.check_operation_permission(userinfo['id'], PERMISSION_GROUP_ADD)
conn = db_engine.connect()
trans = conn.begin()
try:
# 查看是否已经有该code的用户组
if not tool.is_code_unique(t_group, item_in.code, conn):
raise MyException(status_code=HTTP_400_BAD_REQUEST,
detail={
'code': MULTI_DATA,
'msg': 'code repeat'
})
# 新增用户组
print('insert group start')
group_sql = t_group.insert().values({
'pid': item_in.pid,
'name': item_in.name,
'code': item_in.code,
'intro': item_in.intro,
'creator': userinfo['name']
})
group_res = conn.execute(group_sql)
if item_in.role_id:
# 指定了角色，绑定用户组 - 角色关系
tool.bind_group_role(group_res.lastrowid, item_in.role_id,
userinfo, conn)
trans.commit()
return ItemOutOperateSuccess()
except MyException as mex:
trans.rollback()
raise mex
except Exception as ex:
trans.rollback()
raise MyException(
status_code=HTTP_500_INTERNAL_SERVER_ERROR,
detail=ItemOutOperateFailed(code=HTTP_500_INTERNAL_SERVER_ERROR,
msg=str(ex)))
finally:
conn.close()
async def bind_group_role(item_in: ItemInBindGroupRole,
userinfo: dict = Depends(
tool.get_userinfo_from_token)):
"""
绑定用户组-角色\n
:param item_in:\n
:param userinfo:\n
:return:
"""
with db_engine.connect() as conn:
tool.bind_group_role(item_in.group_id, item_in.role_id, userinfo, conn)
return ItemOutOperateSuccess()
async def edit_role(role_id: int,
item_in: ItemInEditRole,
userinfo: dict = Depends(tool.get_userinfo_from_token)):
"""
修改角色\n
:param role_id:\n
:param item_in:\n
:param userinfo:\n
:return:
"""
# 鉴权
tool.check_operation_permission(userinfo['id'], PERMISSION_ROLE_EDIT)
conn = db_engine.connect()
try:
# 查找角色
role_sql = t_role.select().where(
t_role.c.id == role_id).limit(1).with_for_update()
role_obj = conn.execute(role_sql).fetchone()
if not role_obj:
raise MyException(status_code=HTTP_404_NOT_FOUND,
detail={
'code': HTTP_404_NOT_FOUND,
'msg': 'role not exists'
})
# 修改角色
data = {'editor': userinfo['name']}
if item_in.pid:
data['pid'] = item_in.pid
if item_in.name:
data['name'] = item_in.name
if item_in.intro:
data['intro'] = item_in.intro
update_role_sql = t_role.update().where(
t_role.c.id == role_id).values(data)
conn.execute(update_role_sql)
return ItemOutOperateSuccess()
except MyException as mex:
raise mex
except:
raise MyException(status_code=HTTP_500_INTERNAL_SERVER_ERROR,
detail=ItemOutOperateFailed(
code=HTTP_500_INTERNAL_SERVER_ERROR,
msg='inter server error'))
finally:
conn.close()
async def disable_group(group_id: int,
userinfo: dict = Depends(
tool.get_userinfo_from_token)):
"""
禁用用户组\n
:param group_id:\n
:param userinfo:\n
:return:
"""
# 鉴权
tool.check_operation_permission(userinfo['id'], PERMISSION_GROUP_DISABLE)
conn = db_engine.connect()
trans = conn.begin()
try:
# 查找用户组
group_sql = t_group.select().where(
t_group.c.id == group_id).limit(1).with_for_update()
conn.execute(group_sql).fetchone()
# 修改用户组状态为禁用
update_group_sql = t_group.update().where(
and_(
t_group.c.id == group_id,
t_group.c.status == TABLE_STATUS_VALID,
t_group.c.sub_status == TABLE_SUB_STATUS_VALID,
)).values({
'status': TABLE_STATUS_INVALID,
'sub_status': TABLE_SUB_STATUS_INVALID_DISABLE
})
conn.execute(update_group_sql)
# 提交事务
trans.commit()
return ItemOutOperateSuccess()
except MyException as mex:
raise mex
except:
trans.rollback()
raise MyException(status_code=HTTP_500_INTERNAL_SERVER_ERROR,
detail=ItemOutOperateFailed(
code=HTTP_500_INTERNAL_SERVER_ERROR,
msg='inter server error'))
finally:
conn.close()
async def get_groups(userinfo: dict = Depends(tool.get_userinfo_from_token),
p: Optional[int] = Query(settings.web.page,
description='第几页'),
ps: Optional[int] = Query(settings.web.page_size,
description='每页条数')):
item_out = ItemOutGroupList()
# 鉴权
tool.check_operation_permission(userinfo['id'], PERMISSION_GROUP_VIEW)
with db_engine.connect() as conn:
# 获取当前有多少数据
count_sql = select([func.count(t_group.c.id)])
total = conn.execute(count_sql).scalar()
# 获取分页后的用户组列表
group_sql = select([
t_group.c.id,
t_group.c.pid,
t_group.c.name,
t_group.c.code,
t_group.c.intro,
t_group.c.status,
t_group.c.sub_status,
]).order_by('sort', 'id').limit(ps).offset((p - 1) * ps)
group_obj_list = conn.execute(group_sql).fetchall()
item_out.data = ListDataGroup(
result=[
ItemOutGroup(
id=group_obj.id,
name=group_obj.name,
code=group_obj.code,
intro=group_obj.intro,
status=group_obj.status,
sub_status=group_obj.sub_status,
) for group_obj in group_obj_list
],
total=total,
p=p,
ps=ps,
)
return item_out
async def enable_menu(menu_id: int, userinfo: dict = Depends(tool.get_userinfo_from_token)):
"""
启用菜单\n
:param menu_id:\n
:param userinfo:\n
:return:
"""
# 鉴权
tool.check_operation_permission(userinfo['id'], PERMISSION_MENU_ENABLE)
conn = db_engine.connect()
trans = conn.begin()
try:
# 1.查找菜单
menu_sql = t_menu.select().where(t_menu.c.id == menu_id).limit(1).with_for_update()
menu_obj = conn.execute(menu_sql).fetchone()
if not menu_obj:
raise MyException(status_code=HTTP_404_NOT_FOUND, detail={'code': HTTP_404_NOT_FOUND, 'msg': 'menu not exists'})
# 2.修改菜单状态为启用
update_menu_sql = t_menu.update().where(and_(
t_menu.c.id == menu_id,
t_menu.c.status == TABLE_STATUS_INVALID,
t_menu.c.sub_status == TABLE_SUB_STATUS_INVALID_DISABLE,
)).values({
'status': TABLE_STATUS_VALID,
'sub_status': TABLE_SUB_STATUS_VALID
})
conn.execute(update_menu_sql)
# 3.提交事务
trans.commit()
return ItemOutOperateSuccess()
except MyException as mex:
raise mex
except:
trans.rollback()
raise MyException(status_code=HTTP_500_INTERNAL_SERVER_ERROR, detail=ItemOutOperateFailed(code=HTTP_500_INTERNAL_SERVER_ERROR, msg='inter server error'))
finally:
conn.close()
async def bind_role_permission(role_id: int,
item_in: List[int],
userinfo: dict = Depends(
tool.get_userinfo_from_token)):
"""
绑定角色-权限\n
:param role_id:\n
:param item_in:\n
:param userinfo:\n
:return:
"""
conn = db_engine.connect()
trans = conn.begin()
try:
# 鉴权
tool.check_operation_permission(userinfo['id'],
PERMISSION_ROLE_PERMISSION_BIND)
# 查询角色是否存在
role = tool.get_role(role_id, conn)
if not role.is_super:
# 不是超级管理员，绑定权限
for permission_id in item_in:
# 绑定角色权限
tool.bind_role_permission(role_id, permission_id, userinfo,
conn)
trans.commit()
else:
trans.rollback()
return ItemOutOperateSuccess()
except:
trans.rollback()
raise MyException(status_code=HTTP_500_INTERNAL_SERVER_ERROR,
detail=ItemOutOperateFailed(
code=HTTP_500_INTERNAL_SERVER_ERROR,
msg='inter server error'))
finally:
conn.close()
def get_role(role_id, conn=None):
"""
获取角色
:param role_id: 角色id
:param conn: 数据库连接
:return:
"""
if conn:
role = conn.execute(select([
t_role.c.id,
t_role.c.pid,
t_role.c.code,
t_role.c.name,
t_role.c.intro,
t_role.c.is_super,
]).where(and_(
t_role.c.id == role_id,
t_role.c.status == TABLE_STATUS_VALID
)).limit(1)).fetchone()
else:
with db_engine.connect() as conn:
role = conn.execute(select([
t_role.c.id,
t_role.c.pid,
t_role.c.code,
t_role.c.name,
t_role.c.intro,
t_role.c.is_super,
]).where(and_(
t_role.c.id == role_id,
t_role.c.status == TABLE_STATUS_VALID
)).limit(1)).fetchone()
if not role:
raise MyException(status_code=HTTP_404_NOT_FOUND,
detail={'code': HTTP_404_NOT_FOUND, 'msg': 'role is not exists'})
else:
return role
async def enable_role(role_id: int,
userinfo: dict = Depends(tool.get_userinfo_from_token)):
"""
启用角色\n
:param role_id:\n
:param userinfo:\n
:return:
"""
# 鉴权
tool.check_operation_permission(userinfo['id'], PERMISSION_ROLE_ENABLE)
conn = db_engine.connect()
try:
# 查找角色
role_sql = t_role.select().where(
t_role.c.id == role_id).limit(1).with_for_update()
conn.execute(role_sql).fetchone()
# 修改角色状态为启用
update_role_sql = t_role.update().where(
and_(
t_role.c.id == role_id,
t_role.c.status == TABLE_STATUS_INVALID,
t_role.c.sub_status == TABLE_SUB_STATUS_INVALID_DISABLE,
)).values({
'status': TABLE_STATUS_VALID,
'sub_status': TABLE_SUB_STATUS_VALID
})
conn.execute(update_role_sql)
return ItemOutOperateSuccess()
except:
raise MyException(status_code=HTTP_500_INTERNAL_SERVER_ERROR,
detail=ItemOutOperateFailed(
code=HTTP_500_INTERNAL_SERVER_ERROR,
msg='inter server error'))
finally:
conn.close()
async def add_user(item_in: ItemInAddUser,
userinfo: dict = Depends(tool.get_userinfo_from_token)):
"""
添加用户\n
:param item_in:\n
:param userinfo:\n
:return:
"""
# 鉴权
tool.check_operation_permission(userinfo['id'], PERMISSION_USER_ADD)
conn = db_engine.connect()
trans = conn.begin()
try:
# 1.新增用户
# 1.1 设置用户盐值
user_salt = tool.get_rand_str(6)
# 1.2 执行新增
user_val = {
'name': item_in.name,
'head_img_url': item_in.head_img_url,
'mobile': item_in.mobile,
'email': item_in.email,
'salt': user_salt,
'password': md5(item_in.password, user_salt),
'creator': userinfo['name']
}
user_sql = t_user.insert().values(user_val)
user_res = conn.execute(user_sql)
# 2.新增账号
# 添加一个用户名的登录账号
account_sql = t_account.insert().values({
'user_id': user_res.lastrowid,
'open_code': item_in.name,
'category': TABLE_ACCOUNT_CATEGORY_CUSTOM,
'creator': userinfo['name']
})
conn.execute(account_sql)
if item_in.email:
# 填写了邮箱，添加一个邮箱的登录账号
account_sql = t_account.insert().values({
'user_id': user_res.lastrowid,
'open_code': item_in.email,
'category': TABLE_ACCOUNT_CATEGORY_EMAIL,
'creator': userinfo['name']
})
conn.execute(account_sql)
if item_in.mobile:
# 填写了手机号，添加一个手机号的登录账号
account_sql = t_account.insert().values({
'user_id': user_res.lastrowid,
'open_code': item_in.mobile,
'category': TABLE_ACCOUNT_CATEGORY_PHONE,
'creator': userinfo['name']
})
conn.execute(account_sql)
if item_in.role_id:
# 3.指定了角色，绑定用户角色关系
tool.bind_user_role(user_res.lastrowid, item_in.role_id, userinfo,
conn)
if item_in.group_id:
# 4.指定了组，绑定用户与组关系
tool.bind_user_group(user_res.lastrowid, item_in.group_id,
userinfo, conn)
trans.commit()
return ItemOutOperateSuccess()
except MyException as mex:
trans.rollback()
raise mex
except Exception as ex:
trans.rollback()
raise MyException(
status_code=HTTP_500_INTERNAL_SERVER_ERROR,
detail=ItemOutOperateFailed(code=HTTP_500_INTERNAL_SERVER_ERROR,
msg=str(ex)))
finally:
conn.close()
async def get_users(userinfo: dict = Depends(tool.get_userinfo_from_token),
p: Optional[int] = Query(settings.web.page,
description='第几页'),
ps: Optional[int] = Query(settings.web.page_size,
description='每页条数'),
name: Optional[str] = Query(None, description='用户名'),
mobile: Optional[str] = Query(None,
description='用户手机号',
regex=REGEX_MOBILE)):
item_out = ItemOutUserList()
# 检查权限
tool.check_operation_permission(userinfo['id'], PERMISSION_USER_VIEW)
with db_engine.connect() as conn:
# 获取当前有多少数据
count_sql = select([func.count(t_user.c.id)])
# 获取分页后的用户列表
user_sql = select([
t_user.c.id,
t_user.c.name,
t_user.c.head_img_url,
t_user.c.mobile,
t_user.c.status,
t_user.c.sub_status,
])
if name is not None:
# 用户名过滤
name = name.strip()
count_sql = count_sql.where(t_user.c.name.like(
'%{}%'.format(name)))
user_sql = user_sql.where(t_user.c.name.like('%{}%'.format(name)))
if mobile is not None:
# 用户手机号过滤
mobile = mobile.strip()
count_sql = count_sql.where(
t_user.c.mobile.like('%{}%'.format(mobile)))
user_sql = user_sql.where(
t_user.c.mobile.like('%{}%'.format(mobile)))
total = conn.execute(count_sql).scalar()
user_sql = user_sql.order_by('sort', 'id').limit(ps).offset(
(p - 1) * ps)
user_obj_list = conn.execute(user_sql).fetchall()
item_out.data = ListDataUser(
result=[
ItemOutUser(
id=user_obj.id,
name=user_obj.name,
head_img_url=user_obj.head_img_url,
mobile=user_obj.mobile,
status=user_obj.status,
sub_status=user_obj.sub_status,
) for user_obj in user_obj_list
],
total=total,
p=p,
ps=ps,
)
return item_out
async def login(item_in: ItemInLogin):
# 响应模型
item_out = ItemOutLogin()
print(settings.web.captcha_redis_key.format(item_in.captcha_key))
# 缓存中取验证码
captcha_cache = redis_conn.get(
settings.web.captcha_redis_key.format(item_in.captcha_key))
if not captcha_cache:
# 未取到验证码
item_out.code = RESP_CODE_CAPTCHA_EXPIRE
item_out.msg = '验证码已失效'
raise MyException(status_code=HTTP_404_NOT_FOUND, detail=item_out)
# 检查验证码是否正确
if captcha_cache != item_in.captcha_val:
item_out.code = RESP_CODE_CAPTCHA_ERROR
item_out.msg = '验证码错误'
raise MyException(status_code=HTTP_404_NOT_FOUND, detail=item_out)
with db_engine.connect() as conn:
# 检查账号
account_sql = select([
t_account.c.user_id, t_account.c.open_code, t_account.c.category,
t_account.c.status
]).where(t_account.c.open_code == item_in.open_code).limit(1)
account_res = conn.execute(account_sql).fetchone()
if not account_res:
# 账号不存在
item_out.code = RESP_CODE_ACCOUNT_EXIST_NOT
item_out.msg = '账号不存在'
raise MyException(HTTP_404_NOT_FOUND, detail=item_out)
if account_res.status != TABLE_STATUS_VALID:
# 账号无效
item_out.code = RESP_CODE_ACCOUNT_EXIST_NOT
item_out.msg = '账号无效'
raise MyException(HTTP_404_NOT_FOUND, detail=item_out)
# 检查用户
user_sql = select([
t_user.c.id, t_user.c.name, t_user.c.head_img_url, t_user.c.mobile,
t_user.c.password, t_user.c.salt, t_user.c.status
]).where(t_user.c.id == account_res.user_id).limit(1)
user_res = conn.execute(user_sql).fetchone()
if not user_res:
# 用户不存在
item_out.code = RESP_CODE_ACCOUNT_EXIST_NOT
item_out.msg = '用户不存在'
raise MyException(HTTP_404_NOT_FOUND, detail=item_out)
if user_res.status != TABLE_STATUS_VALID:
# 用户无效
item_out.code = RESP_CODE_ACCOUNT_EXIST_NOT
item_out.msg = '用户无效'
raise MyException(HTTP_404_NOT_FOUND, detail=item_out)
# 检查密码是否正确
password = md5(item_in.password, user_res.salt)
if password != user_res.password:
# 密码不正确
item_out.code = RESP_CODE_ACCOUNT_EXIST_NOT
item_out.msg = '登录密码错误'
raise MyException(HTTP_404_NOT_FOUND, detail=item_out)
# 所有验证项都通过
# 存入缓存中的用户信息
userinfo_cache = {
'id': user_res.id,
'open_code': account_res.open_code,
'category': account_res.category,
'name': user_res.name,
'head_img_url': user_res.head_img_url,
'mobile': user_res.mobile,
}
# 存入缓存中的用户信息的key，即token
token = tool.create_token(user_res.id)
token_key = settings.web.token_redis_key.format(token)
redis_conn.setex(token_key, settings.web.token_expire_time,
json.dumps(userinfo_cache))
# 回传的用户信息
userinfo_back = {
'id': user_res.id,
'open_code': account_res.open_code,
'name': user_res.name,
'head_img_url': user_res.head_img_url,
'mobile': user_res.mobile,
'token': token
}
item_out.data = ItemLogin(**userinfo_back)
item_out.msg = '登录成功'
return item_out
async def edit_user(user_id: int,
item_in: ItemInEditUser,
userinfo: dict = Depends(tool.get_userinfo_from_token)):
"""
修改用户\n
:param user_id:\n
:param item_in:\n
:param userinfo:\n
:return:
"""
# 鉴权
tool.check_operation_permission(userinfo['id'], PERMISSION_USER_EDIT)
conn = db_engine.connect()
trans = conn.begin()
try:
# 1.查找用户
user_sql = t_user.select().where(
t_user.c.id == user_id).limit(1).with_for_update()
user_obj = conn.execute(user_sql).fetchone()
if not user_obj:
raise MyException(status_code=HTTP_404_NOT_FOUND,
detail={
'code': HTTP_404_NOT_FOUND,
'msg': 'user not exists'
})
# 2.修改用户
user_val = {'editor': userinfo['name']}
if item_in.name:
user_val['name'] = item_in.name
if item_in.head_img_url:
user_val['head_img_url'] = item_in.head_img_url
if item_in.mobile:
user_val['mobile'] = item_in.mobile
if item_in.email:
user_val['email'] = item_in.email
if item_in.password:
user_val['password'] = item_in.password
update_user_sql = t_user.update().where(
t_user.c.id == user_id).values(user_val)
conn.execute(update_user_sql)
# 3.修改账号
# 3.1 获取账号
account_sql = t_account.select().where(
t_account.c.user_id == user_obj.id).order_by(
'sort', 'id').with_for_update()
account_res = conn.execute(account_sql).fetchall()
# 3.2 遍历账号，并修改
for account in account_res:
if account.category == TABLE_ACCOUNT_CATEGORY_CUSTOM and item_in.name and account.open_code != item_in.name:
# 账号类型为自定义，并且修改了用户名
tmp_account_update_sql = t_account.update().where(
t_account.c.id == account.id).values({
'open_code':
item_in.name,
'editor':
userinfo['name']
})
conn.execute(tmp_account_update_sql)
elif account.category == TABLE_ACCOUNT_CATEGORY_PHONE and item_in.mobile and account.open_code != item_in.mobile:
# 账号类型为手机号，并且用户修改了手机号
tmp_account_update_sql = t_account.update().where(
t_account.c.id == account.id).values({
'open_code':
item_in.mobile,
'editor':
userinfo['name']
})
conn.execute(tmp_account_update_sql)
elif account.category == TABLE_ACCOUNT_CATEGORY_EMAIL and item_in.email and account.open_code != item_in.email:
# 账号类型为手机号，并且用户修改了手机号
tmp_account_update_sql = t_account.update().where(
t_account.c.id == account.id).values({
'open_code':
item_in.mobile,
'editor':
userinfo['name']
})
conn.execute(tmp_account_update_sql)
if item_in.role_id:
# 4.指定了角色，绑定用户角色关系
tool.bind_user_role(user_id, item_in.role_id, userinfo, conn)
if item_in.group_id:
# 5.指定了组，绑定用户与组关系
tool.bind_user_group(user_id, item_in.group_id, userinfo, conn)
# 提交事务
trans.commit()
return ItemOutOperateSuccess()
except MyException as mex:
raise mex
except:
trans.rollback()
raise MyException(status_code=HTTP_500_INTERNAL_SERVER_ERROR,
detail=ItemOutOperateFailed(
code=HTTP_500_INTERNAL_SERVER_ERROR,
msg='inter server error'))
finally:
conn.close()
