基于Python与MySQL、Redis和MongoDB数据库的新闻管理系统

项目介绍

项目采用MySQL、Redis和MongoDB三种数据库与python进行数据交互实现在命令行运行的新闻管理系统，根据三种不同数据库对数据的不同特性，将数据分别保存，提高数据的持久性、安全性和高效性。 设置管理员和新闻编辑两种身份，为不同的用户设置不同的权限，实现编辑新闻、审核新闻、删除新闻、增加用户、删除用户等功能 。

项目结构

软件环境

Python运行环境：Python 3.8 编译软件：PyCharm

MySQL运行环境：MySQL8.0 视图软件：Navicat Premium 15

Redis运行环境：redis 视图软件：RedisDesktopManager

MongoDB运行环境：mongodb 视图软件：Studio 3T

项目结构

数据库功能简析

MySQL：关系型数据库，存放用户、用户角色、新闻、新闻类别数据库的基本信息

Redis：NOSQL型数据库，缓存新闻数据，定时删除普通新闻，不删除置顶新闻

MongoDB：介于NOSQL型数据库和关系型数据库之间的数据库，用来存放新闻的图片和文本信息

项目流程图

实验代码

连接数据库

mysql_db.py：

import mysql.connector.pooling

# 定义数据库的连接信息
__config = {
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "abcdABCD1234",
    "database": "vega"
}
try:
    pool = mysql.connector.pooling.MySQLConnectionPool(
        **__config,
        pool_size=10
    )
except Exception as e:
    print(e)

redis_db.py：

import redis

try:
    pool = redis.ConnectionPool(
        host="localhost",
        port=6379,
        password="abcdABCD1234",
        db=15,
        max_connections=20
    )
except Exception as e:
    print(e)

mongo_db.py：

from pymongo import MongoClient

client = MongoClient(host="localhost", port=27017)  # 创建客户端对象
client.admin.authenticate("admin", "abcdABCD1234")  # 切换到admin逻辑库，传入参数为用户名和密码

连接MySQL数据表

user_dao.py:

from db.mysql_db import pool

class UserDao():
    # 验证用户登陆
    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 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 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)
        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 search_user_count(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 update_user(self, username, password, email, role_id, 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)
        finally:
            if "con" in dir():
                con.close()

    # 删除指定id的新闻列表
    def delete_by_id(self, id):
        try:
            con = pool.get_connection()
            con.start_transaction()
            cursor = con.cursor()
            sql = "DELETE FROM t_user 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()

    # 根据用户名查询用户id
    def search_user_id(self, username):
        try:
            con = pool.get_connection()
            cursor = con.cursor()
            sql = "SELECT id FROM t_user WHERE username=%s"
            cursor.execute(sql, [username])
            user_id = cursor.fetchone()[0]
            return user_id
        except Exception as e:
            print(e)
        finally:
            if "con" in dir():
                con.close()

role_dao.py:

from db.mysql_db import pool

class RoleDao():
    # 查询角色列表
    def search_role_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()

type_dao.py:

from db.mysql_db import pool

class TypeDao:
    # 查询新闻类型列表
    def search_news_category_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()

news_dao.py:

from db.mysql_db import pool

class NewsDao:
    # 获取待审批新闻页
    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()[0]
            return count_page
        except Exception as e:
            print(e)