数据库课设
前言
这里两三天帮同学写了一个简单的包含 UI 界面的数据库课设作业。
由于本人美术能力有限，以及是第一次接触 pyqt5 ，所以实际的 UI 界面比较省略，并且时间有限，一些逻辑也没有优化，各位看看就好。
PS：这里的所有 UI 界面都是由 QtDesigner 画出来的，还记得以前有人告诉我一定要学会手写使用 pyqt5 再使用 QtDesigner 画图，所以做课设的时候有取巧。
项目目录
因为是给同学做的，项目目录也没有很好的分块而是直接放在一个文件夹里面，各位将就看一下。
项目需求以及sql代码
建立的是一个企业人事管理系统，表的关系如下：
- 建表 sql 语句
create database classDesign;
use classDesign;
# 用户表
create table user (
id int primary key auto_increment,
username varchar(16) unique,
password varchar(16)
);
# 插入用户数据
INSERT INTO USER (username, password)
VALUES
("admin", "12345"),
("mcx", "qwe"),
("lj", "asd"),
("hn", "zxc");
# 日志
create table log (
username varchar(16),
action enum("login", "query", "delete", "add") not null,
msg varchar(16),
time timestamp not null default CURRENT_TIMESTAMP
);
# 部门表（和职员表一对多关系）
create table dep (
id int primary key auto_increment,
name varchar(16) not null,
des varchar(32)
);
# 插入数据
INSERT INTO `dep` (`name`, `des`) VALUES ('运营', '好销售，不责任手段');
INSERT INTO `dep` (`name`, `des`) VALUES ('销售', '什么都可以销售');
INSERT INTO `dep` (`name`, `des`) VALUES ('教育', '教书育人');
# 职员详细信息
create table emp_detail (
id int primary key auto_increment,
addr varchar(16),
email varchar(16),
time timestamp not null default CURRENT_TIMESTAMP
);
# 插入数据（其实 id 和 time 会自动填入不用插入）
INSERT INTO `classdesign`.`emp_detail` (`id`, `addr`, `email`, `time`) VALUES ('1', '东方', '123@qq.com', '2022-07-03 15:41:32');
INSERT INTO `classdesign`.`emp_detail` (`id`, `addr`, `email`, `time`) VALUES ('2', '北方', '456@qq.com', '2022-07-03 15:42:19');
INSERT INTO `classdesign`.`emp_detail` (`id`, `addr`, `email`, `time`) VALUES ('3', '西方', 'qwwer@qq.com', '2022-07-03 15:42:44');
INSERT INTO `classdesign`.`emp_detail` (`id`, `addr`, `email`, `time`) VALUES ('4', '南方', 'asd@qq.com', '2022-07-03 15:43:23');
# 职员表
create table emp (
id int primary key auto_increment,
name varchar(16) not null,
dep_id int not null,
detail_id int not null,
foreign key(dep_id) references dep(id),
foreign key(detail_id) references emp_detail(id)
);
# 插入数据
INSERT INTO `emp` (`name`, `dep_id`, `detail_id`) VALUES ('奥特曼', '1', '2');
INSERT INTO `emp` (`name`, `dep_id`, `detail_id`) VALUES ('迪迦', '1', '3');
INSERT INTO `emp` (`name`, `dep_id`, `detail_id`) VALUES ('戴拿', '2', '4');
INSERT INTO `emp` (`name`, `dep_id`, `detail_id`) VALUES ('盖亚', '3', '1');
# 项目表（和职员表，多对多关系，即一个项目可以有多个职员负责，一个职员可以负责多个项目）
create table project(
id int primary key auto_increment,
name varchar(16)
);
# 插入数据
INSERT INTO `project` (`name`) VALUES ('数据库');
INSERT INTO `project` (`name`) VALUES ('网络通信');
# 多对多关系的第三方表
create table emp2pro(
id int primary key auto_increment,
emp_id int,
pro_id int,
foreign key(emp_id) references emp(id)
on update cascade
on delete cascade,
foreign key(pro_id) references project(id)
on update cascade
on delete cascade
);
# 插入数据
INSERT INTO `emp2pro` (`emp_id`, `pro_id`) VALUES ('1', '1');
INSERT INTO `emp2pro` (`emp_id`, `pro_id`) VALUES ('1', '2');
INSERT INTO `emp2pro` (`emp_id`, `pro_id`) VALUES ('3', '2');
python 代码实现
FirstWindow.py
from PyQt5.QtGui import *
from PyQt5.QtCore import *
from PyQt5.QtWidgets import * # 导入PyQt5部件
from PyQt5.QtWidgets import QMainWindow, QApplication,QLabel,QTableWidgetItem,QPushButton,QLineEdit,QGridLayout,QWidget,QTableWidget
class Ui_Dialog(object):
def setupUi(self, Dialog):
if not Dialog.objectName():
Dialog.setObjectName(u"Dialog")
Dialog.resize(646, 453)
self.textBrowser = QTextBrowser(Dialog)
self.textBrowser.setObjectName(u"textBrowser")
self.textBrowser.setGeometry(QRect(120, 160, 441, 71))
self.login = QPushButton(Dialog) # 登录按钮
self.login.setObjectName(u"login")
self.login.setGeometry(QRect(160, 320, 91, 41))
self.login.clicked.connect(self.user_login)
self.reg = QPushButton(Dialog) # 注册按钮
self.reg.setObjectName(u"reg")
self.reg.setGeometry(QRect(400, 320, 91, 41))
self.reg.clicked.connect(self.user_reg)
self.retranslateUi(Dialog)
QMetaObject.connectSlotsByName(Dialog)
# setupUi
def retranslateUi(self, Dialog):
Dialog.setWindowTitle(QCoreApplication.translate("Dialog", u"Dialog", None))
self.textBrowser.setHtml(QCoreApplication.translate("Dialog", u"<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.0//EN\" \"http://www.w3.org/TR/REC-html40/strict.dtd\">\n"
"<html><head><meta name=\"qrichtext\" content=\"1\" /><style type=\"text/css\">\n"
"p, li { white-space: pre-wrap; }\n"
"</style></head><body style=\" font-family:'SimSun'; font-size:9pt; font-weight:400; font-style:normal;\">\n"
"<p style=\" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;\"><span style=\" font-size:28pt; font-weight:600;\">\u6b22\u8fce\u4f7f\u7528 XXX \u7cfb\u7edf</span></p></body></html>", None))
self.login.setText(QCoreApplication.translate("Dialog", u"\u767b\u5f55", None))
self.reg.setText(QCoreApplication.translate("Dialog", u"\u6ce8\u518c", None))
# retranslateUi
def user_login(self): # 用户登录页面显示
from PLogin import login_window
self.window = login_window()
self.window.show()
def user_reg(self): # 用户注册页面展示
from PReg import reg_window
self.window = reg_window()
self.window.show()
main.py
from PyQt5.QtGui import *
from PyQt5.QtCore import *
from PyQt5.QtWidgets import * # 导入PyQt5部件
from PyQt5.QtWidgets import QMainWindow, QApplication,QLabel,QTableWidgetItem,QPushButton,QLineEdit,QGridLayout,QWidget,QTableWidget
from FirstWindow import Ui_Dialog
class Window(QDialog,Ui_Dialog): # 实例化初始界面
def __init__(self):
super().__init__()
self.setupUi(self)
def setup_ui(self):
pass
app = QApplication([])
window = Window()
window.show()
app.exec_()
PLogin.py
from PyQt5 import QtCore
from PyQt5.QtGui import *
from PyQt5.QtCore import *
from PyQt5.QtWidgets import * # 导入PyQt5部件
from PyQt5.QtWidgets import QMainWindow, QApplication,QLabel,QTableWidgetItem,QPushButton,QLineEdit,QGridLayout,QWidget,QTableWidget
class Login(object):
def setupUi(self, Dialog):
if not Dialog.objectName():
Dialog.setObjectName(u"Dialog")
Dialog.resize(647, 450)
self.lineEdit = QLineEdit(Dialog) # 用户名
self.lineEdit.setObjectName(u"lineEdit")
self.lineEdit.setGeometry(QRect(280, 220, 121, 31))
self.textBrowser = QTextBrowser(Dialog)
self.textBrowser.setObjectName(u"textBrowser")
self.textBrowser.setGeometry(QRect(100, 70, 441, 71))
self.lineEdit_2 = QLineEdit(Dialog) # 密码
self.lineEdit_2.setObjectName(u"lineEdit_2")
self.lineEdit_2.setGeometry(QRect(280, 280, 121, 31))
self.lineEdit_2.setEchoMode(QLineEdit.Password) # 密码输入星号显示
self.label = QLabel(Dialog)
self.label.setObjectName(u"label")
self.label.setGeometry(QRect(200, 230, 72, 15))
self.label_2 = QLabel(Dialog)
self.label_2.setObjectName(u"label_2")
self.label_2.setGeometry(QRect(200, 290, 72, 15))
self.pushButton = QPushButton(Dialog) # 登录
self.pushButton.setObjectName(u"pushButton")
self.pushButton.setGeometry(QRect(200, 350, 211, 28))
self.pushButton.clicked.connect(self.login)
self.retranslateUi(Dialog)
QMetaObject.connectSlotsByName(Dialog)
# setupUi
def retranslateUi(self, Dialog):
Dialog.setWindowTitle(QCoreApplication.translate("Dialog", u"Dialog", None))
self.textBrowser.setHtml(QCoreApplication.translate("Dialog", u"<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.0//EN\" \"http://www.w3.org/TR/REC-html40/strict.dtd\">\n"
"<html><head><meta name=\"qrichtext\" content=\"1\" /><style type=\"text/css\">\n"
"p, li { white-space: pre-wrap; }\n"
"</style></head><body style=\" font-family:'SimSun'; font-size:9pt; font-weight:400; font-style:normal;\">\n"
"<p style=\" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;\"><span style=\" font-size:28pt; font-weight:600;\">\u6b22\u8fce\u4f7f\u7528 XXX \u7cfb\u7edf</span></p></body></html>", None))
self.label.setText(QCoreApplication.translate("Dialog", u"\u7528\u6237\u540d", None))
self.label_2.setText(QCoreApplication.translate("Dialog", u"\u5bc6\u7801", None))
self.pushButton.setText(QCoreApplication.translate("Dialog", u"\u767b\u5f55", None))
# retranslateUi
def login(self): # 验证登录
cursor = self.connect_database()
sql = "select id from user where username = (%s) and password = (%s)"
username = self.lineEdit.text() # 获取用户名
# print(username)
password = self.lineEdit_2.text() # 获取密码
res = cursor.execute(sql, (username, password))
# print(cursor.fetchall()[0].get("id"))
if res == 0: # 账号密码错误
from login_err import err_window
self.window = err_window()
self.window.show()
# time.sleep(2)
# self.window.close()
else:
id = cursor.fetchall()[0].get("id")
if id != 1:
print("登陆成功！！！！！")
sql = "insert into log(username, action) values(%s, 'login')"
cursor.execute(sql, (username,))
from PUserWindow import user_window
self.window = user_window()
self.window.show()
else:
print("登陆成功！！！！！")
sql = "insert into log(username, action) values(%s, 'login')"
cursor.execute(sql, (username,))
from PAdminWindow import admin_window
self.window = admin_window()
self.window.show()
def connect_database(self): # 连接数据库
import pymysql
conn = pymysql.connect(
host="127.0.0.1",
port=3306,
user="root",
password="", # 数据库密码
database="classDesign",
charset="utf8", # 编码千万不要加 -
autocommit = True
) # 连接数据库
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 产生一个游标对象（cmd 的 光标）,帮助执行命令
return cursor
class login_window(QDialog, Login): # 打开登录页面
def __init__(self):
super().__init__()
self.setupUi(self)
log_err.py
from PyQt5.QtGui import *
from PyQt5.QtCore import *
from PyQt5.QtWidgets import * # 导入PyQt5部件
from PyQt5.QtWidgets import QMainWindow, QApplication,QLabel,QTableWidgetItem,QPushButton,QLineEdit,QGridLayout,QWidget,QTableWidget
class Ui_Dialog(object):
def setupUi(self, Dialog):
if not Dialog.objectName():
Dialog.setObjectName(u"Dialog")
Dialog.resize(400, 300)
self.textBrowser = QTextBrowser(Dialog)
self.textBrowser.setObjectName(u"textBrowser")
self.textBrowser.setGeometry(QRect(80, 120, 256, 51))
self.textBrowser.repaint()
self.retranslateUi(Dialog)
QMetaObject.connectSlotsByName(Dialog)
# setupUi
def retranslateUi(self, Dialog):
Dialog.setWindowTitle(QCoreApplication.translate("Dialog", u"Dialog", None))
self.textBrowser.setHtml(QCoreApplication.translate("Dialog", u"<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.0//EN\" \"http://www.w3.org/TR/REC-html40/strict.dtd\">\n"
"<html><head><meta name=\"qrichtext\" content=\"1\" /><style type=\"text/css\">\n"
"p, li { white-space: pre-wrap; }\n"
"</style></head><body style=\" font-family:'SimSun'; font-size:9pt; font-weight:400; font-style:normal;\">\n"
"<p style=\" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;\"><span style=\" font-size:20pt; font-weight:600;\">\u8d26\u53f7\u6216\u5bc6\u7801\u9519\u8bef</span></p></body></html>", None))
# retranslateUi
class err_window(QDialog, Ui_Dialog): # 登录失败页面
def __init__(self):
super().__init__()
self.setupUi(self)
PReg.py
from PyQt5.QtGui import *
from PyQt5.QtCore import *
from PyQt5.QtWidgets import * # 导入PyQt5部件
from PyQt5.QtWidgets import QMainWindow, QApplication,QLabel,QTableWidgetItem,QPushButton,QLineEdit,QGridLayout,QWidget,QTableWidget
class Ui_Dialog(object):
def setupUi(self, Dialog):
if not Dialog.objectName():
Dialog.setObjectName(u"Dialog")
Dialog.resize(647, 450)
self.username = QLineEdit(Dialog)
self.username.setObjectName(u"username")
self.username.setGeometry(QRect(280, 220, 121, 31))
self.textBrowser = QTextBrowser(Dialog)
self.textBrowser.setObjectName(u"textBrowser")
self.textBrowser.setGeometry(QRect(100, 70, 441, 71))
self.password = QLineEdit(Dialog)
self.password.setObjectName(u"password")
self.password.setGeometry(QRect(280, 280, 121, 31))
self.password.setEchoMode(QLineEdit.Password)
self.label = QLabel(Dialog)
self.label.setObjectName(u"label")
self.label.setGeometry(QRect(200, 230, 72, 15))
self.label_2 = QLabel(Dialog)
self.label_2.setObjectName(u"label_2")
self.label_2.setGeometry(QRect(200, 290, 72, 15))
self.pushButton = QPushButton(Dialog)
self.pushButton.setObjectName(u"pushButton")
self.pushButton.setGeometry(QRect(200, 350, 211, 28))
self.pushButton.clicked.connect(self.reg)
self.retranslateUi(Dialog)
QMetaObject.connectSlotsByName(Dialog)
# setupUi
def retranslateUi(self, Dialog):
Dialog.setWindowTitle(QCoreApplication.translate("Dialog", u"Dialog", None))
self.textBrowser.setHtml(QCoreApplication.translate("Dialog", u"<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.0//EN\" \"http://www.w3.org/TR/REC-html40/strict.dtd\">\n"
"<html><head><meta name=\"qrichtext\" content=\"1\" /><style type=\"text/css\">\n"
"p, li { white-space: pre-wrap; }\n"
"</style></head><body style=\" font-family:'SimSun'; font-size:9pt; font-weight:400; font-style:normal;\">\n"
"<p style=\" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;\"><span style=\" font-size:28pt; font-weight:600;\">\u6b22\u8fce\u4f7f\u7528 XXX \u7cfb\u7edf</span></p></body></html>", None))
self.label.setText(QCoreApplication.translate("Dialog", u"\u7528\u6237\u540d", None))
self.label_2.setText(QCoreApplication.translate("Dialog", u"\u5bc6\u7801", None))
self.pushButton.setText(QCoreApplication.translate("Dialog", u"\u6ce8\u518c", None))
# retranslateUi
def connect_database(self): # 连接数据库
import pymysql
conn = pymysql.connect(
host="127.0.0.1",
port=3306,
user="root",
password="",
database="classDesign",
charset="utf8", # 编码千万不要加 -
autocommit=True
) # 连接数据库
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 产生一个游标对象（cmd 的 光标）,帮助执行命令
return cursor
def reg(self):
username = self.username.text()
password = self.password.text()
# print(username, password)
cursor = self.connect_database()
sql = "select id from user where username = '%s' " % username
res = cursor.execute(sql)
if res != 0:
from reg_err import err_window
self.window = err_window()
self.window.show()
else:
sql = "insert into user(username, password) values(%s, %s)"
cursor.execute(sql, (username, password))
print("注册成功！！！！")
from PLogin import login_window
self.window = login_window()
self.window.show()
class reg_window(QDialog, Ui_Dialog): # 打开注册页面
def __init__(self):
super().__init__()
self.setupUi(self)
reg_err.py
from PyQt5.QtGui import *
from PyQt5.QtCore import *
from PyQt5.QtWidgets import * # 导入PyQt5部件
from PyQt5.QtWidgets import QMainWindow, QApplication,QLabel,QTableWidgetItem,QPushButton,QLineEdit,QGridLayout,QWidget,QTableWidget
class Ui_Dialog(object):
def setupUi(self, Dialog):
if not Dialog.objectName():
Dialog.setObjectName(u"Dialog")
Dialog.resize(400, 300)
self.textBrowser = QTextBrowser(Dialog)
self.textBrowser.setObjectName(u"textBrowser")
self.textBrowser.setGeometry(QRect(80, 120, 241, 51))
self.retranslateUi(Dialog)
QMetaObject.connectSlotsByName(Dialog)
# setupUi
def retranslateUi(self, Dialog):
Dialog.setWindowTitle(QCoreApplication.translate("Dialog", u"Dialog", None))
self.textBrowser.setHtml(QCoreApplication.translate("Dialog", u"<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.0//EN\" \"http://www.w3.org/TR/REC-html40/strict.dtd\">\n"
"<html><head><meta name=\"qrichtext\" content=\"1\" /><style type=\"text/css\">\n"
"p, li { white-space: pre-wrap; }\n"
"</style></head><body style=\" font-family:'SimSun'; font-size:9pt; font-weight:400; font-style:normal;\">\n"
"<p style=\" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;\"><span style=\" font-size:20pt; font-weight:600;\">\u7528\u6237\u540d\u5df2\u5b58\u5728</span></p></body></html>", None))
# retranslateUi
class err_window(QDialog, Ui_Dialog): # 登录失败页面
def __init__(self):
super().__init__()
self.setupUi(self)
PAdminWindow.py
from PyQt5 import QtCore
from PyQt5.QtGui import *
from PyQt5.QtCore import *
from PyQt5.QtWidgets import * # 导入PyQt5部件
from PyQt5.QtWidgets import QMainWindow, QApplication,QLabel,QTableWidgetItem,QPushButton,QLineEdit,QGridLayout,QWidget,QTableWidget
class Ui_Dialog(object):
def setupUi(self, Dialog):
if not Dialog.objectName():
Dialog.setObjectName(u"Dialog")
Dialog.resize(735, 581)
self.tableWidget = QTableWidget(Dialog)
self.tableWidget.setObjectName(u"tableWidget")
self.tableWidget.setGeometry(QRect(70, 30, 521, 501))
self.load()
self.pushButton_4 = QPushButton(Dialog) # 删除
self.pushButton_4.setObjectName(u"pushButton_4")
self.pushButton_4.setGeometry(QRect(630, 410, 93, 28))
self.pushButton_4.clicked.connect(self.delete)
self.pushButton_5 = QPushButton(Dialog) # 添加
self.pushButton_5.setObjectName(u"pushButton_5")
self.pushButton_5.setGeometry(QRect(630, 360, 93, 28))
self.pushButton_5.clicked.connect(self.add)
self.pushButton_6 = QPushButton(Dialog) # 查询
self.pushButton_6.setObjectName(u"pushButton_6")
self.pushButton_6.setGeometry(QRect(630, 310, 93, 28))
self.pushButton_6.clicked.connect(self.search)
self.pushButton_7 = QPushButton(Dialog) # 查看日志
self.pushButton_7.setObjectName(u"pushButton_7")
self.pushButton_7.setGeometry(QRect(630, 460, 93, 28))
self.pushButton_7.clicked.connect(self.log)
self.retranslateUi(Dialog)
QMetaObject.connectSlotsByName(Dialog)
# setupUi
def retranslateUi(self, Dialog):
Dialog.setWindowTitle(QCoreApplication.translate("Dialog", u"Dialog", None))
self.pushButton_4.setText(QCoreApplication.translate("Dialog", u"\u5220\u9664\u4fe1\u606f", None))
self.pushButton_5.setText(QCoreApplication.translate("Dialog", u"\u6dfb\u52a0\u4fe1\u606f", None))
self.pushButton_6.setText(QCoreApplication.translate("Dialog", u"\u67e5\u8be2\u4fe1\u606f", None))
self.pushButton_7.setText(QCoreApplication.translate("Dialog", u"\u67e5\u770b\u65e5\u5fd7", None))
# retranslateUi
def connect_database(self): # 连接数据库
import pymysql
conn = pymysql.connect(
host="127.0.0.1",
port=3306,
user="root",
password="",
database="classDesign",
charset="utf8", # 编码千万不要加 -
autocommit=True
) # 连接数据库
cursor = conn.cursor() # 产生一个游标对象（cmd 的 光标）,帮助执行命令
return cursor
def load(self):
cursor = self.connect_database()
sql = """
SELECT
emp.`name`,
dep.`name`,
dep.des,
emp_detail.addr,
emp_detail.email,
emp_detail.time,
project.`name`
FROM
emp
INNER JOIN dep ON emp.dep_id = dep.id
INNER JOIN emp2pro ON emp2pro.emp_id = emp.id
INNER JOIN emp_detail ON emp.detail_id = emp_detail.id
INNER JOIN project ON emp2pro.pro_id = project.id
"""
cursor.execute(sql)
rows = cursor.fetchall()
row = cursor.rowcount # 取得记录个数，用于设置表格的行数
# print(rows[0].keys())
col = len(rows[0]) # 取得字段数，用于设置表格的列数
self.tableWidget.setRowCount(row)
self.tableWidget.setColumnCount(col)
# print(rows)
for i in range(row):
for j in range(col):
temp_data = rows[i][j] # 临时记录，不能直接插入表格
data = QTableWidgetItem(str(temp_data)) # 转换后可插入表格
# data = data.setGeometry(QRect(70, 30, 661, 501))
self.tableWidget.setItem(i, j, data)
def log(self):
from PLogWindow import log_window
self.window = log_window()
self.window.show()
def search(self):
from PSearch import search_window
self.window = search_window()
self.window.show()
def add(self):
from PAdd import add_window
self.window = add_window()
self.window.show()
def delete(self):
from PDelete import delete_window
self.window = delete_window()
self.window.show()
class admin_window(QDialog, Ui_Dialog): # 打开管理员登录后的页面
def __init__(self):
super().__init__()
self.setupUi(self)
PUserWindow.py
from PyQt5.QtGui import *
from PyQt5.QtCore import *
from PyQt5.QtWidgets import * # 导入PyQt5部件
from PyQt5.QtWidgets import QMainWindow, QApplication,QLabel,QTableWidgetItem,QPushButton,QLineEdit,QGridLayout,QWidget,QTableWidget
class Ui_Dialog(object):
def setupUi(self, Dialog):
if not Dialog.objectName():
Dialog.setObjectName(u"Dialog")
Dialog.resize(735, 581)
self.tableWidget = QTableWidget(Dialog)
self.tableWidget.setObjectName(u"tableWidget")
self.tableWidget.setGeometry(QRect(70, 30, 521, 501))
self.load()
self.pushButton_4 = QPushButton(Dialog) # 删除
self.pushButton_4.setObjectName(u"pushButton_4")
self.pushButton_4.setGeometry(QRect(630, 410, 93, 28))
self.pushButton_4.clicked.connect(self.delete)
self.pushButton_5 = QPushButton(Dialog) # 添加
self.pushButton_5.setObjectName(u"pushButton_5")
self.pushButton_5.setGeometry(QRect(630, 360, 93, 28))
self.pushButton_5.clicked.connect(self.add)
self.pushButton_6 = QPushButton(Dialog) # 查询
self.pushButton_6.setObjectName(u"pushButton_6")
self.pushButton_6.setGeometry(QRect(630, 310, 93, 28))
self.pushButton_6.clicked.connect(self.search)
self.retranslateUi(Dialog)
QMetaObject.connectSlotsByName(Dialog)
# setupUi
def retranslateUi(self, Dialog):
Dialog.setWindowTitle(QCoreApplication.translate("Dialog", u"Dialog", None))
self.pushButton_4.setText(QCoreApplication.translate("Dialog", u"\u5220\u9664\u4fe1\u606f", None))
self.pushButton_5.setText(QCoreApplication.translate("Dialog", u"\u6dfb\u52a0\u4fe1\u606f", None))
self.pushButton_6.setText(QCoreApplication.translate("Dialog", u"\u67e5\u8be2\u4fe1\u606f", None))
# retranslateUi
def connect_database(self): # 连接数据库
import pymysql
conn = pymysql.connect(
host="127.0.0.1",
port=3306,
user="root",
password="",
database="classDesign",
charset="utf8", # 编码千万不要加 -
autocommit=True
) # 连接数据库
cursor = conn.cursor() # 产生一个游标对象（cmd 的 光标）,帮助执行命令
return cursor
def load(self):
cursor = self.connect_database()
sql = """
SELECT
emp.`name`,
dep.`name`,
dep.des,
emp_detail.addr,
emp_detail.email,
emp_detail.time,
project.`name`
FROM
emp
INNER JOIN dep ON emp.dep_id = dep.id
INNER JOIN emp2pro ON emp2pro.emp_id = emp.id
INNER JOIN emp_detail ON emp.detail_id = emp_detail.id
INNER JOIN project ON emp2pro.pro_id = project.id
"""
cursor.execute(sql)
rows = cursor.fetchall()
row = cursor.rowcount # 取得记录个数，用于设置表格的行数
# print(rows[0].keys())
col = len(rows[0]) # 取得字段数，用于设置表格的列数
self.tableWidget.setRowCount(row)
self.tableWidget.setColumnCount(col)
# print(rows)
for i in range(row):
for j in range(col):
temp_data = rows[i][j] # 临时记录，不能直接插入表格
data = QTableWidgetItem(str(temp_data)) # 转换后可插入表格
# data = data.setGeometry(QRect(70, 30, 661, 501))
self.tableWidget.setItem(i, j, data)
def search(self):
from PSearch import search_window
self.window = search_window()
self.window.show()
def add(self):
from PAdd import add_window
self.window = add_window()
self.window.show()
def delete(self):
from PDelete import delete_window
self.window = delete_window()
self.window.show()
class user_window(QDialog, Ui_Dialog): # 打开普通用户登录后的页面
def __init__(self):
super().__init__()
self.setupUi(self)
PSearch.py
from PyQt5 import QtCore
from PyQt5.QtGui import *
from PyQt5.QtCore import *
from PyQt5.QtWidgets import * # 导入PyQt5部件
from PyQt5.QtWidgets import QMainWindow, QApplication,QLabel,QTableWidgetItem,QPushButton,QLineEdit,QGridLayout,QWidget,QTableWidget
class Ui_Dialog(object):
def setupUi(self, Dialog):
if not Dialog.objectName():
Dialog.setObjectName(u"Dialog")
Dialog.resize(741, 740)
self.lineEdit = QLineEdit(Dialog)
self.lineEdit.setObjectName(u"lineEdit")
self.lineEdit.setGeometry(QRect(290, 80, 281, 81))
self.label = QLabel(Dialog)
self.label.setObjectName(u"label")
self.label.setGeometry(QRect(130, 110, 141, 21))
self.pushButton = QPushButton(Dialog) # 职工姓名查询
self.pushButton.setObjectName(u"pushButton")
self.pushButton.setGeometry(QRect(130, 250, 111, 41))
self.pushButton.clicked.connect(self.name)
self.pushButton_2 = QPushButton(Dialog) # 部门查询
self.pushButton_2.setObjectName(u"pushButton_2")
self.pushButton_2.setGeometry(QRect(330, 250, 111, 41))
self.pushButton_2.clicked.connect(self.dep)
self.pushButton_3 = QPushButton(Dialog) # 项目查询
self.pushButton_3.setObjectName(u"pushButton_3")
self.pushButton_3.setGeometry(QRect(520, 250, 111, 41))
self.pushButton_3.clicked.connect(self.project)
self.tableWidget = QTableWidget(Dialog)
self.tableWidget.setObjectName(u"tableView")
self.tableWidget.setGeometry(QRect(130, 320, 511, 371))
self.retranslateUi(Dialog)
QMetaObject.connectSlotsByName(Dialog)
# setupUi
def retranslateUi(self, Dialog):
Dialog.setWindowTitle(QCoreApplication.translate("Dialog", u"Dialog", None))
self.label.setText(
QCoreApplication.translate("Dialog", u"\u8bf7\u8f93\u5165\u67e5\u8be2\u7684\u5185\u5bb9", None))
self.pushButton.setText(QCoreApplication.translate("Dialog", u"\u804c\u5de5\u59d3\u540d\u67e5\u8be2", None))
self.pushButton_2.setText(QCoreApplication.translate("Dialog", u"\u90e8\u95e8\u67e5\u8be2", None))
self.pushButton_3.setText(QCoreApplication.translate("Dialog", u"\u9879\u76ee\u67e5\u8be2", None))
# retranslateUi
def connect_database(self): # 连接数据库
import pymysql
conn = pymysql.connect(
host="127.0.0.1",
port=3306,
user="root",
password="",
database="classDesign",
charset="utf8", # 编码千万不要加 -
autocommit=True
) # 连接数据库
cursor = conn.cursor() # 产生一个游标对象（cmd 的 光标）,帮助执行命令
return cursor
def name(self):
cursor = self.connect_database()
name = self.lineEdit.text()
# print(name)
sql = """
SELECT
emp.`name`,
emp_detail.addr,
emp_detail.email,
emp_detail.time,
dep.`name`,
dep.des
FROM
emp
INNER JOIN emp_detail ON emp.detail_id = emp_detail.id
INNER JOIN dep ON emp.dep_id = dep.id
WHERE
emp.name = %s
"""
res = cursor.execute(sql, (name,))
if res != 0:
rows = cursor.fetchall()
row = cursor.rowcount # 取得记录个数，用于设置表格的行数
# print(rows[0].keys())
col = len(rows[0]) # 取得字段数，用于设置表格的列数
self.tableWidget.setRowCount(row)
self.tableWidget.setColumnCount(col)
# print(rows)
for i in range(row):
for j in range(col):
temp_data = rows[i][j] # 临时记录，不能直接插入表格
data = QTableWidgetItem(str(temp_data)) # 转换后可插入表格
# data = data.setGeometry(QRect(70, 30, 661, 501))
self.tableWidget.setItem(i, j, data)
self.log("query", name)
else:
self.log("query", name)
# print("没有该条信息")
from search_err import err_window
self.window = err_window()
self.window.show()
def log(self, action, name):
sql = "insert into log(action, msg) values(%s, %s)"
cursor = self.connect_database()
cursor.execute(sql, (action, name))
def dep(self):
cursor = self.connect_database()
name = self.lineEdit.text()
sql = """
SELECT
dep.`name`,
emp.`name`,
emp_detail.addr,
emp_detail.email,
emp_detail.time
FROM
dep
INNER JOIN emp ON emp.dep_id = dep.id
INNER JOIN emp_detail ON emp.detail_id = emp_detail.id
WHERE
dep.name = %s
"""
res = cursor.execute(sql, (name,))
if res != 0:
rows = cursor.fetchall()
row = cursor.rowcount # 取得记录个数，用于设置表格的行数
# print(rows[0].keys())
col = len(rows[0]) # 取得字段数，用于设置表格的列数
self.tableWidget.setRowCount(row)
self.tableWidget.setColumnCount(col)
# print(rows)
for i in range(row):
for j in range(col):
temp_data = rows[i][j] # 临时记录，不能直接插入表格
data = QTableWidgetItem(str(temp_data)) # 转换后可插入表格
# data = data.setGeometry(QRect(70, 30, 661, 501))
self.tableWidget.setItem(i, j, data)
self.log("query", name)
else:
self.log("query", name)
# print("没有该条信息")
from search_err import err_window
self.window = err_window()
self.window.show()
def project(self):
cursor = self.connect_database()
name = self.lineEdit.text()
sql = """
SELECT
project.`name`,
emp.`name`,
emp_detail.addr,
emp_detail.email,
emp_detail.time,
dep.`name`,
dep.des
FROM
project
INNER JOIN emp2pro ON emp2pro.pro_id = project.id
INNER JOIN emp ON emp2pro.emp_id = emp.id
INNER JOIN emp_detail ON emp.detail_id = emp_detail.id
INNER JOIN dep ON emp.dep_id = dep.id
WHERE
project.name = %s
"""
res = cursor.execute(sql, (name,))
if res != 0:
rows = cursor.fetchall()
row = cursor.rowcount # 取得记录个数，用于设置表格的行数
# print(rows[0].keys())
col = len(rows[0]) # 取得字段数，用于设置表格的列数
self.tableWidget.setRowCount(row)
self.tableWidget.setColumnCount(col)
# print(rows)
for i in range(row):
for j in range(col):
temp_data = rows[i][j] # 临时记录，不能直接插入表格
data = QTableWidgetItem(str(temp_data)) # 转换后可插入表格
# data = data.setGeometry(QRect(70, 30, 661, 501))
self.tableWidget.setItem(i, j, data)
self.log("query", name)
else:
self.log("query", name)
# print("没有该条信息")
from search_err import err_window
self.window = err_window()
self.window.show()
class search_window(QDialog, Ui_Dialog): # 打开查询的页面
def __init__(self):
super().__init__()
self.setupUi(self)
search_err.py
from PyQt5 import QtCore
from PyQt5.QtGui import *
from PyQt5.QtCore import *
from PyQt5.QtWidgets import * # 导入PyQt5部件
from PyQt5.QtWidgets import QMainWindow, QApplication,QLabel,QTableWidgetItem,QPushButton,QLineEdit,QGridLayout,QWidget,QTableWidget
class Ui_Dialog(object):
def setupUi(self, Dialog):
if not Dialog.objectName():
Dialog.setObjectName(u"Dialog")
Dialog.resize(400, 301)
self.textBrowser = QTextBrowser(Dialog)
self.textBrowser.setObjectName(u"textBrowser")
self.textBrowser.setGeometry(QRect(60, 120, 301, 61))
self.retranslateUi(Dialog)
QMetaObject.connectSlotsByName(Dialog)
# setupUi
def retranslateUi(self, Dialog):
Dialog.setWindowTitle(QCoreApplication.translate("Dialog", u"Dialog", None))
self.textBrowser.setHtml(QCoreApplication.translate("Dialog", u"<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.0//EN\" \"http://www.w3.org/TR/REC-html40/strict.dtd\">\n"
"<html><head><meta name=\"qrichtext\" content=\"1\" /><style type=\"text/css\">\n"
"p, li { white-space: pre-wrap; }\n"
"</style></head><body style=\" font-family:'SimSun'; font-size:9pt; font-weight:400; font-style:normal;\">\n"
"<p style=\" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;\"><span style=\" font-size:20pt; font-weight:600;\">\u6ca1\u6709\u8be5\u6761\u4fe1\u606f\u5b58\u5728</span></p></body></html>", None))
# retranslateUi
class err_window(QDialog, Ui_Dialog): # 打开查询失败的页面
def __init__(self):
super().__init__()
self.setupUi(self)
PAdd.py
from PyQt5 import QtCore
from PyQt5.QtGui import *
from PyQt5.QtCore import *
from PyQt5.QtWidgets import * # 导入PyQt5部件
from PyQt5.QtWidgets import QMainWindow, QApplication,QLabel,QTableWidgetItem,QPushButton,QLineEdit,QGridLayout,QWidget,QTableWidget
class Ui_Dialog(object):
def setupUi(self, Dialog):
if not Dialog.objectName():
Dialog.setObjectName(u"Dialog")
Dialog.resize(880, 459)
self.lineEdit = QLineEdit(Dialog) # 姓名
self.lineEdit.setObjectName(u"lineEdit")
self.lineEdit.setGeometry(QRect(220, 80, 211, 41))
self.label = QLabel(Dialog)
self.label.setObjectName(u"label")
self.label.setGeometry(QRect(110, 90, 141, 21))
self.lineEdit_2 = QLineEdit(Dialog) # add
self.lineEdit_2.setObjectName(u"lineEdit_2")
self.lineEdit_2.setGeometry(QRect(220, 150, 211, 41))
self.label_2 = QLabel(Dialog)
self.label_2.setObjectName(u"label_2")
self.label_2.setGeometry(QRect(110, 160, 141, 21))
self.label_3 = QLabel(Dialog)
self.label_3.setObjectName(u"label_3")
self.label_3.setGeometry(QRect(110, 230, 141, 21))
self.lineEdit_3 = QLineEdit(Dialog) # email
self.lineEdit_3.setObjectName(u"lineEdit_3")
self.lineEdit_3.setGeometry(QRect(220, 220, 211, 41))
self.label_4 = QLabel(Dialog)
self.label_4.setObjectName(u"label_4")
self.label_4.setGeometry(QRect(490, 120, 141, 21))
self.lineEdit_4 = QLineEdit(Dialog) # 部门姓名
self.lineEdit_4.setObjectName(u"lineEdit_4")
self.lineEdit_4.setGeometry(QRect(600, 110, 211, 41))
self.label_5 = QLabel(Dialog)
self.label_5.setObjectName(u"label_5")
self.label_5.setGeometry(QRect(490, 190, 141, 21))
self.lineEdit_5 = QLineEdit(Dialog) # 项目
self.lineEdit_5.setObjectName(u"lineEdit_5")
self.lineEdit_5.setGeometry(QRect(600, 180, 211, 41))
self.pushButton = QPushButton(Dialog)
self.pushButton.setObjectName(u"pushButton")
self.pushButton.setGeometry(QRect(380, 330, 171, 61))
self.pushButton.clicked.connect(self.submit)
self.retranslateUi(Dialog)
QMetaObject.connectSlotsByName(Dialog)
# setupUi
def retranslateUi(self, Dialog):
Dialog.setWindowTitle(QCoreApplication.translate("Dialog", u"Dialog", None))
self.lineEdit.setText("")
self.label.setText(QCoreApplication.translate("Dialog", u"\u804c\u5458\u59d3\u540d", None))
self.lineEdit_2.setText("")
self.label_2.setText(QCoreApplication.translate("Dialog", u"\u804c\u5458\u5730\u5740", None))
self.label_3.setText(QCoreApplication.translate("Dialog", u"\u804c\u5458\u90ae\u7bb1", None))
self.lineEdit_3.setText("")
self.label_4.setText(QCoreApplication.translate("Dialog", u"\u6240\u5c5e\u90e8\u95e8", None))
self.lineEdit_4.setText("")
self.label_5.setText(QCoreApplication.translate("Dialog", u"\u52a0\u5165\u9879\u76ee", None))
self.lineEdit_5.setText("")
self.pushButton.setText(QCoreApplication.translate("Dialog", u"\u6dfb\u52a0\u6570\u636e", None))
# retranslateUi
def connect_database(self): # 连接数据库
import pymysql
conn = pymysql.connect(
host="127.0.0.1",
port=3306,
user="root",
password="",
database="classDesign",
charset="utf8", # 编码千万不要加 -
autocommit=True
) # 连接数据库
cursor = conn.cursor() # 产生一个游标对象（cmd 的 光标）,帮助执行命令
return cursor
def submit(self):
emp_name = self.lineEdit.text()
emp_add = self.lineEdit_2.text()
emp_email = self.lineEdit_3.text()
# 部门
dep = self.lineEdit_4.text()
dep_id = None
# 项目
pro = self.lineEdit_5.text()
pro_id = None
# print(emp_name,emp_add,emp_email, dep, pro)
cursor = self.connect_database()
# 判断部门是否存在，如果不存在，添加并且返回 部门的 id 值
if len(dep) != 0:
sql_dep = "select id from dep where name = %s"
res = cursor.execute(sql_dep, dep)
if res == 0: # 不存在该部门
sql_insert_dep = "insert into dep(name) values ('%s')" % (dep)
cursor.execute(sql_insert_dep)
cursor.execute(sql_dep, dep)
dep_id = cursor.fetchall()[0][0]
# print(dep_id)
self.log("add", dep)
# 判断 项目 是否存在，如果不存在，添加并且返回 项目的 id 值
if len(pro) != 0:
sql_pro = "select id from project where name = %s"
res = cursor.execute(sql_pro, pro)
if res == 0: # 不存在该项目
sql_insert_pro = "insert into project(name) values ('%s')" % (pro)
cursor.execute(sql_insert_pro)
cursor.execute(sql_pro, pro)
pro_id = cursor.fetchall()[0][0]
# print(pro_id)
self.log("add", pro)
if len(emp_name) != 0:
sql_detail = "insert into emp_detail(addr, email) values('%s', '%s')" % (emp_add, emp_email)
cursor.execute(sql_detail)
sql_detail_id = "select id from emp_detail where addr = '%s'" % emp_add
cursor.execute(sql_detail_id)
detail_id = cursor.fetchall()[0][0]
# print(detail_id)
sql_emp = "insert into emp(name, dep_id, detail_id) values('%s', %d, %d)" % (emp_name, dep_id, detail_id)
# print(sql_emp)
cursor.execute(sql_emp)
if pro_id is not None:
sql_emp_id = "select id from emp where name = '%s'" % emp_name
cursor.execute(sql_emp_id)
emp_id = cursor.fetchall()[0][0]
# print(emp_id, pro_id)
# print(emp_id)
sql_emp2project = "insert into emp2pro(emp_id, pro_id) values(%d, %d)" % (emp_id, pro_id)
# print(sql_emp2project)
cursor.execute(sql_emp2project)
self.log("add", emp_name)
from add_success import success_window
self.window = success_window()
self.window.show()
def log(self, action, name):
sql = "insert into log(action, msg) values(%s, %s)"
cursor = self.connect_database()
cursor.execute(sql, (action, name))
class add_window(QDialog, Ui_Dialog): # 添加数据的页面
def __init__(self):
super().__init__()
self.setupUi(self)
add_success.py
from PyQt5.QtGui import *
from PyQt5.QtCore import *
from PyQt5.QtWidgets import * # 导入PyQt5部件
from PyQt5.QtWidgets import QMainWindow, QApplication,QLabel,QTableWidgetItem,QPushButton,QLineEdit,QGridLayout,QWidget,QTableWidget
class Ui_Dialog(object):
def setupUi(self, Dialog):
if not Dialog.objectName():
Dialog.setObjectName(u"Dialog")
Dialog.resize(432, 179)
self.textBrowser = QTextBrowser(Dialog)
self.textBrowser.setObjectName(u"textBrowser")
self.textBrowser.setGeometry(QRect(40, 40, 361, 91))
self.retranslateUi(Dialog)
QMetaObject.connectSlotsByName(Dialog)
# setupUi
def retranslateUi(self, Dialog):
Dialog.setWindowTitle(QCoreApplication.translate("Dialog", u"Dialog", None))
self.textBrowser.setHtml(QCoreApplication.translate("Dialog", u"<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.0//EN\" \"http://www.w3.org/TR/REC-html40/strict.dtd\">\n"
"<html><head><meta name=\"qrichtext\" content=\"1\" /><style type=\"text/css\">\n"
"p, li { white-space: pre-wrap; }\n"
"</style></head><body style=\" font-family:'SimSun'; font-size:9pt; font-weight:400; font-style:normal;\">\n"
"<p align=\"center\" style=\" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;\"><span style=\" font-size:20pt; font-weight:600;\">\u4fe1\u606f\u6dfb\u52a0\u6210\u529f\uff01\uff01\uff01</span></p></body></html>", None))
# retranslateUi
class success_window(QDialog, Ui_Dialog): # 添加数据成功的页面
def __init__(self):
super().__init__()
self.setupUi(self)
PDelete.py
from PyQt5 import QtCore
from PyQt5.QtGui import *
from PyQt5.QtCore import *
from PyQt5.QtWidgets import * # 导入PyQt5部件
from PyQt5.QtWidgets import QMainWindow, QApplication,QLabel,QTableWidgetItem,QPushButton,QLineEdit,QGridLayout,QWidget,QTableWidget
class Ui_Dialog(object):
def setupUi(self, Dialog):
if not Dialog.objectName():
Dialog.setObjectName(u"Dialog")
Dialog.resize(741, 322)
self.lineEdit = QLineEdit(Dialog)
self.lineEdit.setObjectName(u"lineEdit")
self.lineEdit.setGeometry(QRect(290, 80, 281, 81))
self.label = QLabel(Dialog)
self.label.setObjectName(u"label")
self.label.setGeometry(QRect(130, 110, 141, 21))
self.pushButton = QPushButton(Dialog) # 按姓名删除
self.pushButton.setObjectName(u"pushButton")
self.pushButton.setGeometry(QRect(130, 250, 111, 41))
self.pushButton.clicked.connect(self.name)
self.pushButton_2 = QPushButton(Dialog) # 按部门删除
self.pushButton_2.setObjectName(u"pushButton_2")
self.pushButton_2.setGeometry(QRect(330, 250, 111, 41))
self.pushButton_2.clicked.connect(self.dep)
self.pushButton_3 = QPushButton(Dialog) # 按项目删除
self.pushButton_3.setObjectName(u"pushButton_3")
self.pushButton_3.setGeometry(QRect(520, 250, 111, 41))
self.pushButton_3.clicked.connect(self.pro)
self.retranslateUi(Dialog)
QMetaObject.connectSlotsByName(Dialog)
# setupUi
def retranslateUi(self, Dialog):
Dialog.setWindowTitle(QCoreApplication.translate("Dialog", u"Dialog", None))
self.label.setText(QCoreApplication.translate("Dialog", u"\u8bf7\u8f93\u5165\u5220\u9664\u7684\u5185\u5bb9", None))
self.pushButton.setText(QCoreApplication.translate("Dialog", u"\u804c\u5de5\u59d3\u540d\u5220\u9664", None))
self.pushButton_2.setText(QCoreApplication.translate("Dialog", u"\u90e8\u95e8\u5220\u9664", None))
self.pushButton_3.setText(QCoreApplication.translate("Dialog", u"\u9879\u76ee\u5220\u9664", None))
# retranslateUi
def connect_database(self): # 连接数据库
import pymysql
conn = pymysql.connect(
host="127.0.0.1",
port=3306,
user="root",
password="",
database="classDesign",
charset="utf8", # 编码千万不要加 -
autocommit=True
) # 连接数据库
cursor = conn.cursor() # 产生一个游标对象（cmd 的 光标）,帮助执行命令
return cursor
def name(self):
name = self.lineEdit.text()
sql = "delete from emp where name = '%s'" % name
cursor = self.connect_database()
cursor.execute(sql)
self.log("delete", name)
from delete_success import success_window
self.window = success_window()
self.window.show()
def dep(self):
dep = self.lineEdit.text()
sql_pro_id = "select id from dep where name = %s"
cursor = self.connect_database()
res = cursor.execute(sql_pro_id, dep)
dep_id = cursor.fetchall()[0][0]
sql = "delete from emp where dep_id = %d" % dep_id
sql2 = "delete from dep where name = '%s'" % dep
cursor.execute(sql)
cursor.execute(sql2)
self.log("delete", dep)
from delete_success import success_window
self.window = success_window()
self.window.show()
def pro(self):
pro = self.lineEdit.text()
sql_pro_id = "select id from project where name = %s"
cursor = self.connect_database()
res = cursor.execute(sql_pro_id, pro)
pro_id = cursor.fetchall()[0][0]
sql = "delete from emp2pro where pro_id = %d" % pro_id
cursor.execute(sql)
self.log("delete", pro)
from delete_success import success_window
self.window = success_window()
self.window.show()
def log(self, action, name):
sql = "insert into log(action, msg) values(%s, %s)"
cursor = self.connect_database()
cursor.execute(sql, (action, name))
class delete_window(QDialog, Ui_Dialog): # 打开删除的页面
def __init__(self):
super().__init__()
self.setupUi(self)
delete_success.py
from PyQt5.QtGui import *
from PyQt5.QtCore import *
from PyQt5.QtWidgets import * # 导入PyQt5部件
from PyQt5.QtWidgets import QMainWindow, QApplication,QLabel,QTableWidgetItem,QPushButton,QLineEdit,QGridLayout,QWidget,QTableWidget
class Ui_Dialog(object):
def setupUi(self, Dialog):
if not Dialog.objectName():
Dialog.setObjectName(u"Dialog")
Dialog.resize(431, 179)
self.textBrowser = QTextBrowser(Dialog)
self.textBrowser.setObjectName(u"textBrowser")
self.textBrowser.setGeometry(QRect(40, 40, 361, 91))
self.retranslateUi(Dialog)
QMetaObject.connectSlotsByName(Dialog)
# setupUi
def retranslateUi(self, Dialog):
Dialog.setWindowTitle(QCoreApplication.translate("Dialog", u"Dialog", None))
self.textBrowser.setHtml(QCoreApplication.translate("Dialog", u"<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.0//EN\" \"http://www.w3.org/TR/REC-html40/strict.dtd\">\n"
"<html><head><meta name=\"qrichtext\" content=\"1\" /><style type=\"text/css\">\n"
"p, li { white-space: pre-wrap; }\n"
"</style></head><body style=\" font-family:'SimSun'; font-size:9pt; font-weight:400; font-style:normal;\">\n"
"<p align=\"center\" style=\" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;\"><span style=\" font-size:20pt; font-weight:600;\">\u4fe1\u606f\u5220\u9664\u6210\u529f\uff01\uff01\uff01</span></p></body></html>", None))
# retranslateUi
class success_window(QDialog, Ui_Dialog): # 删除成功的页面
def __init__(self):
super().__init__()
self.setupUi(self)
PLogWindow.py
from PyQt5 import QtCore
from PyQt5.QtGui import *
from PyQt5.QtCore import *
from PyQt5.QtWidgets import * # 导入PyQt5部件
from PyQt5.QtWidgets import QMainWindow, QApplication,QLabel,QTableWidgetItem,QPushButton,QLineEdit,QGridLayout,QWidget,QTableWidget
class Ui_Dialog(object):
def setupUi(self, Dialog):
if not Dialog.objectName():
Dialog.setObjectName(u"Dialog")
Dialog.resize(741, 561)
self.tableWidget = QTableWidget(Dialog)
self.tableWidget.setObjectName(u"tableWidget")
self.tableWidget.setGeometry(QRect(70, 30, 571, 501))
self.load()
self.retranslateUi(Dialog)
QMetaObject.connectSlotsByName(Dialog)
# setupUi
def retranslateUi(self, Dialog):
Dialog.setWindowTitle(QCoreApplication.translate("Dialog", u"Dialog", None))
# retranslateUi
def connect_database(self): # 连接数据库
import pymysql
conn = pymysql.connect(
host="127.0.0.1",
port=3306,
user="root",
password="",
database="classDesign",
charset="utf8", # 编码千万不要加 -
autocommit=True
) # 连接数据库
cursor = conn.cursor() # 产生一个游标对象（cmd 的 光标）,帮助执行命令
return cursor
def load(self):
cursor = self.connect_database()
cursor.execute('select * from log')
rows = cursor.fetchall()
row = cursor.rowcount # 取得记录个数，用于设置表格的行数
# print(rows[0].keys())
col = len(rows[0]) # 取得字段数，用于设置表格的列数
self.tableWidget.setRowCount(row)
self.tableWidget.setColumnCount(col)
# print(rows)
for i in range(row):
for j in range(col):
temp_data = rows[i][j] # 临时记录，不能直接插入表格
data = QTableWidgetItem(str(temp_data)) # 转换后可插入表格
# data = data.setGeometry(QRect(70, 30, 661, 501))
self.tableWidget.setItem(i, j, data)
class log_window(QDialog, Ui_Dialog): # 打开日志的页面
def __init__(self):
super().__init__()
self.setupUi(self)
一些小 Bug
- 用户登录成功后会展示该系统有的信息，但如果职员没有记录在部门或是项目中就不会显示，比如说初始数据中的盖亚就不会显示
- 按理来说管理员应该有注销普通用户或是有修改普通用户密码的权力，但由于时间关系并没有实现
- 查询信息时，虽然可以按照职员名称、项目名称、部门名称进行查询，但如果该部门（或是项目）下没有任何职员，那么该系统会显示“该条信息不存在”
- 日志表会出现 username 为 Null 的情况，这时候操作的用户应该是上面最近登录的用户（因为是单线程），出现这样的情况是因为进行 窗口信号传参的时候逻辑上并没有理清楚，加上对 pyqt5 信号操作还不太明白，就选择这样一种取巧、折中的方式实现该功能。
除了上述已列出的问题之外，应该还存在其他的不足和 Bug，如果大家有发现，请告知。
代码演示
其他
该项目相关代码已上传至 github 留作纪念 SmallDataBaseProject
