基于MySQL、Redis和MongoDB数据库开发命令行新闻管理系统
摘要
项目采用MySQL、Redis和MongoDB三种数据库与python进行数据交互实现在命令行运行的新闻管理系统，根据三种不同数据库对数据的不同特性，将数据分别保存，提高数据的持久性、安全性和高效性
引言
据了解，淘宝采用的存储数据平台式MySQL，当然是基于MySQL开发出适合自己的数据库，事实上，对查询数据的使用率是远远高于对数据进行增删改的，为了了解数据如果安全、持久地保存在数据库，并高效访问，作为新手的我，希望通过这个项目能提高自身对数据的了解。
本项目并非前端页面，而是python包，通过在python环境下点击运行。它的功能类似现在大多的新闻管理系统，能够编辑发布新闻、对新闻进行增删改查操作、编辑增加用户和对用用户进行增删改查操作
系统结构
软件与环境
Python运行环境：Python 3.8 编译软件：PyCharm
MySQL运行环境：MySQL8.0 视图软件：Navicat Premium 15
Redis运行环境：redis 视图软件：RedisDesktopManager
MongoDB运行环境：mongodb 视图软件：Studio 3T
项目结构
项目使用相关模块
- Coloram模块：向控制台输出彩色字体
- redis-py模块：python与redis连接
- MySQL-Connector模块：python与MySQL连接
数据库功能简析
- MySQL：关系型数据库，存放用户、用户角色、新闻、新闻类别数据库的基本信息
- redis：NOSQL型数据库，缓存新闻数据，定时删除普通新闻，不删除置顶新闻
- MongoDB：介于NOSQL型数据库和关系型数据库之间的数据库，用来存放新闻的图片和文本信息
项目流程图
实验代码
设计数据库接口连接：
Mysql_db.py
import mysql.connector.pooling
__config = {
"host": "localhost",
"port": 1314,
"user": "root",
"password": "123456",
"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="eclass2020",
db=1,
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", "eclass2020")
python连接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)
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()
# 查询所有新闻的总页数
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()
# 删除指定id的新闻列表
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 insert_new(self, title, editor_id, type_id, content_id, is_top):
try:
con = pool.get_connection()
con.start_transaction()
cursor = con.cursor()
sql = "INSERT INTO t_news(title, editor_id, type_id, content_id, " \
"is_top, state) " \
"VALUES(%s, %s, %s, %s, %s, %s)"
cursor.execute(sql, (title, editor_id, type_id,
content_id, is_top, "待审批"))
con.commit()
except Exception as e:
if "con" in dir():
con.rollback()
print(e)
finally:
if "con" in dir():
con.close()
# 根据新闻id查询新闻保存带redis中的内容
def search_cache(self, id):
try:
con = pool.get_connection()
cursor = con.cursor()
sql = "SELECT n.title,u.username,t.type,n.content_id," \
"n.is_top,n.create_time " \
"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.id=%s"
cursor.execute(sql, [id])
result = cursor.fetchone()
return result
except Exception as e:
print(e)
finally:
if "con" in dir():
con.close()
# 根据id查询新闻内容
def search_new_by_id(self, 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 update_new(self, id, title, type_id, content_id, is_top):
try:
con = pool.get_connection()
con.start_transaction()
cursor = con.cursor()
sql = "UPDATE t_news SET title=%s, type_id=%s, content_id=%s, " \
"is_top=%s, state=%s, update_time=NOW() WHERE id=%s"
cursor.execute(sql, (title, type_id, content_id,
is_top, "待审批", id))
con.commit()
except Exception as e:
if "con" in dir():
con.rollback()
print(e)
finally:
if "con" in dir():
con.close()
# 根据id获取content_id
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()
redis和mongodb主要用在news表表中，为此创建它们与news表的数据连接表
redis_news_dao.py
from db.redis_db import pool
import redis
class RedisNewsDao:
def insert(self, id, title, username, type, content, is_top, create_time):
con = redis.Redis(
connection_pool=pool
)
try:
con.hmset(id, {
"title": title,
"author": username,
"type": type,
"content": content,
"is_top": is_top,
"create_time": create_time
})
if is_top == 0:
con.expire(id, 24*60*60)
except Exception as e:
print(e)
finally:
del con
def delete_cache(self, id):
con = redis.Redis(
connection_pool=pool
)
try:
con.delete(id)
except Exception as e:
print(e)
finally:
del con
mongo_news_dao.py
from db.mongo_db import client
from bson.objectid import ObjectId
class MongoNewsDao:
# 添加新闻正文内容到mongodb中
def insert(self, title, content):
try:
client.vega.news.insert_one(
{"title": title, "content": content})
except Exception as e:
print(e)
# 根据title查询新闻正文主键
def search_new_id(self, title):
try:
news = client.vega.news.find_one({"title": title})
return str(news["_id"])
except Exception as e:
print(e)
# 根据主键id更新新闻内容
def update_by_id(self, id, title, content):
try:
client.vega.news.update_one(
{"_id": ObjectId(id)}, {"$set": {"title": title, "content": content}})
except Exception as e:
print(e)
# 根据主键id查找新闻内容
def select_content_by_id(self, id):
try:
news = client.vega.news.find_one({"_id": ObjectId(id)})
return news["content"]
except Exception as e:
print(e)
# 根据主键id删除新闻
def delete_new_by_id(self, id):
try:
client.vega.news.delete_one({"_id": ObjectId(id)})
except Exception as e:
print(e)
为mysql数据库中的四张表创建service封装文件
user_service.py
from db.user_dao import UserDao
class UserService:
__user_dao = UserDao()
# 验证用户登录
def login(self, username, password):
result = self.__user_dao.login(username, password)
return result
# 查询用户角色
def search_user_role(self, username):
role = self.__user_dao.search_user_role(username)
return role
# 添加用户记录
def insert(self, username, password, email, role_id):
self.__user_dao.insert(username, password, email, role_id)
# 获取用户列表
def search_user_list(self, page):
result = self.__user_dao.search_user_list(page)
return result
# 查询用户总页数
def search_user_count(self):
count_page = self.__user_dao.search_user_count()
return count_page
# 更新用户信息
def update_user(self, username, password, email, role_id, id):
self.__user_dao.update_user(username, password, email, role_id, id)
# 删除指定id的新闻列表
def delete_by_id(self, id):
self.__user_dao.delete_by_id(id)
# 根据用户名查询用户id
def search_user_id(self, username):
user_id = self.__user_dao.search_user_id(username)
return user_id
role_service.py
from db.role_dao import RoleDao
class RoleService:
_role_dao = RoleDao()
# 查询角色列表
def search_role_list(self):
result = self._role_dao.search_role_list()
return result
type_service.py
from db.type_dao import TypeDao
class TypeService:
__type_dao = TypeDao()
# 查询新闻类型列表
def search_news_category_list(self):
result = self.__type_dao.search_news_category_list()
return result
news_service.py
from db.news_dao import NewsDao
from db.redis_news_dao import RedisNewsDao
from db.mongo_news_dao import MongoNewsDao
class NewsService:
__news_dao = NewsDao()
__redis_news_dao = RedisNewsDao()
__mongo_news_dao = MongoNewsDao()
# 查询待审批新闻列表
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
# 更新待审批新闻
def update_unreview_news(self, id):
self.__news_dao.update_unreview_news(id)
# 获取所有新闻列表
def search_list(self, page):
result = self.__news_dao.search_list(page)
return result
# 查询所有新闻的总页数
def search_count_page(self):
count_page = self.__news_dao.search_count_page()
return count_page
# 删除指定id的新闻列表
def delete_by_id(self, id):
content_id = self.__news_dao.search_content_id(id)
self.__news_dao.delete_by_id(id)
self.__mongo_news_dao.delete_new_by_id(content_id)
# 添加新闻
def insert_new(self, title, editor_id, type_id, content, is_top):
self.__mongo_news_dao.insert(title, content)
content_id = self.__mongo_news_dao.search_new_id(title)
self.__news_dao.insert_new(title, editor_id, type_id, content_id, is_top)
# 查找用户缓存的记录
def search_cache(self, id):
result = self.__news_dao.search_cache(id)
return result
# 向redis中缓存新闻数据
def cache_news(self, id, title, username, type, content, is_top, create_time):
self.__redis_news_dao.insert(id, title, username, type, content, is_top, create_time)
# 删除缓存中的新闻数据
def delete_cache(self, id):
self.__redis_news_dao.delete_cache(id)
# 根据id查询新闻内容
def search_new_by_id(self, id):
result = self.__news_dao.search_new_by_id(id)
return result
# 更新新闻
def update_new(self, id, title, type_id, content, is_top):
content_id = self.__news_dao.search_content_id(id)
self.__mongo_news_dao.update_by_id(content_id, title, content)
self.__news_dao.update_new(id, title, type_id, content_id, is_top)
self.__redis_news_dao.delete_cache(id)
# 根据id获取content_id
def search_content_id(self, id):
content_id = self.__news_dao.search_content_id(id)
return content_id
# 根据主键id查找新闻内容
def select_content_by_id(self, id):
content = self.__mongo_news_dao.select_content_by_id(id)
return content
在编程好数据交互配置后，最终实现页面显示代码
项目首页代码
from service.user_service import UserService
from service.news_service import NewsService
from service.role_service import RoleService
from service.type_service import TypeService
from colorama import Fore, Style
from getpass import getpass
import sys
import time
import os
__user_service = UserService()
__news_service = NewsService()
__role_service = RoleService()
__type_service = TypeService()
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)
# 登陆成功
if result:
# 查询角色
role = __user_service.search_user_role(username)
os.system("cls")
while True:
...
elif opt == "2":
sys.exit(0)
新闻编辑员身份代码
if role == '新闻编辑':
os.system('cls')
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':
os.system('cls')
title = input("\n\t新闻标题：")
user_id = __user_service.search_user_id(username)
result = __type_service.search_news_category_list()
for index in range(len(result)):
one = result[index]
print(Fore.LIGHTBLUE_EX, "\n\t%d.%s" % (index + 1, one[1]))
print(Style.RESET_ALL)
opt = input("\n\t类别编号：")
type_id = result[int(opt) - 1][0]
# 新闻正文内容——文件读取
path = input("\n\t输入文件路径: ")
file = open(path, "r", encoding="utf-8")
content = file.read()
file.close()
is_top = input("\n\t置顶级别（0-5）：")
is_commite = input("\n\t是否提交（Y/N）：")
if is_commite == 'Y' or is_commite == 'y':
__news_service.insert_new(title, user_id, type_id, content, is_top)
print("\n\t添加成功（3秒自动返回）")
time.sleep(3)
if opt == '2':
page = 1
while True:
os.system('cls')
count_page = __news_service.search_count_page()
result = __news_service.search_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':
os.system('cls')
break
elif opt == 'prev' and page > 1:
page -= 1
elif opt == 'next' and page < count_page:
page += 1
elif opt == 'prev' and page == 1:
print("\n\t当前是第一页，无法跳转到上一页（3秒自动返回）")
time.sleep(3)
elif opt == 'next' and page == count_page:
print("\n\t当前是最后一页，无法跳转到下一页（3秒自动返回）")
time.sleep(3)
elif 1 <= int(opt) <= 10:
news_id = result[int(opt) - 1][0]
# 修改新闻
result = __news_service.search_new_by_id(news_id)
title = result[0]
type = result[1]
is_top = result[2]
print("\n\t新闻原标题：%s" % title)
new_title = input("\n\t新闻新标题：")
print("\n\t新闻原类型：%s" % type)
result = __type_service.search_news_category_list()
for index in range(len(result)):
one = result[index]
print(Fore.LIGHTBLUE_EX, "\n\t%d.%s" % (index + 1, one[1]))
print(Style.RESET_ALL)
opt = input("\n\t新闻新类别编号：")
type_id = result[int(opt) - 1][0]
# 输入新闻内容
path = input("\n\t输入文件路径: ")
file = open(path, "r", encoding="utf-8")
content = file.read()
file.close()
print("\n\t原置顶级别：%s" % is_top)
new_is_top = input("\n\t新置顶级别（0-5）：")
is_commite = input("\n\t是否提交（Y/N）：")
if is_commite == 'Y' or is_commite == 'y':
__news_service.update_new(news_id, new_title, type_id, content, new_is_top)
print("\n\t新闻修改成功（3秒自动返回）")
time.sleep(3)
elif opt == 'back':
break
elif opt == 'exit':
sys.exit(0)
管理员身份代码
if 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输入操作编号：")
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':
os.system('cls')
break
elif opt == 'prev' and page > 1:
page -= 1
elif opt == 'next' and page < count_page:
page += 1
elif opt == 'prev' and page == 1:
print("\n\t当前是第一页，无法跳转到上一页（3秒自动返回）")
time.sleep(3)
elif opt == 'next' and page == count_page:
print("\n\t当前是最后一页，无法跳转到下一页（3秒自动返回）")
time.sleep(3)
elif 1 <= int(opt) <= 10:
news_id = result[int(opt) - 1][0]
__news_service.update_unreview_news(news_id)
result = __news_service.search_cache(news_id)
title = result[0]
username = result[1]
type = result[2]
content_id = result[3]
# TODO 查新新闻正文
content = __news_service.select_content_by_id(content_id)
is_top = result[4]
create_time = str(result[5])
__news_service.cache_news(news_id, title, username, type,
content, is_top, create_time)
elif opt == '2':
page = 1
while True:
os.system('cls')
count_page = __news_service.search_count_page()
result = __news_service.search_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':
os.system('cls')
break
elif opt == 'prev' and page > 1:
page -= 1
elif opt == 'next' and page < count_page:
page += 1
elif opt == 'prev' and page == 1:
print("\n\t当前是第一页，无法跳转到上一页（3秒自动返回）")
time.sleep(3)
elif opt == 'next' and page == count_page:
print("\n\t当前是最后一页，无法跳转到下一页（3秒自动返回）")
time.sleep(3)
elif 1 <= int(opt) <= 10:
news_id = result[int(opt) - 1][0]
__news_service.delete_by_id(news_id)
__news_service.delete_cache(news_id)
elif opt == 'back':
os.system('cls')
break
elif opt == '2':
while True:
os.system("cls")
print(Fore.LIGHTGREEN_EX, "\n\t1. 添加用户")
print(Fore.LIGHTGREEN_EX, "\n\t2. 修改用户")
print(Fore.LIGHTGREEN_EX, "\n\t3. 删除用户")
print(Fore.LIGHTRED_EX, "\n\tback. 返回上一层")
print(Style.RESET_ALL)
opt = input("\n\t输入操作编号：")
if opt == 'back':
os.system('cls')
break
elif opt == '1':
os.system("cls")
username = input("\n\t用户名：")
password = getpass("\n\t密码：")
repassword = getpass("\n\t确认密码：")
if password != repassword:
print(Fore.LIGHTRED_EX, "\n\t两次密码不一样，请重新输入（3秒自动返回）")
print(Style.RESET_ALL)
time.sleep(3)
continue
email = input("\n\t邮箱: ")
result = __role_service.search_role_list()
for index in range(len(result)):
one = result[index]wu
print(Fore.LIGHTBLUE_EX, "\n\t%d.%s" % (index + 1, one[1]))
print(Style.RESET_ALL)
opt = input("\n\t角色编号：")
role_id = result[int(opt) - 1][0]
__user_service.insert(username, password, email, role_id)
print("\n\t添加成功（3秒自动返回）")
time.sleep(3)
elif opt == '2':
page = 1
while True:
os.system("cls")
count_page = __user_service.search_user_count()
result = __user_service.search_user_list(page)
for index in range(len(result)):
one = result[index]
print(Fore.LIGHTBLUE_EX, "\n\t%d\t%s\t%s" % (index + 1, one[1], one[2]))
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':
os.system('cls')
break
elif opt == 'prev' and page > 1:
page -= 1
elif opt == 'next' and page < count_page:
page += 1
elif opt == 'prev' and page == 1:
print("\n\t当前是第一页，无法跳转到上一页（3秒自动返回）")
time.sleep(3)
elif opt == 'next' and page == count_page:
print("\n\t当前是最后一页，无法跳转到下一页（3秒自动返回）")
time.sleep(3)
elif 1 <= int(opt) <= 10:
user_id = result[int(opt) - 1][0]
os.system("cls")
username = input("\n\t新用户名：")
password = getpass("\n\t新密码：")
repassword = getpass("\n\t确认密码：")
if password != repassword:
print(Fore.LIGHTRED_EX, "\n\t两次密码不一样，请重新输入（3秒自动返回）")
print(Style.RESET_ALL)
time.sleep(3)
continue
email = input("\n\t新邮箱: ")
result = __role_service.search_role_list()
for index in range(len(result)):
one = result[index]
print(Fore.LIGHTBLUE_EX, "\n\t%d.%s" % (index + 1, one[1]))
print(Style.RESET_ALL)
opt = input("\n\t新角色编号：")
role_id = result[int(opt) - 1][0]
opt = input("\n\t是否修改【Y/N】")
if opt == 'Y' or opt == 'y':
__user_service.update_user(username, password, email, role_id, user_id)
print("\n\t添加成功（3秒自动返回）")
time.sleep(3)
elif opt == '3':
page = 1
while True:
os.system('cls')
count_page = __user_service.search_user_count()
result = __user_service.search_user_list(page)
for index in range(len(result)):
one = result[index]
print(Fore.LIGHTBLUE_EX, "\n\t%d\t%s\t%s" % (index + 1, one[1], one[2]))
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':
os.system('cls')
break
elif opt == 'prev' and page > 1:
page -= 1
elif opt == 'next' and page < count_page:
page += 1
elif opt == 'prev' and page == 1:
print("\n\t当前是第一页，无法跳转到上一页（3秒自动返回）")
time.sleep(3)
elif opt == 'next' and page == count_page:
print("\n\t当前是最后一页，无法跳转到下一页（3秒自动返回）")
time.sleep(3)
elif 1 <= int(opt) <= 10:
user_id = result[int(opt) - 1][0]
__user_service.delete_by_id(user_id)
print("\n\t删除成功（3秒自动返回）")
time.sleep(3)
elif opt == 'back':
os.system('cls')
break
elif opt == 'back':
break
elif opt == 'exit':
sys.exit(0)
实验结果
项目入口页面
新闻编辑员功能页面
管理员功能页面
总结与展望
本项目看起来不复杂，简单易学，但是内容涵盖面宽，知识面多，既能理解现在流行的数据库的情况，作为python新手，也能让我快速学习掌握python与数据库的连接交互。由于涉及知识面不足，本项目是在命令行执行的，没有实现在web实现，后续继续学习知识后，可以将其在web实现出来，这样能全面发展。
545
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
