0925DataGrid

import sys
from PyQt5.QtGui import *
from PyQt5.QtWidgets import *
from PyQt5.QtCore import *
from PyQt5.QtSql import *
import re

def createDataBase():
    db = QSqlDatabase.addDatabase('QSQLITE')
    db.setDatabaseName('./db/925database.db')

    if not db.open():
        return False

    query = QSqlQuery()

    query.exec('create table student(id int primary key, name vchar, sex vchar, age int, deparment vchar)')

    query.exec("insert into student values(1, 'Robert', 'male', 20, 'CS')")
    query.exec("insert into student values(2, 'John', 'male', 22, 'Finance')")
    query.exec("insert into student values(3, 'Jacky', 'male', 26, 'Machinary')")
    query.exec("insert into student values(4, 'Lucy', 'female', 24, 'Law')")
    query.exec("insert into student values(5, 'Ross', 'female', 23, 'Language')")
    query.exec("insert into student values(6, 'Zoe', 'female', 27, 'CS')")
    query.exec("insert into student values(7, 'Bob', 'male', 27, 'Finance')")
    query.exec("insert into student values(8, 'Carry', 'male', 24, 'Machinary')")
    query.exec("insert into student values(9, 'Ben', 'male', 21, 'CS')")
    query.exec("insert into student values(10, 'Sue', 'female', 20, 'Language')")

    return True

class DataGrid(QWidget):
    def __init__(self):
        super(DataGrid, self).__init__()

        self.setWindowTitle('0925DataGrid')
        self.resize(750, 300)

        self.queryModel = None

        self.tableView = None
        self.totalPageLabel = None
        self.currentPageLabel = None
        self.switchPageEdit = None
        self.prevBtn = None
        self.nextBtn = None
        self.switchPageBtn = None

        self.totalRecordLabel = None
        self.currentPage = 0
        self.totalPage = 0
        self.totalRecordCount = 0
        self.PageRecordCount = 5

        self.db = None
        self.initUI()

    def initUI(self):
        self.createWin()
        self.setTableView()

        self.prevBtn.clicked.connect(self.onPrevBtn)
        self.nextBtn.clicked.connect(self.onNextBtn)
        self.switchPageBtn.clicked.connect(self.onSwitchBtn)

    def closeEvent(self, QCloseEvent):
        self.db.close()

        #上方页面控制布局
    def createWin(self):
        self.prevBtn = QPushButton("Previous Page")
        self.nextBtn = QPushButton("Next Page")
        goToLbl = QLabel('转到第')
        page = QLabel('页')

        self.switchPageEdit = QLineEdit()
        self.switchPageEdit.setFixedWidth(40)
        self.switchPageBtn = QPushButton("Go")

        hBox = QHBoxLayout()
        hBox.addWidget(self.prevBtn)
        hBox.addWidget(self.nextBtn)
        hBox.addWidget(goToLbl)
        hBox.addWidget(self.switchPageEdit)
        hBox.addWidget(page)
        hBox.addWidget(self.switchPageBtn)
        hBox.addWidget(QSplitter())

        #中部数据显示窗口布局
        self.tableView = QTableView()
        self.tableView.horizontalHeader().setStretchLastSection(True)
        self.tableView.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)

        #下方状态显示布局
        self.totalPageLabel = QLabel()
        self.totalPageLabel.setFixedWidth(70)
        self.currentPageLabel = QLabel()
        self.currentPageLabel.setFixedWidth(70)
        self.totalRecordLabel = QLabel()
        self.totalRecordLabel.setFixedWidth(70)

        sHBox = QHBoxLayout()
        sHBox.addWidget(self.totalPageLabel)
        sHBox.addWidget(self.currentPageLabel)
        sHBox.addWidget(QSplitter())
        sHBox.addWidget(self.totalRecordLabel)

        #窗口总布局
        mainVBox = QVBoxLayout()
        mainVBox.addLayout(hBox)
        mainVBox.addWidget(self.tableView)
        mainVBox.addLayout(sHBox)

        self.setLayout(mainVBox)

    def setTableView(self):
        print('***Setting table view')
        self.db = QSqlDatabase.addDatabase('QSQLITE')
        self.db.setDatabaseName('./db/925database.db')
        self.db.open()

        self.queryModel = QSqlQueryModel(self)
        self.currentPage = 1
        self.totalRecordCount = self.getTotalRecordCount()
        self.totalPage = self.getPageCount()
        self.updateStatus()
        self.setTotalPageLabel()
        self.setTotalRecordLabel()

        self.recordQuery(0)

        self.tableView.setModel(self.queryModel)

        self.queryModel.setHeaderData(0, Qt.Horizontal, 'ID')
        self.queryModel.setHeaderData(1, Qt.Horizontal, 'Name')
        self.queryModel.setHeaderData(2, Qt.Horizontal, 'Gender')
        self.queryModel.setHeaderData(3, Qt.Horizontal, 'Age')
        self.queryModel.setHeaderData(4, Qt.Horizontal, 'School')

    def getTotalRecordCount(self):
        #从student表中选择所有数据然后生成表
        self.queryModel.setQuery('select * from student')
        rowCount = self.queryModel.rowCount()
        print('Row Count = ' + str(rowCount))
        return rowCount

    def getPageCount(self):
        if self.totalRecordCount % self.PageRecordCount == 0:
            return (self.totalRecordCount/self.PageRecordCount)
        else:
            return (self.totalRecordCount/self.PageRecordCount + 1)

    def recordQuery(self, limitIndex):
        #从第limitIndex行开始，提取self.PageRecoedCount个记录
        szQuery = ("select * from student limit %d, %d" % (limitIndex, self.PageRecordCount))
        print('Query sql = '+ szQuery)
        self.queryModel.setQuery(szQuery)

    def updateStatus(self):
        szCurrentText = ("当前第%d页" %self.currentPage)
        self.currentPageLabel.setText(szCurrentText)

        if self.currentPage == 1:
            self.prevBtn.setEnabled(False)
            self.nextBtn.setEnabled(True)
        elif self.currentPage == self.totalPage:
            self.prevBtn.setEnabled(True)
            self.nextBtn.setEnabled(False)
        else:
            self.prevBtn.setEnabled(True)
            self.nextBtn.setEnabled(True)

    def setTotalRecordLabel(self):
        szTotalRecordText = ("共%d条记录" %self.totalRecordCount)
        print(szTotalRecordText)
        self.totalRecordLabel.setText(szTotalRecordText)

    def setTotalPageLabel(self):
        szTotalPageLabel = ("总共%d页" %self.totalPage)
        print(szTotalPageLabel)
        self.totalPageLabel.setText(szTotalPageLabel)

    def onPrevBtn(self):
        print('***preBtn was clicked')
        limitIndex = (self.currentPage-2) * self.PageRecordCount
        self.recordQuery(limitIndex)
        self.currentPage -= 1
        self.updateStatus()

    def onNextBtn(self):
        print('***nextBtn was clicked')
        limitIndex = self.currentPage * self.PageRecordCount
        self.recordQuery(limitIndex)
        self.currentPage += 1
        self.updateStatus()

    def onSwitchBtn(self):
        szText = self.switchPageEdit.text()
        #原书这里正则表达式(r'^[-+]?[0-9]+\.[0-9]+$')有误，因为有\.表示中间可以说是任何字符，导致数字中间夹杂字母，例如1a1，是可以通过正则测试的
        #这里如果用QIntValidator会不会更好
        pattern = re.compile(r'^[-+]?[0-9]?[0-9]+$')
        match = pattern.match(szText)

        if not match:
            QMessageBox.information(self, "Warining", "Please input number")
            return

        if szText == '':
            QMessageBox.information(self, "Warning", "Please input page you want jump to")
            return

        pageIndex = int(szText)

        if pageIndex>self.totalPage or pageIndex<1:
            QMessageBox.information(self, "Warning", "No such Page")
            return

        limitIndex = (pageIndex - 1) * self.PageRecordCount

        self.recordQuery(limitIndex)
        self.currentPage = pageIndex
        self.updateStatus()


if __name__ == '__main__':
    app = QApplication(sys.argv)
    win = DataGrid()
    win.show()
    sys.exit(app.exec_())