本文介绍了使用Python开发的新闻管理系统，详细阐述了项目的流程、操作管理和结构，包括管理员登录、新闻与用户管理。文章还提到了在cmd中执行项目的方法，并提供了数据库结构的创建链接及关键模块的代码文件名。
版权声明：本文为博主原创文章，未经博主允许不得转载。
一、项目介绍
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 = "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()
user_dao.py
import os, sys
sys.path.append("..")
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), 'HELLOWORD')=%s" # 十六进制转二进制，再解密
cursor.execute(sql, [username, password])
# 从游标中取出1条记录
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, 'HELLOWORD')), %s, %s)" # HELLOWORD：密钥
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_count_page(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 search_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 update(self, id, username, password, email, role_id):
try:
con = pool.get_connection()
# 开启事务
con.start_transaction()
# 创建游标
cursor = con.cursor()
sql = "UPDATE t_user SET username=%s, password=HEX(AES_ENCRYPT(%s, 'HELLOWORD')), email=%s, role_id=%s WHERE id=%s" # HELLOWORD：密钥
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()
news_service.py
import sys, os
sys.path.append('..')
from db.news_dao import NewsDao
class NewService:
__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
# 新闻审批
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
# 删除新闻
def delete_by_id(self, id):
self.__news_dao.delete_by_id(id)
role_service.py
import sys, os
sys.path.append('..')
from db.role_dao import RoleDao
class RoleService:
__role_dao = RoleDao()
# 查询角色列表
def search_list(self):
result = self.__role_dao.search_list()
return result
user_service.py
import os, sys
sys.path.append("..")
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_count_page(self):
count_page = self.__user_dao.search_count_page()
return count_page
# 查询用户分页记录
def search_list(self, page):
result = self.__user_dao.search_list(page)
return result
# 更改用户信息
def update(self, id, username, password, email, role_id):
self.__user_dao.update(id, username, password, email, role_id)
# 删除用户记录：根据用户 id 删除
def delete_by_id(self, id):
self.__user_dao.delete_by_id(id)
app.py
from colorama import Fore, Style
# 遮挡用户输入密码的内容
from getpass import getpass
from service.user_service import UserService
from service.news_service import NewService
from service.role_service import RoleService
import os
import sys
import time
__user_service = UserService()
__news_service = NewService()
__role_service = RoleService()
# 最外层的轮询死循环
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 = input("\n\t密码：")
result = __user_service.login(username, password)
# 登录成功
if result == True:
# 查询角色
role = __user_service.search_user_role(username)
while True:
# 清理控制台内容
os.system("cls")
if role == '新闻编辑':
pass
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输入操作编号：")
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循环
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{}/{}".format(page, count_page[0]))
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[0]*10:
page += 1
elif int(opt) >= 1 and int(opt) <= 10:
news_id = result[int(opt)-1][0]
__news_service.update_unreview_news(news_id)
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{}/{}".format(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 int(opt) >= 1 and int(opt) <= 10:
news_id = result[int(opt)-1][0]
__news_service.delete_by_id(news_id)
elif opt == 'back':
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.LIGHTGREEN_EX, "\n\tback.返回上一层")
print(Style.RESET_ALL)
opt = input("\n\t输入操作编号")
if opt == '1':
# 清理屏幕
os.system("cls")
username = input("\n\t用户名：")
password = input("\n\t密码：")
re_password = input("\n\t重复密码：")
if password != re_password:
print("\n\t两次密码不一致，3秒自动返回")
time.sleep(3)
continue
email = input("\n\t邮箱：")
result = __role_service.search_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 角色编号：")
# 输入的角色编号 转成 角色 id
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_count_page()
# 查询用户列表
result = __user_service.search_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.LIGHTBLUE_EX, "\n\tback.返回上一页")
print(Fore.LIGHTBLUE_EX, "\n\tprev.上一页")
print(Fore.LIGHTBLUE_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 int(opt) >= 1 and int(opt) <= 10:
os.system("cls")
# 获得用户主键值
user_id = result[int(opt)-1][0]
username = input("\n\t新的用户名：")
password = input("\n\t新的密码：")
re_password = input("\n\t重复密码：")
if password != re_password:
print(Fore.LIGHTRED_EX, "\n\t两次密码输入不一致,3秒后自动返回")
print(Style.RESET_ALL)
time.sleep(3)
break
email = input("\n\t新的邮箱：")
result = __role_service.search_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':
# 查询要修改用户的ID
__user_service.update(user_id, username, password, email, role_id)
print("\n\t保存成功，3秒后自动返回")
time.sleep(3)
elif opt == '3':
page = 1
while True:
os.system("cls")
count_page = __user_service.search_count_page()
result = __user_service.search_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.LIGHTBLUE_EX, "\n\tback.返回上一层")
print(Fore.LIGHTBLUE_EX, "\n\tprev.上一页")
print(Fore.LIGHTBLUE_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 int(opt) >= 1 and int(opt) <= 10:
os.system("cls")
user_id = result[int(opt)-1][0]
__user_service.delete_by_id(user_id)
print("\n\t删除成功，3秒后自动返回")
time.sleep(3)
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':
# 0：代表安全退出
sys.exit(0)
版权声明：本文为博主原创文章，未经博主允许不得转载。
扫一扫
226
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
