数据库课设
前言
这里两三天帮同学写了一个简单的包含 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()._