【MK】Python全栈 阶段二：习题汇总 十：MySQL的综合应用：实践二：《新闻管理系统》数据库项目开发实战：Python+MySQL

版权声明：本文为博主原创文章，未经博主允许不得转载。

---

---

一、项目介绍

1.1 流程介绍

1.2 操作管理

1、 管理员身份

1.3 项目结构

1.4 程序运行

1、登录界面效果图展示：使用管理员身份登录

2、登录成功后选择：新闻管理、用户管理

3、…

1.5 cmd 执行

1、复制路径

2、cmd 执行

二、代码实现

新闻管理系统

  用户名：admin 密码：ABC123456

   数据库结构创建： https://blog.csdn.net/InitialHeart2021/article/details/119893583

mysql_db.py

import mysql.connector.pooling

__config = {
   
    'host': 'localhost',
    'port': 3306,
    'user': 'root',
    'password': '123456',
    'database': 'lsj2021_project_news'
}
try:
    pool = mysql.connector.pooling.MySQLConnectionPool(
        **__config,
        pool_size=10
    )
except Exception as e:
    print(e)

news_dao.py

import os, sys
sys.path.append('..')
from db.mysql_db import pool

class NewsDao:
    # 查询待审批的新闻列表  page:分页页数
    def search_unreview_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 " \
                  "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()
            return count_page
        except Exception as e:
            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_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()
                # print('报错！！！')


    # 查询新闻总页数
    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 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()

# service = NewsDao()
# result = service.search_unreview_list(1)
# print(result)

role_dao.py

import sys, os
sys.path.append('..')
from db.mysql_db import pool

class RoleDao:
    # 查询角色列表
    def search_list(self):
        try:
            con = pool.get_connection()
            cursor = con.cursor()
            sql =