class PermissionRepository:
def __init__(self):
self.db_conn = DbConnection()
def add(self, **kwargs):
cursor = self.db_conn.connect()
sql = """ insert into permission(%s) values(%s)"""
key_list = []
value_list = []
for k, v in kwargs.items():
key_list.append(k)
value_list.append('%%(%s)s' % k)
sql = sql % (','.join(key_list), ','.join(value_list))
cursor.execute(sql, kwargs)
self.db_conn.close()
def fetch_all(self):
cursor = self.db_conn.connect()
sql = """
select nid,caption, module,func from permission
"""
cursor.execute(sql)
result = cursor.fetchall()
self.db_conn.close()
return result
class UserTypeToPermissionRepository:
def __init__(self):
self.db_conn = DbConnection()
def add(self, **kwargs):
cursor = self.db_conn.connect()
sql = """ insert into user_type_to_permission(%s) values(%s)"""
key_list = []
value_list = []
for k, v in kwargs.items():
key_list.append(k)
value_list.append('%%(%s)s' % k)
sql = sql % (','.join(key_list), ','.join(value_list))
cursor.execute(sql, kwargs)
self.db_conn.close()
def fetch_permission_by_type_id(self, user_type_id):
cursor = self.db_conn.connect()
sql = """
select
permission.nid as nid,
permission.caption as caption,
permission.module as module,
permission.func as func
from
user_type_to_permission
left join permission on user_type_to_permission.permission_id = permission.nid
where user_type_to_permission.user_type_id = %s
"""
cursor.execute(sql, user_type_id)
result = cursor.fetchall()
self.db_conn.close()
return result
class UserInfoRepository:
def __init__(self):
self.db_conn = DbConnection()
def add(self, **kwargs):
cursor = self.db_conn.connect()
sql = """ insert into user_info(%s) values(%s)"""
key_list = []
value_list = []
for k, v in kwargs.items():
key_list.append(k)
value_list.append('%%(%s)s' % k)
sql = sql % (','.join(key_list), ','.join(value_list))
cursor.execute(sql, kwargs)
self.db_conn.close()
def fetch_by_user_pwd(self, username, password):
"""
根据用户名密码获取账户信息
:param username:
:param password:
:return:
"""
cursor = self.db_conn.connect()
sql = """
select
user_info.nid as nid,
user_info.username as username,
user_info. user_type_id as user_type_id,
user_type.caption as user_type_caption
from
user_info
left join user_type on user_info.user_type_id=user_type.nid
where
user_info.username=%s and user_info.password=%s
"""
cursor.execute(sql, [
username,
password,
])
result = cursor.fetchone()
self.db_conn.close()
return result
def exist(self, username):
sql = "select 1 from user_info where username=%s"
cursor = self.db_conn.connect()
cursor.execute(sql, [
username,
])
result = cursor.fetchone()
self.db_conn.close()
return result
def create(self, username, password, email, user_type):
pass
class UserInfoRepository:
def __init__(self):
self.db_conn = DbConnection()
def add(self, **kwargs):
cursor = self.db_conn.connect()
sql = """ insert into user_info(%s) values(%s)"""
key_list = []
value_list = []
for k, v in kwargs.items():
key_list.append(k)
value_list.append('%%(%s)s' % k)
sql = sql % (','.join(key_list), ','.join(value_list))
cursor.execute(sql, kwargs)
self.db_conn.close()
def fetch_by_user_pwd(self, username, password):
"""
根据用户名密码获取账户信息
:param username:
:param password:
:return:
"""
cursor = self.db_conn.connect()
sql = """
select
user_info.nid as nid,
user_info.username as username,
user_info. user_type_id as user_type_id,
user_type.caption as user_type_caption
from
user_info
left join user_type on user_info.user_type_id=user_type.nid
where
user_info.username=%s and user_info.password=%s
"""
cursor.execute(sql, [username, password, ])
result = cursor.fetchone()
self.db_conn.close()
return result
def exist(self, username):
sql = "select 1 from user_info where username=%s"
cursor = self.db_conn.connect()
cursor.execute(sql, [username,])
result = cursor.fetchone()
self.db_conn.close()
return result
def create(self,username,password,email,user_type):
pass
class PermissionRepository:
def __init__(self):
self.db_conn = DbConnection()
# 添加
def add(self, **kwargs):
# 连接并获取到游标
cursor = self.db_conn.connect()
# sql语句 -> 插入
sql = """ insert into permission(%s) values(%s) """
key_list = []
value_list = []
for k, v in kwargs.items():
key_list.append(k)
# %% => 字符'%'
value_list.append('%%(%s)s' % k)
sql = sql % (','.join(key_list), ','.join(value_list))
# 执行
cursor.execute(sql, kwargs)
# 提交执行并关闭
self.db_conn.close()
# 获取数据
def fetch_all(self):
# 1. 先连接
cursor = self.db_conn.connect()
'''
caption -> 操作名称
module -> 位于哪个模块内
func -> 模块内的方法名
'''
sql = """
select
nid,
caption,
module,
func
from
permission
"""
cursor.execute(sql)
result = cursor.fetchall()
self.db_conn.close()
return result
class UserTypeRepository:
def __init__(self):
self.db_conn = DbConnection()
def add(self, caption):
"""
在user_type表中添加角色
:param caption:
:return:
"""
cursor = self.db_conn.connect()
sql = """
insert into user_type(caption) values(%s)
"""
cursor.execute(sql, [
caption,
])
self.db_conn.close()
def del_role(self, role):
"""
在user_type表中删除角色
:param role:
:return:
"""
cursor = self.db_conn.connect()
sql = "delete from user_type WHERE caption=%s"
cursor.execute(sql, role)
self.db_conn.close()
def get_info(self):
"""
获取user_type表中所有用户角色类型
:return:
"""
cursor = self.db_conn.connect()
sql = 'select * from user_type'
cursor.execute(sql)
result = cursor.fetchall()
self.db_conn.close()
return result
class UserTypeRepository:
def __init__(self):
self.db_conn = DbConnection()
def add(self, caption):
'''
根据用户名密码获取账户信息
'''
cursor = self.db_conn.connect()
sql = """
insert into user_type(caption) values(%s)
"""
cursor.execute(sql, [
caption,
])
self.db_conn.close()
class UserTypeRepository:
def __init__(self):
self.db_conn = DbConnection()
def add(self, caption):
"""
根据用户名密码获取账户信息
:param username:
:param password:
:return:
"""
cursor = self.db_conn.connect()
sql = """
insert into user_type(caption) values(%s)
"""
cursor.execute(sql, [caption,])
self.db_conn.close()
class UserTypeRepository:
def __init__(self):
self.db_conn = DbConnection()
def add(self, caption):
"""
根据用户名密码获取账户信息
:param username:
:param password:
:return:
"""
cursor = self.db_conn.connect()
sql = """
insert into user_type(caption) values(%s)
"""
cursor.execute(sql, [
caption,
])
self.db_conn.close()
