新闻管理系统（四）封装news表相关

db中封装news_dao.py

#!/usr/bin/python3
# -*- coding: utf-8 -*-

from vega.db.mysql_db import pool

class NewsDao(object):
    # 查询待审批列表
    def search_unreview_list(self, page):
        try:
            con = pool.get_connection()
            cursor = con.cursor()
            sql="SELECT n.id,n.title,u.username,t.type " \
                "FROM t_news n JOIN t_type t ON n.type_id=t.id " \
                "JOIN t_user u ON u.id=n.editor_id " \
                "WHERE n.state=%s " \
                "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 search_unreview_count_page(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()  # 将连接放回连接池

service中封装news_service.py

#!/usr/bin/python3
# -*- coding: utf-8 -*-

from vega.db.news_dao import NewsDao

class NewsService(object):
    __news_dao=NewsDao()

    # 查询未审批的新闻列表
    def search_unreview_list(self, page):
        result = self.__news_dao.search_unreview_list(page)
        return result
        
    # 查询待审批新闻的总页数
    def search_unreview_count_page(self):
        count_page=self.__news_dao.search_unreview_count_page()
        return count_page

app.py中封装审批新闻相关

#!/usr/bin/python3
# -*- coding: utf-8 -*-
# @Time     : 2021/9/25 20:44
# @Author   : InsaneLoafer
# @File     : app.py

import os
import sys
import time
from colorama import Fore,Style
from getpass import getpass
from service.user_service import UserService
from service.news_service import NewsService


__user_service=UserService()
__news_service=NewsService()

while True:
    os.system('cls')  # 清理控制台
    print(Fore.LIGHTBLUE_EX,"\n\t=======================")
    print(Fore.LIGHTBLUE_EX,"\n\t欢迎使用新闻管理系统")
    print(Fore.LIGHTBLUE_EX,"\n\t=======================")
    print(Fore.LIGHTGREEN_EX,"\n\t1.登录系统")
    print(Fore.LIGHTGREEN_EX,"\n\t2.退出系统")
    print(Style.RESET_ALL)
    opt=input("\n\t输入操作编号：")

    if opt=="1":
        username=input("\n\t用户名：")
        password=getpass("\n\t密码：")
        result=__user_service.login(username,password)  # getpass可以隐藏密码
        # 登录成功
        if result:
            # 查询角色
            role=__user_service.search_user_role(username)
            os.system("cls")
            while True:
                if role=="新闻编辑":
                    print("test")
                elif role=="管理员":
                    print(Fore.LIGHTGREEN_EX,"\n\t1.新闻管理")
                    print(Fore.LIGHTGREEN_EX,"\n\t2.用户管理")
                    print(Fore.LIGHTRED_EX,"\n\tback.退出登录")
                    print(Fore.LIGHTRED_EX,"\n\texit.退出系统")
                    print(Style.RESET_ALL)
                    opt = input("\n\t输入操作编号：")
                    if opt=='1':
                        while True:
                            os.system("cls")
                            print(Fore.LIGHTGREEN_EX, "\n\t1.审批新闻")
                            print(Fore.LIGHTGREEN_EX, "\n\t2.删除新闻")
                            print(Fore.LIGHTRED_EX, "\n\tback.返回上一层")
                            print(Style.RESET_ALL)
                            opt = input("\n\t输入操作编号：")  # TODO:新闻操作
                            if opt == '1':
                                page = 1
                                while True:
                                    os.system("cls")
                                    count_page = __news_service.search_unreview_count_page()
                                    result = __news_service.search_unreview_list(page)
                                    for index in range(len(result)):
                                        one = result[index]
                                        print(Fore.LIGHTBLUE_EX, "\n\t%d\t%s\t%s\t%s" %
                                              (index + 1, one[1], one[2], one[3]))
                                    print(Fore.LIGHTBLUE_EX, "\n\t----------------------")
                                    print(Fore.LIGHTBLUE_EX, "\n\t%d/%d" % (page, count_page))
                                    print(Fore.LIGHTBLUE_EX, "\n\t----------------------")
                                    print(Fore.LIGHTRED_EX, "\n\tback.返回上一层")
                                    print(Fore.LIGHTRED_EX, "\n\tprev.上一页")
                                    print(Fore.LIGHTRED_EX, "\n\tnext.下一页")
                                    print(Style.RESET_ALL)
                                    opt = input("\n\t输入操作编号：")
                                    if opt == "back":
                                        break
                                    elif opt == "prev" and page > 1:
                                        page -= 1
                                    elif opt == "next" and page < count_page:
                                        page += 1
                            elif opt == "back":
                                break

                    elif opt=="back":
                        break
                    elif opt=="exit":
                        sys.exit(0)
        else:
            print("\n\t登录失败（3秒自动返回）")
            time.sleep(3)
    elif opt=="2":
        sys.exit(0)  # 退出系统