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_())