1.概况
在学习python时跟着课程书写了一个简单的新闻管理系统。但只写了管理员的登录界面，而新闻编辑页面需要用到其他类型的数据库，就并未对新闻编辑界面进行书写（可就课程设计进行一个简单参考）
2.sql表的创建
其中一共包含了4张sql表。分别为：
2.1新闻表
其中editor_id是用户表的id，content_id是新闻类型表的id
CREATE TABLE `t_news` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(40) NOT NULL,
`editor_id` int(10) unsigned NOT NULL,
`type_id` int(10) unsigned NOT NULL,
`content_id` varchar(12) NOT NULL,
`is_top` tinyint(3) unsigned NOT NULL,
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`statu` enum('草稿','待审批','已审批','隐藏') NOT NULL,
PRIMARY KEY (`id`),
KEY `editor_id` (`editor_id`),
KEY `type_id` (`type_id`),
KEY `is_top` (`is_top`),
KEY `create_time` (`create_time`),
KEY `statu` (`statu`)
) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=utf8mb4;2.2角色表
用户角色只有两种：1.管理员 2.新闻编辑
CREATE TABLE `t_role` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`role` varchar(20) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `role` (`role`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;2.3.新闻类型表
新闻的类型可自己定义。
提供一个参考：我定义的是体育、历史、娱乐、新闻、科技这5类
CREATE TABLE `t_type` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`type` varchar(20) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `type` (`type`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;2.4.用户表
其中role_id是角色表的id
CREATE TABLE `t_user` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(20) NOT NULL,
`password` varchar(500) NOT NULL,
`email` varchar(100) NOT NULL,
`role_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`),
KEY `username_2` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4;2.5.参考数据（如果执行了2.1，2.2，2.3，2.4可跳过2.5）
如果不想一张一张表地去创建和生成数据，这里我将提供4个表的（sql执行文件），可直接在navicat中执行，会建好我给出的4张表，并且有一些提供测试的数据。
将提供的sql执行文件下载后：
1.点击右键选择运行sql文件
2.点击这3个点，寻找要执行的sql文件
3.将这4个sql文件依次执行，这里面会包含测试数据！！！一次只能执行一个sql文件记得将4个文件都要执行
4.执行sql文件
3.python代码
1）新建一个News_management_system的项目
2）在项目下新建db包，service包以及在根目录下新建一个app.py的python文件
3）在db包下新建4个文件：1.mysql_db.py 2.news_dao.py 3.role_dao.py 4.user_dao.py
4）在service包下新建3个py文件：1.news_service.py 2.role_service.py 3.user_service.py
具体如下图（使用红框框起来部分，因为我对app.py进行了打包，所以会出现其他文件）：
3.1bd包
3.1.1mysql_db.py
host：如果你的数据库装在本机上，这直接输入localhost，如果不是，需要输入数据库的地址（记得开启数据库远程访问权限）
port：mysql数据库端口默认3306
user：输入登录数据库的用户名（一般为root）
database：输入你要使用的数据库名称
这里需要先安装包：pip install mysql-connector
如果超时请使用清华镜像源进行安装
import mysql.connector.pooling
__config={
"host":"localhost",
"port":"3306",
"user":"your_user",
"password":"your_password",
"database":"your_database"
}
try:
pool=mysql.connector.pooling.MySQLConnectionPool(
**__config,
pool_size=10
)
except Exception as e:
print(f"err:{e}")3.1.2news_dao.py
import math
from db.mysql_db import pool
class NewsDao:
#查询待审批新闻
def search_unreview_list(self,page):
try:
con = pool.get_connection()
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.statu = %s "\
"limit %s,%s"
cursor = con.cursor()
cursor.execute(sql,("待审批",(page-1)*10,10))
result = cursor.fetchall()
return result
except Exception as e:
print(f"err:{e}")
finally:
if "con" in dir():
con.close()
#查询待审批新闻页数
def search_unreview_page_list(self):
try:
con = pool.get_connection()
sql = "select "\
"count(*)"\
"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.statu = %s "
cursor = con.cursor()
cursor.execute(sql,["待审批"])
page = math.ceil(cursor.fetchone()[0]/10)
return page
except Exception as e:
print(f"err:{e}")
finally:
if "con" in dir():
con.close()
#审批新闻
def update_unreview_news(self,id):
try:
con = pool.get_connection()
con.start_transaction()
sql = "update t_news set statu=%s where id=%s"
cursor = con.cursor()
cursor.execute(sql,("已审批",id))
con.commit()
except Exception as e:
if "con" in dir():
con.rollback()
print(f"err:{e}")
finally:
if "con" in dir():
con.close()
#查询所有新闻
def search_list(self,page):
try:
con = pool.get_connection()
sql = "select "\
"n.id,n.title,t.type,u.username,n.statu "\
"from t_news n "\
"join t_type t on n.type_id = t.id "\
"join t_user u on n.editor_id = u.id "\
"limit %s,%s"
cursor = con.cursor()
cursor.execute(sql,((page-1)*10,10))
result = cursor.fetchall()
# print(result)
return result
except Exception as e:
print(f"err:{e}")
return []
finally:
if "con" in dir():
con.close()
#查询所有新闻页数
def search_count_page(self):
try:
con = pool.get_connection()
sql = "select "\
"count(*)"\
"from t_news n "\
"join t_type t on n.type_id = t.id "\
"join t_user u on n.editor_id = u.id "
cursor = con.cursor()
cursor.execute(sql)
page = math.ceil(cursor.fetchone()[0]/10)
return page
except Exception as e:
print(f"err:{e}")
finally:
if "con" in dir():
con.close()
#删除新闻
def delete_by_id(self,id):
try:
con = pool.get_connection()
con.start_transaction()
sql = "delete from t_news where id=%s"
cursor = con.cursor()
cursor.execute(sql,[id])
con.commit()
except Exception as e:
if "con" in dir():
con.rollback()
print(f"err:{e}")
finally:
if "con" in dir():
con.close()3.1.3role_dao.py
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)
return cursor.fetchall()
except Exception as e:
print(e)
finally:
if "con" in dir():
con.close()3.1.4user_dao.py
from db.mysql_db import pool
class UserDao:
#用户登录
def login(self,username,password):
try:
con = pool.get_connection()
sql = "select count(*) from t_user where username = %s and AES_DECRYPT(UNHEX(password),'HelloWorld') = %s"
cursor = con.cursor()
cursor.execute(sql,(username,password))
count = cursor.fetchone()[0]
return True if count == 1 else False
except Exception as e:
print(f"err:{e}")
finally:
if con in dir():
con.close()
#搜索用户角色
def search_user_role(self,username):
try:
con = pool.get_connection()
cursor = con.cursor()
sql = "select role from t_user u join t_role r on u.role_id = r.id where username = %s"
cursor.execute(sql,(username))
result = cursor.fetchone()[0]
return result
except Exception as e:
print(f"err:{e}")
finally:
if con in dir():
con.close()
#添加记录
def insert_user(self,username,password,emai,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,emai,role_id))
con.commit()
except Exception as e:
if "con" in dir():
con.rollback()
print(f"err:{e}")
finally:
if con in dir():
con.close()
#查询所有人数页数
def search_user_page(self):
try:
con = pool.get_connection()
sql = "select ceil(count(*)/10) from t_user "
cursor = con.cursor()
cursor.execute(sql)
page = cursor.fetchone()[0]
return page
except Exception as e:
print(f"err:{e}")
finally:
if "con" in dir():
con.close()
#查询所有用户
def seach_user_list(self,page):
try:
con = pool.get_connection()
cursor = con.cursor()
sql = "select u.id,u.username,u.email,r.role from t_user u join t_role r on u.role_id=r.id limit %s,%s"
cursor.execute(sql,((page-1)*10,10))
result=cursor.fetchall()
return result
except Exception as e:
if "con" in dir():
con.rollback()
print(f"err:{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()
sql = "update t_user set username=%s,password=HEX(AES_ENCRYPT(%s,'HelloWorld')),email=%s,role_id=%s where id=%s"
cursor = con.cursor()
cursor.execute(sql,(username,password,email,role_id,id))
con.commit()
except Exception as e:
if "con" in dir():
con.rollback()
print(f"err:{e}")
finally:
if "con" in dir():
con.close()
# 删除用户
def delete(self, id):
try:
con = pool.get_connection()
con.start_transaction()
sql = "delete from t_user where id=%s"
cursor = con.cursor()
cursor.execute(sql, [id])
con.commit()
except Exception as e:
if "con" in dir():
con.rollback()
print(f"err:{e}")
finally:
if "con" in dir():
con.close()3.2service包
3.2.1news_service.py
from db.news_dao import NewsDao
class NewsService:
__news_service = NewsDao()
#查询待审批新闻
def search_unreview_list(self,page):
return self.__news_service.search_unreview_list(page)
#查询待审批新闻页数
def search_unreview_page_list(self):
return self.__news_service.search_unreview_page_list()
#审批新闻
def update_unreview_news(self,id):
self.__news_service.update_unreview_news(id)
#查询所有新闻
def search_list(self,page):
return self.__news_service.search_list(page)
#查询所有新闻页数
def search_count_page(self):
return self.__news_service.search_count_page()
#删除新闻
def delete_by_id(self,id):
self.__news_service.delete_by_id(id)3.2.2role_service.py
from db.role_dao import RoleDao
class RoleService:
__role_dao=RoleDao()
def search_list(self):
return self.__role_dao.search_list()3.2.3user_service.py
from db.user_dao import UserDao
class UserService:
__user_dao = UserDao()
#用户登录
def login(self,username,password):
return self.__user_dao.login(username,password)
#搜索用户角色
def search_user_role(self,username):
return self.__user_dao.search_user_role(username)
#添加记录
def insert_user(self,username,password,emai,role_id):
self.__user_dao.insert_user(username,password,emai,role_id)
#查询所有人数页数
def search_user_page(self):
return self.__user_dao.search_user_page()
#查询所有用户
def seach_user_list(self,page):
return self.__user_dao.seach_user_list(page)
# 修改用户信息
def update(self, id, username, password, email, role_id):
self.__user_dao.update(id,username,password,email,role_id)
# 删除用户
def delete(self, id):
self.__user_dao.delete(id)3.3app.py文件
直接运行app.py则可启动程序，不过在命令终端启动显示会好看一些
使用命令终端启动格式为：
D:/python_project/News_management_system/News_management_system/.venv/Scripts/python.exe D:\python_project\News_management_system\News_management_system\app.py其中，前面为使用的环境，后面为执行的文件，如果不填前面，会使用环境变量中的python环境
只对程序进行了一个简单书写，并未顾及显示格式，
import time
from colorama import Fore,Back,Style
from service.user_service import UserService
from service.news_service import NewsService
from service.role_service import RoleService
import os
import sys
__user_service = UserService()
__news_service = NewsService()
__role_service = RoleService()
# reslut = __user_service.login("admin","123456")
#
# print(reslut)
while True:
os.system('cls')
print(Fore.LIGHTBLUE_EX,"\n"+"\t"*3+"="*80,)
print(Fore.LIGHTBLUE_EX,"\n"+"\t"*7+"新闻管理系统")
print(Fore.LIGHTBLUE_EX,"\n"+"\t"*3+"="*80,end="")
print("\n"+"\t"*3+"1.登录系统",end="")
print("\n"+"\t"*3+"2.推出系统",end="")
chooce = input("\n"+"\t"*3+"输入操作编号：")
print(Style.RESET_ALL,end="")
if chooce == "1":
username = input("\n"+"\t"*3+"用户名：")
password = input("\n"+"\t"*3+"密码：")
if __user_service.login(username,password):
while True:
os.system('cls')
print(Fore.LIGHTBLUE_EX,"\n" + "\t" * 3 + "1.新闻管理", end="")
print(Fore.LIGHTBLUE_EX,"\n" + "\t" * 3 + "2.用户管理", end="")
print(Fore.LIGHTBLUE_EX,"\n" + "\t" * 3 + "back.推出登录", end="")
print(Fore.LIGHTBLUE_EX,"\n" + "\t" * 3 + "exit.推出系统", end="")
chooce = input("\n" + "\t" * 3 + "输入操作编号：")
# print(Style.RESET_ALL,end="")
if chooce == "1":
while True:
os.system('cls')
print(Fore.LIGHTBLUE_EX,"\n" + "\t" * 3 + "1.审批新闻", end="")
print(Fore.LIGHTBLUE_EX,"\n" + "\t" * 3 + "2.删除新闻", end="")
print(Fore.LIGHTBLUE_EX,"\n" + "\t" * 3 + "back.返回上一层", end="")
chooce = input("\n" + "\t" * 3 + "输入操作编号：")
if chooce == "1":
page = 1
while True:
os.system('cls')
count_page = __news_service.search_unreview_page_list()
news = __news_service.search_unreview_list(page)
for i in range(len(news)):
print(Fore.LIGHTBLUE_EX,"\n"+"\t"*3+f"%d\t%s\t%s\t%s"%(i+1,news[i][1],news[i][2],news[i][3]),end="")
print(Fore.LIGHTBLUE_EX, "\n" + "\t" * 3 +"%d/%d"%(page,count_page), end="")
print(Fore.LIGHTBLUE_EX, "\n" + "\t" * 3 + "back.返回上一层", end="")
print(Fore.LIGHTBLUE_EX, "\n" + "\t" * 3 + "prev.上一页", end="")
print(Fore.LIGHTBLUE_EX, "\n" + "\t" * 3 + "next.下一页", end="")
chooce = input("\n" + "\t" * 3 + "输入操作编号：")
if chooce=="back":
break
elif chooce=="prev":
if page>1:
page-=1
else:
print("当前页为首页！")
continue
elif chooce=="next":
if page<count_page:
page+=1
else:
print("当前页为尾页！！")
continue
elif int(chooce)>=1 and int(chooce)<=10:
__news_service.update_unreview_news(news[int(chooce)-1][0])
# time.sleep(3)
else:
print(Fore.LIGHTBLUE_EX, "\n" + "\t" * 3 + "输入有误！！", end="")
time.sleep(3)
elif chooce == "2":
page = 1
while True:
os.system('cls')
count_page = __news_service.search_count_page()
news = __news_service.search_list(page)
for i in range(len(news)):
print(Fore.LIGHTBLUE_EX,"\n"+"\t"*3+f"%d\t%s\t%s\t%s\t"%(i+1,news[i][1],news[i][2],news[i][3]),news[i][4],end="")
print(Fore.LIGHTBLUE_EX, "\n" + "\t" * 3 +"%d/%d"%(page,count_page), end="")
print(Fore.LIGHTBLUE_EX, "\n" + "\t" * 3 + "back.返回上一层", end="")
print(Fore.LIGHTBLUE_EX, "\n" + "\t" * 3 + "prev.上一页", end="")
print(Fore.LIGHTBLUE_EX, "\n" + "\t" * 3 + "next.下一页", end="")
chooce = input("\n" + "\t" * 3 + "输入操作编号：")
if chooce=="back":
break
elif chooce=="prev":
if page>1:
page-=1
else:
print("\n\t\t\t当前页为首页！")
time.sleep(3)
continue
elif chooce=="next":
if page<count_page:
page+=1
else:
print("\n\t\t\t当前页为尾页！！")
time.sleep(3)
continue
elif int(chooce)>=1 and int(chooce)<=10:
__news_service.delete_by_id(news[int(chooce)-1][0])
# time.sleep(3)
else:
print(Fore.LIGHTBLUE_EX, "\n" + "\t" * 3 + "输入有误！！", end="")
time.sleep(3)
elif chooce == "back":
break
else:
print("\n\t\t\t输入不存在！！", end="")
time.sleep(3)
# print(Style.RESET_ALL, end="")
elif chooce == "2":
while True:
os.system('cls')
print(Fore.LIGHTBLUE_EX, "\n" + "\t" * 3 + "1.添加用户", end="")
print(Fore.LIGHTBLUE_EX, "\n" + "\t" * 3 + "2.修改用户", end="")
print(Fore.LIGHTBLUE_EX, "\n" + "\t" * 3 + "3.删除用户", end="")
print(Fore.LIGHTBLUE_EX, "\n" + "\t" * 3 + "back.推出登录", end="")
chooce = input("\n" + "\t" * 3 + "输入操作编号：")
if chooce=="1":
while True:
os.system('cls')
username = input("\n" + "\t" * 3 + "用户名：")
password = input("\n" + "\t" * 3 + "密码：")
repassword = input("\n" + "\t" * 3 + "重复密码：")
if password==repassword:
email = input("\n" + "\t" * 3 + "邮箱：")
role_type=__role_service.search_list()
for i in range(len(role_type)):
print("\n\t\t\t%s\t%s"%(i+1,role_type[i][1]))
id = input("\n" + "\t" * 3 + "账户类型：")
role_id = role_type[int(id)][0]
confirm = input("\n\t\t\t确认添加用户信息（Y/N）")
if confirm=="Y" or confirm=="y":
__user_service.insert_user(username,password,email,role_id)
print("\n\t\t\t添加成功（3秒后返回）")
time.sleep(3)
continue
else:
print(Fore.LIGHTBLUE_EX, "\n" + "\t" * 3 + "重复密码有误！！")
time.sleep(3)
elif chooce=="2":
page=1
os.system('cls')
count_page = __user_service.search_user_page()
users = __user_service.seach_user_list(page)
for i in range(len(users)):
print(Fore.LIGHTBLUE_EX,
"\n" + "\t" * 3 + f"%d\t%s\t%s\t%s" % (i + 1, users[i][1],users[i][2],users[i][3]),
end="")
print(Fore.LIGHTBLUE_EX, "\n" + "\t" * 3 + "%d/%d" % (page, count_page), end="")
print(Fore.LIGHTBLUE_EX, "\n" + "\t" * 3 + "back.返回上一层", end="")
print(Fore.LIGHTBLUE_EX, "\n" + "\t" * 3 + "prev.上一页", end="")
print(Fore.LIGHTBLUE_EX, "\n" + "\t" * 3 + "next.下一页", end="")
chooce = input("\n" + "\t" * 3 + "输入操作编号：")
if chooce == "back":
break
elif chooce == "prev":
if page > 1:
page -= 1
else:
print("当前页为首页！")
continue
elif chooce == "next":
if page < count_page:
page += 1
else:
print("当前页为尾页！！")
continue
elif int(chooce) >= 1 and int(chooce) < len(users):
os.system('cls')
id=users[int(chooce)-1][0]
username = input("\n" + "\t" * 3 + "用户名：")
password = input("\n" + "\t" * 3 + "密码：")
repassword = input("\n" + "\t" * 3 + "重复密码：")
if password == repassword:
email = input("\n" + "\t" * 3 + "邮箱：")
role_type = __role_service.search_list()
for i in range(len(role_type)):
print("\n\t\t\t%s\t%s" % (i + 1, role_type[i][1]))
role_id = input("\n" + "\t" * 3 + "账户类型：")
role_id = role_type[int(role_id)-1][0]
confirm = input("\n\t\t\t确认修改用户信息（Y/N）")
if confirm == "Y" or confirm == "y":
__user_service.update(id,username, password, email, role_id)
print("\n\t\t\t修改成功（3秒后返回）")
time.sleep(3)
continue
# time.sleep(3)
else:
print(Fore.LIGHTBLUE_EX, "\n" + "\t" * 3 + "输入有误！！", end="")
time.sleep(3)
elif chooce=="3":
page = 1
os.system('cls')
count_page = __user_service.search_user_page()
users = __user_service.seach_user_list(page)
for i in range(len(users)):
print(Fore.LIGHTBLUE_EX,
"\n" + "\t" * 3 + f"%d\t%s\t%s\t%s" % (i + 1, users[i][1], users[i][2], users[i][3]),
end="")
print(Fore.LIGHTBLUE_EX, "\n" + "\t" * 3 + "%d/%d" % (page, count_page), end="")
print(Fore.LIGHTBLUE_EX, "\n" + "\t" * 3 + "back.返回上一层", end="")
print(Fore.LIGHTBLUE_EX, "\n" + "\t" * 3 + "prev.上一页", end="")
print(Fore.LIGHTBLUE_EX, "\n" + "\t" * 3 + "next.下一页", end="")
chooce = input("\n" + "\t" * 3 + "输入操作编号：")
if chooce == "back":
break
elif chooce == "prev":
if page > 1:
page -= 1
else:
print("当前页为首页！")
continue
elif chooce == "next":
if page < count_page:
page += 1
else:
print("当前页为尾页！！")
continue
elif int(chooce) >= 1 and int(chooce) < len(users):
id=users[int(chooce)-1][0]
__user_service.delete(id)
print("\n\t\t\t删除成功（3秒后返回）")
time.sleep(3)
continue
elif chooce=="back":
break
else:
print("\n\t\t\t输入不存在！！", end="")
elif chooce == "back":
break
elif chooce == "exit":
sys.exit(0)
else:
print("\n\t\t\t输入不存在！！",end="")
time.sleep(3)
else:
print("\n" + "\t" * 3 +"账号或密码错误！！3秒后将返回登录",end="")
elif chooce == "2":
sys.exit(0)
else:
print("输入不存在！！3秒后将返回登录",end="")
time.sleep(3)
4.附加内容：使用pyinstaller打包程序，生成一个可执行的exe文件
安装pyinstaller包：pip install pyinstaller
直接使用命令打包：pyinstaller app.py
此时生成的exe文件会在dist文件夹中
(可直接在pycharm上的终端（terminal）执行，此时使用的是你为你建的虚拟环境)
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
