def seach_list(self):
try:
pool = pooldb.connection()
cursor = pool.cursor()
sql = "SELECT id,role from t_role"
cursor.execute(sql)
result = cursor.fetchall()
return result
except Exception as e:
print(e)
finally:
if "pool" in dir():
pool.close()
def login(self, username, password):
try:
pool = pooldb.connection()
cursor = pool.cursor()
sql = "SELECT COUNT(*) FROM t_user WHERE username=%s AND password=%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 "pool" in dir():
pool.close()
def seach_user_count(self):
try:
pool = pooldb.connection()
cursor = pool.cursor()
sql = "SELECT CEIL(count(*)/10) from t_user"
cursor.execute(sql)
count_page = cursor.fetchone()[0]
return count_page
except Exception as e:
# pool.rollback()
print(e)
finally:
if "pool" in dir():
pool.close()
def seach_user_role(self, username):
try:
pool = pooldb.connection()
cursor = pool.cursor()
sql = "SELECT r.role FROM t_user t JOIN t_role r on t.role_id=r.id WHERE t.username=%s"
cursor.execute(sql, [username])
role = cursor.fetchone()[0]
return role
except Exception as e:
# pool.rollback()
print(e)
finally:
if "pool" in dir():
pool.close()
def seach_unreview_count_page(self):
try:
pool = pooldb.connection()
cursor = pool.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 "pool" in dir():
pool.close()
def delete_by_id(self, id):
try:
pool = pooldb.connection()
cursor = pool.cursor()
sql = "delete from t_user where id=%s"
cursor.execute(sql, [id])
pool.commit()
except Exception as e:
if "pool" in dir():
pool.rollback()
print(e)
finally:
if "pool" in dir():
pool.close()
def insert_user(self, username, password, email, role_id):
try:
pool = pooldb.connection()
cursor = pool.cursor()
sql = "INSERT INTO t_user(username,password,email,role_id) VALUES(%s,%s,%s,%s)"
cursor.execute(sql, (username, password, email, role_id))
pool.commit()
except Exception as e:
if "pool" in dir():
pool.rollback()
print(e)
finally:
if "pool" in dir():
pool.close()
def update_unreview(self, id):
try:
pool = pooldb.connection()
cursor = pool.cursor()
sql = "update t_news set state=%s where id=%s"
cursor.execute(sql, ("已审批", id))
pool.commit()
except Exception as e:
if "pool" in dir():
pool.rollback()
print(e)
finally:
if "pool" in dir():
pool.close()
def seach_user_list(self, page):
try:
pool = pooldb.connection()
cursor = pool.cursor()
sql="SELECT u.id,u.username,t.role from "\
"t_user u join t_role t on u.role_id=t.id "\
"order by u.id "\
"limit %s,%s"
cursor.execute(sql, ((page - 1) * 10, 10))
result = cursor.fetchall()
return result
except Exception as e:
# pool.rollback()
print(e)
finally:
if "pool" in dir():
pool.close()
def seach_list(self, page):
try:
pool = pooldb.connection()
cursor = pool.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 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 "pool" in dir():
pool.close()
def delete_by_id(self, id):
try:
pool = pooldb.connection()
cursor = pool.cursor()
sql = "delete from t_news where id=%s"
cursor.execute(sql, [id])
pool.commit()
except Exception as e:
if "pool" in dir():
pool.rollback()
print(e)
finally:
if "pool" in dir():
pool.close()
# new=NewsDao()
# print(new.seach_unreview_list(1))
def update_user(self, id, username, password, email, role_id):
try:
pool = pooldb.connection()
cursor = pool.cursor()
sql = "update t_user set username=%s,password=%s,email=%s,role_id=%s where id=%s"
cursor.execute(sql, (username, password, email, role_id, id))
pool.commit()
except Exception as e:
if "pool" in dir():
pool.rollback()
print(e)
finally:
if "pool" in dir():
pool.close()
#
# if __name__ == "__main__":
# u=UserDao()
# print(u.seach_user_list(1))