def inventory_used(self):
# 通过进度条表示库存余量
currentQryModel = QSqlQueryModel(self)
currentQryModel.setQuery("select RFID from 工件信息表")
allnum = currentQryModel.rowCount()
progress = int((100 * allnum / 156) + 0.5)
self.ui.progressBar.setValue(progress)
def __init__(self, tag_number):
super().__init__()
print('processing query...')
qry = QSqlQuery(db)
query = 'SELECT name, ename, startno, starttime FROM name WHERE ecard = %i OR ecard2 = %i' % (
tag_number, tag_number)
qry.prepare(query)
qry.exec()
model = QSqlQueryModel()
model.setQuery(qry)
print(model.rowCount())
mapper = QDataWidgetMapper()
form = QFormLayout()
layout = QVBoxLayout()
first_name = QLineEdit()
start_number = QLineEdit()
form.addRow(QLabel("Startnummer"), start_number)
form.addRow(QLabel("Fornavn"), first_name)
mapper.setModel(model)
mapper.addMapping(first_name, 0)
mapper.addMapping(start_number, 2)
mapper.toFirst()
layout.addLayout(form)
widget = QWidget()
widget.setLayout(layout)
self.setCentralWidget(widget)
#controls = QHBoxLayout()
'''
def dbToTableView(self,commandSQL):
try:
QApplication.processEvents()
if QSqlDatabase.contains("qt_sql_default_connection"):
db = QSqlDatabase.database("qt_sql_default_connection")
else:
db = QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName(self.dbPath)
db.open()
projectModel = QSqlQueryModel()
projectModel.setQuery(commandSQL, db)
projectModel.setHeaderData(0, Qt.Horizontal, 'پلاک')
projectModel.setHeaderData(1, Qt.Horizontal, 'متقاضی')
projectModel.setHeaderData(2, Qt.Horizontal, 'نوع انجام کار')
projectModel.setHeaderData(3, Qt.Horizontal, 'تاریخ بازدید')
projectModel.setHeaderData(4, Qt.Horizontal, 'ساعت بازدید')
projectModel.setHeaderData(5, Qt.Horizontal, 'نقشه بردار')
projectModel.setHeaderData(6, Qt.Horizontal, 'نماینده')
projectModel.setHeaderData(7, Qt.Horizontal, 'تاریخ ثبت')
projectModel.setHeaderData(8, Qt.Horizontal, 'توضیحات')
self.ui.tableView_result.setModel(projectModel)
self.rowCount = projectModel.rowCount()
self.tableResult = projectModel
db.close()
QApplication.processEvents()
except:
self.errorM('مشکل در ارتباط با دیتابیس\n {}')
def show_demo(self):
def test(x):
print(len(x))
# Создаем модель
sqm = QSqlQueryModel(parent=self)
sqm.setQuery(
'SELECT id, lastName, firstName, patrName, birthDate, sex, notes '
'FROM Client WHERE deleted = 0 AND deathDate IS NULL '
'LIMIT 20')
# Задаем заголовки для столбцов модели
sqm.setHeaderData(1, Qt.Horizontal, 'Фамилия')
sqm.setHeaderData(2, Qt.Horizontal, 'Имя')
sqm.setHeaderData(3, Qt.Horizontal, 'Отчество')
sqm.setHeaderData(4, Qt.Horizontal, 'Дата рождения')
sqm.setHeaderData(5, Qt.Horizontal, 'Пол')
sqm.setHeaderData(6, Qt.Horizontal, 'Примечание')
# Задаем для таблицы только что созданную модель
self.clients_table.setModel(sqm)
self.clients_table.hideColumn(0)
self.clients_table.resizeColumnsToContents()
self.clients_table.horizontalHeader().setStretchLastSection(True)
self.clients_table.horizontalHeader().setHighlightSections(False)
self.clients_table.verticalHeader().hide()
self.clients_table.clearSelection()
self.lbl_suitable_customers_count.setText(str(sqm.rowCount()))
self.lbl_selected_customers_count.setText('0')
self.clients_table.selectionModel().selectionChanged.connect(
self.show_selected_customers_count)
def thing_by_id(self, idx):
query = QSqlQueryModel()
query.setQuery("select * from things "\
"where id={}"\
.format(idx),
db=self.db)
if query.rowCount() == 0:
return None
return self.thing_by_sqlrecord(query.record(0))
def thing_by_name(self, name):
query = QSqlQueryModel()
query.setQuery("select * from things "\
"where name='{}'"\
.format(name),
db=self.db)
if query.rowCount() == 0:
return None
return self.thing_by_sqlrecord(query.record(0))
def rules(self):
query = QSqlQueryModel()
query.setQuery("select * from rules")
if query.lastError().isValid():
print(query.lastError().text(), file=sys.stderr)
ret = []
for i in range(query.rowCount()):
rule = self.rule_by_sqlrecord(query.record(i))
ret.append(rule)
return ret
def getTotalRecordCount(tiku, ti_bao):
db = QSqlDatabase.addDatabase('QSQLITE')
db.setDatabaseName('{}'.format(tiku))
if not db.open():
print("无法建立")
return False
queryModel = QSqlQueryModel()
queryModel.setQuery("select * from {}".format(ti_bao))
rowCount = queryModel.rowCount()
return rowCount
def rules_with_thing(self, thing):
query = QSqlQueryModel()
query.setQuery("select * from rules "\
"where {} = ANY(things)"\
.format(thing.idx))
if query.lastError().isValid():
print(query.lastError().text(), file=sys.stderr)
ret = []
for i in range(query.rowCount()):
rule = self.rule_by_sqlrecord(query.record(i))
ret.append(rule)
return ret
def getMaxbatch(self, label):
myModel = QSqlQueryModel(self)
if label == 'enter':
myModel.setQuery("select 批次 from 入库记录表")
elif label == 'out':
myModel.setQuery("select 批次 from 出库记录表")
n = myModel.rowCount()
if myModel.record(0).isNull('批次') == True: # 检测入库记录里的最大批次
max_batch = 0
else:
max_batch = myModel.record(n - 1).value('批次') # 批次按顺序排列，查找最大批次
print('共有%d条记录，最大批次为%d' % (n, max_batch))
return max_batch
def things(self, types):
query = QSqlQueryModel()
if types is not None:
query.setQuery("select * from things "\
"where type=ANY({})"\
.format(_array_string(types)))
else:
query.setQuery("select * from things")
if query.lastError().isValid():
print(query.lastError().text(), file=sys.stderr)
ret = []
for i in range(query.rowCount()):
thing = self.thing_by_sqlrecord(query.record(i))
ret.append(thing)
return ret
def getDestination_id(self):
# สร้างมาเพื่อให้ return ค่า des_id ในตาราง destination กลับไป
temp = self.comboBox.currentText()
desModel = QSqlQueryModel()
selectQuery = QSqlQuery()
selectQuery.prepare('SELECT des_id,des_name From destination')
if selectQuery.exec():
desModel.setQuery(selectQuery)
for i in range(desModel.rowCount()):
if temp == desModel.index(i, 1).data():
return desModel.index(i, 0).data()
else:
pass
else:
print('SELECT FALSE = ' + selectQuery.lastError().text())
class UserData(QWidget):
def __init__(self, parent=None):
super(UserData, self).__init__(parent)
# Declare Database Connections
self.db = None
# Layout Manager
self.layout = QVBoxLayout()
# Query Model
self.queryModel = QSqlQueryModel()
# Table View
self.tableView = QTableView()
self.tableView.setModel(self.queryModel)
self.initUI()
self.initializedModel()
def initUI(self):
self.tableView.horizontalHeader().setStretchLastSection(True)
self.tableView.horizontalHeader().setSectionResizeMode(
QHeaderView.Stretch)
self.layout.addWidget(self.tableView)
self.setLayout(self.layout)
self.setWindowTitle("UserData")
self.resize(1500, 800)
def initializedModel(self):
self.db = QSqlDatabase.addDatabase("QSQLITE")
self.db.setDatabaseName("project.db")
if not self.db.open():
return False
self.queryModel.setHeaderData(0, Qt.Horizontal, "First Name")
self.queryModel.setHeaderData(1, Qt.Horizontal, "Last Name")
self.queryModel.setHeaderData(2, Qt.Horizontal, "DOB")
self.queryModel.setHeaderData(3, Qt.Horizontal, "Gender")
self.queryModel.setHeaderData(4, Qt.Horizontal, "State")
self.queryModel.setHeaderData(5, Qt.Horizontal, "Blood Group")
self.queryModel.setHeaderData(6, Qt.Horizontal, "Mobile")
self.queryModel.setHeaderData(7, Qt.Horizontal, "Email ID")
self.queryModel.setHeaderData(8, Qt.Horizontal, "Donation Details")
self.queryModel.setHeaderData(9, Qt.Horizontal, "Medical Details")
self.queryModel.setHeaderData(10, Qt.Horizontal, "Username")
self.queryModel.setHeaderData(11, Qt.Horizontal, "Password")
# Get all the records of the table
sql = "SELECT * FROM user"
self.queryModel.setQuery(sql, self.db)
self.totalRecordCount = self.queryModel.rowCount()
def queryCache(self, SPath, manageLocalStorage):
projectModel = QSqlQueryModel()
if self.db.open():
query = QSqlQuery(self.db)
# print("in queryCache")
# print(SPath)
query.prepare(
"select SPathRec, Title, Artist from cache WHERE SPath=:SPath "
)
query.bindValue(":SPath", SPath)
query.exec_()
projectModel.setQuery(query)
# print("num of row returned queryCache:")
# print(query.numRowsAffected())
# print("query row count")
if projectModel.rowCount() == 0:
print("get recommendation, nothing is in Cache for this song")
recommendedSongsPathList = []
getRecom = GetRecommendation(manageLocalStorage)
relevantSongDict = getRecom.fetchRelevantSongOffline(SPath)
if SPath:
recommendedSongsPathList = getRecom.predict(
SPath, relevantSongDict)
else:
print("problem with SongPath so can't call predict")
# if playing song is also recommended then remove it
if SPath in recommendedSongsPathList:
recommendedSongsPathList.remove(SPath)
# for item in recommendedSongsPathList:
# print(item)
# build cache
if self.writeCache(recommendedSongsPathList, SPath):
print("re Querying ")
projectModel.clear()
requery = QSqlQuery(self.db)
requery.prepare(
"select SPathRec, Title, Artist from cache WHERE SPath=:SPath "
)
requery.bindValue(":SPath", SPath)
requery.exec_()
projectModel.setQuery(requery)
# print("row Count After re query ")
# print(projectModel.rowCount())
else:
print("Query failed")
return projectModel
def Absolute_statis(self, label):
myModel = QSqlQueryModel(self)
if label == 'current':
myModel.setQuery("select 种类 from 工件信息表")
elif label == 'enter':
myModel.setQuery("select 种类 from 入库记录表")
elif label == 'out':
myModel.setQuery("select 种类 from 出库记录表")
allnum = myModel.rowCount()
all_dict = {}
for i in range(allnum):
rec = myModel.record(i)
if rec.value('种类') in all_dict:
all_dict[rec.value('种类')] += 1
else:
all_dict[rec.value('种类')] = 1
return all_dict
class RequestData(QWidget):
def __init__(self, parent=None):
super(RequestData, self).__init__(parent)
# Declare Database Connections
self.db = None
# Layout Manager
self.layout = QVBoxLayout()
# Query Model
self.queryModel = QSqlQueryModel()
# Table View
self.tableView = QTableView()
self.tableView.setModel(self.queryModel)
self.initUI()
self.initializedModel()
def initUI(self):
self.tableView.horizontalHeader().setStretchLastSection(True)
self.tableView.horizontalHeader().setSectionResizeMode(
QHeaderView.Stretch)
self.layout.addWidget(self.tableView)
self.setLayout(self.layout)
self.setWindowTitle("RequestData")
self.resize(1200, 600)
def initializedModel(self):
self.db = QSqlDatabase.addDatabase("QSQLITE")
self.db.setDatabaseName("project.db")
if not self.db.open():
return False
self.queryModel.setHeaderData(0, Qt.Horizontal, "Receiver Name")
self.queryModel.setHeaderData(3, Qt.Horizontal, "DOB")
self.queryModel.setHeaderData(2, Qt.Horizontal, "Gender")
self.queryModel.setHeaderData(3, Qt.Horizontal, "Blood Group")
self.queryModel.setHeaderData(3, Qt.Horizontal, "City")
self.queryModel.setHeaderData(3, Qt.Horizontal, "State")
self.queryModel.setHeaderData(3, Qt.Horizontal, "Mobile")
# Get all the records of the table
sql = "SELECT * FROM requests"
self.queryModel.setQuery(sql, self.db)
self.totalRecordCount = self.queryModel.rowCount()
def closeEvent(self, event):
self.db.close()
def rules_by_tids(self, t_ids, exact):
query = QSqlQueryModel()
if exact:
query.setQuery("select * from rules "\
"where things={}"\
.format(_array_string(t_ids)),
db=self.db)
else:
query.setQuery("select * from rules "\
"where things @>{}"\
.format(_array_string(t_ids)),
db=self.db)
if query.lastError().isValid():
print(query.lastError().text(), file=sys.stderr)
ret = []
for i in range(query.rowCount()):
rule = self.rule_by_sqlrecord(query.record(i))
ret.append(rule)
return ret
def dbToTableView(self, commandSQL):
try:
QApplication.processEvents()
if QSqlDatabase.contains("qt_sql_default_connection"):
db = QSqlDatabase.database("qt_sql_default_connection")
else:
db = QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName(self.dbPath)
db.open()
projectModel = QSqlQueryModel()
projectModel.setQuery(commandSQL, db)
projectModel.setHeaderData(0, Qt.Horizontal, 'پلاک')
projectModel.setHeaderData(1, Qt.Horizontal, 'بخش')
projectModel.setHeaderData(2, Qt.Horizontal, 'تعداد جلد')
projectModel.setHeaderData(3, Qt.Horizontal, 'تعداد صفحات')
projectModel.setHeaderData(4, Qt.Horizontal, 'نوع')
projectModel.setHeaderData(5, Qt.Horizontal, 'همکار تقاضا کننده')
projectModel.setHeaderData(6, Qt.Horizontal, 'تحویل گیرنده')
projectModel.setHeaderData(7, Qt.Horizontal, 'علت درخواست')
projectModel.setHeaderData(8, Qt.Horizontal, 'توضیحات')
projectModel.setHeaderData(9, Qt.Horizontal, 'تاریخ تحویل')
projectModel.setHeaderData(10, Qt.Horizontal, 'ساعت تحویل')
projectModel.setHeaderData(11, Qt.Horizontal, 'تاریخ بازگشت')
projectModel.setHeaderData(12, Qt.Horizontal, 'ساعت بازگشت')
self.ui.tableView_result.setModel(projectModel)
# self.ui.tableView_result.show()
self.rowCount = projectModel.rowCount()
self.tableResult = projectModel
db.close()
QApplication.processEvents()
except Exception as e:
if e.message != '':
errormsg = e.message
else:
errormsg = " "
self.errorM('مشکل در ارتباط با دیتابیس\n {}'.format(errormsg))
def write_csv(model: QtSql.QSqlQueryModel):
filename, _ = QFileDialog \
.getSaveFileName(None, "Export data to CSV",
".",
CSV_FILE_FILTER, CSV_FILE_FILTER)
data = list()
with open(filename, "a") as f:
# process headers
for i in range(0, model.columnCount()):
header = str(
model.headerData(i, QtCore.Qt.Horizontal,
QtCore.Qt.DisplayRole))
data.append('"' + header + '"')
# write headers
f.write(";".join(data))
f.write("\n")
# process rows
for i in range(model.rowCount()):
data.clear()
for j in range(model.columnCount()):
cell = str(model.data(model.index(i, j)))
data.append('"' + cell + '"')
f.write(";".join(data))
f.write("\n")
def getCurrentNum(self):
currentQryModel = QSqlQueryModel(self)
currentQryModel.setQuery("select RFID from 工件信息表")
allnum = currentQryModel.rowCount()
return allnum, currentQryModel
class DataGrid(QWidget):
def __init__(self):
super().__init__()
self.setWindowTitle("分页查询例子")
self.resize(750,300)
# 查询模型
self.queryModel = None
# 数据表
self.tableView = None
# 总数页文本
self.totalPageLabel = None
# 当前页文本
self.currentPageLabel = None
# 转到页输入框
self.switchPageLineEdit = None
# 前一页按钮
self.prevButton = None
# 后一页按钮
self.nextButton = None
# 转到页按钮
self.switchPageButton = None
# 当前页
self.currentPage = 0
# 总页数
self.totalPage = 0
# 总记录数
self.totalRecrodCount = 0
# 每页显示记录数
self.PageRecordCount = 5
self.initUI()
def initUI(self):
# 创建窗口
self.createWindow()
# 设置表格
self.setTableView()
# 信号槽连接
self.prevButton.clicked.connect(self.onPrevButtonClick )
self.nextButton.clicked.connect(self.onNextButtonClick )
self.switchPageButton.clicked.connect(self.onSwitchPageButtonClick )
# 创建数据库
# 创建窗口
def createWindow(self):
# 操作布局
operatorLayout = QHBoxLayout()
self.prevButton = QPushButton("前一页")
self.nextButton = QPushButton("后一页")
self.switchPageButton = QPushButton("Go")
self.switchPageLineEdit = QLineEdit()
self.switchPageLineEdit.setFixedWidth(40)
switchPage = QLabel("转到第")
page = QLabel("页")
operatorLayout.addWidget(self.prevButton)
operatorLayout.addWidget(self.nextButton)
operatorLayout.addWidget(switchPage)
operatorLayout.addWidget(self.switchPageLineEdit)
operatorLayout.addWidget(page)
operatorLayout.addWidget(self.switchPageButton)
operatorLayout.addWidget( QSplitter())
# 状态布局
statusLayout = QHBoxLayout()
self.totalPageLabel = QLabel()
self.totalPageLabel.setFixedWidth(70)
self.currentPageLabel = QLabel()
self.currentPageLabel.setFixedWidth(70)
self.totalRecordLabel = QLabel()
self.totalRecordLabel.setFixedWidth(70)
statusLayout.addWidget(self.totalPageLabel)
statusLayout.addWidget(self.currentPageLabel)
statusLayout.addWidget( QSplitter() )
statusLayout.addWidget(self.totalRecordLabel)
# 设置表格属性
self.tableView = QTableView()
# 表格宽度的自适应调整
self.tableView.horizontalHeader().setStretchLastSection(True)
self.tableView.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
# 创建界面
mainLayout = QVBoxLayout(self);
mainLayout.addLayout(operatorLayout);
mainLayout.addWidget(self.tableView);
mainLayout.addLayout(statusLayout);
self.setLayout(mainLayout)
# 设置表格
def setTableView(self):
print('*** step2 SetTableView' )
# 声明查询模型
self.queryModel = QSqlQueryModel(self)
# 设置当前页
self.currentPage = 1;
# 得到总记录数
self.totalRecrodCount = self.getTotalRecordCount()
# 得到总页数
self.totalPage = self.getPageCount()
# 刷新状态
self.updateStatus()
# 设置总页数文本
self.setTotalPageLabel()
# 设置总记录数
self.setTotalRecordLabel()
# 记录查询
self.recordQuery(0)
# 设置模型
self.tableView.setModel(self.queryModel)
print('totalRecrodCount=' + str(self.totalRecrodCount) )
print('totalPage=' + str(self.totalPage) )
# 设置表格表头
self.queryModel.setHeaderData(0,Qt.Horizontal,"编号")
self.queryModel.setHeaderData(1,Qt.Horizontal,"姓名")
self.queryModel.setHeaderData(2,Qt.Horizontal,"性别")
self.queryModel.setHeaderData(3,Qt.Horizontal,"年龄")
self.queryModel.setHeaderData(4,Qt.Horizontal,"院系")
# 得到记录数
def getTotalRecordCount(self):
self.queryModel.setQuery("select * from student")
rowCount = self.queryModel.rowCount()
print('rowCount=' + str(rowCount) )
return rowCount
# 得到页数
def getPageCount(self):
if self.totalRecrodCount % self.PageRecordCount == 0 :
return (self.totalRecrodCount / self.PageRecordCount )
else :
return (self.totalRecrodCount / self.PageRecordCount + 1)
# 记录查询
def recordQuery(self, limitIndex ):
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.prevButton.setEnabled( False )
self.nextButton.setEnabled( True )
elif self.currentPage == self.totalPage :
self.prevButton.setEnabled( True )
self.nextButton.setEnabled( False )
else :
self.prevButton.setEnabled( True )
self.nextButton.setEnabled( True )
# 设置总数页文本
def setTotalPageLabel(self):
szPageCountText = ("总共%d页" % self.totalPage )
self.totalPageLabel.setText(szPageCountText)
# 设置总总记录数
def setTotalRecordLabel(self):
szTotalRecordText = ("共%d条" % self.totalRecrodCount )
print('*** setTotalRecordLabel szTotalRecordText=' + szTotalRecordText )
self.totalRecordLabel.setText(szTotalRecordText)
# 前一页按钮按下
def onPrevButtonClick(self):
print('*** onPrevButtonClick ');
limitIndex = (self.currentPage - 2) * self.PageRecordCount
self.recordQuery( limitIndex)
self.currentPage -= 1
self.updateStatus()
# 后一页按钮按下
def onNextButtonClick(self):
print('*** onNextButtonClick ');
limitIndex = self.currentPage * self.PageRecordCount
self.recordQuery( limitIndex)
self.currentPage += 1
self.updateStatus()
# 转到页按钮按下
def onSwitchPageButtonClick(self):
# 得到输入字符串
szText = self.switchPageLineEdit.text()
#数字正则表达式
pattern = re.compile(r'^[-+]?[0-9]+\.[0-9]+$')
match = pattern.match(szText)
# 判断是否为数字
if not match :
QMessageBox.information(self, "提示", "请输入数字" )
return
# 是否为空
if szText == '' :
QMessageBox.information(self, "提示" , "请输入跳转页面" )
return
#得到页数
pageIndex = int(szText)
#判断是否有指定页
if pageIndex > self.totalPage or pageIndex < 1 :
QMessageBox.information(self, "提示", "没有指定的页面，请重新输入" )
return
#得到查询起始行号
limitIndex = (pageIndex-1) * self.PageRecordCount
#记录查询
self.recordQuery(limitIndex);
#设置当前页
self.currentPage = pageIndex
#刷新状态
self.updateStatus();
class QmyMainWindow(QMainWindow):
def __init__(self, parent=None):
super().__init__(parent) #调用父类构造函数，创建窗体
self.ui = Ui_MainWindow() #创建UI对象
self.ui.setupUi(self) #构造UI界面
self.setCentralWidget(self.ui.splitter)
# tableView显示属性设置
self.ui.tableView.setSelectionBehavior(QAbstractItemView.SelectRows)
self.ui.tableView.setSelectionMode(QAbstractItemView.SingleSelection)
self.ui.tableView.setAlternatingRowColors(True)
self.ui.tableView.verticalHeader().setDefaultSectionSize(22)
self.ui.tableView.horizontalHeader().setDefaultSectionSize(60)
## self.ui.tableView.resizeColumnsToContents()
## ==============自定义功能函数============
def __getFieldNames(self): ##获取所有字段名称
emptyRec = self.qryModel.record() #获取空记录，只有字段名
self.fldNum = {} #字段名与序号的字典
for i in range(emptyRec.count()):
fieldName = emptyRec.fieldName(i)
self.fldNum.setdefault(fieldName)
self.fldNum[fieldName] = i
print(self.fldNum)
def __openTable(self): ##查询数据
self.qryModel = QSqlQueryModel(self)
self.qryModel.setQuery(
'''SELECT empNo, Name, Gender, Birthday, Province,
Department, Salary FROM employee ORDER BY empNo'''
)
if self.qryModel.lastError().isValid():
QMessageBox.critical(
self, "错误",
"数据表查询错误,错误信息\n" + self.qryModel.lastError().text())
return
self.ui.statusBar.showMessage("记录条数：%d" % self.qryModel.rowCount())
self.__getFieldNames() #获取字段名和序号
##设置字段显示名，直接使用序号
self.qryModel.setHeaderData(0, Qt.Horizontal, "工号")
self.qryModel.setHeaderData(1, Qt.Horizontal, "姓名")
self.qryModel.setHeaderData(2, Qt.Horizontal, "性别")
self.qryModel.setHeaderData(3, Qt.Horizontal, "出生日期")
self.qryModel.setHeaderData(4, Qt.Horizontal, "省份")
self.qryModel.setHeaderData(5, Qt.Horizontal, "部门")
self.qryModel.setHeaderData(6, Qt.Horizontal, "工资")
## self.qryModel.setHeaderData(self.fldNum["empNo"], Qt.Horizontal, "工号")
## self.qryModel.setHeaderData(self.fldNum["Name"], Qt.Horizontal, "姓名")
## self.qryModel.setHeaderData(self.fldNum["Gender"], Qt.Horizontal, "性别")
## self.qryModel.setHeaderData(self.fldNum["Birthday"], Qt.Horizontal, "出生日期")
## self.qryModel.setHeaderData(self.fldNum["Province"], Qt.Horizontal, "省份")
## self.qryModel.setHeaderData(self.fldNum["Department"], Qt.Horizontal, "部门")
## self.qryModel.setHeaderData(self.fldNum["Salary"], Qt.Horizontal, "工资")
##创建界面组件与数据模型的字段之间的数据映射
self.mapper = QDataWidgetMapper()
self.mapper.setModel(self.qryModel) #设置数据模型
## self.mapper.setSubmitPolicy(QDataWidgetMapper.AutoSubmit)
##界面组件与qryModel的具体字段之间的联系
## self.mapper.addMapping(self.ui.dbSpinEmpNo, self.fldNum["empNo"])
## self.mapper.addMapping(self.ui.dbEditName, self.fldNum["Name"])
## self.mapper.addMapping(self.ui.dbComboSex, self.fldNum["Gender"])
## self.mapper.addMapping(self.ui.dbEditBirth, self.fldNum["Birthday"])
## self.mapper.addMapping(self.ui.dbComboProvince, self.fldNum["Province"] )
## self.mapper.addMapping(self.ui.dbComboDep, self.fldNum["Department"] )
## self.mapper.addMapping(self.ui.dbSpinSalary,self.fldNum["Salary"] )
self.mapper.addMapping(self.ui.dbSpinEmpNo, 0)
self.mapper.addMapping(self.ui.dbEditName, 1)
self.mapper.addMapping(self.ui.dbComboSex, 2)
self.mapper.addMapping(self.ui.dbEditBirth, 3)
self.mapper.addMapping(self.ui.dbComboProvince, 4)
self.mapper.addMapping(self.ui.dbComboDep, 5)
self.mapper.addMapping(self.ui.dbSpinSalary, 6)
self.mapper.toFirst() #移动到首记录
self.selModel = QItemSelectionModel(self.qryModel) #关联选择模型
self.selModel.currentRowChanged.connect(
self.do_currentRowChanged) #选择行变化时
self.ui.tableView.setModel(self.qryModel) #设置数据模型
self.ui.tableView.setSelectionModel(self.selModel) #设置选择模型
self.ui.actOpenDB.setEnabled(False)
def __refreshTableView(self): ##刷新tableView显示
index = self.mapper.currentIndex()
curIndex = self.qryModel.index(index, 1) #QModelIndex
self.selModel.clearSelection() #清空选择项
self.selModel.setCurrentIndex(curIndex, QItemSelectionModel.Select)
## ==========由connectSlotsByName() 自动连接的槽函数==================
@pyqtSlot() ##“打开数据库”按钮
def on_actOpenDB_triggered(self):
dbFilename, flt = QFileDialog.getOpenFileName(
self, "选择数据库文件", "", "SQL Lite数据库(*.db *.db3)")
if (dbFilename == ''):
return
#打开数据库
self.DB = QSqlDatabase.addDatabase("QSQLITE") #添加 SQL LITE数据库驱动
self.DB.setDatabaseName(dbFilename) #设置数据库名称
## DB.setHostName()
## DB.setUserName()
## DB.setPassword()
if self.DB.open(): #打开数据库
self.__openTable() #打开数据表
else:
QMessageBox.warning(self, "错误", "打开数据库失败")
@pyqtSlot() ##首记录
def on_actRecFirst_triggered(self):
self.mapper.toFirst()
self.__refreshTableView()
@pyqtSlot() ##前一记录
def on_actRecPrevious_triggered(self):
self.mapper.toPrevious()
self.__refreshTableView()
@pyqtSlot() ##后一条记录
def on_actRecNext_triggered(self):
self.mapper.toNext()
self.__refreshTableView()
@pyqtSlot() ##最后一条记录
def on_actRecLast_triggered(self):
self.mapper.toLast()
self.__refreshTableView()
## =============自定义槽函数===============================
def do_currentRowChanged(self, current, previous): ##记录移动时触发
if (current.isValid() == False):
self.ui.dbLabPhoto.clear() #清除图片显示
return
self.mapper.setCurrentIndex(current.row()) #更新数据映射的行号
first = (current.row() == 0) #是否首记录
last = (current.row() == self.qryModel.rowCount() - 1) #是否尾记录
self.ui.actRecFirst.setEnabled(not first) #更新使能状态
self.ui.actRecPrevious.setEnabled(not first)
self.ui.actRecNext.setEnabled(not last)
self.ui.actRecLast.setEnabled(not last)
curRec = self.qryModel.record(current.row()) #获取当前记录,QSqlRecord类型
empNo = curRec.value("EmpNo") #不需要加 toInt()函数
query = QSqlQuery(self.DB)
query.prepare(
'''SELECT EmpNo, Memo, Photo FROM employee WHERE EmpNo = :ID''')
query.bindValue(":ID", empNo)
## if not query.exec_(): #注意，在PyQt5.11.2之前的版本里只能使用exec_()函数
if not query.exec(
): #注意，在PyQt5.11.2添加了遗漏的overload型exec()函数，在PyQt5.11.2里没问题了
QMessageBox.critical(self, "错误",
"执行SQL语句错误\n" + query.lastError().text())
return
else:
query.first()
picData = query.value("Photo")
if (picData == None): #图片字段内容为空
self.ui.dbLabPhoto.clear()
else: #显示照片
pic = QPixmap()
pic.loadFromData(picData)
W = self.ui.dbLabPhoto.size().width()
self.ui.dbLabPhoto.setPixmap(pic.scaledToWidth(W))
memoData = query.value("Memo") #显示备注
self.ui.dbEditMemo.setPlainText(memoData)
class DataGrid(QWidget):
def __init__(self):
super().__init__()
self.setWindowTitle("分頁查詢範例")
self.resize(750, 300)
# 查詢模型
self.queryModel = None
# 資料表
self.tableView = None
# 總頁數
self.totalPageLabel = None
# 目前頁
self.currentPageLabel = None
# 轉到頁輸入框
self.switchPageLineEdit = None
# 前一頁按鈕
self.prevButton = None
# 後一頁按鈕
self.nextButton = None
# 轉到頁按鈕
self.switchPageButton = None
# 目前頁
self.currentPage = 0
# 總頁數
self.totalPage = 0
# 總記錄數
self.totalRecrodCount = 0
# 每頁顯示記錄數
self.PageRecordCount = 5
self.initUI()
def initUI(self):
# 建立視窗
self.createWindow()
# 設定表格
self.setTableView()
# 訊號/槽連接
self.prevButton.clicked.connect(self.onPrevButtonClick)
self.nextButton.clicked.connect(self.onNextButtonClick)
self.switchPageButton.clicked.connect(self.onSwitchPageButtonClick)
# 建立視窗
def createWindow(self):
# 操作佈局
operatorLayout = QHBoxLayout()
self.prevButton = QPushButton("前一頁")
self.nextButton = QPushButton("後一頁")
self.switchPageButton = QPushButton("Go")
self.switchPageLineEdit = QLineEdit()
self.switchPageLineEdit.setFixedWidth(40)
switchPage = QLabel("轉到第")
page = QLabel("頁")
operatorLayout.addWidget(self.prevButton)
operatorLayout.addWidget(self.nextButton)
operatorLayout.addWidget(switchPage)
operatorLayout.addWidget(self.switchPageLineEdit)
operatorLayout.addWidget(page)
operatorLayout.addWidget(self.switchPageButton)
operatorLayout.addWidget(QSplitter())
# 狀態佈局
statusLayout = QHBoxLayout()
self.totalPageLabel = QLabel()
self.totalPageLabel.setFixedWidth(70)
self.currentPageLabel = QLabel()
self.currentPageLabel.setFixedWidth(70)
self.totalRecordLabel = QLabel()
self.totalRecordLabel.setFixedWidth(70)
statusLayout.addWidget(self.totalPageLabel)
statusLayout.addWidget(self.currentPageLabel)
statusLayout.addWidget(QSplitter())
statusLayout.addWidget(self.totalRecordLabel)
# 設定表格屬性
self.tableView = QTableView()
# 表格寬度的自我調整
self.tableView.horizontalHeader().setStretchLastSection(True)
self.tableView.horizontalHeader().setSectionResizeMode(
QHeaderView.Stretch)
# 建立介面
mainLayout = QVBoxLayout(self)
mainLayout.addLayout(operatorLayout)
mainLayout.addWidget(self.tableView)
mainLayout.addLayout(statusLayout)
self.setLayout(mainLayout)
# 設定表格
def setTableView(self):
print('*** step2 SetTableView')
# 宣告查詢模型
self.queryModel = QSqlQueryModel(self)
# 設定目前頁
self.currentPage = 1
# 取得總記錄數
self.totalRecrodCount = self.getTotalRecordCount()
# 取得總頁數
self.totalPage = self.getPageCount()
# 刷新狀態
self.updateStatus()
# 設定總頁數標籤
self.setTotalPageLabel()
# 設定總記錄數標籤
self.setTotalRecordLabel()
# 記錄查詢
self.recordQuery(0)
# 設定模型
self.tableView.setModel(self.queryModel)
print('totalRecrodCount=' + str(self.totalRecrodCount))
print('totalPage=' + str(self.totalPage))
# 設定表格表頭
self.queryModel.setHeaderData(0, Qt.Horizontal, "編號")
self.queryModel.setHeaderData(1, Qt.Horizontal, "姓名")
self.queryModel.setHeaderData(2, Qt.Horizontal, "性別")
self.queryModel.setHeaderData(3, Qt.Horizontal, "年齡")
self.queryModel.setHeaderData(4, Qt.Horizontal, "院系")
# 取得記錄數
def getTotalRecordCount(self):
self.queryModel.setQuery("select * from student")
rowCount = self.queryModel.rowCount()
print('rowCount=' + str(rowCount))
return rowCount
# 取得頁數
def getPageCount(self):
if self.totalRecrodCount % self.PageRecordCount == 0:
return (self.totalRecrodCount / self.PageRecordCount)
else:
return (self.totalRecrodCount / self.PageRecordCount + 1)
# 記錄查詢
def recordQuery(self, limitIndex):
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.prevButton.setEnabled(False)
self.nextButton.setEnabled(True)
elif self.currentPage == self.totalPage:
self.prevButton.setEnabled(True)
self.nextButton.setEnabled(False)
else:
self.prevButton.setEnabled(True)
self.nextButton.setEnabled(True)
# 設取總數頁標籤
def setTotalPageLabel(self):
szPageCountText = ("總共%d頁" % self.totalPage)
self.totalPageLabel.setText(szPageCountText)
# 設定總記錄數標籤
def setTotalRecordLabel(self):
szTotalRecordText = ("共%d筆" % self.totalRecrodCount)
print('*** setTotalRecordLabel szTotalRecordText=' + szTotalRecordText)
self.totalRecordLabel.setText(szTotalRecordText)
# 按下前一頁按鈕
def onPrevButtonClick(self):
print('*** onPrevButtonClick ')
limitIndex = (self.currentPage - 2) * self.PageRecordCount
self.recordQuery(limitIndex)
self.currentPage -= 1
self.updateStatus()
# 按下後一頁按鈕
def onNextButtonClick(self):
print('*** onNextButtonClick ')
limitIndex = self.currentPage * self.PageRecordCount
self.recordQuery(limitIndex)
self.currentPage += 1
self.updateStatus()
# 按下轉到頁按鈕
def onSwitchPageButtonClick(self):
# 取得輸入字串
szText = self.switchPageLineEdit.text()
# 數字規則運算式
pattern = re.compile(r'^[-+]?[0-9]+\.[0-9]+$')
match = pattern.match(szText)
# 判斷是否為數字
if not match:
QMessageBox.information(self, "提示", "請輸入數字")
return
# 是否為空
if szText == '':
QMessageBox.information(self, "提示", "請輸入跳越頁面")
return
# 取得頁數
pageIndex = int(szText)
# 判斷是否有指定頁
if pageIndex > self.totalPage or pageIndex < 1:
QMessageBox.information(self, "提示", "沒有指定的頁面，請重新輸入")
return
# 取得查詢起始列號
limitIndex = (pageIndex - 1) * self.PageRecordCount
# 記錄查詢
self.recordQuery(limitIndex)
# 設定目前頁
self.currentPage = pageIndex
# 刷新狀態
self.updateStatus()
class QmyMainWindow(QtWidgets.QMainWindow):
def __init__(self, parent=None):
super().__init__(parent)
self.ui = Ui_MainWindow()
self.ui.setupUi(self)
self.setCentralWidget(self.ui.splitter)
self.ui.tableView.setSelectionBehavior(QAbstractItemView.SelectRows)
self.ui.tableView.setSelectionMode(QAbstractItemView.SingleSelection)
self.ui.tableView.setAlternatingRowColors(True)
self.ui.tableView.verticalHeader().setDefaultSectionSize(22)
self.ui.tableView.horizontalHeader().setDefaultSectionSize(60)
@pyqtSlot()
def on_actOpenDB_triggered(self):
dbFilename, flt = QFileDialog.getOpenFileName(
self, "选择数据库文件", "", "SQL Lite数据库(*.db *.db3)")
if (dbFilename == ''):
return
self.DB = QSqlDatabase.addDatabase("QSQLITE")
self.DB.setDatabaseName(dbFilename)
if self.DB.open():
self.__openTable()
else:
QMessageBox.warning(self, "错误", "打开数据库失败")
def __openTable(self):
self.qryModel = QSqlQueryModel(self)
self.qryModel.setQuery(
'''SELECT empNo, Name, Gender, Birthday, Province, Department, Salary FROM employee ORDER BY empNo'''
)
if self.qryModel.lastError().isValid():
QMessageBox.critical(
self, "错误",
"数据表查询错误，错误信息\n" + self.qryModel.lastError().text())
return
self.ui.statusBar.showMessage("记录条数:%d" % self.qryModel.rowCount())
self.__getFieldNames()
self.qryModel.setHeaderData(0, Qt.Horizontal, "工号")
self.qryModel.setHeaderData(1, Qt.Horizontal, "姓名")
self.qryModel.setHeaderData(2, Qt.Horizontal, "性别")
self.qryModel.setHeaderData(3, Qt.Horizontal, "出生日期")
self.qryModel.setHeaderData(4, Qt.Horizontal, "省份")
self.qryModel.setHeaderData(5, Qt.Horizontal, "部门")
self.qryModel.setHeaderData(6, Qt.Horizontal, "工资")
self.mapper = QDataWidgetMapper()
self.mapper.setModel(self.qryModel)
self.mapper.addMapping(self.ui.dbSpinEmpNo, 0)
self.mapper.addMapping(self.ui.dbEditName, 1)
self.mapper.addMapping(self.ui.dbComboSex, 2)
self.mapper.addMapping(self.ui.dbEditBirth, 3)
self.mapper.addMapping(self.ui.dbComboProvince, 4)
self.mapper.addMapping(self.ui.dbComboDep, 5)
self.mapper.addMapping(self.ui.dbSpinSalary, 6)
self.mapper.toFirst()
self.selModel = QItemSelectionModel(self.qryModel)
self.selModel.currentRowChanged.connect(self.do_currentRowChanged)
self.ui.tableView.setModel(self.qryModel)
self.ui.tableView.setSelectionModel(self.selModel)
self.ui.actOpenDB.setEnabled(False)
def __refreshTableView(self):
index = self.mapper.currentIndex()
curIndex = self.qryModel.index(index, 1)
self.selModel.clearSelection()
self.selModel.setCurrentIndex(curIndex, QItemSelectionModel.Select)
def __getFieldNames(self):
emptyRec = self.qryModel.record()
self.fldNum = {}
for i in range(emptyRec.count()):
fieldName = emptyRec.fieldName(i)
self.fldNum.setdefault(fieldName)
self.fldNum[fieldName] = i
print(self.fldNum)
@pyqtSlot()
def on_actRecFirst_triggered(self):
self.mapper.toFirst()
self.__refreshTableView()
@pyqtSlot()
def on_actRecPrevious_triggered(self):
self.mapper.toPrevious()
self.__refreshTableView()
@pyqtSlot()
def on_actRecNext_triggered(self):
self.mapper.toNext()
self.__refreshTableView()
@pyqtSlot()
def on_actRecLast_triggered(self):
self.mapper.toLast()
self.__refreshTableView()
def do_currentRowChanged(self, current, previous):
if (current.isValid() == False):
self.ui.dbLabPhoto.clear()
return
self.mapper.setCurrentIndex(current.row())
first = (current.row() == 0)
last = (current.row() == self.qryModel.rowCount() - 1)
self.ui.actRecFirst.setEnabled(not first)
self.ui.actRecPrevious.setEnabled(not first)
self.ui.actRecNext.setEnabled(not last)
self.ui.actRecLast.setEnabled(not last)
curRec = self.qryModel.record(current.row())
empNo = curRec.value("EmpNo")
query = QSqlQuery(self.DB)
query.prepare(
'''SELECT EmpNo, Memo, Photo FROM employee WHERE EmpNo = :ID''')
query.bindValue(":ID", empNo)
if not query.exec():
QMessageBox.critical(self, "错误",
"执行SQL语句错误\n" + query.lastError().text())
return
else:
query.first()
picData = query.value("Photo")
if (picData == None):
self.ui.dbLabPhoto.clear()
else:
pic = QPixmap()
pic.loadFromData(picData)
W = self.ui.dbLabPhoto.size().width()
self.ui.dbLabPhoto.setPixmap(pic.scaledToWidth(W))
memoData = query.value("Memo")
self.ui.dbEditMemo.setPlainText(memoData)
class cNews(QDialog, Ui_Form):
def __init__(self):
super(cNews, self).__init__()
self.setupUi(self)
# 信号槽连接
self.prevButton.clicked.connect(self.onPrevButtonClick)
self.nextButton.clicked.connect(self.onNextButtonClick)
self.addnew.clicked.connect(lambda: self.callnewsadd(0))
self.switchPageButton.clicked.connect(self.onSwitchPageButtonClick)
# 查询模型
self.currentPage = 0
# 总页数
self.totalPage = 0
# 总记录数
self.totalRecrodCount = 0
# 每页显示记录数
self.PageRecordCount = 10
self.setTableView()
# self.tableView.resizeColumnsToContents()
self.tableView.setContextMenuPolicy(Qt.CustomContextMenu)
self.tableView.doubleClicked.connect(self.dbclick)
self.tableView.customContextMenuRequested.connect(self.genmenu)
def callnewsadd(self, sid):
ae = cnewsadd()
ae.sid = sid
if sid == 0:
ae.groupBox.setTitle('添加新闻')
else:
ae.groupBox.setTitle('修改新闻')
ae.fetchdata(sid)
ae.mySignal.connect(self.getDialogSignal)
ae.setWindowModality(Qt.ApplicationModal)
ae.show()
def dbclick(self):
id = self.tableView.currentIndex().row()
md = self.queryModel.record(id).value('Id')
self.callnewsadd(md)
def getDialogSignal(self, content):
pass
def delitem(self, i):
md = self.queryModel.record(i)
qid = md.value('Id')
sql = "update newspaper set nav_url=1 where Id=%d" % (qid)
d = self.db.exec(sql)
if d:
QMessageBox.information(self, '成功', '删除成功')
self.queryModel.setQuery(
"select Id,newsdate,title,author,pic_url '封面图片' from newspaper where nav_url='' order by newsdate desc"
)
def genmenu(self, pos):
for i in self.tableView.selectionModel().selection().indexes():
menu = QMenu()
item1 = menu.addAction(u"删除")
action = menu.exec_(self.tableView.mapToGlobal(pos))
if action == item1:
self.delitem(i.row())
return
else:
return
# 设置表格
def setTableView(self):
gl._init()
self.db = QSqlDatabase.addDatabase('QMYSQL')
self.db.setHostName(gl.get_value('HOSTNAME'))
self.db.setPort(gl.get_value('PORT'))
self.db.setDatabaseName(gl.get_value('DBNAME'))
self.db.setUserName(gl.get_value('USRNAME'))
self.db.setPassword(gl.get_value('PWD'))
# 设置数据库名称
# self.db.setDatabaseName('./database.db')
# 打开数据库
self.db.open()
# 声明查询模型
self.queryModel = QSqlQueryModel(self)
# 设置当前页
self.currentPage = 1
# 得到总记录数
self.totalRecrodCount = self.getTotalRecordCount()
# 得到总页数
self.totalPage = self.getPageCount()
# 刷新状态
self.updateStatus()
# 设置总页数文本
self.setTotalPageLabel()
# 设置总记录数
self.setTotalRecordLabel()
# 记录查询
self.recordQuery(0)
# 设置模型
self.tableView.setModel(self.queryModel)
self.updateStatus()
print('totalRecrodCount=' + str(self.totalRecrodCount))
print('totalPage=' + str(self.totalPage))
# 得到记录数
def getTotalRecordCount(self):
self.queryModel.setQuery(
"select Id,newsdate,title,author,pic_url '封面图片' from newspaper where nav_url='' order by newsdate desc"
)
rowCount = self.queryModel.rowCount()
print('rowCount=' + str(rowCount))
return rowCount
# 得到页数
def getPageCount(self):
if self.totalRecrodCount % self.PageRecordCount == 0:
return (self.totalRecrodCount / self.PageRecordCount)
else:
return int((self.totalRecrodCount / self.PageRecordCount + 1))
# 记录查询
def recordQuery(self, limitIndex):
szQuery = (
"select Id,newsdate,title,author,pic_url '封面图片' from newspaper where nav_url='' order by newsdate desc limit %d,%d"
% (limitIndex, self.PageRecordCount))
print('query sql=' + szQuery)
self.queryModel.setQuery(szQuery)
# 刷新状态
def updateStatus(self):
szCurrentText = ("当前第%d页" % self.currentPage)
print(szCurrentText)
print(self.currentPageLabel)
self.currentPageLabel.setText(szCurrentText)
print('current=' + str(self.currentPage) + 'tt=' + str(self.totalPage))
#设置按钮是否可用
if self.currentPage == 1:
if self.totalPage == 1:
self.prevButton.setEnabled(False)
self.nextButton.setEnabled(False)
else:
self.prevButton.setEnabled(False)
self.nextButton.setEnabled(True)
elif self.currentPage >= self.totalPage:
self.prevButton.setEnabled(True)
self.nextButton.setEnabled(False)
else:
self.prevButton.setEnabled(True)
self.nextButton.setEnabled(True)
# 设置总数页文本
def setTotalPageLabel(self):
szPageCountText = ("总共%d页" % self.totalPage)
self.totalPageLabel.setText(szPageCountText)
# 设置总记录数
def setTotalRecordLabel(self):
szTotalRecordText = ("共%d条" % self.totalRecrodCount)
print('*** setTotalRecordLabel szTotalRecordText=' + szTotalRecordText)
self.totalRecordLabel.setText(szTotalRecordText)
# 前一页按钮按下
def onPrevButtonClick(self):
print('*** onPrevButtonClick ')
limitIndex = (self.currentPage - 2) * self.PageRecordCount
self.recordQuery(limitIndex)
self.currentPage -= 1
self.updateStatus()
# 后一页按钮按下
def onNextButtonClick(self):
print('*** onNextButtonClick ')
limitIndex = self.currentPage * self.PageRecordCount
self.recordQuery(limitIndex)
self.currentPage += 1
self.updateStatus()
# 转到页按钮按下
def onSwitchPageButtonClick(self):
# 得到输入字符串
szText = self.switchPageLineEdit.text()
#数字正则表达式
pattern = re.compile(r'^[-+]?[0-9]+\.[0-9]+$')
match = 1
# 判断是否为数字
if not match:
QMessageBox.information(self, "提示", "请输入数字")
return
# 是否为空
if szText == '':
QMessageBox.information(self, "提示", "请输入跳转页面")
return
#得到页数
pageIndex = int(szText)
#判断是否有指定页
if pageIndex > self.totalPage or pageIndex < 1:
QMessageBox.information(self, "提示", "没有指定的页面，请重新输入")
return
#得到查询起始行号
limitIndex = (pageIndex - 1) * self.PageRecordCount
#记录查询
self.recordQuery(limitIndex)
#设置当前页
self.currentPage = pageIndex
#刷新状态
self.updateStatus()
class DBViewer(QDialog):
resized = pyqtSignal(object)
def __init__(self, ctx, par, *args, **kwargs):
super(DBViewer, self).__init__(*args, **kwargs)
self.setAttribute(Qt.WA_DeleteOnClose)
self.setWindowFlags(self.windowFlags() | Qt.WindowSystemMenuHint
| Qt.WindowMinMaxButtonsHint)
self.ctx = ctx
self.par = par
self.layout = QVBoxLayout()
self.query_model = QSqlQueryModel()
self.toolbar = QToolBar()
self.table_view = QTableView()
self.table_view.setSelectionBehavior(QAbstractItemView.SelectRows)
self.export_excel = QPushButton(
self.ctx.export_icon,
'Export to Excel',
)
self.refresh_btn = QPushButton("Refresh")
self.close_btn = QPushButton("Close")
self.delete_rows_btn = QPushButton("Delete Selected Row(s)")
self.delete_rows_btn.setEnabled(False)
self.initModel()
self.initUI()
self.sigConnect()
def initUI(self):
self.layout.setContentsMargins(11, 0, 11, 11)
self.toolbar.addWidget(self.export_excel)
self.layout.addWidget(self.toolbar)
self.close_btn.setAutoDefault(True)
self.close_btn.setDefault(True)
self.refresh_btn.setAutoDefault(False)
self.refresh_btn.setDefault(False)
self.delete_rows_btn.setAutoDefault(False)
self.delete_rows_btn.setDefault(False)
self.export_excel.setAutoDefault(False)
self.export_excel.setDefault(False)
self.table_view.resizeColumnsToContents()
self.layout.addWidget(self.table_view)
hLayout = QHBoxLayout()
hLayout.addWidget(self.delete_rows_btn)
hLayout.addStretch()
hLayout.addWidget(self.refresh_btn)
hLayout.addWidget(self.close_btn)
self.layout.addLayout(hLayout)
self.setLayout(self.layout)
self.setWindowTitle("Patients Record")
wds = [
self.table_view.columnWidth(c)
for c in range(self.table_view.model().columnCount())
]
self.resize(sum(wds) + 40, 600)
rect = self.frameGeometry()
rect.moveCenter(QDesktopWidget().availableGeometry().center())
self.move(rect.topLeft().x(), rect.topLeft().y())
def sigConnect(self):
self.close_btn.clicked.connect(self.accept)
self.refresh_btn.clicked.connect(self.on_refresh)
self.delete_rows_btn.clicked.connect(self.on_delete_rows)
self.export_excel.clicked.connect(self.on_export)
self.resized.connect(self.on_window_resize)
self.table_view.horizontalHeader().sectionResized.connect(
self.on_column_resize)
def initModel(self):
self.table_model = QSqlTableModel(db=self.ctx.database.patient_db)
self.table_model.setTable('patients')
self.table_model.setEditStrategy(QSqlTableModel.OnFieldChange)
self.table_model.select()
self.table_view.setModel(self.table_model)
self.table_view.selectionModel().selectionChanged.connect(
self.on_rows_selected)
def on_export(self):
filename, _ = QFileDialog.getSaveFileName(self, "Export to Excel", "",
"Excel Workbook (*.xlsx)")
if not filename:
return
workbook = Workbook(filename)
worksheet = workbook.add_worksheet()
bold = workbook.add_format({'bold': True})
sql = "SELECT * FROM PATIENTS"
self.query_model.setQuery(sql, self.ctx.database.patient_db)
for row in range(self.query_model.rowCount() + 1):
for col in range(self.query_model.record(row).count()):
if row == 0:
worksheet.write(row, col,
self.query_model.record().fieldName(col),
bold)
worksheet.write(row + 1, col,
self.query_model.record(row).value(col))
workbook.close()
QMessageBox.information(self, "Success",
"Records can be found in " + filename + " .")
def on_refresh(self):
self.initModel()
def on_column_resize(self, id, oldsize, size):
width = self.size().width()
self.column_ratio[id] = size / width
def on_window_resize(self, event):
old_width = event.oldSize().width()
width = event.size().width()
if old_width == -1:
self.column_ratio = [
self.table_view.columnWidth(c) / width
for c in range(self.table_view.model().columnCount())
]
else:
self.table_view.horizontalHeader().sectionResized.disconnect(
self.on_column_resize)
[
self.table_view.setColumnWidth(c, r * width)
for c, r in enumerate(self.column_ratio)
]
self.table_view.horizontalHeader().sectionResized.connect(
self.on_column_resize)
def on_rows_selected(self):
self.selected_rows = sorted(
set(index.row() for index in self.table_view.selectedIndexes()))
print(self.selected_rows)
self.delete_rows_btn.setEnabled(len(self.selected_rows) != 0)
def on_delete_rows(self):
result = []
for row in self.selected_rows:
res = self.table_model.removeRow(row)
result.append(res)
if not all(result):
print(self.table_model.lastError())
self.ctx.records_count -= len(self.selected_rows)
self.delete_rows_btn.setEnabled(False)
self.on_refresh()
self.par.info_panel.no_edit.setText(str(self.ctx.records_count + 1))
def resizeEvent(self, event):
self.resized.emit(event)
return super(DBViewer, self).resizeEvent(event)
class StudentMainUi(QMainWindow):
def __init__(self):
super().__init__()
#self.search_layout = QtWidgets.QHBoxLayout()
self.OpenRuslt = 0
# 查询模型
self.queryModel = None
# 数据表
self.tableView = None
# 当前页
self.currentPage = 0
# 总页数
self.totalPage = 0
# 总记录数
self.totalRecord = 0
# 每页数据数
self.pageRecord = 10
self.initUi()
# 初始化UI界面
def initUi(self):
# 设置窗口标题
self.setWindowTitle("欢迎使用图书查询")
# 设置窗口大小
self.resize(700, 500)
self.widget2 = QtWidgets.QWidget()
self.layout = QGridLayout()
self.Hlayout1 = QHBoxLayout()
self.Hlayout2 = QHBoxLayout()
self.Hlayout3 = QHBoxLayout()
self.Hlayout = QHBoxLayout()
# Hlayout1
# 创建一个文本框部件
self.line = QtWidgets.QLineEdit()
self.ButtonSerch = QtWidgets.QPushButton('查询')
self.combox = QtWidgets.QComboBox()
self.item = ['ISBN', 'BookName']
self.combox.addItems(self.item)
self.Hlayout1.addWidget(self.line)
self.Hlayout1.addWidget(self.ButtonSerch)
self.Hlayout1.addWidget(self.combox)
# Hlayout2初始化
self.jumpToLabel = QLabel("跳转到第")
self.pageEdit = QLineEdit()
self.pageEdit.setFixedWidth(30)
s = "/" + str(self.totalPage) + "页"
self.pageLabel = QLabel(s)
self.jumpToButton = QtWidgets.QPushButton("跳转")
self.prevButton = QtWidgets.QPushButton("前一页")
self.prevButton.setFixedWidth(60)
self.backButton = QtWidgets.QPushButton("后一页")
self.backButton.setFixedWidth(60)
Hlayout = QHBoxLayout()
Hlayout.addWidget(self.jumpToLabel)
Hlayout.addWidget(self.pageEdit)
Hlayout.addWidget(self.pageLabel)
Hlayout.addWidget(self.jumpToButton)
Hlayout.addWidget(self.prevButton)
Hlayout.addWidget(self.backButton)
widget = QtWidgets.QWidget()
widget.setLayout(Hlayout)
self.Hlayout3.addWidget(widget)
# 创建一个按钮组
self.group_box = QtWidgets.QGroupBox()
self.group_box_layout = QtWidgets.QVBoxLayout()
self.group_box.setLayout(self.group_box_layout)
# Table_View
self.tableView = QtWidgets.QTableView()
self.tableView.setFixedWidth(500)
# self.tableView.horizontalHeader().setStretchLastSection(True)
# self.tableView.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
self.tableView.setEditTriggers(QAbstractItemView.NoEditTriggers)
self.queryModel = QSqlQueryModel()
self.ButtonSerch_clicked()
self.tableView.setModel(self.queryModel)
self.queryModel.setHeaderData(0, Qt.Horizontal, "ISBN")
self.queryModel.setHeaderData(1, Qt.Horizontal, "BookName")
self.queryModel.setHeaderData(2, Qt.Horizontal, "Author")
self.queryModel.setHeaderData(3, Qt.Horizontal, "Publisher")
self.queryModel.setHeaderData(4, Qt.Horizontal, "Date")
self.queryModel.setHeaderData(5, Qt.Horizontal, "Score")
self.queryModel.setHeaderData(6, Qt.Horizontal, "Photo")
# 创建按钮组的按钮
self.ButtonCreatDb = QtWidgets.QPushButton("打开数据库")
self.ButtonCreatDb.setFixedHeight(40)
self.ButtonCreatDb.setFixedWidth(90)
self.ButtonClose = QtWidgets.QPushButton("退出")
self.ButtonClose.setFixedHeight(40)
self.ButtonClose.setFixedWidth(90)
self.StudentInfo = QtWidgets.QTextEdit(" ")
self.StudentInfo.setFixedHeight(100)
self.StudentInfo.setFixedWidth(200)
# 添加按钮到按钮组中
self.group_box_layout.addWidget(self.StudentInfo)
self.group_box_layout.addWidget(self.ButtonCreatDb)
self.group_box_layout.addWidget(self.ButtonClose)
self.Hlayout2.addWidget(self.group_box)
self.Hlayout2.addWidget(self.tableView)
self.layout.addLayout(self.Hlayout2, 1, 0)
self.layout.addLayout(self.Hlayout1, 0, 0)
self.layout.addLayout(self.Hlayout3, 2, 0)
# self.layout.addWidget(self.StudentInfo,0,0)
self.widget2.setLayout(self.layout)
# 发射信号 clicked Or Triggered
self.ButtonCreatDb.clicked.connect(self.openDb) # 打开数据库信号
self.ButtonClose.clicked.connect(self.close) # 关闭信号
self.ButtonSerch.clicked.connect(self.ButtonSerch_clicked) # 查询
self.backButton.clicked.connect(self.backButtonClicked)
self.prevButton.clicked.connect(self.prevButtonClicked)
self.jumpToButton.clicked.connect(self.jumpToButtonClicked)
self.setCentralWidget(self.widget2)
self.show()
# -----------------------------------------------槽函数----------------------------------------
"""打开数据库的槽函数"""
def openDb(self):
db = QSqlDatabase.addDatabase('QSQLITE')
db.setDatabaseName('F:/pycharm项目/图书管理系统NEW/AllDataBase/book.db')
db.open()
# 显示数据库
self.model = QtSql.QSqlTableModel()
# 将数据库显示在表中
self.tableView.setModel(self.model)
self.model.setTable('BookData') # 设置数据模型的数据表
self.model.setEditStrategy(
QtSql.QSqlTableModel.OnManualSubmit) # 不允许字段更改
self.totalRecord = self.model.rowCount()
self.totalPage = int(
(self.totalRecord + self.pageRecord - 1) / self.pageRecord)
label = "/" + str(int(self.totalPage)) + "页"
self.pageLabel.setText(label)
self.model.select() # 查询所有数据
# 设置表格头
self.model.setHeaderData(0, QtCore.Qt.Horizontal, 'ISBN')
self.model.setHeaderData(1, QtCore.Qt.Horizontal, 'BookName')
self.model.setHeaderData(2, QtCore.Qt.Horizontal, 'Author')
self.model.setHeaderData(3, QtCore.Qt.Horizontal, 'Publisher')
self.model.setHeaderData(4, QtCore.Qt.Horizontal, 'Date')
self.model.setHeaderData(5, QtCore.Qt.Horizontal, 'Score')
self.model.setHeaderData(6, QtCore.Qt.Horizontal, 'Photo')
self.OpenRuslt += 1
# 显示有多少数据
self.queryModel = QSqlQueryModel()
queryCondition = "select * from BookData"
self.queryModel.setQuery(queryCondition)
self.totalRecord = self.queryModel.rowCount()
self.totalPage = int(
(self.totalRecord + self.pageRecord - 1) / self.pageRecord)
label = "/" + str(int(self.totalPage)) + "页"
self.pageLabel.setText(label)
def setButtonStatus(self):
if(self.currentPage == self.totalPage):
self.prevButton.setEnabled(True)
self.backButton.setEnabled(False)
if(self.currentPage == 1):
self.backButton.setEnabled(True)
self.prevButton.setEnabled(False)
if(self.currentPage < self.totalPage and self.currentPage > 1):
self.prevButton.setEnabled(True)
self.backButton.setEnabled(True)
# 得到记录数
def getTotalRecordCount(self):
self.queryModel.setQuery("SELECT * FROM BookData")
self.totalRecord = self.queryModel.rowCount()
return
# 得到总页数
def getPageCount(self):
self.getTotalRecordCount()
# 上取整
self.totalPage = int(
(self.totalRecord + self.pageRecord - 1) / self.pageRecord)
print(self.totalPage)
return
def on_tableWidget_currentCellChanged(
self,
currentRow,
currentColumn,
previousRow,
previousColumn):
"""
当前单元格改变
"""
pass
def recordQuery(self, index):
"""
查找
"""
self.queryModel = QSqlQueryModel()
self.tableView.setModel(self.queryModel)
conditionChoice = self.combox.currentText()
if (conditionChoice == "ISBN"):
conditionChoice = 'ISBN'
elif (conditionChoice == "BookName"):
conditionChoice = 'BookName'
if (self.line.text() == ""):
queryCondition = "select * from BookData"
self.queryModel.setQuery(queryCondition)
self.totalRecord = self.queryModel.rowCount()
self.totalPage = int(
(self.totalRecord + self.pageRecord - 1) / self.pageRecord)
label = "/" + str(int(self.totalPage)) + "页"
self.pageLabel.setText(label)
queryCondition = (
"select * from BookData ORDER BY %s limit %d,%d " %
(conditionChoice, index, self.pageRecord))
self.queryModel.setQuery(queryCondition)
self.setButtonStatus()
return
# 得到模糊查询条件
temp = self.line.text()
s = '%'
for i in range(0, len(temp)):
s = s + temp[i] + "%"
queryCondition = (
"SELECT * FROM BookData WHERE %s LIKE '%s' ORDER BY %s " %
(conditionChoice, s, conditionChoice))
self.queryModel.setQuery(queryCondition)
self.totalRecord = self.queryModel.rowCount()
self.totalPage = int(
(self.totalRecord + self.pageRecord - 1) / self.pageRecord)
label = "/" + str(int(self.totalPage)) + "页"
self.pageLabel.setText(label)
queryCondition = (
"SELECT * FROM BookData WHERE %s LIKE '%s' ORDER BY %s LIMIT %d,%d " %
(conditionChoice, s, conditionChoice, index, self.pageRecord))
self.queryModel.setQuery(queryCondition)
self.setButtonStatus()
return
def ButtonSerch_clicked(self):
self.currentPage = 1
self.pageEdit.setText(str(self.currentPage))
self.getPageCount()
s = "/" + str(int(self.totalPage)) + "页"
self.pageLabel.setText(s)
index = (self.currentPage - 1) * self.pageRecord
self.recordQuery(index)
return
# 向前翻页
def prevButtonClicked(self):
self.currentPage -= 1
if (self.currentPage <= 1):
self.currentPage = 1
self.pageEdit.setText(str(self.currentPage))
index = (self.currentPage - 1) * self.pageRecord
self.recordQuery(index)
return
# 向后翻页
def backButtonClicked(self):
self.currentPage += 1
if (self.currentPage >= int(self.totalPage)):
self.currentPage = int(self.totalPage)
self.pageEdit.setText(str(self.currentPage))
index = (self.currentPage - 1) * self.pageRecord
self.recordQuery(index)
return
# 点击跳转
def jumpToButtonClicked(self):
if (self.pageEdit.text().isdigit()):
self.currentPage = int(self.pageEdit.text())
if (self.currentPage > self.totalPage):
self.currentPage = self.totalPage
if (self.currentPage <= 1):
self.currentPage = 1
else:
self.currentPage = 1
index = (self.currentPage - 1) * self.pageRecord
self.pageEdit.setText(str(self.currentPage))
self.recordQuery(index)
return
class DataGrid(QWidget):
def __init__(self, input_num):
super().__init__()
self.setWindowTitle("订单信息查询，按号查找")
self.setWindowFlags(Qt.WindowStaysOnTopHint)
self.input_num = input_num
self.resize(750, 300)
# 查询模型
self.queryModel = None
# 数据表
self.tableView = None
self.labelMMM = None
# 总数页文本
self.totalPageLabel = None
# 当前页文本
self.currentPageLabel = None
# 转到页输入框
self.switchPageLineEdit = None
# 前一页按钮
self.prevButton = None
# 后一页按钮
self.nextButton = None
# 转到页按钮
self.switchPageButton = None
# 当前页
self.currentPage = 0
# 总页数
self.totalPage = 0
# 总记录数
self.totalRecrodCount = 0
# 每页显示记录数
self.PageRecordCount = 5
self.db = None
self.initUI()
def initUI(self):
# 创建窗口
self.createWindow()
# 设置表格
self.setTableView()
# 信号槽连接
self.prevButton.clicked.connect(self.onPrevButtonClick)
self.nextButton.clicked.connect(self.onNextButtonClick)
self.switchPageButton.clicked.connect(self.onSwitchPageButtonClick)
self.changeButton.clicked.connect(self.change)
self.deleteButton.clicked.connect(self.delete)
def closeEvent(self, event):
# 关闭数据库
self.db.close()
# 创建窗口
def createWindow(self):
# 操作布局
operatorLayout = QHBoxLayout()
self.prevButton = QPushButton("前一页")
self.nextButton = QPushButton("后一页")
self.changeButton = QPushButton("修改")
self.deleteButton = QPushButton("删除")
self.labelMMM = QLabel(" " * 20)
self.switchPageButton = QPushButton("Go")
self.switchPageLineEdit = QLineEdit()
self.switchPageLineEdit.setFixedWidth(40)
switchPage = QLabel("转到第")
page = QLabel("页")
operatorLayout.addWidget(self.prevButton)
operatorLayout.addWidget(self.nextButton)
operatorLayout.addWidget(switchPage)
operatorLayout.addWidget(self.switchPageLineEdit)
operatorLayout.addWidget(page)
operatorLayout.addWidget(self.switchPageButton)
operatorLayout.addWidget(self.labelMMM)
operatorLayout.addWidget(self.changeButton)
operatorLayout.addWidget(self.deleteButton)
operatorLayout.addWidget(QSplitter())
# 状态布局
statusLayout = QHBoxLayout()
self.totalPageLabel = QLabel()
self.totalPageLabel.setFixedWidth(70)
self.currentPageLabel = QLabel()
self.currentPageLabel.setFixedWidth(70)
self.totalRecordLabel = QLabel()
self.totalRecordLabel.setFixedWidth(70)
statusLayout.addWidget(self.totalPageLabel)
statusLayout.addWidget(self.currentPageLabel)
statusLayout.addWidget(QSplitter())
statusLayout.addWidget(self.totalRecordLabel)
# 设置表格属性
self.tableView = QTableView()
# 表格宽度的自适应调整
self.tableView.horizontalHeader().setStretchLastSection(True)
self.tableView.horizontalHeader().setSectionResizeMode(
QHeaderView.Stretch)
# 创建界面
mainLayout = QVBoxLayout(self)
mainLayout.addLayout(operatorLayout)
mainLayout.addWidget(self.tableView)
mainLayout.addLayout(statusLayout)
self.setLayout(mainLayout)
# 设置表格
def setTableView(self):
print('*** step2 SetTableView')
self.db = QSqlDatabase.addDatabase('QMYSQL')
self.db.setHostName("67.209.xxx.xxx")
self.db.setDatabaseName("db_sharedbike")
self.db.setUserName("root")
self.db.setPassword("xxxxxxxxx")
# 打开数据库
self.db.open()
# 声明查询模型
self.queryModel = QSqlQueryModel(self)
# 设置当前页
self.currentPage = 1
# 得到总记录数
self.totalRecrodCount = self.getTotalRecordCount()
# 得到总页数
self.totalPage = self.getPageCount()
# 刷新状态
self.updateStatus()
# 设置总页数文本
self.setTotalPageLabel()
# 设置总记录数
self.setTotalRecordLabel()
# 记录查询
self.recordQuery(0)
# 设置模型
self.tableView.setModel(self.queryModel)
print('totalRecrodCount=' + str(self.totalRecrodCount))
print('totalPage=' + str(self.totalPage))
# 设置表格表头
self.queryModel.setHeaderData(0, Qt.Horizontal, "订单编号")
self.queryModel.setHeaderData(1, Qt.Horizontal, "用户编号")
self.queryModel.setHeaderData(2, Qt.Horizontal, "自行车编号")
self.queryModel.setHeaderData(3, Qt.Horizontal, "下单时间")
self.queryModel.setHeaderData(4, Qt.Horizontal, "结账时间")
# 得到记录数
def getTotalRecordCount(self):
self.queryModel.setQuery("select * from order_info where id = %s" %
(self.input_num))
rowCount = self.queryModel.rowCount()
print('rowCount=' + str(rowCount))
return rowCount
# 得到页数
def getPageCount(self):
if self.totalRecrodCount == 0:
self.currentPage = 0
if self.totalRecrodCount % self.PageRecordCount == 0:
return (self.totalRecrodCount / self.PageRecordCount)
else:
return (int(self.totalRecrodCount / self.PageRecordCount) + 1)
# 记录查询
def recordQuery(self, limitIndex):
szQuery = ("select * from order_info where id=%s limit %d,%d" %
(self.input_num, 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 == 0:
self.prevButton.setEnabled(False)
self.nextButton.setEnabled(False)
return
if self.currentPage == 1:
self.prevButton.setEnabled(False)
else:
self.prevButton.setEnabled(True)
if self.currentPage == self.totalPage:
self.nextButton.setEnabled(False)
else:
self.nextButton.setEnabled(True)
# 设置总数页文本
def setTotalPageLabel(self):
szPageCountText = ("总共%d页" % self.totalPage)
self.totalPageLabel.setText(szPageCountText)
# 设置总记录数
def setTotalRecordLabel(self):
szTotalRecordText = ("共%d条" % self.totalRecrodCount)
print('*** setTotalRecordLabel szTotalRecordText=' + szTotalRecordText)
self.totalRecordLabel.setText(szTotalRecordText)
def update(self):
print("it is me")
# 设置表格
self.setTableView()
def change(self):
from .chaneAdminByNum import chaneAdmin
self.chanadminbynum = chaneAdmin(self.update)
self.chanadminbynum.show()
def delete(self):
from .DeleteAdminByNum import DeleteAdminByNum
self.deleteadminbynum = DeleteAdminByNum(self.update)
self.deleteadminbynum.show()
# 前一页按钮按下
def onPrevButtonClick(self):
print('*** onPrevButtonClick ')
limitIndex = (self.currentPage - 2) * self.PageRecordCount
self.recordQuery(limitIndex)
self.currentPage -= 1
self.updateStatus()
# 后一页按钮按下
def onNextButtonClick(self):
print('*** onNextButtonClick ')
limitIndex = self.currentPage * self.PageRecordCount
self.recordQuery(limitIndex)
self.currentPage += 1
self.updateStatus()
# 转到页按钮按下
def onSwitchPageButtonClick(self):
# 得到输入字符串
szText = self.switchPageLineEdit.text()
#数字正则表达式
#pattern = re.compile(r'^[-+]?[0-9]+\.[0-9]+$')
#match = pattern.match(szText)
try:
match = None
match = int(szText)
except Exception as e:
print(e)
# 判断是否为数字
if not match:
QMessageBox.information(self, "提示", "请输入数字")
return
# 是否为空
if szText == '':
QMessageBox.information(self, "提示", "请输入跳转页面")
return
#得到页数
pageIndex = int(szText)
#判断是否有指定页
if pageIndex > self.totalPage or pageIndex < 1:
QMessageBox.information(self, "提示", "没有指定的页面，请重新输入")
return
#得到查询起始行号
limitIndex = (pageIndex - 1) * self.PageRecordCount
#记录查询
self.recordQuery(limitIndex)
#设置当前页
self.currentPage = pageIndex
#刷新状态
self.updateStatus()
class DataGrid(QWidget):
def __init__(self):
super().__init__()
self.setWindowTitle("分页查询例子")
self.resize(750,300)
# 查询模型
self.queryModel = None
# 数据表
self.tableView = None
# 总数页文本
self.totalPageLabel = None
# 当前页文本
self.currentPageLabel = None
# 转到页输入框
self.switchPageLineEdit = None
# 前一页按钮
self.prevButton = None
# 后一页按钮
self.nextButton = None
# 转到页按钮
self.switchPageButton = None
# 当前页
self.currentPage = 0
# 总页数
self.totalPage = 0
# 总记录数
self.totalRecrodCount = 0
# 每页显示记录数
self.PageRecordCount = 5
self.db = None
self.initUI()
def initUI(self):
# 创建窗口
self.createWindow()
# 设置表格
self.setTableView()
# 信号槽连接
self.prevButton.clicked.connect(self.onPrevButtonClick )
self.nextButton.clicked.connect(self.onNextButtonClick )
self.switchPageButton.clicked.connect(self.onSwitchPageButtonClick )
def closeEvent(self, event):
# 关闭数据库
self.db.close()
# 创建窗口
def createWindow(self):
# 操作布局
operatorLayout = QHBoxLayout()
self.prevButton = QPushButton("前一页")
self.nextButton = QPushButton("后一页")
self.switchPageButton = QPushButton("Go")
self.switchPageLineEdit = QLineEdit()
self.switchPageLineEdit.setText(str(self.currentPage))
self.switchPageLineEdit.setFixedWidth(40)
switchPage = QLabel("转到第")
page = QLabel("页")
operatorLayout.addWidget(self.prevButton)
operatorLayout.addWidget(self.nextButton)
operatorLayout.addWidget(switchPage)
operatorLayout.addWidget(self.switchPageLineEdit)
operatorLayout.addWidget(page)
operatorLayout.addWidget(self.switchPageButton)
operatorLayout.addWidget( QSplitter())
# 状态布局
statusLayout = QHBoxLayout()
self.totalPageLabel = QLabel()
self.totalPageLabel.setFixedWidth(70)
self.currentPageLabel = QLabel()
self.currentPageLabel.setFixedWidth(70)
self.totalRecordLabel = QLabel()
self.totalRecordLabel.setFixedWidth(70)
statusLayout.addWidget(self.totalPageLabel)
statusLayout.addWidget(self.currentPageLabel)
statusLayout.addWidget( QSplitter() )
statusLayout.addWidget(self.totalRecordLabel)
# 设置表格属性
self.tableView = QTableView()
# 表格宽度的自适应调整
self.tableView.horizontalHeader().setStretchLastSection(True)
self.tableView.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
# 创建界面
mainLayout = QVBoxLayout(self);
mainLayout.addLayout(operatorLayout);
mainLayout.addWidget(self.tableView);
mainLayout.addLayout(statusLayout);
self.setLayout(mainLayout)
# 设置表格
def setTableView(self):
print('*** step2 SetTableView' )
self.db = QSqlDatabase.addDatabase('QSQLITE')
# 设置数据库名称
self.db.setDatabaseName('./db/database.db')
# 打开数据库
self.db.open()
# 声明查询模型
self.queryModel = QSqlQueryModel(self)
# 设置当前页
self.currentPage = 1;
# 得到总记录数
self.totalRecrodCount = self.getTotalRecordCount()
# 得到总页数
self.totalPage = self.getPageCount()
# 刷新状态
self.updateStatus()
# 设置总页数文本
self.setTotalPageLabel()
# 设置总记录数
self.setTotalRecordLabel()
# 记录查询
self.recordQuery(0)
# 设置模型
self.tableView.setModel(self.queryModel)
print('totalRecrodCount=' + str(self.totalRecrodCount) )
print('totalPage=' + str(self.totalPage) )
# 设置表格表头
self.queryModel.setHeaderData(0,Qt.Horizontal,"编号")
self.queryModel.setHeaderData(1,Qt.Horizontal,"姓名")
self.queryModel.setHeaderData(2,Qt.Horizontal,"性别")
self.queryModel.setHeaderData(3,Qt.Horizontal,"年龄")
self.queryModel.setHeaderData(4,Qt.Horizontal,"院系")
# 得到记录数
def getTotalRecordCount(self):
self.queryModel.setQuery("select * from student")
rowCount = self.queryModel.rowCount()
print('rowCount=' + str(rowCount) )
return rowCount
# 得到页数
def getPageCount(self):
if self.totalRecrodCount % self.PageRecordCount == 0 :
return (self.totalRecrodCount / self.PageRecordCount )
else :
return (self.totalRecrodCount / self.PageRecordCount + 1)
# 记录查询
def recordQuery(self, limitIndex ):
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.prevButton.setEnabled( False )
self.nextButton.setEnabled( True )
elif self.currentPage == self.totalPage :
self.prevButton.setEnabled( True )
self.nextButton.setEnabled( False )
else :
self.prevButton.setEnabled( True )
self.nextButton.setEnabled( True )
# 设置总数页文本
def setTotalPageLabel(self):
szPageCountText = ("总共%d页" % self.totalPage )
self.totalPageLabel.setText(szPageCountText)
# 设置总记录数
def setTotalRecordLabel(self):
szTotalRecordText = ("共%d条" % self.totalRecrodCount )
print('*** setTotalRecordLabel szTotalRecordText=' + szTotalRecordText )
self.totalRecordLabel.setText(szTotalRecordText)
# 前一页按钮按下
def onPrevButtonClick(self):
print('*** onPrevButtonClick ');
limitIndex = (self.currentPage - 2) * self.PageRecordCount
self.recordQuery( limitIndex)
self.currentPage -= 1
self.updateStatus()
# 后一页按钮按下
def onNextButtonClick(self):
print('*** onNextButtonClick ');
limitIndex = self.currentPage * self.PageRecordCount
self.recordQuery( limitIndex)
self.currentPage += 1
self.updateStatus()
# 转到页按钮按下
def onSwitchPageButtonClick(self):
# 得到输入字符串
szText = self.switchPageLineEdit.text()
#数字正则表达式
pattern = re.compile(r'^[0-9]+$')
match = pattern.match(szText)
# 判断是否为数字
if not match :
QMessageBox.information(self, "提示", "请输入数字" )
return
# 是否为空
if szText == '' :
QMessageBox.information(self, "提示" , "请输入跳转页面" )
return
#得到页数
pageIndex = int(szText)
#判断是否有指定页
if pageIndex > self.totalPage or pageIndex < 1 :
QMessageBox.information(self, "提示", "没有指定的页面，请重新输入" )
return
#得到查询起始行号
limitIndex = (pageIndex-1) * self.PageRecordCount
#记录查询
self.recordQuery(limitIndex)
#设置当前页
self.currentPage = pageIndex
#刷新状态
self.updateStatus()
def recipe_search(self):
"""
PURPOSE
-----
To search and display the recipes from the desired query from the user using sqlite3 on a table-view
OUTPUT
-----
Displays a table of rows containing recipe titles that the user can double click on to view information about it
"""
self.conn.open() # Open our connection to database
self.setWindowTitle("Recipe Application")
if len(self.search_text.strip(' ')) == 0: # If the query is empty, throw an error message
error_dialog = QErrorMessage()
error_dialog.showMessage(
f"It seems you haven't input anything to search for the {self.search_category.lower()}'s associated recipes! Please try again.")
error_dialog.exec_()
else: # Otherwise we proceed as normally
self.search_list = self.search_text.split(',') # Create an array of desired ingredients
recipe_query = "" # Empty string to construct our query
for elements in self.search_list:
# Here we construct our query through a for-loop
# There shouldn't be many elements in our array in the first place, so no worries of efficiency
recipe_query += f"`{self.search_category}` LIKE '%" + elements + "%' AND "
if len(recipe_query) > 5: # On the off chance that there is nothing input into the text box, throw error
recipe_query = recipe_query[:len(recipe_query) - 5] # to remove last AND
cmd = \
f"""
SELECT `Recipe Title` FROM recipes R WHERE {recipe_query}
"""
title_query = QSqlQuery(self.conn) # Establish our query, prepare, and execute it
title_query.prepare(cmd)
title_query.exec_()
title_model = QSqlQueryModel() # Adds our queried information into a read-only model
title_model.setQuery(title_query)
if title_model.rowCount() == 0: # If no recipes return, throw error message saying there exists no recipe
# with their desired information
empty_error_dialog = QErrorMessage()
empty_error_dialog.showMessage(
f"It appears that there are no recipes pertaining to your search. Please try again!")
empty_error_dialog.exec_()
else:
# Adds information from out query model to a QTableView to be seen
self.title_view.setModel(title_model)
self.title_view.setWindowTitle("Recipe List")
self.title_view.doubleClicked.connect(lambda: self.pass_info(self.title_view.selectionModel().currentIndex()))
# Adds functionality of view_recipe when double clicking cell
self.title_view.setMaximumHeight(500) # Set max height of table + window
self.setMaximumHeight(500)
self.title_view.resizeColumnToContents(0) # Sets size of column to contents, i.e. longest recipe title
# Each cell is ~ 30 pixels, so we make our window slightly smaller than the rows can fit if it
# is less than 16 cells (which populates ~ 500 pixel height) to keep width consistent w/ scroll bar
# and to make sure we have no empty space in our window
self.title_view.resize(self.title_view.columnWidth(0) + 40, 25*title_model.rowCount())
self.resize(self.title_view.columnWidth(0) + 40, 25*title_model.rowCount())
self.title_view.show()
self.show()
class RecordsViewer(QDialog):
def __init__(self):
super(RecordsViewer, self).__init__()
self.resize(800, 500)
self.setWindowTitle('myOCR——我的识别历史')
self.setWindowIcon(QIcon('./images/icon.png'))
self.queryModel = None
self.tableView = None
self.currentPage = 0
self.totalPage = 0
self.totalRecord = 0
self.pageRecord = 10
self.initUI()
def initUI(self):
self.vbox = QVBoxLayout()
self.h1box = QHBoxLayout()
self.h2box = QHBoxLayout()
self.searchEdit = QLineEdit()
self.searchEdit.setFixedHeight(32)
self.searchEdit.setFont(QFont("苏新诗柳楷繁", 15))
self.searchButton = QPushButton("查询")
self.searchButton.setFixedHeight(32)
self.searchButton.setFont(QFont("苏新诗柳楷繁", 15))
self.condisionComboBox = QComboBox()
searchCondision = ['按识别编号查询', '按识别时间查询', '按识别类型查询', '按识别文字查询']
self.condisionComboBox.setFixedHeight(32)
self.condisionComboBox.setFont(QFont("苏新诗柳楷繁", 15))
self.condisionComboBox.addItems(searchCondision)
self.h1box.addWidget(self.searchEdit)
self.h1box.addWidget(self.condisionComboBox)
self.h1box.addWidget(self.searchButton)
self.jumpToLabel = QLabel(self)
self.jumpToLabel.setText("跳转到第")
self.jumpToLabel.setFont(QFont("苏新诗柳楷繁", 12))
self.jumpToLabel.setFixedWidth(90)
self.pageEdit = QLineEdit()
self.pageEdit.setFixedWidth(30)
self.pageEdit.setFont(QFont("苏新诗柳楷繁", 12))
s = "/" + str(self.totalPage) + "页"
self.pageLabel = QLabel(s)
self.pageLabel.setFont(QFont("苏新诗柳楷繁", 12))
self.pageLabel.setFixedWidth(40)
self.jumpToButton = QPushButton(self)
self.jumpToButton.setText("跳转")
self.jumpToButton.setFont(QFont("苏新诗柳楷繁", 12))
self.jumpToButton.setFixedHeight(30)
self.jumpToButton.setFixedWidth(60)
self.prevButton = QPushButton("前一页")
self.prevButton.setFont(QFont("苏新诗柳楷繁", 12))
self.prevButton.setFixedHeight(30)
self.prevButton.setFixedWidth(80)
self.backButton = QPushButton("后一页")
self.backButton.setFont(QFont("苏新诗柳楷繁", 12))
self.backButton.setFixedHeight(30)
self.backButton.setFixedWidth(80)
self.detailbutton = QPushButton(self)
self.detailbutton.setText("详细信息")
self.detailbutton.setFixedWidth(90)
self.detailbutton.setFont(QFont("苏新诗柳楷繁", 12))
self.detailbutton.clicked.connect(self.detailInfo)
self.hbox = QHBoxLayout()
self.hbox.addWidget(self.jumpToLabel)
self.hbox.addWidget(self.pageEdit)
self.hbox.addWidget(self.pageLabel)
self.hbox.addWidget(self.jumpToButton)
self.hbox.addStretch(1)
self.hbox.addWidget(self.detailbutton)
self.hbox.addStretch(1)
self.hbox.addWidget(self.prevButton)
self.hbox.addWidget(self.backButton)
widget = QWidget()
widget.setLayout(self.hbox)
widget.setFixedWidth(600)
self.h2box.addWidget(widget)
self.db = QSqlDatabase.addDatabase("QSQLITE")
self.db.setDatabaseName('./db/myOCR.db')
self.db.open()
self.tableView = QTableView()
self.tableView.horizontalHeader().setStretchLastSection(True)
self.tableView.setEditTriggers(QAbstractItemView.NoEditTriggers)
self.tableView.setSelectionBehavior(QAbstractItemView.SelectRows)
self.tableView.setFont(QFont("苏新诗柳楷繁", 12))
self.tableView.horizontalHeader().setFont(QFont("苏新诗柳楷繁", 12))
self.queryModel = QSqlQueryModel()
self.searchButtonClicked()
self.tableView.setModel(self.queryModel)
self.queryModel.setHeaderData(0, Qt.Horizontal, "识别编号")
self.queryModel.setHeaderData(1, Qt.Horizontal, "识别时间")
self.queryModel.setHeaderData(2, Qt.Horizontal, "识别类型")
self.queryModel.setHeaderData(3, Qt.Horizontal, "识别文字")
self.vbox.addLayout(self.h1box)
self.vbox.addWidget(self.tableView)
self.vbox.addLayout(self.h2box)
self.setLayout(self.vbox)
self.searchButton.clicked.connect(self.searchButtonClicked)
self.prevButton.clicked.connect(self.prevButtonClicked)
self.backButton.clicked.connect(self.backButtonClicked)
self.jumpToButton.clicked.connect(self.jumpToButtonClicked)
self.searchEdit.returnPressed.connect(self.searchButtonClicked)
def setButtonStatus(self):
if (self.currentPage == self.totalPage):
self.prevButton.setEnabled(True)
self.backButton.setEnabled(False)
if (self.currentPage == 1):
self.backButton.setEnabled(True)
self.prevButton.setEnabled(False)
if (self.currentPage < self.totalPage and self.currentPage > 1):
self.prevButton.setEnabled(True)
self.backButton.setEnabled(True)
def getTotalRecordCount(self):
self.queryModel.setQuery(
"select RecordId,RecordTime,RecordType,RecordText from records")
self.totalRecord = self.queryModel.rowCount()
return
def getPageCount(self):
self.getTotalRecordCount()
self.totalPage = int(
(self.totalRecord + self.pageRecord - 1) / self.pageRecord)
return
def recordQuery(self, index):
conditionChoice = self.condisionComboBox.currentText()
if (conditionChoice == "按识别编号查询"):
conditionChoice = 'RecordId'
elif (conditionChoice == "按识别时间查询"):
conditionChoice = 'RecordTime'
elif (conditionChoice == "按识别类型查询"):
conditionChoice = 'RecordType'
else:
conditionChoice = 'RecordText'
if (self.searchEdit.text() == ""):
queryCondition = "select RecordId,RecordTime,RecordType,RecordText from records order by RecordTime DESC"
self.queryModel.setQuery(queryCondition)
self.totalRecord = self.queryModel.rowCount()
self.totalPage = int(
(self.totalRecord + self.pageRecord - 1) / self.pageRecord)
label = "/" + str(int(self.totalPage)) + "页"
self.pageLabel.setText(label)
queryCondition = (
"select RecordId,RecordTime,RecordType,RecordText from records order by %s desc limit %d,%d "
% (conditionChoice, index, self.pageRecord))
self.queryModel.setQuery(queryCondition)
self.setButtonStatus()
return
temp = self.searchEdit.text()
s = '%'
for i in range(0, len(temp)):
s = s + temp[i] + "%"
queryCondition = (
"select RecordId,RecordTime,RecordType,RecordText from records where %s like '%s' order by %s desc"
% (conditionChoice, s, conditionChoice))
self.queryModel.setQuery(queryCondition)
self.totalRecord = self.queryModel.rowCount()
if (self.totalRecord == 0):
print(
QMessageBox.information(self, "提醒", "查询无记录", QMessageBox.Yes,
QMessageBox.Yes))
queryCondition = "select RecordId,RecordTime,RecordType,RecordText from records order by RecordTime DESC"
self.queryModel.setQuery(queryCondition)
self.totalRecord = self.queryModel.rowCount()
self.totalPage = int(
(self.totalRecord + self.pageRecord - 1) / self.pageRecord)
label = "/" + str(int(self.totalPage)) + "页"
self.pageLabel.setText(label)
queryCondition = (
"select RecordId,RecordTime,RecordType,RecordText from records order by %s desc limit %d,%d "
% (conditionChoice, index, self.pageRecord))
self.queryModel.setQuery(queryCondition)
self.setButtonStatus()
return
self.totalPage = int(
(self.totalRecord + self.pageRecord - 1) / self.pageRecord)
label = "/" + str(int(self.totalPage)) + "页"
self.pageLabel.setText(label)
queryCondition = (
"select RecordId,RecordTime,RecordType,RecordText from records where %s like '%s' order by %s desc limit %d,%d "
% (conditionChoice, s, conditionChoice, index, self.pageRecord))
self.queryModel.setQuery(queryCondition)
self.setButtonStatus()
return
def searchButtonClicked(self):
self.currentPage = 1
self.pageEdit.setText(str(self.currentPage))
self.getPageCount()
s = "/" + str(int(self.totalPage)) + "页"
self.pageLabel.setText(s)
index = (self.currentPage - 1) * self.pageRecord
self.recordQuery(index)
return
def prevButtonClicked(self):
self.currentPage -= 1
if (self.currentPage <= 1):
self.currentPage = 1
self.pageEdit.setText(str(self.currentPage))
index = (self.currentPage - 1) * self.pageRecord
self.recordQuery(index)
return
def backButtonClicked(self):
self.currentPage += 1
if (self.currentPage >= int(self.totalPage)):
self.currentPage = int(self.totalPage)
self.pageEdit.setText(str(self.currentPage))
index = (self.currentPage - 1) * self.pageRecord
self.recordQuery(index)
return
def jumpToButtonClicked(self):
if (self.pageEdit.text().isdigit()):
self.currentPage = int(self.pageEdit.text())
if (self.currentPage > self.totalPage):
self.currentPage = self.totalPage
if (self.currentPage <= 1):
self.currentPage = 1
else:
self.currentPage = 1
index = (self.currentPage - 1) * self.pageRecord
self.pageEdit.setText(str(self.currentPage))
self.recordQuery(index)
return
def detailInfo(self):
index_ = self.tableView.currentIndex().row()
if (index_ == -1):
print(
QMessageBox.warning(self, "警告", "您没有选中任何记录", QMessageBox.Yes,
QMessageBox.Yes))
return
else:
str = self.queryModel.data(self.queryModel.index(index_, 0))
type = self.queryModel.data(self.queryModel.index(index_, 2))
if (type == '身份证识别'):
self.recordDetail_id(str)
else:
self.recordDetail(str)
def recordDetail(self, RecordId):
recorddetaildialog = RecordDetailDialog(RecordId)
recorddetaildialog.show()
recorddetaildialog.exec_()
def recordDetail_id(self, RecordId):
recorddetail_id = RecordDetailDialog_id(RecordId)
recorddetail_id.show()
recorddetail_id.exec_()
