def fetch_one_by_user_pwd(self, username, password):
ret = None
cursor = self.db_conn.connect()
sql = """select nid,username,email,last_login,vip,user_type from UserInfo where username=%s and password=%s"""
cursor.execute(sql, (username, password))
db_result = cursor.fetchone()
self.db_conn.close()
if db_result:
ret = User(nid=db_result['nid'],
username=db_result['username'],
email=db_result['email'],
last_login=db_result['last_login'],
user_type=UserType(nid=db_result['user_type']),
vip_type=VipType(nid=db_result['vip'])
)
return ret
def fetch_all(self):
ret_list = []
cursor = self.db_conn.connect()
sql = """select nid,username,email,last_login,vip,user_type from UserInfo """
cursor.execute(sql)
db_result = cursor.fetchall()
self.db_conn.close()
if db_result:
for user in db_result:
user_obj = User(nid=user['nid'],
username=user['username'],
email=user['email'],
last_login=user['last_login'],
user_type=UserType(nid=user['user_type']),
vip_type=VipType(nid=user['vip'])
)
ret_list.append(user_obj)
return ret_list
def fetch_all_by_user_type(self, *args, **kwargs):
ret = None
cursor = self.db_conn.connect()
sql = """select nid,username,email,last_login,vip,user_type from userinfo where user_type=1"""
cursor.execute(sql)
db_result = cursor.fetchall()
self.db_conn.close()
if db_result:
ret = UserModel(
nid=db_result['nid'],
username=db_result['username'],
email=db_result['email'],
last_login=db_result['last_login'],
user_type_obj=UserType(nid=db_result['user_type']),
vip_type_obj=VipType(nid=db_result['vip']),
)
return ret
return db_result
def fetch_one_by_email(self, email, password):
ret = None
cursor = self.db_conn.connect()
sql = """select nid,username,password,email,last_login,vip,user_type from userinfo where email=%s and password=%s"""
cursor.execute(sql, (email, password))
db_result = cursor.fetchone()
self.db_conn.close()
print(type(db_result), db_result)
if db_result:
ret = UserModel(
nid=db_result['nid'],
username=db_result['username'],
password=db_result['password'],
email=db_result['email'],
last_login=db_result['last_login'],
user_type_obj=UserType(nid=db_result['user_type']),
vip_type_obj=VipType(nid=db_result['vip']),
)
return ret
return db_result
def fetch_all(self, user_type_nid):
ret = None
cursor = self.db_conn.connect()
sql = """select nid,username,email,last_login,vip,user_type from userinfo where user_type=%s"""
cursor.execute(sql, (user_type_nid))
db_result = cursor.fetchall() # 返回列表嵌套字典
self.db_conn.close()
# print('db_result', db_result)
if db_result:
result_all = []
for item in db_result:
result_all.append(User(nid=item['nid'],
username=item['username'],
email=item['email'],
last_login=item['last_login'],
user_type=UserType(
nid=item['user_type']),
vip_type=VipType(nid=item['vip'])
))
return result_all
def fetch_customer_by_page(self, start, offset):
ret = None
cursor = self.db_conn.connect()
sql = """select nid,username,email,vip,user_type,password,last_login from userinfo order by nid desc limit %s offset %s """
cursor.execute(sql, (offset, start))
db_result = cursor.fetchall()
self.db_conn.close()
customer_obj = []
if db_result:
for item in db_result:
ret = UserModel(
nid=item['nid'],
username=item['username'],
password=item['password'],
email=item['email'],
last_login=item['last_login'],
user_type_obj=UserType(nid=item['user_type']),
vip_type_obj=VipType(nid=item['vip']),
)
customer_obj.append(ret)
return customer_obj
return db_result