QTableView显示SQLite数据
# coding:utf-8
from PyQt5.QtCore import Qt
from PyQt5 import QtGui,QtCore,QtWidgets,QtSql
import sys
class MainUi(QtWidgets.QMainWindow):
def __init__(self):
super().__init__()
self.initUi()
# 初始化UI界面
def initUi(self):
# 设置窗口标题
self.setWindowTitle("州的先生 - 在PyQt5中使用数据库")
# 设置窗口大小
self.resize(600, 400)
# 创建一个窗口部件
self.widget = QtWidgets.QWidget()
# 创建一个网格布局
self.grid_layout = QtWidgets.QGridLayout()
# 设置窗口部件的布局为网格布局
self.widget.setLayout(self.grid_layout)
# 创建一个按钮组
box = QtWidgets.QGroupBox('数据库按钮')
self.group_box = box
self.group_box_layout = QtWidgets.QVBoxLayout()
self.group_box.setLayout(self.group_box_layout)
# 创建一个表格部件
self.table_widget = QtWidgets.QTableView()
# 将上述两个部件添加到网格布局中
self.grid_layout.addWidget(self.group_box, 0, 0)
self.grid_layout.addWidget(self.table_widget, 0, 1)
# 创建按钮组的按钮
self.b_create_db = QtWidgets.QPushButton("创建数据库")
self.b_create_db.clicked.connect(self.create_db)
self.b_view_data = QtWidgets.QPushButton("浏览数据")
self.b_view_data.clicked.connect(self.view_data)
self.b_add_row = QtWidgets.QPushButton("添加一行")
self.b_add_row.clicked.connect(self.add_row_data)
self.b_delete_row = QtWidgets.QPushButton("删除一行")
self.b_delete_row.clicked.connect(self.del_row_data)
self.b_close = QtWidgets.QPushButton("退出")
self.b_close.clicked.connect(self.close)
# 添加按钮到按钮组中
self.group_box_layout.addWidget(self.b_create_db)
self.group_box_layout.addWidget(self.b_view_data)
self.group_box_layout.addWidget(self.b_add_row)
self.group_box_layout.addWidget(self.b_delete_row)
self.group_box_layout.addWidget(self.b_close)
# 设置UI界面的核心部件
self.setCentralWidget(self.widget)
def create_db(self):
try:
# 调用输入框获取数据库名称
# 调用输入框获取数据库名称
db_text, db_action = QtWidgets.QInputDialog.getText(self, '数据库名称', '请输入数据库名称', QtWidgets.QLineEdit.Normal)
if (db_text.replace(' ', '') != '') and (db_action is True):
print(db_text)
self.db_name = db_text
# 添加一个sqlite数据库连接并打开
db = QtSql.QSqlDatabase.addDatabase('QSQLITE')
db.setDatabaseName('{}.sqlite'.format(db_text))
db.open()
# 实例化一个查询对象
query = QtSql.QSqlQuery()
# 创建一个数据库表
query.exec_("create table zmister(ID int primary key, "
"site_name varchar(20), site_url varchar(100))")
# 插入三条数据
query.exec_("insert into zmister values(1000, '州的先生', 'https://zmister.com')")
query.exec_("insert into zmister values(1001, '百度', 'http://www.baidu.com')")
query.exec_("insert into zmister values(1002, '腾讯', 'http://www.qq.com')")
print('创建数据库成功')
except Exception as e:
print(e)
# 浏览数据
def view_data(self):
# 实例化一个可编辑数据模型
self.model = QtSql.QSqlTableModel()
self.table_widget.setModel(self.model)
self.model.setTable('zmister') # 设置数据模型的数据表
self.model.setEditStrategy(QtSql.QSqlTableModel.OnFieldChange) # 允许字段更改
self.model.select() # 查询所有数据
# 设置表格头
self.model.setHeaderData(0, QtCore.Qt.Horizontal, 'ID')
self.model.setHeaderData(1, QtCore.Qt.Horizontal, '站点名称')
self.model.setHeaderData(2, QtCore.Qt.Horizontal, '站点地址')
# 添加一行数据行
def add_row_data(self):
# 如果存在实例化的数据模型对象
if self.model:
self.model.insertRows(self.model.rowCount(), 1)
else:
self.create_db()
# 删除一行数据
def del_row_data(self):
if self.model:
self.model.removeRow(self.table_widget.currentIndex().row())
else:
self.create_db()
if __name__ == "__main__":
app = QtWidgets.QApplication(sys.argv)
form = MainUi()
form.show()
sys.exit(app.exec_())mysql数据库连接db = QtSql.QSqlDatabase.addDatabase('QMYSQL')
db.setHostName('主机名')
db.setDatabaseName('数据库名')
db.setUserName('用户名')
db.setPassword('密码')
db.open()
分页显示
'''
分页显示数据
limit n,m
limit 10,20
'''
import sys
import re
from PyQt5.QtWidgets import*
from PyQt5.QtCore import Qt
from PyQt5.QtSql import *
class DataGrid(QWidget):
def createTableAndInit(self):
# 添加数据库
self.db = QSqlDatabase.addDatabase('QSQLITE')
# 设置数据库名称
self.db.setDatabaseName('./db/database.db')
# 判断是否打开
if not self.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,'张三1','男',20,'计算机')")
query.exec("insert into student values(2,'李四1','男',19,'经管')")
query.exec("insert into student values(3,'王五1','男',22,'机械')")
query.exec("insert into student values(4,'赵六1','男',21,'法律')")
query.exec("insert into student values(5,'小明1','男',20,'英语')")
query.exec("insert into student values(6,'小李1','女',19,'计算机')")
query.exec("insert into student values(7,'小张1','男',20,'机械')")
query.exec("insert into student values(8,'小刚1','男',19,'经管')")
query.exec("insert into student values(9,'张三2','男',21,'计算机')")
query.exec("insert into student values(10,'张三3','女',20,'法律')")
query.exec("insert into student values(11,'王五2','男',19,'经管')")
query.exec("insert into student values(12,'张三4','男',20,'计算机')")
query.exec("insert into student values(13,'小李2','男',20,'机械')")
query.exec("insert into student values(14,'李四2','女',19,'经管')")
query.exec("insert into student values(15,'赵六3','男',21,'英语')")
query.exec("insert into student values(16,'李四2','男',19,'法律')")
query.exec("insert into student values(17,'小张2','女',22,'经管')")
query.exec("insert into student values(18,'李四3','男',21,'英语')")
query.exec("insert into student values(19,'小李3','女',19,'法律')")
query.exec("insert into student values(20,'王五3','女',20,'机械')")
query.exec("insert into student values(21,'张三4','男',22,'计算机')")
query.exec("insert into student values(22,'小李2','男',20,'法律')")
query.exec("insert into student values(23,'张三5','男',19,'经管')")
query.exec("insert into student values(24,'小张3','女',20,'计算机')")
query.exec("insert into student values(25,'李四4','男',22,'英语')")
query.exec("insert into student values(26,'赵六2','男',20,'机械')")
query.exec("insert into student values(27,'小李3','女',19,'英语')")
query.exec("insert into student values(28,'王五4','男',21,'经管')")
return True
def __init__(self):
super().__init__()
self.setWindowTitle("分页查询例子")
self.resize(750, 300)
self.createTableAndInit()
# 当前页
self.currentPage = 0
# 总页数
self.totalPage = 0
# 总记录数
self.totalRecrodCount = 0
# 每页显示记录数
self.PageRecordCount = 6
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.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):
# 声明查询模型
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()
# 得到页数
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();
if __name__ == '__main__':
app = QApplication(sys.argv)
# 创建窗口
example = DataGrid()
# 显示窗口
example.show()
sys.exit(app.exec_())
使用可视化的方式对SQLite数据库进行增、删、改、查操作
'''
使用可视化的方式对SQLite数据库进行增、删、改、查操作
QTableView
QSqlTableModel
'''
import sys
from PyQt5.QtCore import *
from PyQt5.QtGui import *
from PyQt5.QtWidgets import *
from PyQt5.QtSql import *
def initializeModel(model):
model.setTable('people')
model.setEditStrategy(QSqlTableModel.OnFieldChange)
model.select()
model.setHeaderData(0, Qt.Horizontal,'ID')
model.setHeaderData(1, Qt.Horizontal, '姓名')
model.setHeaderData(2, Qt.Horizontal, '地址')
def createView(title,model):
view = QTableView()
view.setModel(model)
view.setWindowTitle(title)
return view
def findrow(i):
delrow = i.row()
print('del row=%s' % str(delrow))
def addrow():
ret = model.insertRows(model.rowCount(),1)
print('insertRow=%s' % str(ret))
if __name__ == '__main__':
app = QApplication(sys.argv)
db = QSqlDatabase.addDatabase('QSQLITE')
db.setDatabaseName('./db/database.db')
model = QSqlTableModel()
delrow = -1
initializeModel(model)
view = createView("展示数据",model)
view.clicked.connect(findrow)
dlg = QDialog()
layout = QVBoxLayout()
layout.addWidget(view)
addBtn = QPushButton('添加一行')
addBtn.clicked.connect(addrow)
delBtn = QPushButton('删除一行')
delBtn.clicked.connect(lambda :model.removeRow(view.currentIndex().row()))
layout.addWidget(view)
layout.addWidget(addBtn)
layout.addWidget(delBtn)
dlg.setLayout(layout)
dlg.setWindowTitle("Database Demo")
dlg.resize(500,400)
dlg.show()
sys.exit(app.exec())
天道酬勤 循序渐进 技压群雄
