PyQt5快速入门(八)PyQt5数据库操作

一、SQLite数据库

1、SQLite简介

SQLite是一个轻量级的数据库，实现了自给自足、无服务器、零配置、事务性的SQL数据库引擎，主要作为手机应用的数据库以及小型桌面应用的数据库。

官方网站：

https://www.sqlite.org

2、SQLite常用操作

创建数据库文件，创建后进行入SQLite命令行模式。

sqlite3 DatabaseName.db

查看已经存在的数据库文件，在SQLite命令行模式执行：

.databases

打开已经存在的数据库文件，如果数据库文件不存在，则创建。

sqlite3 DatabaseName.db

查看帮助信息，在SQLite命令行模式执行：

.help

创建表，在SQLite命令行模式执行：

create table person(id integer primary key, name text);

插入数据到表：

insert into person(id, name) values(1, "zhangsan");

查询操作：

select * from person;

查询表的结构：

.schema person

3、SQLite管理工具

SQLite有多个开源且优秀的DBMS(数据库管理系统)，提供了界面操作SQLite数据库。

SQLiteStudio是一款非常专业的SQLite数据库管理软件，体积小巧，功能强大，支持中文，免安装。

SQLiteStudio下载：

https://sqlitestudio.pl/index.rvt?act=download

二、连接数据库

1、数据库驱动类型

PyQt中，QSqlDatabase类用于连接数据库，可以使用数据库驱动与不同的数据库进行交互，一个QSqlDatabase实例代表一次数据库连接。可用数据库驱动类型如下：

QDB2 IBM DB2驱动程序

QMYSQL MySQL驱动程序

QOCI Oracle调用接口驱动程序

QODBC ODBC驱动程序(包括MS SQL Server)

QPSQL PostgreSQL驱动程序

QSQLITE SQLite3驱动程序

QSQLITE2 SQLite2驱动程序

2、QSqlDatabase常用方法

QSqlDatabase常用方法如下：

addDataBase:设置连接数据库的数据库驱动类型

setDatabaseName：设置所连接的数据库名称

setHostName：设置数据库所在的主机名称

setUserName：指定连接的用户名

setPassword：设置连接对象的密码

commit：提交事务，如果执行成功返回True。

rollback：回滚数据库事务

close：关闭数据库连接

3、数据库连接实例

import sys

from PyQt5.QtSql import QSqlDatabase

from PyQt5.QtCore import *

if __name__ == "__main__":

app = QCoreApplication(sys.argv)

db = QSqlDatabase.addDatabase("QSQLITE")

db.setDatabaseName("/home/user/test.db")

if db.open():

print("open DB success.")

sys.exit(app.exec_())

三、执行SQL语句

QSqlQuery具有执行和操作SQL语句的功能，可以执行DDL和DML类型的SQL查询，QSqlQuery.exec_()用于执行SQL操作。

import sys

from PyQt5.QtSql import QSqlDatabase, QSqlQuery

from PyQt5.QtCore import *

def createDB():

db = QSqlDatabase.addDatabase("QSQLITE")

db.setDatabaseName("/home/user/test.db")

if db.open():

query = QSqlQuery()

query.exec_("create table person(id int primary key, name varchar(20), address varchar(30))")

query.exec_("insert into person values(1, 'Bauer', 'beijing')")

query.exec_("insert into person values(2, 'Jack', 'shanghai')")

query.exec_("insert into person values(3, 'Alex', 'chengdu')")

db.close()

if __name__ == "__main__":

app = QCoreApplication(sys.argv)

createDB()

sys.exit(app.exec_())

执行完SQL语句后，如果没有其它数据库操作，需要使用db.close关闭数据库连接，因为数据库连接资源是有限的，不再使用的数据库连接必须关闭，否则数据库连接资源最终会被耗尽，导致程序无法正常连接数据库。

如果在PyQt的窗口中需要读取数据库的数据并进行显示，则需要在窗口初始化时打开数据库，在窗口关闭时关闭数据库连接。

import sys

from PyQt5.QtSql import QSqlDatabase, QSqlQuery

from PyQt5.QtCore import *

from PyQt5.QtWidgets import *

class MainWindow(QWidget):

def __init__(self, parent=None):

super(MainWindow, self).__init__(parent)

self.db = QSqlDatabase.addDatabase("QSQLITE")

self.db.setDatabaseName("/home/user/test.db")

self.db.open()

def closeEvent(self, event):

self.db.close()

if __name__ == "__main__":

app = QApplication(sys.argv)

window = MainWindow()

window.show()

sys.exit(app.exec_())

四、数据库模型视图

Qt中的QSqlTableModel是一个高级接口，提供了可读可写的数据模型，用于在单个表中读取和保存数据，可以在QTableView展示数据库的表格。当连接到数据库后，使用seTable设置要查询的表，使用setFilter函数设置过滤器条件，然后使用select函数进行查询操作。可以使用setEditerStrategy函数设置编辑策略，可设置编辑策略如下：

QSqlTableModel.OnFieldChange：所有变更实时更新到数据库

QSqlTableModel.OnRowChange：当用户选择不同的行时，在当前行进行更新

QSqlTableModel.OnManuallSubmit：手动提交，不自动提交

import sys

from PyQt5.QtSql import QSqlDatabase, QSqlQuery, QSqlTableModel

from PyQt5.QtCore import *

from PyQt5.QtWidgets import *

class MainWindow(QWidget):

def __init__(self, parent=None):

super(MainWindow, self).__init__(parent)

self.db = QSqlDatabase.addDatabase("QSQLITE")

self.db.setDatabaseName("/home/user/test.db")

self.db.open()

self.model = QSqlTableModel()

self.initializedModel()

self.tableView = QTableView()

self.tableView.setModel(self.model)

self.layout = QVBoxLayout()

addButton = QPushButton("add")

deleteButton = QPushButton("delete")

hLayout = QHBoxLayout()

hLayout.addWidget(addButton)

hLayout.addWidget(deleteButton)

self.layout.addWidget(self.tableView)

self.layout.addLayout(hLayout)

self.setLayout(self.layout)

self.resize(600, 400)

addButton.clicked.connect(self.onAddRow)

deleteButton.clicked.connect(self.onDeleteRow)

def initializedModel(self):

self.model.setTable("person")

self.model.setEditStrategy(QSqlTableModel.OnFieldChange)

self.model.select()

self.model.setHeaderData(0, Qt.Horizontal, "ID")

self.model.setHeaderData(1, Qt.Horizontal, "Name")

self.model.setHeaderData(2, Qt.Horizontal, "Address")

def onAddRow(self):

self.model.insertRows(self.model.rowCount(), 1)

self.model.submit()

def onDeleteRow(self):

self.model.removeRow(self.tableView.currentIndex().row())

self.model.submit()

self.model.select()

def closeEvent(self, event):

self.db.close()

if __name__ == "__main__":

app = QApplication(sys.argv)

window = MainWindow()

window.show()

sys.exit(app.exec_())

五、分页查询

1、数据准备

分页使用数据为学生信息student表，可以使用SQLite命令行使用SQL语句插入，也可以使用Python程序创建表并插入数据。

db = QSqlDatabase.addDatabase("QSQLITE")

db.setDatabaseName("/home/user/test.db")

if not db.open():

return False

query = QSqlQuery()

query.exec_("create table student(id int primary key, name varchar(20), sex varchar(8), age int);")

query.exec_("insert into student values(1, 'Bauer', 'Man', 25)")

query.exec_("insert into student values(2, 'Alex', 'Man', 24)")

query.exec_("insert into student values(3, 'Mary', 'Female', 23)")

query.exec_("insert into student values(4, 'Jack', 'Man', 25)")

query.exec_("insert into student values(5, 'xiaoming', 'Man', 24)")

query.exec_("insert into student values(6, 'xiaohong', 'Female', 23)")

query.exec_("insert into student values(7, 'xiaowang', 'Man', 25)")

query.exec_("insert into student values(8, 'xiaozhang', 'Man', 25)")

query.exec_("insert into student values(9, 'xiaoli', 'Man', 25)")

query.exec_("insert into student values(10, 'xiaohan', 'Man', 25)")

2、分页窗口

分页窗口包括标签、前一页、后一页、跳转按钮等。

import sys

from PyQt5.QtSql import QSqlDatabase, QSqlQuery, QSqlTableModel, QSqlQueryModel

from PyQt5.QtCore import *

from PyQt5.QtWidgets import *

class DataGrid(QWidget):

def __init__(self, parent=None):

super(DataGrid, self).__init__(parent)

# 数据库连接

self.db = None

# 布局管理器

self.layout = QVBoxLayout()

# 查询模型

self.queryModel = QSqlQueryModel()

# 表格视图

self.tableView = QTableView()

self.tableView.setModel(self.queryModel)

#

self.totalPageLabel = QLabel()

self.currentPageLabel = QLabel()

self.switchPageLineEdit = QLineEdit()

self.prevButton = QPushButton("Prev")

self.nextButton = QPushButton("Next")

self.switchPageButton = QPushButton("Switch")

self.currentPage = 0

self.totalPage = 0

self.totalRecordCount = 0

self.pageRecordCount = 5

def initUI(self):

self.tableView.horizontalHeader().setStretchLastSection(True)

self.tableView.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)

self.layout.addWidget(self.tableView)

hLayout = QHBoxLayout()

hLayout.addWidget(self.prevButton)

hLayout.addWidget(self.nextButton)

hLayout.addWidget(QLabel("跳转到"))

self.switchPageLineEdit.setFixedWidth(40)

hLayout.addWidget(self.switchPageLineEdit)

hLayout.addWidget(QLabel("页"))

hLayout.addWidget(self.switchPageButton)

hLayout.addWidget(QLabel("当前页："))

hLayout.addWidget(self.currentPageLabel)

hLayout.addWidget(QLabel("总页数："))

hLayout.addWidget(self.totalPageLabel)

hLayout.addStretch(1)

self.layout.addLayout(hLayout)

self.setLayout(self.layout)

self.setWindowTitle("DataGrid")

self.resize(600, 300)

def closeEvent(self, event):

self.db.close()

if __name__ == "__main__":

app = QApplication(sys.argv)

window = DataGrid()

window.initUI()

window.show()

sys.exit(app.exec_())

3、分页查询实现

读取数据库的student表，初始化表格数据模型。

import sys

from PyQt5.QtSql import QSqlDatabase, QSqlQuery, QSqlTableModel, QSqlQueryModel

from PyQt5.QtCore import *

from PyQt5.QtWidgets import *

import re

class DataGrid(QWidget):

def __init__(self, parent=None):

super(DataGrid, self).__init__(parent)

# 声明数据库连接

self.db = None

# 布局管理器

self.layout = QVBoxLayout()

# 查询模型

self.queryModel = QSqlQueryModel()

# 表格视图

self.tableView = QTableView()

self.tableView.setModel(self.queryModel)

#

self.totalPageLabel = QLabel()

self.currentPageLabel = QLabel()

self.switchPageLineEdit = QLineEdit()

self.prevButton = QPushButton("Prev")

self.nextButton = QPushButton("Next")

self.switchPageButton = QPushButton("Switch")

# 当前页

self.currentPage = 1

# 总页数

self.totalPage = None

# 总记录数

self.totalRecordCount = None

# 每页记录数

self.pageRecordCount = 4

self.initUI()

self.initializedModel()

self.setUpConnect()

self.updateStatus()

def initUI(self):

self.tableView.horizontalHeader().setStretchLastSection(True)

self.tableView.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)

self.layout.addWidget(self.tableView)

hLayout = QHBoxLayout()

hLayout.addWidget(self.prevButton)

hLayout.addWidget(self.nextButton)

hLayout.addWidget(QLabel("跳转到"))

self.switchPageLineEdit.setFixedWidth(40)

hLayout.addWidget(self.switchPageLineEdit)

hLayout.addWidget(QLabel("页"))

hLayout.addWidget(self.switchPageButton)

hLayout.addWidget(QLabel("当前页："))

hLayout.addWidget(self.currentPageLabel)

hLayout.addWidget(QLabel("总页数："))

hLayout.addWidget(self.totalPageLabel)

hLayout.addStretch(1)

self.layout.addLayout(hLayout)

self.setLayout(self.layout)

self.setWindowTitle("DataGrid")

self.resize(600, 300)

def setUpConnect(self):

self.prevButton.clicked.connect(self.onPrevPage)

self.nextButton.clicked.connect(self.onNextPage)

self.switchPageButton.clicked.connect(self.onSwitchPage)

def initializedModel(self):

self.db = QSqlDatabase.addDatabase("QSQLITE")

self.db.setDatabaseName("/home/user/test.db")

if not self.db.open():

return False

self.queryModel.setHeaderData(0, Qt.Horizontal, "ID")

self.queryModel.setHeaderData(1, Qt.Horizontal, "Name")

self.queryModel.setHeaderData(2, Qt.Horizontal, "Sex")

self.queryModel.setHeaderData(3, Qt.Horizontal, "Age")

# 获取表的所有记录数

sql = "SELECT * FROM student"

self.queryModel.setQuery(sql, self.db)

self.totalRecordCount = self.queryModel.rowCount()

if self.totalRecordCount % self.pageRecordCount == 0:

self.totalPage = self.totalRecordCount / self.pageRecordCount

else:

self.totalPage = int(self.totalRecordCount / self.pageRecordCount) + 1

# 显示第1页

sql = "SELECT * FROM student limit %d,%d" % (0, self.pageRecordCount)

self.queryModel.setQuery(sql, self.db)

def onPrevPage(self):

self.currentPage -= 1

limitIndex = (self.currentPage - 1) * self.pageRecordCount

self.queryRecord(limitIndex)

self.updateStatus()

def onNextPage(self):

self.currentPage += 1

limitIndex = (self.currentPage - 1) * self.pageRecordCount

self.queryRecord(limitIndex)

self.updateStatus()

def onSwitchPage(self):

szText = self.switchPageLineEdit.text()

pattern = re.compile('^[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.queryRecord(limitIndex)

self.currentPage = pageIndex

self.updateStatus()

# 根据分页查询记录

def queryRecord(self, limitIndex):

sql = "SELECT * FROM student limit %d,%d" % (limitIndex, self.pageRecordCount)

self.queryModel.setQuery(sql)

# 更新空间状态

def updateStatus(self):

self.currentPageLabel.setText(str(self.currentPage))

self.totalPageLabel.setText(str(self.totalPage))

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 closeEvent(self, event):

self.db.close()

if __name__ == "__main__":

app = QApplication(sys.argv)

window = DataGrid()

window.show()

sys.exit(app.exec_())