实验室药品管理系统
Python写了个实验室药品管理系统，简陋但能用
功能介绍
这段代码实现了一个药品管理系统的基本功能，包括：
-
连接数据库：建立与SQLite数据库文件的连接。
-
创建数据库表格：创建名为"purchase"和"usage"的数据库表格。
-
主窗口：显示应用程序的主窗口，包括菜单栏和状态栏。
-
添加购买记录：通过菜单栏选项添加购买药品的记录。
-
查询购买记录：通过菜单栏选项查询购买药品的记录，并在对话框中显示查询结果。
-
添加使用记录：通过菜单栏选项添加使用药品的记录。
-
查询使用记录：通过菜单栏选项查询使用药品的记录，并在对话框中显示查询结果。
-
导出购买记录：通过菜单栏选项将购买记录导出到Excel文件。
-
导出使用记录：通过菜单栏选项将使用记录导出到Excel文件。
-
计算购买价格总和：通过菜单栏选项计算购买记录中所有药品价格的总和。
-
显示购买药品记录表格：在主窗口中显示购买药品记录的表格。
-
显示使用药品记录表格：在主窗口中显示使用药品记录的表格。
上述功能通过使用PyQt6库的各种类和方法实现，包括窗口类、菜单类、对话框类、表格类等。数据库操作使用了QSqlQuery类来执行SQL查询和更新操作，表格的显示和更新使用了QTableWidget类。
代码
import sys
from PyQt6.QtCore import Qt, QDateTime, QUrl
from PyQt6.QtSql import QSqlDatabase, QSqlQuery
from PyQt6.QtWidgets import QApplication, QMainWindow, QWidget, QVBoxLayout, QLabel, QLineEdit, QPushButton, QMessageBox, QTableWidget, QTableWidgetItem, QDateTimeEdit, QHeaderView, QDialog, QInputDialog, QFileDialog, QComboBox
from PyQt6.QtGui import QIcon, QDesktopServices
from openpyxl import Workbook
def createConnection():
try:
db = QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName("medicine.db")
if not db.open():
QMessageBox.critical(None, "无法打开数据库", "无法建立数据库连接！",
QMessageBox.StandardButton.Ok)
return False
return True
except Exception as e:
QMessageBox.critical(None, "错误", str(e),
QMessageBox.StandardButton.Ok)
return False
######################################### 创建数据库#####################################################
def createPurchaseTable():
query = QSqlQuery()
query.exec(
"""
CREATE TABLE IF NOT EXISTS purchase (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
purchase_time TEXT NOT NULL,
purchaser TEXT NOT NULL,
specification TEXT NOT NULL,
unit TEXT NOT NULL,
price REAL NOT NULL
)
"""
)
def createUsageTable():
query = QSqlQuery()
query.exec(
"""
CREATE TABLE IF NOT EXISTS usage (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
usage_time TEXT NOT NULL,
user TEXT NOT NULL
)
"""
)
####################################### 主窗口######################################################
class MainWindow(QMainWindow):
def __init__(self):
super().__init__()
self.setWindowTitle("梅老师课题组药品管理系统")
self.resize(800, 600)
# 设置应用程序图标
app_icon = QIcon("icon-lab.png")
self.setWindowIcon(app_icon)
self.createStatusBar()
self.purchaseWindow = PurchaseWindow()
self.usageWindow = UsageWindow()
menuBar = self.menuBar()
# 购买药品功能
purchaseMenu = menuBar.addMenu("购买药品")
purchaseAction = purchaseMenu.addAction("添加购买记录")
purchaseAction.triggered.connect(self.showPurchaseWindow)
purchaseQueryAction = purchaseMenu.addAction("查询购买记录")
purchaseQueryAction.triggered.connect(self.queryPurchaseRecords)
# 使用药品功能
usageMenu = menuBar.addMenu("使用药品")
usageAction = usageMenu.addAction("添加使用记录")
usageAction.triggered.connect(self.showUsageWindow)
usageQueryAction = usageMenu.addAction("查询使用记录")
usageQueryAction.triggered.connect(self.queryUsageRecords)
# 其他功能
otherMenu = menuBar.addMenu("其他功能")
exportPurchaseAction = otherMenu.addAction("导出购买记录")
exportPurchaseAction.triggered.connect(self.exportPurchaseRecords)
exportUsageAction = otherMenu.addAction("导出使用记录")
exportUsageAction.triggered.connect(self.exportUsageRecords)
calculatePriceSumAction = otherMenu.addAction("计算购买价格总和")
calculatePriceSumAction.triggered.connect(
self.calculatePurchasePriceSum)
##################################### 购买药品列表#########################################################
self.purchaseTable = QTableWidget()
self.purchaseTable.setColumnCount(7)
self.purchaseTable.setHorizontalHeaderLabels(
["ID", "药品名称", "购买时间", "购买人", "规格", "单位", "价格"])
self.purchaseTable.setEditTriggers(
QTableWidget.EditTrigger.NoEditTriggers)
self.purchaseTable.setSelectionBehavior(
QTableWidget.SelectionBehavior.SelectRows)
self.purchaseTable.setSelectionMode(
QTableWidget.SelectionMode.SingleSelection)
##################################### 使用药品列表#########################################################
self.usageTable = QTableWidget()
self.usageTable.setColumnCount(4)
self.usageTable.setHorizontalHeaderLabels(
["ID", "药品名称", "使用时间", "使用人"])
self.usageTable.setEditTriggers(
QTableWidget.EditTrigger.NoEditTriggers)
self.usageTable.setSelectionBehavior(
QTableWidget.SelectionBehavior.SelectRows)
self.usageTable.setSelectionMode(
QTableWidget.SelectionMode.SingleSelection)
# 隐藏表格序号
self.purchaseTable.verticalHeader().setVisible(False)
self.usageTable.verticalHeader().setVisible(False)
# 设置表格水平标头属性
self.purchaseTable.horizontalHeader().setSectionResizeMode(
QHeaderView.ResizeMode.Stretch)
self.purchaseTable.horizontalHeader().setDefaultAlignment(
Qt.AlignmentFlag.AlignCenter)
self.usageTable.horizontalHeader().setSectionResizeMode(
QHeaderView.ResizeMode.Stretch)
self.usageTable.horizontalHeader().setDefaultAlignment(Qt.AlignmentFlag.AlignCenter)
layout = QVBoxLayout()
layout.addWidget(QLabel("购买药品记录"))
layout.addWidget(self.purchaseTable)
layout.addWidget(QLabel("使用药品记录"))
layout.addWidget(self.usageTable)
centralWidget = QWidget()
centralWidget.setLayout(layout)
self.setCentralWidget(centralWidget)
self.updatePurchaseTable()
self.updateUsageTable()
############################################# 主窗口样式#############################################
# 设置菜单栏样式
self.menuBar().setStyleSheet("""
QMenuBar {
background-color: #FAF7F0;
color: #000000;
}
QMenuBar::item {
background-color: #FAF7F0;
color: #000000;
border: 1px solid #000000;
padding: 2px;
margin: 1px
}
QMenuBar::item:selected {
background-color: #000000;
color: #ffffff;
border: 1px solid #ffffff;
padding: 2px;
margin: 1px
}
QMenu::item:selected {
background-color: #000000;
color: #ffffff;
}
""")
def showPurchaseWindow(self):
self.purchaseWindow.show()
def showUsageWindow(self):
self.usageWindow.show()
def createStatusBar(self):
status_label = QLabel()
status_label.setText(
'<a href="https://www.wu555.ink"><font color="gray">© IamWu555</font></a>')
status_label.setAlignment(Qt.AlignmentFlag.AlignRight)
status_label.setOpenExternalLinks(True)
status_label.linkActivated.connect(self.openLink)
self.statusBar().addPermanentWidget(status_label)
def openLink(self, link):
QDesktopServices.openUrl(QUrl(link))
########################################### 更新购买药品列表####################################################
def updatePurchaseTable(self):
query = QSqlQuery()
query.exec("SELECT * FROM purchase ORDER BY purchase_time DESC")
self.purchaseTable.clearContents()
self.purchaseTable.setRowCount(0)
row = 0
while query.next():
id = query.value(0)
name = query.value(1)
purchase_time = query.value(2)
purchaser = query.value(3)
specification = query.value(4)
unit = query.value(5)
price = query.value(6)
self.purchaseTable.insertRow(row)
self.purchaseTable.setItem(row, 0, QTableWidgetItem(str(id)))
self.purchaseTable.setItem(row, 1, QTableWidgetItem(name))
self.purchaseTable.setItem(row, 2, QTableWidgetItem(purchase_time))
self.purchaseTable.setItem(row, 3, QTableWidgetItem(purchaser))
self.purchaseTable.setItem(row, 4, QTableWidgetItem(specification))
self.purchaseTable.setItem(row, 5, QTableWidgetItem(unit))
self.purchaseTable.setItem(row, 6, QTableWidgetItem(str(price)))
row += 1
########################################### 更新使用药品列表####################################################
def updateUsageTable(self):
query = QSqlQuery()
query.exec("SELECT * FROM usage ORDER BY usage_time DESC")
self.usageTable.clearContents()
self.usageTable.setRowCount(0)
row = 0
while query.next():
id = query.value(0)
name = query.value(1)
usage_time = query.value(2)
user = query.value(3)
self.usageTable.insertRow(row)
self.usageTable.setItem(row, 0, QTableWidgetItem(str(id)))
self.usageTable.setItem(row, 1, QTableWidgetItem(name))
self.usageTable.setItem(row, 2, QTableWidgetItem(usage_time))
self.usageTable.setItem(row, 3, QTableWidgetItem(user))
row += 1
# 购买记录表格样式
self.purchaseTable.setStyleSheet("""
QTableWidget {
background-color: #fff;
border: none;
}
QTableWidget::item {
border-bottom: 1px solid #eee;
padding: 5px;
}
QTableWidget::item:selected {
background-color: #f4a261;
}
""")
# 使用记录表格样式
self.usageTable.setStyleSheet("""
QTableWidget {
background-color: #fff;
border: none;
}
QTableWidget::item {
border-bottom: 1px solid #eee;
padding: 5px;
}
QTableWidget::item:selected {
background-color: #f4a261;
}
""")
########################################### 查询购买药品列表####################################################
def queryPurchaseRecords(self):
name, ok = QInputDialog.getText(self, "查询购买记录", "药品名称:")
if ok:
query = QSqlQuery()
query.prepare("SELECT * FROM purchase WHERE name=:name")
query.bindValue(":name", name)
if query.exec():
if not query.next():
QMessageBox.information(self, "信息", "没有查询到相关记录！",
QMessageBox.StandardButton.Ok)
return
query.previous()
headers = ['ID', '药品名称', '购买时间', '购买者', '规格', '单位', '价格']
dialog = QueryDialog(query, headers)
dialog.setWindowTitle('查询结果')
dialog.table.horizontalHeader().setSectionResizeMode(
QHeaderView.ResizeMode.Stretch)
dialog.table.verticalHeader().setVisible(False)
row = 0
while query.next():
dialog.table.insertRow(row)
col = 0
for header in headers:
data = query.value(header)
dialog.table.setItem(
row, col, QTableWidgetItem(str(data)))
col += 1
row += 1
query.finish()
dialog.exec()
else:
QMessageBox.critical(self, "错误", "查询购买记录失败！错误信息：" +
query.lastError().text(), QMessageBox.StandardButton.Ok)
########################################### 查询使用药品列表####################################################
def queryUsageRecords(self):
name, ok = QInputDialog.getText(self, "查询使用记录", "药品名称:")
if ok:
query = QSqlQuery()
query.prepare("SELECT * FROM usage WHERE name=:name")
query.bindValue(":name", name)
if query.exec():
if not query.next():
QMessageBox.information(self, "信息", "没有查询到相关记录！",
QMessageBox.StandardButton.Ok)
return
query.previous()
headers = [query.record().fieldName(i)
for i in range(query.record().count())]
headers = ['ID', '药品名称', '使用时间', '使用人']
dialog = QueryDialog(query, headers)
dialog.setWindowTitle('查询结果')
dialog.table.horizontalHeader().setSectionResizeMode(
QHeaderView.ResizeMode.Stretch)
dialog.table.verticalHeader().setVisible(False)
row = 0
while query.next():
dialog.table.insertRow(row)
col = 0
for header in headers:
data = query.value(header)
dialog.table.setItem(
row, col, QTableWidgetItem(str(data)))
col += 1
row += 1
dialog.exec()
query.finish()
else:
QMessageBox.critical(
self, "错误", "查询使用记录失败！", QMessageBox.StandardButton.Ok)
########################################### 导出购买记录#####################################################
def exportPurchaseRecords(self):
# 选择保存导出文件的路径
file_path, _ = QFileDialog.getSaveFileName(
self, "导出购买记录", "", "Excel Files (*.xlsx)")
if file_path:
query = QSqlQuery()
query.exec("SELECT * FROM purchase ORDER BY purchase_time DESC")
workbook = Workbook()
sheet = workbook.active
# 写入表头
headers = ["ID", "药品名称", "购买时间", "购买人", "规格", "单位", "价格"]
sheet.append(headers)
# 写入记录
while query.next():
record = [str(query.value(i)) for i in range(7)]
sheet.append(record)
workbook.save(file_path)
QMessageBox.information(self, "导出成功", "购买记录已成功导出为Excel文件！")
########################################### 导出使用记录#####################################################
def exportUsageRecords(self):
# 选择保存导出文件的路径
file_path, _ = QFileDialog.getSaveFileName(
self, "导出使用记录", "", "Excel Files (*.xlsx)")
if file_path:
query = QSqlQuery()
query.exec("SELECT * FROM usage ORDER BY usage_time DESC")
workbook = Workbook()
sheet = workbook.active
# 写入表头
headers = ["ID", "药品名称", "使用时间", "使用人"]
sheet.append(headers)
# 写入记录
while query.next():
record = [str(query.value(i)) for i in range(4)]
sheet.append(record)
workbook.save(file_path)
QMessageBox.information(self, "导出成功", "使用记录已成功导出为Excel文件！")
############################################## 计算购买价格#######################################
def calculatePurchasePriceSum(self):
dialog = QDialog(self)
dialog.setWindowTitle("选择起始时间和结束时间")
layout = QVBoxLayout(dialog)
start_datetime_edit = QDateTimeEdit()
start_datetime_edit.setCalendarPopup(True)
layout.addWidget(start_datetime_edit)
end_datetime_edit = QDateTimeEdit()
end_datetime_edit.setCalendarPopup(True)
layout.addWidget(end_datetime_edit)
confirm_button = QPushButton("确认")
confirm_button.clicked.connect(lambda: self.calculateTotalPrice(
start_datetime_edit.dateTime(), end_datetime_edit.dateTime()))
layout.addWidget(confirm_button)
dialog.setLayout(layout)
dialog.exec()
def calculateTotalPrice(self, start_datetime, end_datetime):
start_time = start_datetime.toString(Qt.DateFormat.ISODate)
end_time = end_datetime.toString(Qt.DateFormat.ISODate)
query = QSqlQuery()
query.prepare(
"SELECT SUM(price) FROM purchase WHERE purchase_time BETWEEN ? AND ?"
)
query.bindValue(0, start_time)
query.bindValue(1, end_time)
query.exec()
if query.next():
total_price = query.value(0)
QMessageBox.information(
self, "购买价格总和", f"选定时间范围内的购买价格总和为: {total_price}"
)
else:
QMessageBox.information(
self, "购买价格总和", "无购买记录或计算错误！"
)
############################################### 购买药品窗口#############################################
class PurchaseWindow(QWidget):
def __init__(self):
super().__init__()
self.setWindowTitle("添加购买记录")
self.setWindowModality(Qt.WindowModality.ApplicationModal)
nameLabel = QLabel("药品名称:")
self.nameLineEdit = QLineEdit()
timeLabel = QLabel("购买时间:")
self.timeLineEdit = QDateTimeEdit(QDateTime.currentDateTime())
self.timeLineEdit.setDisplayFormat("yyyy-MM-dd HH:mm:ss")
purchaserLabel = QLabel("购买人:")
self.purchaserLineEdit = QLineEdit()
specificationLabel = QLabel("规格:")
self.specificationLineEdit = QLineEdit()
unitLabel = QLabel("单位:")
self.unitComboBox = QComboBox()
self.unitComboBox.addItem("请选择单位")
self.unitComboBox.addItem("毫升")
self.unitComboBox.addItem("克")
priceLabel = QLabel("价格:")
self.priceLineEdit = QLineEdit()
addButton = QPushButton("添加")
addButton.clicked.connect(self.addPurchaseRecord)
layout = QVBoxLayout()
layout.addWidget(nameLabel)
layout.addWidget(self.nameLineEdit)
layout.addWidget(timeLabel)
layout.addWidget(self.timeLineEdit)
layout.addWidget(purchaserLabel)
layout.addWidget(self.purchaserLineEdit)
layout.addWidget(specificationLabel)
layout.addWidget(self.specificationLineEdit)
layout.addWidget(unitLabel)
layout.addWidget(self.unitComboBox)
layout.addWidget(priceLabel)
layout.addWidget(self.priceLineEdit)
layout.addWidget(addButton)
self.setLayout(layout)
########################################### 添加购买药品记录############################################
def addPurchaseRecord(self):
try:
name = self.nameLineEdit.text()
if not name:
QMessageBox.warning(self, "名称为空", "请填写药品名称!",
QMessageBox.StandardButton.Ok)
return
purchase_time = self.timeLineEdit.text()
purchaser = self.purchaserLineEdit.text()
if not purchaser:
QMessageBox.warning(self, "购买人为空", "请填写购买人!",
QMessageBox.StandardButton.Ok)
return
specification = self.specificationLineEdit.text()
if not specification:
QMessageBox.warning(self, "规格为空", "请填写规格!",
QMessageBox.StandardButton.Ok)
return
try:
specification = float(self.specificationLineEdit.text())
except ValueError:
QMessageBox.warning(self, "规格错误", "规格必须为数字!",
QMessageBox.StandardButton.Ok)
return
unit = self.unitComboBox.currentText()
if unit == "请选择单位":
QMessageBox.warning(self, "单位未选择", "请选择有效的单位!",
QMessageBox.StandardButton.Ok)
return
price = self.priceLineEdit.text()
if not price:
QMessageBox.warning(self, "价格为空", "请填写价格!",
QMessageBox.StandardButton.Ok)
try:
price = float(self.priceLineEdit.text())
except ValueError:
QMessageBox.warning(self, "价格错误", "价格必须为数字!",
QMessageBox.StandardButton.Ok)
return
query = QSqlQuery()
query.prepare(
"""
INSERT INTO purchase (name, purchase_time, purchaser, specification, unit, price)
VALUES (:name, :purchase_time, :purchaser, :specification, :unit, :price)
"""
)
query.bindValue(":name", name)
query.bindValue(":purchase_time", purchase_time)
query.bindValue(":purchaser", purchaser)
query.bindValue(":specification", specification)
query.bindValue(":unit", unit)
query.bindValue(":price", price)
if query.exec():
self.nameLineEdit.clear()
self.timeLineEdit.setDateTime(QDateTime.currentDateTime())
self.purchaserLineEdit.clear()
self.specificationLineEdit.clear()
self.unitComboBox.setCurrentIndex(0)
self.priceLineEdit.clear()
QMessageBox.information(
self, "成功", "购买记录已添加！", QMessageBox.StandardButton.Ok)
# 更新列表
mainWindow.updatePurchaseTable()
else:
QMessageBox.critical(self, "错误", "添加购买记录失败！",
QMessageBox.StandardButton.Ok)
# 更新列表
mainWindow.updatePurchaseTable()
except Exception as e:
QMessageBox.critical(self, "错误", str(e),
QMessageBox.StandardButton.Ok)
############################################# 使用药品窗口#############################################
class UsageWindow(QWidget):
def __init__(self):
super().__init__()
self.setWindowTitle("添加使用记录")
self.setWindowModality(Qt.WindowModality.ApplicationModal)
nameLabel = QLabel("药品名称:")
self.nameLineEdit = QLineEdit()
timeLabel = QLabel("使用时间:")
self.timeLineEdit = QDateTimeEdit(QDateTime.currentDateTime())
self.timeLineEdit.setDisplayFormat("yyyy-MM-dd HH:mm:ss")
userLabel = QLabel("使用人:")
self.userLineEdit = QLineEdit()
addButton = QPushButton("添加")
addButton.clicked.connect(self.addUsageRecord)
layout = QVBoxLayout()
layout.addWidget(nameLabel)
layout.addWidget(self.nameLineEdit)
layout.addWidget(timeLabel)
layout.addWidget(self.timeLineEdit)
layout.addWidget(userLabel)
layout.addWidget(self.userLineEdit)
layout.addWidget(addButton)
self.setLayout(layout)
##################################### 使用药品记录###############################
def addUsageRecord(self):
try:
name = self.nameLineEdit.text()
if not name:
QMessageBox.warning(self, "名称为空", "请填写药品名称!",
QMessageBox.StandardButton.Ok)
return
usage_time = self.timeLineEdit.text()
user = self.userLineEdit.text()
if not user:
QMessageBox.warning(self, "名称为空", "请填写使用人!",
QMessageBox.StandardButton.Ok)
return
query = QSqlQuery()
query.exec(
f"""
INSERT INTO usage (name, usage_time, user)
VALUES ('{name}', '{usage_time}', '{user}')
"""
)
self.nameLineEdit.clear()
self.timeLineEdit.setDateTime(QDateTime.currentDateTime())
self.userLineEdit.clear()
QMessageBox.information(self, "成功", "使用记录已添加！",
QMessageBox.StandardButton.Ok)
# 更新列表
mainWindow.updateUsageTable()
except Exception as e:
QMessageBox.critical(self, "错误", str(e),
QMessageBox.StandardButton.Ok)
class QueryDialog(QDialog):
def __init__(self, query, headers):
super().__init__()
self.resize(620, 500)
self.query = query
self.headers = headers
self.table = QTableWidget()
self.table.setColumnCount(len(headers))
self.table.setHorizontalHeaderLabels(headers)
layout = QVBoxLayout()
layout.addWidget(self.table)
self.setLayout(layout)
self.fillTable()
def fillTable(self):
row = 0
while self.query.next():
self.table.insertRow(row)
col = 0
for header in self.headers:
data = self.query.value(col)
self.table.setItem(row, col, QTableWidgetItem(str(data)))
col += 1
row += 1
if __name__ == "__main__":
app = QApplication(sys.argv)
if not createConnection():
sys.exit(1)
createPurchaseTable()
createUsageTable()
mainWindow = MainWindow()
mainWindow.setStyleSheet("background-color: #FAF7F0;")
mainWindow.show()
sys.exit(app.exec())