def search_option(self, sql):
global con
try:
con = pool.get_connection()
cursor = con.cursor()
cursor.execute(sql)
result = cursor.fetchall()
return result
except Exception as e:
return e
finally:
con.close()
def search_role(self):
try:
con = pool.get_connection()
cursor = con.cursor()
sql = "SELECT id,role FROM t_role"
cursor.execute(sql)
result = cursor.fetchall()
return result
except Exception as e:
print(e)
finally:
if "con" in dir():
con.close()
def search_count_page(self):
try:
con = pool.get_connection()
cursor = con.cursor()
sql = "SELECT CEIL(COUNT(*)/10) FROM t_user"
cursor.execute(sql)
count_page = cursor.fetchone()[0]
return count_page
except Exception as e:
print(e)
finally:
if "con" in dir():
con.close()
def search_uesr_passwd(self, username):
try:
con = pool.get_connection()
cursor = con.cursor()
sql = "SELECT AES_DECRYPT(UNHEX(password),'HelloWorld') FROM t_user WHERE username = %s"
cursor.execute(sql, [username])
result = cursor.fetchone()[0]
return result
except Exception as e:
print(e)
finally:
if "con" in dir():
con.close()
def search_unreview_list_count(self):
try:
con = pool.get_connection()
cursor = con.cursor()
sql = "SELECT CEIL(COUNT(*)/10) FROM t_news WHERE state = %s"
cursor.execute(sql,["待审批"])
count_page = cursor.fetchone()[0]
return count_page
except Exception as e:
print(e)
finally:
if "con" in dir():
con.close()
def search_userid(self, username):
try:
con = pool.get_connection()
cursor = con.cursor()
sql = "SELECT id FROM t_user WHERE username = %s"
cursor.execute(sql, [username])
userid = cursor.fetchone()[0]
return userid
except Exception as e:
print(e)
finally:
if "con" in dir():
con.close()
def search_user_role(self, username):
try:
con = pool.get_connection()
cursor = con.cursor()
sql = "SELECT r.role FROM t_user u JOIN t_role r "\
"ON u.role_id = r.id WHERE u.username = %s "
cursor.execute(sql, [username])
role = cursor.fetchone()[0]
return role
except Exception as e:
print(e)
finally:
if "con" in dir():
con.close()
def login(self, username, password):
try:
con = pool.get_connection()
cursor = con.cursor()
sql = "SELECT COUNT(*) FROM t_user WHERE username = %s "\
"AND AES_DECRYPT(UNHEX(password),'HelloWorld') = %s"
cursor.execute(sql, (username, password))
count = cursor.fetchone()[0]
return True if count == 1 else False
except Exception as e:
print(e)
finally:
if "con" in dir():
con.close()
def delete_by_id(self,id):
try:
con = pool.get_connection()
con.start_transaction()
cursor = con.cursor()
sql = "DELETE FROM t_news WHERE id = %s"
cursor.execute(sql,[id])
con.commit()
except Exception as e:
if "con" in dir():
con.rollback()
print(e)
finally:
if "con" in dir():
con.close()
def update_unreview_news(self,id):
try:
con = pool.get_connection()
con.start_transaction()
cursor = con.cursor()
sql = "UPDATE t_news SET state = %s WHERE id = %s"
cursor.execute(sql,["已审批",id])
con.commit()
except Exception as e:
if "con" in dir():
con.rollback()
print(e)
finally:
if "con" in dir():
con.close()
def search_content_id(self,id):
try:
con = pool.get_connection()
cursor = con.cursor()
sql = "SELECT content_id "\
"FROM t_news "\
"WHERE id = %s"
cursor.execute(sql,[id])
content_id = cursor.fetchone()[0]
return content_id
except Exception as e:
print(e)
finally:
if "con" in dir():
con.close()
def update(self,id,title,type_id,content_id,is_top,):
try:
con = pool.get_connection()
con.start_transaction()
cursor = con.cursor()
sql = "UPDATE t_news SET title=%s,type_id=%s,content_id=%s,"\
"is_top=%s,state=%s,update_time=NOW() WHERE id=%s"
cursor.execute(sql,[title,type_id,content_id,is_top,"待审批",id])
con.commit()
except Exception as e:
if "con" in dir():
con.rollback()
print(e)
finally:
if "con" in dir():
con.close()
def insert(self,title,editor_id,type_id,content_id,is_top):
try:
con = pool.get_connection()
con.start_transaction()
cursor = con.cursor()
sql = "INSERT INTO t_news(title,editor_id,type_id,content_id,is_top,state) "\
"VALUES(%s,%s,%s,%s,%s,%s)"
cursor.execute(sql,[title,editor_id,type_id,content_id,is_top,"待审批"])
con.commit()
except Exception as e:
if "con" in dir():
con.rollback()
print(e)
finally:
if "con" in dir():
con.close()
def search_by_id(self,id):
try:
con = pool.get_connection()
cursor = con.cursor()
sql = "SELECT n.title,t.type,n.is_top "\
"FROM t_news n "\
"JOIN t_type t ON n.type_id = t.id "\
"WHERE n.id = %s"
cursor.execute(sql,[id])
result = cursor.fetchone()
return result
except Exception as e:
print(e)
finally:
if "con" in dir():
con.close()
def research_username(self, username):
try:
con = pool.get_connection()
cursor = con.cursor()
sql = "SELECT username FROM t_user WHERE username = %s"
cursor.execute(sql, [username])
result = cursor.fetchone()
return result
except Exception as e:
if "con" in dir():
con.rollback()
print(e)
#return e
finally:
if "con" in dir():
con.close()
def search_user_list(self, page):
try:
con = pool.get_connection()
cursor = con.cursor()
sql = "SELECT u.id,u.username,r.role FROM t_user u JOIN t_role r "\
"ON u.role_id = r.id "\
"ORDER BY u.id "\
"LIMIT %s,%s"
cursor.execute(sql, ((page - 1) * 10, 10))
result = cursor.fetchall()
return result
except Exception as e:
print(e)
finally:
if "con" in dir():
con.close()
def insert(self, username, password, email, role_id):
try:
con = pool.get_connection()
con.start_transaction()
cursor = con.cursor()
sql = "INSERT INTO t_user(username,password,email,role_id) "\
"VALUES(%s,HEX(AES_ENCRYPT(%s,'HelloWorld')),%s,%s)"
cursor.execute(sql, [username, password, email, role_id])
con.commit()
except Exception as e:
if "con" in dir():
con.rollback()
print(e)
#return e
finally:
if "con" in dir():
con.close()
def search_list(self,page):
try:
con = pool.get_connection()
cursor = con.cursor()
sql = "SELECT n.id,n.title,t.type,u.username "\
"FROM t_news n JOIN t_type t ON n.type_id = t.id "\
"JOIN t_user u ON n.editor_id = u.id "\
"ORDER BY n.create_time DESC "\
"LIMIT %s,%s"
cursor.execute(sql,((page-1)*10,10))
result = cursor.fetchall()
return result
except Exception as e:
print(e)
finally:
if "con" in dir():
con.close()
def change_option(self, sql):
global con
try:
con = pool.get_connection()
con.start_transaction()
cursor = con.cursor()
cursor.execute(sql)
# result = cursor.fetchall()
con.commit()
except Exception as e:
# print("添加失败")
# 操作失败
con.rollback()
return e
finally:
con.close()
def search_cache(self,id):
try:
con = pool.get_connection()
cursor = con.cursor()
sql = "SELECT n.title,u.username,t.type,n.content_id,"\
"n.is_top,n.create_time "\
"FROM t_news n "\
"JOIN t_type t ON n.type_id = t.id "\
"JOIN t_user u ON n.editor_id = u.id "\
"WHERE n.id = %s"
cursor.execute(sql,[id])
result = cursor.fetchone()
return result
except Exception as e:
print(e)
finally:
if "con" in dir():
con.close()
def update(self, id, username, password, email, role_id):
try:
con = pool.get_connection()
con.start_transaction()
cursor = con.cursor()
sql = "UPDATE t_user SET username = %s, "\
"password = HEX(AES_ENCRYPT(%s,'HelloWorld')),"\
"email = %s,role_id = %s "\
"WHERE id = %s"
cursor.execute(sql, [username, password, email, role_id, id])
con.commit()
except Exception as e:
if "con" in dir():
con.rollback()
print(e)
#return e
finally:
if "con" in dir():
con.close()