def get_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, ))
return cursor.fetchone()[0]
except Exception as e:
print('error ', e)
finally:
if 'con' in dir():
con.close()
def search_list(self):
try:
con = pool.get_connection()
cursor = con.cursor()
sql = "SELECT id, role FROM t_role"
cursor.execute(sql)
return cursor.fetchall()
except Exception as e:
print(e)
finally:
if "con" in dir():
con.close()
def search_user(self,page):
try:
con = pool.get_connection()
cursor = con.cursor()
sql = 'select u.username,u.email,r.role from t_user u inner join t_role r on u.role_id = r.id limit %s,%s;'
cursor.execute(sql,((page-1)*5,5))
result = cursor.fetchall()
return result
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 inner 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 search_username(self):
try:
con = pool.get_connection()
cursor = con.cursor()
sql = 'select username from t_user'
cursor.execute(sql)
username = cursor.fetchall()
return username
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),"123")=%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 search_useid(self, username):
try:
con = pool.get_connection()
cur = con.cursor()
sql = "select id from t_user where username = %s"
cur.execute(sql, [username])
count_page = cur.fetchone()[0]
return count_page
except Exception as e:
print(e)
finally:
if "con" in dir():
con.close()
def search_role(self):
try:
con = pool.get_connection()
cursor = con.cursor()
sql = 'select * from t_role order by id asc;'
cursor.execute(sql)
role = cursor.fetchall()
return role
except Exception as e:
print(e)
finally:
if 'con' in dir():
con.close() # 归还连接
def search_unreview_count_page(self):
try:
con = pool.get_connection()
cursor = con.cursor()
sql = "select ceil(count(*)/10) from t_news n where n.state=%s"
cursor.execute(sql, ["待审批"])
search_page = cursor.fetchone()[0]
return search_page
except Exception as e:
print(e)
finally:
if 'con' in dir():
con.close()
def search_list(self):
try:
con = pool.get_connection()
cur = con.cursor()
sql = "SELECT id,type from t_type order by id"
cur.execute(sql)
result = cur.fetchall()
return result
except Exception as e:
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 search_unreview_count_page(self):
try:
con = pool.get_connection()
cursor = con.cursor()
sql = "select CEIL(count(*)/10) From t_news where status=%s"
cursor.execute(sql, ['unfinished'])
count_page = cursor.fetchone()[0]
return count_page
except Exception as e:
print(e)
finally:
if 'con' in dir():
con.close()
def search_list(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_news_page_count(self):
try:
con = pool.get_connection()
cursor = con.cursor()
sql = 'select ceil(count(*)/5) from t_news;'
cursor.execute(sql)
result = cursor.fetchone()[0]
return result
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 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), 'f**k')=%s;"
cursor.execute(sql, (username, password))
ret = cursor.fetchone()[0]
return True if ret == 1 else False
except Exception as e:
print('error ', e)
finally:
if 'con' in dir():
con.close()
def search_list(self):
try:
con = pool.get_connection()
cursor = con.cursor()
sql = 'SELECT id,type FROM t_type ORDER BY id '
cursor.execute(sql)
result = cursor.fetchall()
return result
except Exception as e:
print(e)
finally:
if 'con' in dir():
con.close()
def search_unapproved_count_page():
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_user_role(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 search_count_page(self):
try:
con = pool.get_connection()
cursor = con.cursor()
sql = "SELECT COUNT(*) FROM t_user WHERE role_id=%s;"
cursor.execute(sql, ["2"])
result = math.ceil(cursor.fetchone()[0] / 10)
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_count_page(self):
try:
con = pool.get_connection()
cursor = con.cursor()
sql = 'SELECT CEIL(COUNT(*)/10) FROM t_news '
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 login(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 update_unreview_news(self,id):
try:
con = pool.get_connection()
cursor = con.cursor()
con.start_transaction()
sql = 'update t_news set state="已审批" where id=%s;'
cursor.execute(sql,[id])
con.commit()
except Exception as e:
if 'con' in dir():
con.rollback()
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_list(self, page):
try:
con = pool.get_connection()
cursor = con.cursor()
sql = "SELECT u.id,u.username,u.email FROM t_user u JOIN t_role r ON u.role_id=r.id " \
"WHERE r.role=%s 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 search_by_id(self, id):
""" 根据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 login(self, username, password):
try:
con = pool.get_connection()
cur = con.cursor()
sql = "select count(*) from t_user where username=%s" \
" and AES_DECRYPT(UNHEX(password),'HelloWorld')=%s"
cur.execute(sql, (username, password))
count = cur.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()
