1) 操作SQLite数据库
'' '
操作SQLite数据库
'' '
import sys
from PyQt5. QtSql import QSqlDatabase, QSqlQuery
def createDB ( ) :
db = QSqlDatabase. addDatabase ( 'QSQLITE' )
# 指定SQLite数据库的文件名
db. setDatabaseName ( './db/database.db' )
if not db. open ( ) :
print ( '无法建立与数据库的连接' )
return False
query = QSqlQuery ( )
query. exec ( 'create table people(id int primary key,name varchar(10),address varchar(50))' )
query. exec ( 'insert into people values(1,"李宁","Shenyang")' )
query. exec ( 'insert into people values(2,"超人","克星")' )
db. close ( )
return True
if __name__ == '__main__' :
createDB ( )
2)使用可视化的方式对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 ( ) )
3)分页显示数据
'' '
分页显示数据
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_ ( ) )