标签：-- self db pyqt5 setFont query 借阅 font 书籍

本节分为两个部分：借阅书籍界面设计、归还书籍界面设计。

主要流程：1、通过进入借阅书籍界面，点击借阅书籍按钮，实现借阅书籍的消息框提醒，和相关数据库内容变动。

     2、通过进入归还书籍界面，点击归还书籍按钮，实现归还书籍的消息框提醒，和相关数据库内容变动。

一、借阅书籍界面

导入的模块：

import sys
from PyQt5.QtWidgets import *
from PyQt5.QtGui import *
from PyQt5.QtCore import *
import qdarkstyle
from PyQt5.QtSql import *
import time

（一）类基础设置

class borrowBookDialog(QDialog):
    borrow_book_success_signal = pyqtSignal()

    def __init__(self, StudentId):
        super(borrowBookDialog, self).__init__()
        self.studentId = StudentId
        self.setUpUI()

        self.setWindowModality(Qt.WindowModal)
        self.setWindowTitle("借阅书籍")

（二）借阅书籍界面设计

1、页面大小布局

        self.resize(300, 400)
        self.layout = QFormLayout()

        # 将垂直布局放置到主窗口中
        self.setLayout(self.layout)

 2、添加标签及大小

（1）添加书籍标签信息

self.borrowStudentLabel = QLabel("借 阅 人：")
        self.borrowStudentIdLabel = QLabel(self.studentId)
        self.titleLabel = QLabel(" 借阅书籍")
        self.bookNameLabel = QLabel("书 名:")
        self.bookIdLabel = QLabel("书 号:")
        self.authNameLabel = QLabel("作 者:")
        self.categoryLabel = QLabel("分 类:")
        self.publisherLabel = QLabel("出 版 社:")
        self.publisherDateLabel = QLabel("出版日期:")
        
                # button控件
        self.borrowBookButton = QPushButton("确认借阅")

（2）设置书籍标签字体的大小

font = QFont()
font.setPixelSize(20)
# 标题字体大小
self.titleLabel.setFont(font)
font.setPixelSize(14)
self.bookNameLabel.setFont(font)
self.bookIdLabel.setFont(font)
self.authNameLabel.setFont(font)
self.categoryLabel.setFont(font)
self.publisherLabel.setFont(font)
self.publisherDateLabel.setFont(font)

（3）设置按钮大小

# 设置button
font.setPixelSize(16)
self.borrowBookButton.setFont(font)
self.borrowBookButton.setFixedHeight(32)
self.borrowBookButton.setFixedWidth(140)

 3、输入框设置

（1）添加输入框

• 分类输入框的类型为：选项输入框类型

        BookCategory = ["哲学", "教育", "生物学", "社会科学", "政治",
                        "法律", "军事", "经济", "文化", "体育", "语言文字", "地理", "天文学", "医学卫生", "农业"]

•  设置标签相对应的输入框

        self.bookNameEdit = QLineEdit()
        self.bookIdEdit = QLineEdit()
        self.authNameEdit = QLineEdit()
        # 分类创建的QComboBox，存储整个分类的列表
        # QComboBox 以占用最少屏幕控件的方式向用户呈现选项列表的方法
        self.categoryEdit = QComboBox()
        self.categoryEdit.addItems(BookCategory)
        self.publisherEdit = QLineEdit()
        self.publishTime = QLineEdit()

（2）设置输入框可输入的长度

• 限制输入的长度

self.bookNameEdit.setMaxLength(10)
self.bookIdEdit.setMaxLength(6)
self.authNameEdit.setMaxLength(10)
self.publisherEdit.setMaxLength(10)

（3）设置输入框的字体、背景、可输入方式

        # 只能读取
        self.bookNameEdit.setReadOnly(True)
        self.bookNameEdit.setStyleSheet("background-color:grey")

        # 通过输入ID返回其余的所有信息
        self.bookIdEdit.setFont(font)
        # 设置Qt的css代码,修改控件的背景颜色
        self.authNameEdit.setFont(font)
        self.authNameEdit.setReadOnly(True)
        self.authNameEdit.setStyleSheet("background-color:grey")

        self.publisherEdit.setFont(font)
        self.publisherEdit.setReadOnly(True)
        self.publisherEdit.setStyleSheet("background-color:grey")

        self.publishTime.setFont(font)
        self.publishTime.setReadOnly(True)
        self.publishTime.setStyleSheet("background-color:grey")

        self.categoryEdit.setFont(font)
        # self.categoryEdit.setReadOnly(True)
        self.categoryEdit.setStyleSheet("background-color:grey")

 4、设置标签的间距

# 设置间距
self.titleLabel.setMargin(8)
# 垂直布局上下行之间的间距
self.layout.setVerticalSpacing(10)

 5、表单布局

        # 添加进Formlayout
        self.layout.addRow("", self.titleLabel)
        self.layout.addRow(self.bookNameLabel, self.bookNameEdit)
        self.layout.addRow(self.bookIdLabel, self.bookIdEdit)
        self.layout.addRow(self.authNameLabel, self.authNameEdit)
        self.layout.addRow(self.categoryLabel, self.categoryEdit)
        self.layout.addRow(self.publisherLabel, self.publisherEdit)
        self.layout.addRow(self.publisherDateLabel, self.publishTime)
        self.layout.addRow("", self.borrowBookButton)

（三）信号传递

1、信号一

• 当输入书号后，自动返回对应书号的相关信息。

self.bookIdEdit.textChanged.connect(self.bookIdEditChange)

 （1）文本框处理

• 接收书号信息，若书号输入框内为空，则执行if语句，清空其他输入框内容。

    def bookIdEditChange(self):
        bookID = self.bookIdEdit.text()

        if bookID == "":
            self.bookNameEdit.clear()
            self.authNameEdit.clear()
            self.publisherEdit.clear()
            self.publishTime.clear()
            self.dropNumEdit.clear()

（2）连接数据库查询书号

        db = QSqlDatabase.addDatabase("QSQLITE")
        db.setDatabaseName("./db/LibraryManagement.db")
        db.open()
        query = QSqlQuery()
        sql = "select * from Book where BookId='%s'" % (bookID)
        query.exec_(sql)

（3）若书号存在，返回其他输入框内的信息

        if query.next():
            self.bookNameEdit.setText(query.value(0))
            self.authNameEdit.setText(query.value(2))
            self.categoryEdit.setCurrentText(query.value(3))
            self.publisherEdit.setText(query.value(4))
            self.publishTime.setText(query.value(5))
        return

2、信号二

• 绑定两个事件：用鼠标点击借阅书籍按钮的功能事件、用回车键触发借阅书籍按钮的功能事件

        self.borrowBookButton.clicked.connect(self.borrwoButtonClicked)
        self.bookIdEdit.returnPressed.connect(self.borrwoButtonClicked)

 （1）接收书号输入框内信息

def borrwoButtonClicked(self):
    """
    获取书号，书号为空或者不存在库中,则弹出错误
    向book_user表中插入记录，更新user表以及book表
    :return:
    """
    BookId = self.bookIdEdit.text()

• 处理书号输入框内信息
  • 若书号为空，进行消息提示。

  • # BookId为空的处理
    if BookId == "":
        print(QMessageBox.warning(self, '警告', '你所要借阅的书籍不存在,请重新输入', QMessageBox.Yes,
                                  QMessageBox.Yes))
        return

  • 若不为空则连接数据库。

  •     # 数据库
    db = QSqlDatabase.addDatabase('QSQLITE')
    db.setDatabaseName('./db/LibraryManagement.db')
    db.open()
    query = QSqlQuery()

  • 执行sql语句，查询书号信息，若不存在，进行消息提示。

  • sql = "select * from Book where BookId='%s'" % BookId
    query.exec_(sql)
    # 如果BookId不存在
    if (not query.next()):
        print(QMessageBox.warning(self, '警告', '你所要借阅的书籍不存在, 请重新输入', QMessageBox.Yes,
                                  QMessageBox.Yes))
        return

 （2）借阅书籍的规则功能设置

• 设置借阅书本上限为五本书。

• sql = "select count(StudentId) from User_Book where StudentId='%s' and BorrowState=1" % self.studentId
  query.exec_(sql)
  
  if (query.next()):
      borrowNum = query.value(0)
      if borrowNum == 5:
          QMessageBox.warning(self, '警告', '您借阅的图书达到上限(5本),借书失败!', QMessageBox.Yes,
                              QMessageBox.Yes)
          return

•  不允许重复借阅同一本书籍

• print(BookId)
  sql = "select count(StudentId) from User_Book where StudentId='%s' and BookId = '%s' and BorrowState=1" % (
      self.studentId, BookId)
  query.exec_(sql)
  if (query.next() and query.value(0)):
      QMessageBox.warning(self, "警告", "您已经借阅了本书并尚未归还，借阅失败!", QMessageBox.Yes, QMessageBox.Yes)
      return

 （3）正常借阅，则更新数据库信息

• 更新User表

• sql = "update User set TimesBorrowed=TimesBorrowed+1, NumBorrowed=NumBorrowed+1 where StudentId='%s'" % self.studentId
  query.exec_(sql)
  db.commit()

•  更新Book表

• sql = "update Book set NumCanBorrow=NumCanBorrow-1, NumBorrowed=NumBorrowed+1 where BookId='%s'" % BookId
  query.exec_(sql)
  db.commit()

•  更新user_book表
• 在user_book表内插入操作的信息

• timenow = time.strftime("%Y-%m-%d", time.localtime(time.time()))
  sql = "insert into User_Book values ('%s', '%s', '%s', NULL, 1)" % (self.studentId, BookId, timenow)
  query.exec_(sql)
  db.commit()

•  最后，提示借阅成功，并返回

• print(QMessageBox.information(self, "提示", "借阅成功", QMessageBox.Yes, QMessageBox.Yes))
  self.borrow_book_success_signal.emit()
  self.close()
  return

 （四）程序入口

if __name__ == '__main__':
    app = QApplication(sys.argv)
    app.setWindowIcon(QIcon("./iron-man.png"))
    app.setStyleSheet(qdarkstyle.load_stylesheet_pyqt5())
    mainMindow = borrowBookDialog("PB15000000")
    mainMindow.show()
    sys.exit(app.exec_())

二、归还书籍界面 

提示：由于归还书记界面和借阅书籍界面大致相同，只有数据库语句的方法不同；若借阅书籍代码结构已理解透彻，可直接跳跃至（三）信号传递--2、信号二的位置。

导入的模块：

import sys
from PyQt5.QtWidgets import *
from PyQt5.QtGui import *
from PyQt5.QtCore import *
import qdarkstyle
from PyQt5.QtSql import *
import time

（一）类基础设置

class returnBookDialog(QDialog):
    return_book_success_signal = pyqtSignal()

    def __init__(self, StudentId):
        super(returnBookDialog, self).__init__()
        self.studentId = StudentId
        self.setUpUI()

        self.setWindowModality(Qt.WindowModal)
        self.setWindowTitle("归还书籍")

（二）借阅书籍界面设计

1、页面大小布局

        self.resize(300, 400)
        self.layout = QFormLayout()

        # 将垂直布局放置到主窗口中
        self.setLayout(self.layout)

 2、添加标签及大小

（1）添加书籍标签信息

        self.returnStudentLabel = QLabel("还 书 人：")
        self.returnStudentIdLabel = QLabel(self.studentId)
        self.titleLabel = QLabel(" 归还书籍")
        self.bookNameLabel = QLabel("书 名:")
        self.bookIdLabel = QLabel("书 号:")
        self.authNameLabel = QLabel("作 者:")
        self.categoryLabel = QLabel("分 类:")
        self.publisherLabel = QLabel("出 版 社:")
        self.publisherDateLabel = QLabel("出版日期:")

        # button控件
        self.returnBookButton = QPushButton("确认归还")

（2）设置标签字体的大小

        font = QFont()
        font.setPixelSize(20)
        self.titleLabel.setFont(font)
        font.setPixelSize(14)
        self.bookNameLabel.setFont(font)
        self.bookIdLabel.setFont(font)
        self.authNameLabel.setFont(font)
        self.categoryLabel.setFont(font)
        self.publisherLabel.setFont(font)
        self.publisherDateLabel.setFont(font)
        self.bookNameEdit.setFont(font)

（3）设置按钮大小

        font.setPixelSize(16)
        self.returnBookButton.setFont(font)
        self.returnBookButton.setFixedHeight(32)
        self.returnBookButton.setFixedWidth(140)

 3、输入框设置

（1）添加输入框

• 分类输入框的类型为：选项输入框类型

        BookCategory = ["哲学", "教育", "生物学", "社会科学", "政治",
                        "法律", "军事", "经济", "文化", "体育", "语言文字", "地理", "天文学", "医学卫生", "农业"]

•  设置标签相对应的输入框

        self.bookNameEdit = QLineEdit()
        self.bookIdEdit = QLineEdit()
        self.authNameEdit = QLineEdit()
        # 分类创建的QComboBox，存储整个分类的列表
        # QComboBox 以占用最少屏幕控件的方式向用户呈现选项列表的方法
        self.categoryEdit = QComboBox()
        self.categoryEdit.addItems(BookCategory)

        self.publisherEdit = QLineEdit()

        self.publishTime = QLineEdit()

（2）设置输入框可输入的长度

• 限制输入的长度

        self.bookNameEdit.setMaxLength(10)
        self.bookIdEdit.setMaxLength(6)
        self.authNameEdit.setMaxLength(10)
        self.publisherEdit.setMaxLength(10)

（3）设置输入框的字体、背景、可输入方式

        self.bookNameEdit.setReadOnly(True)
        self.bookNameEdit.setStyleSheet("background-color:grey")

        # 通过输入ID返回其余的所有信息
        self.bookIdEdit.setFont(font)
        # 设置Qt的css代码,修改控件的背景颜色
        self.authNameEdit.setFont(font)
        self.authNameEdit.setReadOnly(True)
        self.authNameEdit.setStyleSheet("background-color:grey")

        self.publisherEdit.setFont(font)
        self.publisherEdit.setReadOnly(True)
        self.publisherEdit.setStyleSheet("background-color:grey")

        self.publishTime.setFont(font)
        self.publishTime.setReadOnly(True)
        self.publishTime.setStyleSheet("background-color:grey")

        self.categoryEdit.setFont(font)
        # self.categoryEdit.setReadOnly(True)
        self.categoryEdit.setStyleSheet("background-color:grey")

 4、设置标签的间距

        # 设置间距
        self.titleLabel.setMargin(8)
        # 垂直布局上下行之间的间距
        self.layout.setVerticalSpacing(10)

 5、表单布局

        # 添加进Formlayout
        self.layout.addRow("", self.titleLabel)
        self.layout.addRow(self.bookNameLabel, self.bookNameEdit)
        self.layout.addRow(self.bookIdLabel, self.bookIdEdit)
        self.layout.addRow(self.authNameLabel, self.authNameEdit)
        self.layout.addRow(self.categoryLabel, self.categoryEdit)
        self.layout.addRow(self.publisherLabel, self.publisherEdit)
        self.layout.addRow(self.publisherDateLabel, self.publishTime)
        self.layout.addRow("", self.returnBookButton)

（三）信号传递

1、信号一

• 当输入书号后，自动返回对应书号的相关信息。

self.bookIdEdit.textChanged.connect(self.bookIdEditChange)

 （1）文本框处理

• 接收书号信息，若书号输入框内为空，则执行if语句，清空其他输入框内容。

    def bookIdEditChange(self):
        bookID = self.bookIdEdit.text()

        if bookID == "":
            self.bookNameEdit.clear()
            self.authNameEdit.clear()
            self.publisherEdit.clear()
            self.publishTime.clear()
            self.dropNumEdit.clear()

（2）连接数据库查询书号

        db = QSqlDatabase.addDatabase("QSQLITE")
        db.setDatabaseName("./db/LibraryManagement.db")
        db.open()
        query = QSqlQuery()
        sql = "select * from Book where BookId='%s'" % (bookID)
        query.exec_(sql)

（3）若书号存在，返回其他输入框内的信息

        if query.next():
            self.bookNameEdit.setText(query.value(0))
            self.authNameEdit.setText(query.value(2))
            self.categoryEdit.setCurrentText(query.value(3))
            self.publisherEdit.setText(query.value(4))
            self.publishTime.setText(query.value(5))
        return

2、信号二

• 绑定用鼠标点击归还书籍按钮，发送信号

self.returnBookButton.clicked.connect(self.returnButtonClicked)

 （1）接收书号输入框内信息

    def returnButtonClicked(self):
        BookId = self.bookIdEdit.text()

• 处理书号输入框内信息
  • 若书号为空，进行消息提示。

  • # BookId为空的处理
    if BookId == "":
        print(QMessageBox.warning(self, '警告', '你所要借阅的书籍不存在,请重新输入', QMessageBox.Yes,
                                  QMessageBox.Yes))
        return

  • 若不为空则连接数据库。

  •     # 数据库
    db = QSqlDatabase.addDatabase('QSQLITE')
    db.setDatabaseName('./db/LibraryManagement.db')
    db.open()
    query = QSqlQuery()

 （2）归还书籍的逻辑处理

• 若输入的书籍未曾借阅而归还，则进行消息提示

•         sql = "select * from User_Book where StudentId='%s' and BookId='%s' and BorrowState=1" % (
              self.studentId, BookId)
          query.exec_(sql)
          if (not query.next()):
              print(QMessageBox.information(self, '提示', '您并未借阅此书, 故无需归还', QMessageBox.Yes, QMessageBox.Yes))
              return

 （3）正常归还书籍，则更新数据库信息

• 更新User表

•         sql = "update User set NumBorrowed=NumBorrowed-1 where StudentId='%s'" % self.studentId
          query.exec_(sql)
          db.commit()

•  更新Book表

•         sql = "update Book set NumCanBorrow=NumCanBorrow+1, NumBorrowed=NumBorrowed-1 where BookId='%s'" % BookId
          query.exec_(sql)
          db.commit()

•  更新user_book表
• 在user_book表内插入操作的信息

•         timenow = time.strftime("%Y-%m-%d", time.localtime(time.time()))
          sql = "update User_Book set ReturnTime='%s', BorrowState=0 where StudentId='%s' and BookId='%s' and BorrowState=1" % (
          timenow, self.studentId, BookId)
          query.exec_(sql)
          db.commit()

•  最后，提示借阅成功，并返回

•         print(QMessageBox.information(self, '提示', '归还成功!', QMessageBox.Yes, QMessageBox.Yes))
          self.return_book_success_signal.emit()
          self.close()
          return

 （四）程序入口

if __name__ == '__main__':
    app = QApplication(sys.argv)
    app.setWindowIcon(QIcon("./iron-man.png"))
    app.setStyleSheet(qdarkstyle.load_stylesheet_pyqt5())
    mainMindow = returnBookDialog("PB15000000")
    mainMindow.show()
    sys.exit(app.exec_())

 三、完整代码

（一）借阅书籍代码

import sys
from PyQt5.QtWidgets import *
from PyQt5.QtGui import *
from PyQt5.QtCore import *
import qdarkstyle
from PyQt5.QtSql import *
import time


# 创建借阅图书的类
class borrowBookDialog(QDialog):
    borrow_book_success_signal = pyqtSignal()

    def __init__(self, StudentId):
        super(borrowBookDialog, self).__init__()
        self.studentId = StudentId
        self.setUpUI()

        self.setWindowModality(Qt.WindowModal)
        self.setWindowTitle("借阅书籍")

    def setUpUI(self):
        BookCategory = ["哲学", "教育", "生物学", "社会科学", "政治",
                        "法律", "军事", "经济", "文化", "体育", "语言文字", "地理", "天文学", "医学卫生", "农业"]
        self.resize(300, 400)
        self.layout = QFormLayout()

        # 将垂直布局放置到主窗口中
        self.setLayout(self.layout)

        # label控件
        self.borrowStudentLabel = QLabel("借 阅 人：")
        self.borrowStudentIdLabel = QLabel(self.studentId)
        self.titleLabel = QLabel(" 借阅书籍")
        self.bookNameLabel = QLabel("书 名:")
        self.bookIdLabel = QLabel("书 号:")
        self.authNameLabel = QLabel("作 者:")
        self.categoryLabel = QLabel("分 类:")
        self.publisherLabel = QLabel("出 版 社:")
        self.publisherDateLabel = QLabel("出版日期:")

        # button控件
        self.borrowBookButton = QPushButton("确认借阅")

        # lineEdit控件
        self.bookNameEdit = QLineEdit()
        self.bookIdEdit = QLineEdit()
        self.authNameEdit = QLineEdit()
        # 分类创建的QComboBox，存储整个分类的列表
        # QComboBox 以占用最少屏幕控件的方式向用户呈现选项列表的方法
        self.categoryEdit = QComboBox()
        self.categoryEdit.addItems(BookCategory)

        self.publisherEdit = QLineEdit()

        self.publishTime = QLineEdit()

        # 限制输入的长度
        self.bookNameEdit.setMaxLength(10)
        self.bookIdEdit.setMaxLength(6)
        self.authNameEdit.setMaxLength(10)
        self.publisherEdit.setMaxLength(10)

        # 添加进Formlayout
        self.layout.addRow("", self.titleLabel)
        self.layout.addRow(self.bookNameLabel, self.bookNameEdit)
        self.layout.addRow(self.bookIdLabel, self.bookIdEdit)
        self.layout.addRow(self.authNameLabel, self.authNameEdit)
        self.layout.addRow(self.categoryLabel, self.categoryEdit)
        self.layout.addRow(self.publisherLabel, self.publisherEdit)
        self.layout.addRow(self.publisherDateLabel, self.publishTime)
        self.layout.addRow("", self.borrowBookButton)

        # 设置字体
        font = QFont()
        font.setPixelSize(20)
        self.titleLabel.setFont(font)
        font.setPixelSize(14)
        self.bookNameLabel.setFont(font)
        self.bookIdLabel.setFont(font)
        self.authNameLabel.setFont(font)
        self.categoryLabel.setFont(font)
        self.publisherLabel.setFont(font)
        self.publisherDateLabel.setFont(font)

        self.bookNameEdit.setFont(font)
        # 只能读取
        self.bookNameEdit.setReadOnly(True)
        self.bookNameEdit.setStyleSheet("background-color:grey")

        # 通过输入ID返回其余的所有信息
        self.bookIdEdit.setFont(font)
        # 设置Qt的css代码,修改控件的背景颜色
        self.authNameEdit.setFont(font)
        self.authNameEdit.setReadOnly(True)
        self.authNameEdit.setStyleSheet("background-color:grey")

        self.publisherEdit.setFont(font)
        self.publisherEdit.setReadOnly(True)
        self.publisherEdit.setStyleSheet("background-color:grey")

        self.publishTime.setFont(font)
        self.publishTime.setReadOnly(True)
        self.publishTime.setStyleSheet("background-color:grey")

        self.categoryEdit.setFont(font)
        # self.categoryEdit.setReadOnly(True)
        self.categoryEdit.setStyleSheet("background-color:grey")

        # 设置button
        font.setPixelSize(16)
        self.borrowBookButton.setFont(font)
        self.borrowBookButton.setFixedHeight(32)
        self.borrowBookButton.setFixedWidth(140)

        # 设置间距
        self.titleLabel.setMargin(8)
        # 垂直布局上下行之间的间距
        self.layout.setVerticalSpacing(10)
        # 点击借阅图书按钮实现借书功能
        self.borrowBookButton.clicked.connect(self.borrwoButtonClicked)
        self.bookIdEdit.textChanged.connect(self.bookIdEditChange)
        self.bookIdEdit.returnPressed.connect(self.borrwoButtonClicked)

    def borrwoButtonClicked(self):
        """
        获取书号，书号为空或者不存在库中,则弹出错误
        向book_user表中插入记录，更新user表以及book表
        :return:
        """
        BookId = self.bookIdEdit.text()

        # BookId为空的处理
        if BookId == "":
            print(QMessageBox.warning(self, '警告', '你所要借阅的书籍不存在,请重新输入', QMessageBox.Yes,
                                      QMessageBox.Yes))
            return

            # 数据库
        db = QSqlDatabase.addDatabase('QSQLITE')
        db.setDatabaseName('./db/LibraryManagement.db')
        db.open()
        query = QSqlQuery()


        sql = "select * from Book where BookId='%s'" % BookId
        query.exec_(sql)
        # 如果BookId不存在
        if (not query.next()):
            print(QMessageBox.warning(self, '警告', '你所要借阅的书籍不存在, 请重新输入', QMessageBox.Yes,
                                      QMessageBox.Yes))
            return

        # 借书上限5本
        sql = "select count(StudentId) from User_Book where StudentId='%s' and BorrowState=1" % self.studentId
        query.exec_(sql)

        if (query.next()):
            borrowNum = query.value(0)
            if borrowNum == 5:
                QMessageBox.warning(self, '警告', '您借阅的图书达到上限(5本),借书失败!', QMessageBox.Yes,
                                    QMessageBox.Yes)
                return
        # 不允许借重复的书籍
        print(BookId)
        sql = "select count(StudentId) from User_Book where StudentId='%s' and BookId = '%s' and BorrowState=1" % (
            self.studentId, BookId)
        query.exec_(sql)
        if (query.next() and query.value(0)):
            QMessageBox.warning(self, "警告", "您已经借阅了本书并尚未归还，借阅失败!", QMessageBox.Yes, QMessageBox.Yes)
            return

        # 更新user表
        sql = "update User set TimesBorrowed=TimesBorrowed+1, NumBorrowed=NumBorrowed+1 where StudentId='%s'" % self.studentId
        query.exec_(sql)
        db.commit()

        # 更新book表
        sql = "update Book set NumCanBorrow=NumCanBorrow-1, NumBorrowed=NumBorrowed+1 where BookId='%s'" % BookId
        query.exec_(sql)
        db.commit()

        # 插入user_book表
        timenow = time.strftime("%Y-%m-%d", time.localtime(time.time()))
        sql = "insert into User_Book values ('%s', '%s', '%s', NULL, 1)" % (self.studentId, BookId, timenow)
        query.exec_(sql)
        db.commit()
        print(QMessageBox.information(self, "提示", "借阅成功", QMessageBox.Yes, QMessageBox.Yes))
        self.borrow_book_success_signal.emit()
        self.close()
        return


    def bookIdEditChange(self):
        bookID = self.bookIdEdit.text()

        if bookID == "":
            self.bookNameEdit.clear()
            self.authNameEdit.clear()
            self.publisherEdit.clear()
            self.publishTime.clear()
            self.dropNumEdit.clear()
        db = QSqlDatabase.addDatabase("QSQLITE")
        db.setDatabaseName("./db/LibraryManagement.db")
        db.open()
        query = QSqlQuery()
        sql = "select * from Book where BookId='%s'" % (bookID)
        query.exec_(sql)

        if query.next():
            self.bookNameEdit.setText(query.value(0))
            self.authNameEdit.setText(query.value(2))
            self.categoryEdit.setCurrentText(query.value(3))
            self.publisherEdit.setText(query.value(4))
            self.publishTime.setText(query.value(5))
        return


if __name__ == '__main__':
    app = QApplication(sys.argv)
    app.setWindowIcon(QIcon("./iron-man.png"))
    app.setStyleSheet(qdarkstyle.load_stylesheet_pyqt5())
    mainMindow = borrowBookDialog("PB15000000")
    mainMindow.show()
    sys.exit(app.exec_())

 （二）归还书籍代码

import sys
from PyQt5.QtWidgets import *
from PyQt5.QtGui import *
from PyQt5.QtCore import *
import qdarkstyle
from PyQt5.QtSql import *
import time


# 创建归还图书的类
class returnBookDialog(QDialog):
    return_book_success_signal = pyqtSignal()

    def __init__(self, StudentId):
        super(returnBookDialog, self).__init__()
        self.studentId = StudentId
        self.setUpUI()

        self.setWindowModality(Qt.WindowModal)
        self.setWindowTitle("归还书籍")

    def setUpUI(self):
        BookCategory = ["哲学", "教育", "生物学", "社会科学", "政治",
                        "法律", "军事", "经济", "文化", "体育", "语言文字", "地理", "天文学", "医学卫生", "农业"]
        self.resize(300, 400)
        self.layout = QFormLayout()

        # 将垂直布局放置到主窗口中
        self.setLayout(self.layout)

        # label控件
        self.returnStudentLabel = QLabel("还 书 人：")
        self.returnStudentIdLabel = QLabel(self.studentId)
        self.titleLabel = QLabel(" 归还书籍")
        self.bookNameLabel = QLabel("书 名:")
        self.bookIdLabel = QLabel("书 号:")
        self.authNameLabel = QLabel("作 者:")
        self.categoryLabel = QLabel("分 类:")
        self.publisherLabel = QLabel("出 版 社:")
        self.publisherDateLabel = QLabel("出版日期:")

        # button控件
        self.returnBookButton = QPushButton("确认归还")

        # lineEdit控件
        self.bookNameEdit = QLineEdit()
        self.bookIdEdit = QLineEdit()
        self.authNameEdit = QLineEdit()
        # 分类创建的QComboBox，存储整个分类的列表
        # QComboBox 以占用最少屏幕控件的方式向用户呈现选项列表的方法
        self.categoryEdit = QComboBox()
        self.categoryEdit.addItems(BookCategory)

        self.publisherEdit = QLineEdit()

        self.publishTime = QLineEdit()

        # 限制输入的长度
        self.bookNameEdit.setMaxLength(10)
        self.bookIdEdit.setMaxLength(6)
        self.authNameEdit.setMaxLength(10)
        self.publisherEdit.setMaxLength(10)

        # 添加进Formlayout
        self.layout.addRow("", self.titleLabel)
        self.layout.addRow(self.bookNameLabel, self.bookNameEdit)
        self.layout.addRow(self.bookIdLabel, self.bookIdEdit)
        self.layout.addRow(self.authNameLabel, self.authNameEdit)
        self.layout.addRow(self.categoryLabel, self.categoryEdit)
        self.layout.addRow(self.publisherLabel, self.publisherEdit)
        self.layout.addRow(self.publisherDateLabel, self.publishTime)
        self.layout.addRow("", self.returnBookButton)

        # 设置字体
        font = QFont()
        font.setPixelSize(20)
        self.titleLabel.setFont(font)
        font.setPixelSize(14)
        self.bookNameLabel.setFont(font)
        self.bookIdLabel.setFont(font)
        self.authNameLabel.setFont(font)
        self.categoryLabel.setFont(font)
        self.publisherLabel.setFont(font)
        self.publisherDateLabel.setFont(font)

        self.bookNameEdit.setFont(font)
        # 只能读取
        self.bookNameEdit.setReadOnly(True)
        self.bookNameEdit.setStyleSheet("background-color:grey")

        # 通过输入ID返回其余的所有信息
        self.bookIdEdit.setFont(font)
        # 设置Qt的css代码,修改控件的背景颜色
        self.authNameEdit.setFont(font)
        self.authNameEdit.setReadOnly(True)
        self.authNameEdit.setStyleSheet("background-color:grey")

        self.publisherEdit.setFont(font)
        self.publisherEdit.setReadOnly(True)
        self.publisherEdit.setStyleSheet("background-color:grey")

        self.publishTime.setFont(font)
        self.publishTime.setReadOnly(True)
        self.publishTime.setStyleSheet("background-color:grey")

        self.categoryEdit.setFont(font)
        # self.categoryEdit.setReadOnly(True)
        self.categoryEdit.setStyleSheet("background-color:grey")

        # 设置button
        font.setPixelSize(16)
        self.returnBookButton.setFont(font)
        self.returnBookButton.setFixedHeight(32)
        self.returnBookButton.setFixedWidth(140)

        # 设置间距
        self.titleLabel.setMargin(8)
        # 垂直布局上下行之间的间距
        self.layout.setVerticalSpacing(10)
        # 点击归还图书按钮实现还书功能
        self.bookIdEdit.textChanged.connect(self.bookIdEditChange)
        self.returnBookButton.clicked.connect(self.returnButtonClicked)
        # 归还图书功能

    def returnButtonClicked(self):
        """
        获取书号, 书号为空或者并未借阅，则弹出提醒
        更新book_user表user表以及book表
        :return:
        """
        BookId = self.bookIdEdit.text()
        # Book为空，返回要还的书籍不存在
        if (BookId == ""):
            print(QMessageBox.warning(self, '警告', '你要还的书不存在, 请重新输入', QMessageBox.Yes, QMessageBox.Yes))
            return
        # 打开数据库
        db = QSqlDatabase.addDatabase('QSQLITE')
        db.setDatabaseName('./db/LibraryManagement.db')
        db.open()
        query = QSqlQuery()

        # 如果未借阅
        sql = "select * from User_Book where StudentId='%s' and BookId='%s' and BorrowState=1" % (
            self.studentId, BookId)
        query.exec_(sql)
        if (not query.next()):
            print(QMessageBox.information(self, '提示', '您并未借阅此书, 故无需归还', QMessageBox.Yes, QMessageBox.Yes))
            return
            # 更新user表
        sql = "update User set NumBorrowed=NumBorrowed-1 where StudentId='%s'" % self.studentId
        query.exec_(sql)
        db.commit()

        # 更新book表
        sql = "update Book set NumCanBorrow=NumCanBorrow+1, NumBorrowed=NumBorrowed-1 where BookId='%s'" % BookId
        query.exec_(sql)
        db.commit()

        # 更新user_book表
        timenow = time.strftime("%Y-%m-%d", time.localtime(time.time()))
        sql = "update User_Book set ReturnTime='%s', BorrowState=0 where StudentId='%s' and BookId='%s' and BorrowState=1" % (
        timenow, self.studentId, BookId)
        query.exec_(sql)
        db.commit()
        print(QMessageBox.information(self, '提示', '归还成功!', QMessageBox.Yes, QMessageBox.Yes))
        self.return_book_success_signal.emit()
        self.close()
        return

    # 根据bookid返回图书信息
    def bookIdEditChange(self):
        bookID = self.bookIdEdit.text()
        if bookID == "":
            self.bookNameEdit.clear()
            self.authNameEdit.clear()
            self.publisherEdit.clear()
            self.publishTime.clear()
            self.dropNumEdit.clear()
        db = QSqlDatabase.addDatabase("QSQLITE")
        db.setDatabaseName("./db/LibraryManagement.db")
        db.open()
        query = QSqlQuery()
        sql = "select * from Book where BookId='%s'" % (bookID)
        query.exec_(sql)

        if query.next():
            self.bookNameEdit.setText(query.value(0))
            self.authNameEdit.setText(query.value(2))
            self.categoryEdit.setCurrentText(query.value(3))
            self.publisherEdit.setText(query.value(4))
            self.publishTime.setText(query.value(5))

        return


if __name__ == '__main__':
    app = QApplication(sys.argv)
    app.setWindowIcon(QIcon("./iron-man.png"))
    app.setStyleSheet(qdarkstyle.load_stylesheet_pyqt5())
    mainMindow = returnBookDialog("PB15000000")
    mainMindow.show()
    sys.exit(app.exec_())

 四、效果展示

（一）借阅书籍界面展示

• 借阅书籍整体界面

• 输入书号自动返回书籍信息

• 若借阅之后，再次借阅，则提示无法重复借阅消息框

• 若正常情况借阅，则提示借阅成功。

• user表信息中借阅次数+1，用户的正在借阅书籍的总数量+1

• 可借阅量-1，正在借阅中+1

• 借阅学生学号、借阅书号、借阅时间插入user-book操作信息记录表中。

（二）归还书籍界面展示

• 归还书籍界面

• 若查询到未借阅书籍而归还，进行消息提示

• 正常归还书籍，进行消息提示

• 数据库user表，用户的正在借阅书籍的总数量-1

• Book表可借阅书籍数量+1，正借阅中-1

• 在user-book操作信息记录表中插入归还时间。

待后续更新....

标签：--,self,db,pyqt5,setFont,query,借阅,font,书籍
From： https://www.cnblogs.com/LoLong/p/16986297.html