PyQT5一起学做图书管理系统（8）用户管理

阳光穿透心脏的1/2处 2022-03-12 08:48 328阅读 0赞

# PyQT5一起学做图书管理系统（8）用户管理 #

## 环境 ##

系统：windows10系统  
编辑器：Sublime3  
编程语言：python3+pyqt5

## 前言 ##

记得要初始化数据库~，参考第2节

## 分析 ##

对于用户管理，应该包含添加用户，删除用户，用户权限等操作的，但是为了实现简单，添加用户就用注册代替了，所以用户管理，添加用户这一块就省了。

为了删除用户的同时保护数据库的完整性和约束，需要进行以下操作  
\- 从`User`表删除该用户  
\- 将该用户所借阅的所有书籍全部归还  
\- 更新UI界面

针对用户权限这一块，提升普通用户为管理员或者是将管理员转为普通用户，在操作上有点类似  
因为在数据库设计上有专门的字段来区分，所以这一块的操作：

\- 修改用户字段级别  
\- 更新UI界面

## 程序代码 ##

#!/usr/bin/env python
    # -*- coding: utf-8 -*-
    # @Date    : 2018-12-05 21:34:26
    # @Author  : Jimy_Fengqi (jmps515@163.com)
    # @Link    : https://blog.csdn.net/qiqiyingse/
    
    import sys
    from PyQt5.QtWidgets import *
    from PyQt5.QtGui import *
    from PyQt5.QtCore import *
    import qdarkstyle
    from PyQt5.QtSql import *
    import time
    import sip
    from db.userInfoManager import dbpath
    import images
    
    class UserManage(QDialog):
        def __init__(self,parent=None):
            super(UserManage, self).__init__(parent)
            self.resize(280, 400)
            self.layout = QVBoxLayout()
            self.setLayout(self.layout)
            self.setWindowTitle("管理用户")
            # 用户数
            self.userCount = 0
            self.oldDeleteId = ""
            self.oldDeleteName = ""
            self.deleteId = ""
            self.deleteName = ""
            self.setUpUI()
    
        def setUpUI(self):
            self.db = QSqlDatabase.addDatabase("QSQLITE")
            self.db.setDatabaseName(dbpath)
            self.db.open()
            self.query = QSqlQuery()
            self.getResult()
    
            # 表格设置
            self.tableWidget = QTableWidget()
            self.tableWidget.setRowCount(self.userCount)
            self.tableWidget.setColumnCount(3)
            self.tableWidget.setHorizontalHeaderLabels(['账号', '姓名','用户类型'])
            # 不可编辑
            self.tableWidget.setEditTriggers(QAbstractItemView.NoEditTriggers)
            # 标题可拉伸
            self.tableWidget.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
            # 整行选中
            self.tableWidget.setSelectionBehavior(QAbstractItemView.SelectRows)
    
            self.layout.addWidget(self.tableWidget)
            self.font = QFont()
            self.setRows()
    
            self.font.setPixelSize(15)
            self.deleteUserButton = QPushButton("删 除 用 户")
            self.deleteUserButton.setFixedHeight(36)
            self.deleteUserButton.setFixedWidth(180)
            self.deleteUserButton.setFont(self.font)
    
            self.superUserButton = QPushButton("转为超级管理用户")
            self.superUserButton.setFixedHeight(36)
            self.superUserButton.setFixedWidth(180)
            self.superUserButton.setFont(self.font)
    
            self.ordinaryUserButton = QPushButton("转为普通用户")
            self.ordinaryUserButton.setFixedHeight(36)
            self.ordinaryUserButton.setFixedWidth(180)
            self.ordinaryUserButton.setFont(self.font)
    
    
            hlayout = QHBoxLayout()
            hlayout.addWidget(self.superUserButton, Qt.AlignHCenter)
            hlayout.addWidget(self.ordinaryUserButton, Qt.AlignHCenter)
            hlayout.addWidget(self.deleteUserButton, Qt.AlignHCenter)
            self.widget = QWidget()
            self.widget.setLayout(hlayout)
            self.widget.setFixedHeight(48)
            self.layout.addWidget(self.widget, Qt.AlignCenter)
            # 设置信号
            self.deleteUserButton.clicked.connect(self.deleteUser)
            self.tableWidget.itemClicked.connect(self.getStudentInfo)
            self.superUserButton.clicked.connect(self.superUser)
            self.ordinaryUserButton.clicked.connect(self.ordinaryUser)
    
        def superUser(self):
            if (self.deleteId == "" and self.deleteName == ""):
                print(QMessageBox.warning(self, "警告", "请选中用户", QMessageBox.Yes, QMessageBox.Yes))
                return
            elif (self.deleteId == self.oldDeleteId and self.deleteName == self.oldDeleteName):
                print(QMessageBox.warning(self, "警告", "请选中用户", QMessageBox.Yes, QMessageBox.Yes))
                return
            updateQuery=QSqlQuery()   
            if self.query.value(2) == 0:
                sql="UPDATE User SET IsAdmin=%d WHERE userid='%s'"% (1,self.deleteId)
                updateQuery.exec_(sql)
                self.db.commit()
                print(QMessageBox.information(self,"提醒","用户权限修改成功!",QMessageBox.Yes,QMessageBox.Yes))
                self.updateUI()
                return
            else:
    
                QMessageBox.information(self,"提醒","用户权限已经是超级管理员，无需修改!",QMessageBox.Yes,QMessageBox.Yes)
                return
    
    
        def ordinaryUser(self):
            if (self.deleteId == "" and self.deleteName == ""):
                print(QMessageBox.warning(self, "警告", "请选中用户", QMessageBox.Yes, QMessageBox.Yes))
                return
            elif (self.deleteId == self.oldDeleteId and self.deleteName == self.oldDeleteName):
                print(QMessageBox.warning(self, "警告", "请选中用户", QMessageBox.Yes, QMessageBox.Yes))
                return
            updateQuery=QSqlQuery()
            if self.query.value(2) == 1:
                sql="UPDATE User SET IsAdmin=%d WHERE userid='%s'"% (0,self.deleteId)
                updateQuery.exec_(sql)
                self.db.commit()
                print(QMessageBox.information(self,"提醒","用户权限修改成功!",QMessageBox.Yes,QMessageBox.Yes))
                self.updateUI()
                return
            else:
                QMessageBox.information(self,"提醒","用户权限已经是普通用户，无需修改!",QMessageBox.Yes,QMessageBox.Yes)
                return
    
    
        def getResult(self):
            #sql = "SELECT userid,Name FROM User WHERE IsAdmin=0"
            sql = "SELECT userid,Name,IsAdmin FROM User"
            self.query.exec_(sql)
            self.userCount = 0;
            while (self.query.next()):
                self.userCount += 1;
            #sql = "SELECT userid,Name FROM User WHERE IsAdmin=0"
            sql = "SELECT userid,Name,IsAdmin FROM User"
            self.query.exec_(sql)
    
        def setRows(self):
            self.font.setPixelSize(14)
            for i in range(self.userCount):
                if (self.query.next()):
                    useridItem = QTableWidgetItem(self.query.value(0))
                    StudentNameItem = QTableWidgetItem(self.query.value(1))
                    if self.query.value(2) == 1:
                        usertypeItem = QTableWidgetItem('管理员')
                    elif self.query.value(2) == 0:
                        usertypeItem = QTableWidgetItem('普通用户')
                    else:
                        print(self.query.value(0))
                        print(self.query.value(1))
                        print(self.query.value(2))
                        usertypeItem = QTableWidgetItem('用户')
    
    
                    useridItem.setFont(self.font)
                    StudentNameItem.setFont(self.font)
                    usertypeItem.setFont(self.font)
                    useridItem.setTextAlignment(Qt.AlignHCenter | Qt.AlignVCenter)
                    StudentNameItem.setTextAlignment(Qt.AlignHCenter | Qt.AlignVCenter)
                    usertypeItem.setTextAlignment(Qt.AlignHCenter | Qt.AlignVCenter)
                    self.tableWidget.setItem(i, 0, useridItem)
                    self.tableWidget.setItem(i, 1, StudentNameItem)
                    self.tableWidget.setItem(i, 2, usertypeItem)
            return
    
        def getStudentInfo(self, item):
            row = self.tableWidget.currentIndex().row()
            self.tableWidget.verticalScrollBar().setSliderPosition(row)
            self.getResult()
            i = 0
            while (self.query.next() and i != row):
                i = i + 1
            self.oldDeleteId = self.deleteId
            self.oldDeleteName = self.deleteName
            self.deleteId = self.query.value(0)
            self.deleteName = self.query.value(1)
    
        def deleteUser(self):
            if (self.deleteId == "" and self.deleteName == ""):
                print(QMessageBox.warning(self, "警告", "请选中要删除的用户", QMessageBox.Yes, QMessageBox.Yes))
                return
            elif (self.deleteId == self.oldDeleteId and self.deleteName == self.oldDeleteName):
                print(QMessageBox.warning(self, "警告", "请选中要删除的用户", QMessageBox.Yes, QMessageBox.Yes))
                return
            if (QMessageBox.information(self, "提醒", "删除用户:%s,%s\n用户一经删除将无法恢复，是否继续?" % (self.deleteId, self.deleteName),
                                        QMessageBox.Yes | QMessageBox.No,
                                        QMessageBox.No) == QMessageBox.No):
                return
            # 从User表删除用户
            sql = "DELETE FROM User WHERE userid='%s'" % (self.deleteId)
            self.query.exec_(sql)
            self.db.commit()
            # 归还所有书籍
            sql = "SELECT * FROM User_Book  WHERE userid='%s' AND BorrowState=1" % self.deleteId
            self.query.exec_(sql)
            timenow = time.strftime('%Y-%m-%d', time.localtime(time.time()))
            updateQuery=QSqlQuery()
            while (self.query.next()):
                BookID=self.query.value(1)
                sql="UPDATE Book SET NumCanBorrow=NumCanBorrow+1 WHERE BookID='%s'"% BookID
                updateQuery.exec_(sql)
                self.db.commit()
            sql="UPDATE User_Book SET ReturnTime='%s',BorrowState=0 WHERE userid='%s' AND BorrowState=1"%(timenow,self.deleteId)
            self.query.exec_(sql)
            self.db.commit()
            print(QMessageBox.information(self,"提醒","删除用户成功!",QMessageBox.Yes,QMessageBox.Yes))
            self.updateUI()
            return
    
        def updateUI(self):
            self.getResult()
            self.layout.removeWidget(self.widget)
            self.layout.removeWidget(self.tableWidget)
            sip.delete(self.widget)
            sip.delete(self.tableWidget)
    
            # 表格设置
            self.font.setPixelSize(15)
            self.tableWidget = QTableWidget()
            self.tableWidget.setRowCount(self.userCount)
            self.tableWidget.setColumnCount(3)
            self.tableWidget.setHorizontalHeaderLabels(['账号', '姓名','用户类型'])
            # 不可编辑
            self.tableWidget.setEditTriggers(QAbstractItemView.NoEditTriggers)
            # 标题可拉伸
            self.tableWidget.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
            # 整行选中
            self.tableWidget.setSelectionBehavior(QAbstractItemView.SelectRows)
    
            self.layout.addWidget(self.tableWidget)
            self.setRows()
    
            self.deleteUserButton = QPushButton("删 除 用 户")
            self.deleteUserButton.setFixedHeight(36)
            self.deleteUserButton.setFixedWidth(180)
            self.deleteUserButton.setFont(self.font)
    
    
            self.superUserButton = QPushButton("转为超级管理用户")
            self.superUserButton.setFixedHeight(36)
            self.superUserButton.setFixedWidth(180)
            self.superUserButton.setFont(self.font)
    
            self.ordinaryUserButton = QPushButton("转为普通用户")
            self.ordinaryUserButton.setFixedHeight(36)
            self.ordinaryUserButton.setFixedWidth(180)
            self.ordinaryUserButton.setFont(self.font)
            
            hlayout = QHBoxLayout()
            hlayout.addWidget(self.superUserButton, Qt.AlignHCenter)
            hlayout.addWidget(self.ordinaryUserButton, Qt.AlignHCenter)
            hlayout.addWidget(self.deleteUserButton, Qt.AlignHCenter)
    
            self.widget = QWidget()
            self.widget.setLayout(hlayout)
            self.widget.setFixedHeight(48)
    
            self.layout.addWidget(self.widget, Qt.AlignCenter)
            # 设置信号
            self.deleteUserButton.clicked.connect(self.deleteUser)
            self.tableWidget.itemClicked.connect(self.getStudentInfo)
            self.superUserButton.clicked.connect(self.superUser)
            self.ordinaryUserButton.clicked.connect(self.ordinaryUser)
    
    if __name__ == "__main__":
        app = QApplication(sys.argv)
        #app.setWindowIcon(QIcon("./images/MainWindow_1.png"))
        app.setWindowIcon(QIcon(":/images/MainWindow_1.png"))
        app.setStyleSheet(qdarkstyle.load_stylesheet_pyqt5())
        mainMindow = UserManage()
        mainMindow.show()
        sys.exit(app.exec_())

## 程序效果 ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FpcWl5aW5nc2U_size_16_color_FFFFFF_t_70][]

修改用户权限，将普通用户转为管理员，点击OK之后界面开始更新

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FpcWl5aW5nc2U_size_16_color_FFFFFF_t_70 1][]![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FpcWl5aW5nc2U_size_16_color_FFFFFF_t_70 2][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FpcWl5aW5nc2U_size_16_color_FFFFFF_t_70]: /images/20220312/869eb91999464c268fe34c23a72006da.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FpcWl5aW5nc2U_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20190305165549483.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FpcWl5aW5nc2U=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FpcWl5aW5nc2U_size_16_color_FFFFFF_t_70 2]: /images/20220312/82811186d7f045f79a44c451f5037e4f.png