超市销售数据库（python与MySQL）
项目介绍
这个小项目是由MySQL数据库作为后端，python的pyqt5来做ui界面的小程序。
功能介绍
首先小项目是一个登陆界面，输入账号密码即可登入操作界面。
操作界面是由一个按钮组和一张表组成的，左侧的其中三个按钮可以刷新对应的供货表，销售表和出货表。另外三个按钮可以实现数据库的插入删除和修改操作（点击以后出现细化的窗口）。
在数据库方面实现了连带删除，连带修改，同步进货时间等功能。
运行前提
这个小程序使用的是innodb的搜索引擎，所以需要运行tomcat的MySQL数据库环境。
另外还需要python的运行环境。
运行时即运行gui_enter.py。
下面是gui_enter.py的代码
import sys, os if hasattr(sys, 'frozen'): os.environ['PATH'] = sys._MEIPASS + ";" + os.environ['PATH'] from PyQt5.QtWidgets import QWidget, QApplication, QLineEdit, QPushButton, QLabel from PyQt5.QtGui import QIcon, QPixmap, QPalette, QBrush import sys import gui class MyEnter(QWidget): def __init__(self): super(MyEnter, self).__init__() self.initUI() def initUI(self): # 设置窗口的剧本属性（窗口名，窗口图标，窗口大小，窗口锁定） self.setWindowTitle('商丘学院超市管理系统-登录') self.setWindowIcon(QIcon('shop.png')) self.resize(500, 308) self.setFixedSize(self.width(), self.height()) # 设置背景图片 window_pale = QPalette() window_pale.setBrush(self.backgroundRole(), QBrush(QPixmap("enter_img.jpg"))) self.setPalette(window_pale) # 添加登录组件 btn = QPushButton('确认', self) btn.move(300, 180) lbl_1 = QLabel('数据库账号', self) lbl_1.move(225, 100) lbl_2 = QLabel('数据库密码', self) lbl_2.move(225, 150) self.my_le_1 = QLineEdit(self) self.my_le_1.move(305, 95) self.my_le_2 = QLineEdit(self) self.my_le_2.move(305, 145) # 设置登录事件 btn.clicked.connect(self.enter_pass) # 显示 self.show() def enter_pass(self): a_str = self.my_le_1.text() b_str = self.my_le_2.text() # if 后的条件循环改为有返回值的mysql登录函数 if a_str == '123' and b_str == '123': self.mw = gui.MyWidget() self.mw.show() self.close() if __name__ == '__main__': app = QApplication(sys.argv) ME = MyEnter() app.exec_()
下面是gui.py的代码
from PyQt5 import QtWidgets, QtGui from PyQt5.QtWidgets import QMessageBox, QHeaderView import gui_table import pymysql from PyQt5.QtGui import QIcon, QPixmap, QPalette, QBrush class MyWidget(QtWidgets.QMainWindow): def __init__(self): super().__init__() self.initUi() self.conn = pymysql.connect('localhost', 'root', 'root', 'supermarket') def initUi(self): # 设置窗口标题 self.setWindowTitle('商丘学院超市管理系统') self.setWindowIcon(QIcon('shop.png')) # 设置窗口大小 self.resize(900, 700) # 创建一个窗口部件 self.widget = QtWidgets.QWidget() # 创建一个网格布局 self.grid_layout = QtWidgets.QGridLayout() # 设置窗口部件的布局为网格布局 self.widget.setLayout(self.grid_layout) # 创建一个按钮组 self.group_box = QtWidgets.QGroupBox('数据库按钮') self.group_box_layout = QtWidgets.QVBoxLayout() self.group_box.setLayout(self.group_box_layout) # 创建一个表格部件 self.table_widget = QtWidgets.QTableView() # 将上述两个部件添加到网格布局中 self.grid_layout.addWidget(self.group_box, 0, 0) self.grid_layout.addWidget(self.table_widget, 0, 1) # 创建按钮组的按钮 self.b_show_gys = QtWidgets.QPushButton("显示(更新)供应商表") self.b_show_wp = QtWidgets.QPushButton("显示(更新)物品表") self.b_show_ch = QtWidgets.QPushButton("显示(更新)出货表") self.b_delete_row = QtWidgets.QPushButton("删除数据") self.b_add_row = QtWidgets.QPushButton("添加数据") self.b_update = QtWidgets.QPushButton('修改数据') self.b_search = QtWidgets.QPushButton('查找数据') # 添加按钮到按钮组中 self.group_box_layout.addWidget(self.b_show_gys) self.group_box_layout.addWidget(self.b_show_wp) self.group_box_layout.addWidget(self.b_show_ch) self.group_box_layout.addWidget(self.b_delete_row) self.group_box_layout.addWidget(self.b_add_row) self.group_box_layout.addWidget(self.b_update) self.group_box_layout.addWidget(self.b_search) # 添加触发事件 self.b_show_wp.clicked.connect(self.show_data) self.b_show_gys.clicked.connect(self.show_data) self.b_show_ch.clicked.connect(self.show_data) self.b_delete_row.clicked.connect(self.del_data) self.b_add_row.clicked.connect(self.add_data) self.b_update.clicked.connect(self.update_data) self.b_search.clicked.connect(self.search_data) # 设置UI界面的核心部件 self.setCentralWidget(self.widget) # 设置窗口背景 window_pale = QPalette() window_pale.setBrush(self.backgroundRole(), QBrush(QPixmap("supermarket.jpg"))) self.setPalette(window_pale) self.table_widget.horizontalHeader().setStretchLastSection(True) self.table_widget.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch) def show_data(self): sender = self.sender() model = QtGui.QStandardItemModel() cursor = self.conn.cursor() if sender.text() == '显示(更新)供应商表': model.setHorizontalHeaderLabels(['供货编号', '供货名称', '联系方式']) sql = cursor.execute('select * from supplier') data = cursor.fetchall() m = 0 for row in data: n = 0 for item in row: model.setItem(m, n, QtGui.QStandardItem(str(item))) n += 1 m += 1 self.table_widget.setModel(model) elif sender.text() == '显示(更新)物品表': model.setHorizontalHeaderLabels(['物品编号', '物品名称', '供货编号', '存货数量', '进价']) sql = cursor.execute('select * from goods') data = cursor.fetchall() m = 0 for row in data: n = 0 for item in row: model.setItem(m, n, QtGui.QStandardItem(str(item))) n += 1 m += 1 self.table_widget.setModel(model) elif sender.text() == '显示(更新)出货表': model.setHorizontalHeaderLabels(['销售编号', '物品编号', '出货数量', '售价', '出售日期']) sql = cursor.execute('select * from sale') data = cursor.fetchall() m = 0 for row in data: n = 0 for item in row: model.setItem(m, n, QtGui.QStandardItem(str(item))) n += 1 m += 1 self.table_widget.setModel(model) cursor.close() def update_data(self): sender = self.sender() self.mtg_1 = gui_table.MyTableGui(sender, self.conn) self.mtg_1.show() def add_data(self): sender = self.sender() self.mtg_2 = gui_table.MyTableGui(sender, self.conn) self.mtg_2.show() def del_data(self): sender = self.sender() self.mtg_3 = gui_table.MyTableGui(sender, self.conn) self.mtg_3.show() def search_data(self): sender = self.sender() self.mtg_4 = gui_table.MyTableGui(sender, self.conn) self.mtg_4.show() def closeEvent(self, event): mb = QMessageBox.question(self, '系统提示', '是否关闭窗口', QMessageBox.Yes | QMessageBox.No, QMessageBox.Yes) if mb == QMessageBox.Yes: event.accept() else: event.ignore()
最后是gui_table.py的代码
from PyQt5.QtWidgets import QPushButton, QWidget, QHBoxLayout, QLabel, QLineEdit, QVBoxLayout, QMessageBox, QHeaderView from PyQt5.QtGui import QIcon, QStandardItemModel, QStandardItem from PyQt5 import QtWidgets class MyTableGui(QWidget): def __init__(self, sender, conn): super(MyTableGui, self).__init__() global sender_1, con sender_1 = sender con = conn self.initUI() def initUI(self): self.setWindowIcon(QIcon('shop.png')) self.setWindowTitle('选择%s的超市表单' % sender_1.text()) self.resize(500, 100) h_layout = QHBoxLayout(self) btn_gys = QPushButton('供应商表单') btn_wp = QPushButton('物品表单') btn_ch = QPushButton('出货表单') h_layout.addWidget(btn_gys) h_layout.addWidget(btn_wp) h_layout.addWidget(btn_ch) btn_gys.clicked.connect(self.table_gys) btn_wp.clicked.connect(self.table_wp) btn_ch.clicked.connect(self.table_ch) def table_gys(self): self.deltable = Table_gys() self.deltable.show() self.close() def table_wp(self): self.deltable = Table_wp() self.deltable.show() self.close() def table_ch(self): self.deltable = Table_ch() self.deltable.show() self.close() class Table_gys(QWidget): def __init__(self): super().__init__() self.initUI() def initUI(self): self.setWindowIcon(QIcon('shop.png')) if sender_1.text() == '删除数据': self.setWindowTitle('删除-供应商表单') self.resize(400, 130) self.setFixedSize(self.width(), self.height()) lbl_1 = QLabel('供货编号', self) lbl_1.move(70, 20) self.le_1 = QLineEdit(self) self.le_1.move(150, 15) btn = QPushButton('确定', self) btn.move(145, 70) btn.clicked.connect(self.del_data) elif sender_1.text() == '添加数据': self.setWindowTitle('添加-供应商表单') self.resize(400, 300) self.setFixedSize(self.width(), self.height()) lbl_1 = QLabel('供货编号(必填)', self) lbl_1.move(30, 20) lbl_2 = QLabel('供货名称', self) lbl_2.move(70, 70) lbl_3 = QLabel('联系方式', self) lbl_3.move(70, 120) self.le_1 = QLineEdit(self) self.le_1.move(150, 15) self.le_2 = QLineEdit(self) self.le_2.move(150, 65) self.le_3 = QLineEdit(self) self.le_3.move(150, 115) btn = QPushButton('确定', self) btn.move(145, 250) btn.clicked.connect(self.add_data) elif sender_1.text() == '修改数据': self.setWindowTitle('修改-供应商表单') self.resize(400, 300) self.setFixedSize(self.width(), self.height()) lbl_1 = QLabel('要修改的供货编号', self) lbl_1.move(20, 20) lbl_2 = QLabel('供货编号', self) lbl_2.move(70, 70) lbl_3 = QLabel('供货名称', self) lbl_3.move(70, 120) lbl_4 = QLabel('联系方式', self) lbl_4.move(70, 170) self.le_1 = QLineEdit(self) self.le_1.move(150, 15) self.le_2 = QLineEdit(self) self.le_2.move(150, 65) self.le_3 = QLineEdit(self) self.le_3.move(150, 115) self.le_4 = QLineEdit(self) self.le_4.move(150, 165) btn = QPushButton('确定', self) btn.move(145, 250) btn.clicked.connect(self.update_data) elif sender_1.text() == '查找数据': self.setWindowTitle('查找-供应商表单') self.resize(500, 300) self.setFixedSize(self.width(), self.height()) v_layout = QVBoxLayout(self) h_layout = QHBoxLayout() v_layout.addLayout(h_layout) lbl_1 = QLabel('供货编号') self.le_1 = QLineEdit() h_layout.addWidget(lbl_1) h_layout.addWidget(self.le_1) btn = QPushButton('确定', self) v_layout.addWidget(btn) self.table_widget = QtWidgets.QTableView() v_layout.addWidget(self.table_widget) btn.clicked.connect(self.search_data) self.table_widget.horizontalHeader().setStretchLastSection(True) self.table_widget.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch) def del_data(self): try: cursor = con.cursor() cursor.execute('''delete from supplier where supplierid='%s';''' % self.le_1.text()) con.commit() cursor.close() info = QMessageBox.information(self, '系统提示', '执行成功!\n点击Ok返回主界面,点击Cancel返回上一级。', QMessageBox.Ok | QMessageBox.Cancel, QMessageBox.Ok) if info == QMessageBox.Ok: self.close() except: con.rollback() QMessageBox.information(self, '系统提示', '发生错误,无法正常执行该操作,请确认信息无误后再试。', QMessageBox.Ok, QMessageBox.Ok) def add_data(self): try: cursor = con.cursor() a_str = '' if self.le_1.text() != '': a_str = a_str + '\'' + self.le_1.text() + '\'' + ',' else: a_str = a_str + 'null' + ',' if self.le_2.text() != '': a_str = a_str + '\'' + self.le_2.text() + '\'' + ',' else: a_str = a_str + 'null' + ',' if self.le_3.text() != '': a_str = a_str + '\'' + self.le_3.text() + '\'' else: a_str = a_str + 'null' cursor.execute('''insert into supplier values(%s);''' % a_str) con.commit() cursor.close() info = QMessageBox.information(self, '系统提示', '执行成功!\n点击Ok返回主界面,点击Cancel返回上一级。', QMessageBox.Ok | QMessageBox.Cancel, QMessageBox.Ok) if info == QMessageBox.Ok: self.close() except: con.rollback() QMessageBox.information(self, '系统提示', '发生错误,无法正常执行该操作,请确认信息无误后再试。', QMessageBox.Ok, QMessageBox.Ok) def update_data(self): try: cursor = con.cursor() a_str = '' if self.le_2.text() != '': a_str = a_str + 'supplierid=' + '\'' + self.le_2.text() + '\'' if self.le_3.text() != '' or self.le_4.text() != '': a_str = a_str + ',' if self.le_3.text() != '': a_str = a_str + 'suppliername=' + '\'' + self.le_3.text() + '\'' if self.le_4.text() != '': a_str = a_str + ',' if self.le_4.text() != '': a_str = a_str + 'contact=' + '\'' + self.le_4.text() + '\'' cursor.execute('''update supplier set %s where supplierid='%s';''' % (a_str, self.le_1.text())) con.commit() cursor.close() info = QMessageBox.information(self, '系统提示', '执行成功!\n点击Ok返回主界面,点击Cancel返回上一级。', QMessageBox.Ok | QMessageBox.Cancel, QMessageBox.Ok) if info == QMessageBox.Ok: self.close() except: con.rollback() QMessageBox.information(self, '系统提示', '发生错误,无法正常执行该操作,请确认信息无误后再试。', QMessageBox.Ok, QMessageBox.Ok) def search_data(self): try: cursor = con.cursor() cursor.execute('''select * from supplier where supplierid='%s';''' % self.le_1.text()) con.commit() data = cursor.fetchall() model = QStandardItemModel() model.setHorizontalHeaderLabels(['供货编号', '供货名称', '联系方式']) cursor.close() m = 0 for row in data: n = 0 for item in row: model.setItem(m, n, QStandardItem(str(item))) n += 1 m += 1 self.table_widget.setModel(model) except: con.rollback() QMessageBox.information(self, '系统提示', '发生错误,无法正常执行该操作,请确认信息无误后再试。', QMessageBox.Ok, QMessageBox.Ok) class Table_wp(QWidget): def __init__(self): super().__init__() self.initUI() def initUI(self): self.setWindowIcon(QIcon('shop.png')) if sender_1.text() == '删除数据': self.setWindowTitle('删除-物品表单') self.resize(400, 130) self.setFixedSize(self.width(), self.height()) lbl_1 = QLabel('物品编号', self) lbl_1.move(70, 20) self.le_1 = QLineEdit(self) self.le_1.move(150, 15) btn = QPushButton('确定', self) btn.move(145, 70) btn.clicked.connect(self.del_data) elif sender_1.text() == '添加数据': self.setWindowTitle('添加-物品表单') self.resize(400, 300) self.setFixedSize(self.width(), self.height()) lbl_1 = QLabel('物品编号(必填)', self) lbl_1.move(30, 20) lbl_2 = QLabel('物品名称', self) lbl_2.move(70, 70) lbl_3 = QLabel('供货编号(已有编号)', self) lbl_3.move(10, 120) lbl_4 = QLabel('存货数量', self) lbl_4.move(70, 170) lbl_5 = QLabel('进价', self) lbl_5.move(70, 220) self.le_1 = QLineEdit(self) self.le_1.move(150, 15) self.le_2 = QLineEdit(self) self.le_2.move(150, 65) self.le_3 = QLineEdit(self) self.le_3.move(150, 115) self.le_4 = QLineEdit(self) self.le_4.move(150, 165) self.le_5 = QLineEdit(self) self.le_5.move(150, 215) btn = QPushButton('确定', self) btn.move(145, 250) btn.clicked.connect(self.add_data) elif sender_1.text() == '修改数据': self.setWindowTitle('修改-物品表单') self.resize(400, 300) self.setFixedSize(self.width(), self.height()) lbl_1 = QLabel('要修改的物品编号', self) lbl_1.move(20, 20) lbl_2 = QLabel('物品编号', self) lbl_2.move(70, 60) lbl_3 = QLabel('物品名称', self) lbl_3.move(70, 100) lbl_4 = QLabel('供货编号(已有编号)', self) lbl_4.move(10, 140) lbl_5 = QLabel('库存数量', self) lbl_5.move(70, 180) lbl_6 = QLabel('进价', self) lbl_6.move(70, 220) self.le_1 = QLineEdit(self) self.le_1.move(150, 15) self.le_2 = QLineEdit(self) self.le_2.move(150, 55) self.le_3 = QLineEdit(self) self.le_3.move(150, 95) self.le_4 = QLineEdit(self) self.le_4.move(150, 135) self.le_5 = QLineEdit(self) self.le_5.move(150, 175) self.le_6 = QLineEdit(self) self.le_6.move(150, 215) btn = QPushButton('确定', self) btn.move(145, 250) btn.clicked.connect(self.update_data) elif sender_1.text() == '查找数据': self.setWindowTitle('查找-物品表单') self.resize(700, 300) self.setFixedSize(self.width(), self.height()) v_layout = QVBoxLayout(self) h_layout = QHBoxLayout() v_layout.addLayout(h_layout) lbl_1 = QLabel('物品编号') self.le_1 = QLineEdit() h_layout.addWidget(lbl_1) h_layout.addWidget(self.le_1) btn = QPushButton('确定', self) v_layout.addWidget(btn) self.table_widget = QtWidgets.QTableView() v_layout.addWidget(self.table_widget) btn.clicked.connect(self.search_data) self.table_widget.horizontalHeader().setStretchLastSection(True) self.table_widget.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch) def del_data(self): try: cursor = con.cursor() cursor.execute('''delete from goods where goodsid='%s';''' % self.le_1.text()) con.commit() cursor.close() info = QMessageBox.information(self, '系统提示', '执行成功!\n点击Ok返回主界面,点击Cancel返回上一级。', QMessageBox.Ok | QMessageBox.Cancel, QMessageBox.Ok) if info == QMessageBox.Ok: self.close() except: con.rollback() QMessageBox.information(self, '系统提示', '发生错误,无法正常执行该操作,请确认信息无误后再试。', QMessageBox.Ok, QMessageBox.Ok) def add_data(self): try: cursor = con.cursor() a_str = '' if self.le_1.text() != '': a_str = a_str + '\'' + self.le_1.text() + '\'' + ',' else: a_str = a_str + 'null' + ',' if self.le_2.text() != '': a_str = a_str + '\'' + self.le_2.text() + '\'' + ',' else: a_str = a_str + 'null' + ',' if self.le_3.text() != '': a_str = a_str + '\'' + self.le_3.text() + '\'' + ',' else: a_str = a_str + 'null' + ',' if self.le_4.text() != '': a_str = a_str + self.le_4.text() + ',' else: a_str = a_str + 'null' + ',' if self.le_5.text() != '': a_str = a_str + self.le_5.text() else: a_str = a_str + 'null' cursor.execute('''insert into goods values(%s);''' % a_str) con.commit() cursor.close() info = QMessageBox.information(self, '系统提示', '执行成功!\n点击Ok返回主界面,点击Cancel返回上一级。', QMessageBox.Ok | QMessageBox.Cancel, QMessageBox.Ok) if info == QMessageBox.Ok: self.close() except: con.rollback() QMessageBox.information(self, '系统提示', '发生错误,无法正常执行该操作,请确认信息无误后再试。', QMessageBox.Ok, QMessageBox.Ok) def update_data(self): try: cursor = con.cursor() a_str = '' if self.le_2.text() != '': a_str = a_str + 'goodsid=' + '\'' + self.le_2.text() + '\'' if self.le_3.text() != '' or self.le_4.text() != '' or self.le_5.text() != '' or self.le_6.text() != '': a_str = a_str + ',' if self.le_3.text() != '': a_str = a_str + 'goodsname=' + '\'' + self.le_3.text() + '\'' if self.le_4.text() != '' or self.le_5.text() != '' or self.le_6.text() != '': a_str = a_str + ',' if self.le_4.text() != '': a_str = a_str + 'supplierid=' + '\'' + self.le_4.text() + '\'' if self.le_5.text() != '' or self.le_6.text() != '': a_str = a_str + ',' if self.le_5.text() != '': a_str = a_str + 'amount=' + self.le_5.text() if self.le_6.text() != '': a_str = a_str + ',' if self.le_6.text() != '': a_str = a_str + 'supplierprice=' + self.le_6.text() cursor.execute('''update goods set %s where goodsid='%s';''' % (a_str, self.le_1.text())) con.commit() cursor.close() info = QMessageBox.information(self, '系统提示', '执行成功!\n点击Ok返回主界面,点击Cancel返回上一级。', QMessageBox.Ok | QMessageBox.Cancel, QMessageBox.Ok) if info == QMessageBox.Ok: self.close() except: con.rollback() QMessageBox.information(self, '系统提示', '发生错误,无法正常执行该操作,请确认信息无误后再试。', QMessageBox.Ok, QMessageBox.Ok) def search_data(self): try: cursor = con.cursor() cursor.execute('''select * from goods where goodsid='%s';''' % self.le_1.text()) con.commit() data = cursor.fetchall() model = QStandardItemModel() model.setHorizontalHeaderLabels(['物品编号', '物品名称', '供货编号', '存货数量', '进价']) cursor.close() m = 0 for row in data: n = 0 for item in row: model.setItem(m, n, QStandardItem(str(item))) n += 1 m += 1 self.table_widget.setModel(model) except: con.rollback() QMessageBox.information(self, '系统提示', '发生错误,无法正常执行该操作,请确认信息无误后再试。', QMessageBox.Ok, QMessageBox.Ok) class Table_ch(QWidget): def __init__(self): super().__init__() self.initUI() def initUI(self): self.setWindowIcon(QIcon('shop.png')) if sender_1.text() == '删除数据': self.setWindowTitle('删除-出货表单') self.resize(400, 140) self.setFixedSize(self.width(), self.height()) lbl_1 = QLabel('物品编号', self) lbl_1.move(70, 20) lbl_2 = QLabel('销售编号', self) lbl_2.move(70, 60) self.le_1 = QLineEdit(self) self.le_1.move(150, 15) self.le_2 = QLineEdit(self) self.le_2.move(150, 55) btn = QPushButton('确定', self) btn.move(145, 100) btn.clicked.connect(self.del_data) elif sender_1.text() == '添加数据': self.setWindowTitle('添加-出货表单') self.resize(400, 300) self.setFixedSize(self.width(), self.height()) lbl_1 = QLabel('销售编号', self) lbl_1.move(70, 20) lbl_2 = QLabel('物品编号(已有编号)', self) lbl_2.move(10, 70) lbl_3 = QLabel('出货数量', self) lbl_3.move(70, 120) lbl_4 = QLabel('售价', self) lbl_4.move(70, 170) self.le_1 = QLineEdit(self) self.le_1.move(150, 15) self.le_2 = QLineEdit(self) self.le_2.move(150, 65) self.le_3 = QLineEdit(self) self.le_3.move(150, 115) self.le_4 = QLineEdit(self) self.le_4.move(150, 165) btn = QPushButton('确定', self) btn.move(145, 250) btn.clicked.connect(self.add_data) elif sender_1.text() == '修改数据': self.setWindowTitle('修改-出货表单') self.resize(400, 300) self.setFixedSize(self.width(), self.height()) lbl_1 = QLabel('要修改的物品编号', self) lbl_1.move(20, 20) lbl_6 = QLabel('要修改的销售编号', self) lbl_6.move(20, 60) lbl_2 = QLabel('销售编号', self) lbl_2.move(70, 100) lbl_3 = QLabel('物品编号(已有编号)', self) lbl_3.move(10, 140) lbl_4 = QLabel('售出数量', self) lbl_4.move(70, 180) lbl_5 = QLabel('售价', self) lbl_5.move(70, 220) self.le_1 = QLineEdit(self) self.le_1.move(150, 15) self.le_6 = QLineEdit(self) self.le_6.move(150, 55) self.le_2 = QLineEdit(self) self.le_2.move(150, 95) self.le_3 = QLineEdit(self) self.le_3.move(150, 135) self.le_4 = QLineEdit(self) self.le_4.move(150, 175) self.le_5 = QLineEdit(self) self.le_5.move(150, 215) btn = QPushButton('确定', self) btn.move(145, 250) btn.clicked.connect(self.update_data) elif sender_1.text() == '查找数据': self.setWindowTitle('查找-出货表单') self.resize(600, 300) self.setFixedSize(self.width(), self.height()) v_layout = QVBoxLayout(self) h_layout = QHBoxLayout() v_layout.addLayout(h_layout) lbl_1 = QLabel('物品编号') self.le_1 = QLineEdit() h_layout.addWidget(lbl_1) h_layout.addWidget(self.le_1) btn = QPushButton('确定', self) v_layout.addWidget(btn) self.table_widget = QtWidgets.QTableView() v_layout.addWidget(self.table_widget) btn.clicked.connect(self.search_data) self.table_widget.horizontalHeader().setStretchLastSection(True) self.table_widget.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch) def del_data(self): try: cursor = con.cursor() cursor.execute('''delete from sale where goodsid='%s' and saleid='%s';''' % (self.le_1.text(), self.le_2.text())) con.commit() cursor.close() info = QMessageBox.information(self, '系统提示', '执行成功!\n点击Ok返回主界面,点击Cancel返回上一级。', QMessageBox.Ok | QMessageBox.Cancel, QMessageBox.Ok) if info == QMessageBox.Ok: self.close() except: con.rollback() QMessageBox.information(self, '系统提示', '发生错误,无法正常执行该操作,请确认信息无误后再试。', QMessageBox.Ok, QMessageBox.Ok) def add_data(self): try: cursor = con.cursor() a_str = '' if self.le_1.text() != '': a_str = a_str + '\'' + self.le_1.text() + '\'' + ',' else: a_str = a_str + 'null' + ',' if self.le_2.text() != '': a_str = a_str + '\'' + self.le_2.text() + '\'' + ',' else: a_str = a_str + 'null' + ',' if self.le_3.text() != '': a_str = a_str + self.le_3.text() + ',' else: a_str = a_str + 'null' + ',' if self.le_4.text() != '': a_str = a_str + self.le_4.text() else: a_str = a_str + 'null' cursor.execute('''insert into sale values(%s,now());''' % a_str) con.commit() cursor.close() info = QMessageBox.information(self, '系统提示', '执行成功!\n点击Ok返回主界面,点击Cancel返回上一级。', QMessageBox.Ok | QMessageBox.Cancel, QMessageBox.Ok) if info == QMessageBox.Ok: self.close() except: con.rollback() QMessageBox.information(self, '系统提示', '发生错误,无法正常执行该操作,请确认信息无误后再试。', QMessageBox.Ok, QMessageBox.Ok) def update_data(self): try: cursor = con.cursor() a_str = '' if self.le_2.text() != '': a_str = a_str + 'saleid=' + '\'' + self.le_2.text() + '\'' if self.le_3.text() != '' or self.le_4.text() != '' or self.le_5.text() != '': a_str = a_str + ',' if self.le_3.text() != '': a_str = a_str + 'goodsid=' + '\'' + self.le_3.text() + '\'' if self.le_4.text() != '' or self.le_5.text() != '': a_str = a_str + ',' if self.le_4.text() != '': a_str = a_str + 'saleamount=' + self.le_4.text() if self.le_5.text() != '': a_str = a_str + ',' if self.le_5.text() != '': a_str = a_str + 'saleprice=' + self.le_5.text() cursor.execute('''update sale set %s where goodsid='%s' and saleid='%s';''' % (a_str, self.le_1.text(), self.le_6.text())) con.commit() cursor.close() info = QMessageBox.information(self, '系统提示', '执行成功!\n点击Ok返回主界面,点击Cancel返回上一级。', QMessageBox.Ok | QMessageBox.Cancel, QMessageBox.Ok) if info == QMessageBox.Ok: self.close() except: con.rollback() QMessageBox.information(self, '系统提示', '发生错误,无法正常执行该操作,请确认信息无误后再试。', QMessageBox.Ok, QMessageBox.Ok) def search_data(self): try: cursor = con.cursor() cursor.execute('''select * from sale where goodsid='%s';''' % self.le_1.text()) con.commit() data = cursor.fetchall() model = QStandardItemModel() model.setHorizontalHeaderLabels(['销售编号', '物品编号', '出货数量', '售价', '出售日期']) cursor.close() m = 0 for row in data: n = 0 for item in row: model.setItem(m, n, QStandardItem(str(item))) n += 1 m += 1 self.table_widget.setModel(model) except: con.rollback() QMessageBox.information(self, '系统提示', '发生错误,无法正常执行该操作,请确认信息无误后再试。', QMessageBox.Ok, QMessageBox.Ok)
附加的MySQL代码
drop database supermarket; set names gbk; create database supermarket; use supermarket; create table supplier (supplierid varchar(10) primary key, suppliername varchar(10), contact varchar(50))engine=innodb; create table goods (goodsid varchar(10) primary key, goodsname varchar(10), supplierid varchar(10) not null, amount int, supplierprice int, foreign key(supplierid) references supplier(supplierid) on delete cascade on update cascade)engine=innodb; create table sale (saleid varchar(10), goodsid varchar(10) not null, saleamount int, saleprice int, saletime datetime, foreign key(goodsid) references goods(goodsid) on delete cascade on update cascade)engine=innodb; delimiter ## create trigger sale_insert after insert on sale for each row begin declare n int; if new.saleamount>0 then set n=new.saleamount; update goods set amount=amount-n where goodsid=new.goodsid; end if; end;## delimiter ; delimiter ## create trigger sale_update after update on sale for each row begin declare n,o int; if new.saleamount>0 then set n=new.saleamount,o=old.saleamount; update goods set amount=amount+o-n where goodsid=new.goodsid; end if; end;## delimiter ; insert into supplier values('A001','水果','12456'); insert into supplier values('A002','水果','12456'); insert into supplier values('A003','达利','10000'); insert into supplier values('A004','达利好吃点','10001'); insert into supplier values('A005','达利园瑞士卷','10002'); insert into supplier values('A006','乐山有限公司','10003'); insert into supplier values('A007','鼎联商贸有限公司','10004'); insert into supplier values('A008','鼎联商贸有限公司','10005'); insert into supplier values('A009','宽贸商贸有限公司','10006'); insert into supplier values('A010','大风商贸有限公司','10008'); insert into supplier values('A011','全华贸易有限公司','10009'); insert into supplier values('A012','明乐有限公司','10010'); insert into supplier values('A013','百商贸有限公司','10011'); insert into supplier values('A014','丰商贸有限公司','10012'); insert into supplier values('A015','丰商贸有限公司','10013'); insert into supplier values('A016','荣腾商贸有限公司','10014'); insert into supplier values('A017','乐山有限公司','10015'); insert into supplier values('A018','宏大商贸有限公司','10016'); insert into supplier values('A019','苏卫食品有限公司','10017'); insert into supplier values('A020','广博','10018'); insert into goods values('G001','苹果','A001',100,3); insert into goods values('G002','香蕉','A002',200,3); insert into goods values('G003','达利法式软面包香奶味','A003',600,2); insert into goods values('G004','达利好吃点杏仁酥','A004',450,10); insert into goods values('G005','达利园瑞士卷','A005',1600,2); insert into goods values('G006','达能牛奶香脆饼干','A006',430,4); insert into goods values('G007','达利园法式香奶面包','A007',840,3); insert into goods values('G008','大宝SOD蜜','A008',600,40); insert into goods values('G009','大好大香酥花生','A009',680,10); insert into goods values('G010','得力山楂片','A010',1000,3); insert into goods values('G011','德芙黑巧克力','A011',650,8); insert into goods values('G012','雕牌洗洁精','A012',500,10); insert into goods values('G013','飞利浦剃须刀','A013',650,50); insert into goods values('G014','德芙榛子巧克力','A014',700,10); insert into goods values('G015','芬达橙味汽水','A015',600,4); insert into goods values('G016','光明牛奶','A016',500,5); insert into goods values('G017','光阳松花皮蛋','A017',1000,2); insert into goods values('G018','果然多CC果卷','A018',400,5); insert into goods values('G019','果然多特浓奶卷','A019',500,10); insert into goods values('G020','广博削笔机','A020',110,20); insert into sale values('S001','G001',10,4,now()); insert into sale values('S002','G002',20,6,now()); insert into sale values('S002','G003',25,3,now()); insert into sale values('S003','G002',5,6,now()); insert into sale values('S003','G005',20,4,now()); insert into sale values('S003','G004',5,11,now()); insert into sale values('S004','G006',20,5,now()); insert into sale values('S005','G007',15,4,now()); insert into sale values('S006','G008',2,50,now()); insert into sale values('S007','G009',30,11,now()); insert into sale values('S008','G010',20,4,now()); insert into sale values('S009','G012',3,11,now()); insert into sale values('S010','G011',1,9,now()); insert into sale values('S011','G013',2,60,now()); insert into sale values('S012','G014',10,11,now()); insert into sale values('S013','G015',2,5,now()); insert into sale values('S014','G016',2,6,now()); insert into sale values('S015','G017',10,3,now()); insert into sale values('S016','G018',20,6,now()); insert into sale values('S017','G019',30,11,now()); insert into sale values('S018','G020',2,22,now()); insert into sale values('S019','G011',2,9,now()); insert into sale values('S020','G016',2,6,now()); insert into sale values('S021','G001',10,4,now()); insert into sale values('S022','G002',20,6,now()); insert into sale values('S023','G003',25,3,now()); insert into sale values('S024','G002',5,6,now()); insert into sale values('S025','G005',20,4,now()); insert into sale values('S026','G004',5,11,now()); insert into sale values('S027','G006',20,5,now()); insert into sale values('S028','G007',15,4,now()); insert into sale values('S029','G010',20,4,now()); insert into sale values('S030','G012',3,11,now());
最后顺便说一下，直接复制粘贴代码可能有些细节需要修改。
