Pyqt5 简明教程

PyQt5 - Database Handling

PyQt5 库包含 QtSql 模块。它是一个复杂的类系统，用于与许多基于 SQL 的数据库通信。它的 QSqlDatabase 提供通过连接对象进行访问。以下是当前可用的 SQL 驱动程序的列表：

PyQt5 library contains QtSql module. It is an elaborate class system to communicate with many SQL based databases. Its QSqlDatabase provides access through a Connection object. Following is the list of currently available SQL drivers −

Sr.No.	Driver Type & Description
1	QDB2 IBM DB2
2	QIBASE Borland InterBase Driver
3	QMYSQL MySQL Driver
4	QOCI Oracle Call Interface Driver
5	QODBC ODBC Driver (includes Microsoft SQL Server)
6	QPSQL PostgreSQL Driver
7	QSQLITE SQLite version 3 or above
8	QSQLITE2 SQLite version 2

Example

对于本章，使用静态方法与 SQLite 数据库建立连接 −

For this chapter, a connection with a SQLite database is established using the static method −

db = QtSql.QSqlDatabase.addDatabase('QSQLITE')
db.setDatabaseName('sports.db')

QSqlDatabase 类的其他方法如下：

Other methods of QSqlDatabase class are as follows −

Sr.No.	Methods & Description
1	setDatabaseName() Sets the name of the database with which connection is sought
2	setHostName() Sets the name of the host on which the database is installed
3	setUserName() Specifies the user name for connection
4	setPassword() Sets the connection object’s password if any
5	commit() Commits the transactions and returns true if successful
6	rollback() Rolls back the database transaction
7	close() Closes the connection

QSqlQuery 类具有执行和操作 SQL 命令的功能。可以执行 DDL 和 DML 类型的 SQL 查询。第一步是使用以下语句创建 SQlite 数据库 −

QSqlQuery class has the functionality to execute and manipulate SQL commands. Both DDL and DML type of SQL queries can be executed. First step is to create SQlite database using the following statements −

db = QSqlDatabase.addDatabase('QSQLITE')
db.setDatabaseName('sportsdatabase.db')

接下来，使用 QSqlQuery() 方法获取查询对象并调用其最重要的方法 exec_(), 它将包含要执行的 SQL 语句的字符串作为参数。

Next, obtain Query object with QSqlQuery() method and call its most important method exec_(), which takes as an argument a string containing SQL statement to be executed.

query = QtSql.QSqlQuery()
query.exec_("create table sportsmen(id int primary key, " "firstname varchar(20), lastname varchar(20))")

下面的脚本创建了一个 sports.db SQLite 数据库，其中包含一个有五条记录填充的 sportsperson 表。

The following script creates a SQLite database sports.db with a table of sportsperson populated with five records.

import sys
from PyQt5.QtSql import *
from PyQt5.QtCore import *
from PyQt5.QtGui import *
from PyQt5.QtWidgets import *

def createDB():
   db = QSqlDatabase.addDatabase('QSQLITE')
   db.setDatabaseName('sportsdatabase.db')

   if not db.open():
      msg = QMessageBox()
      msg.setIcon(QMessageBox.Critical)
      msg.setText("Error in Database Creation")
      retval = msg.exec_()
      return False
   query = QSqlQuery()

   query.exec_("create table sportsmen(
      id int primary key, ""firstname varchar(20), lastname varchar(20))")

   query.exec_("insert into sportsmen values(101, 'Roger', 'Federer')")
   query.exec_("insert into sportsmen values(102, 'Christiano', 'Ronaldo')")
   query.exec_("insert into sportsmen values(103, 'Ussain', 'Bolt')")
   query.exec_("insert into sportsmen values(104, 'Sachin', 'Tendulkar')")
   query.exec_("insert into sportsmen values(105, 'Saina', 'Nehwal')")
   return True

if __name__ == '__main__':
   app = QApplication(sys.argv)
   createDB()

要确认 SQLite 数据库是否已创建并向其添加 sportsmen 表中的上述记录，请使用名为 SQLiteStudio 的 SQLite GUI 实用程序。

To confirm that the SQLite database is created with above records added in sportsmen table in it, use a SQLite Gui utility called SQLiteStudio.

PyQt 中的 QSqlTableModel 类是一个高级接口，它为以单个表的形式读写记录提供了可编辑的数据模型。此模型用于填充 QTableView 对象。它向用户呈现一个可滚动的可编辑视图，可以将其放在任何顶级窗口上。

QSqlTableModel class in PyQt is a high-level interface that provides editable data model for reading and writing records in a single table. This model is used to populate a QTableView object. It presents to the user a scrollable and editable view that can be put on any top level window.

QSqlTableModel 对象以以下方式声明：

A QSqlTableModel object is declared in the following manner −

model = QtSql.QSqlTableModel()

可以将其编辑策略设置为下列任意一项 −

Its editing strategy can be set to any of the following −

QSqlTableModel.OnFieldChange

All changes will be applied immediately

QSqlTableModel.OnRowChange

Changes will be applied when the user selects a different row

QSqlTableModel.OnManualSubmit

All changes will be cached until either submitAll() or revertAll() is called

Example

在以下示例中，sportsperson 表用作模型，并且策略设置为 −

In the following example, sportsperson table is used as a model and the strategy is set as −

model.setTable('sportsmen')
model.setEditStrategy(QtSql.QSqlTableModel.OnFieldChange)
   model.select()

QTableView 类是 PyQt 中模型/视图框架的一部分。QTableView 对象创建如下 −

QTableView class is part of Model/View framework in PyQt. The QTableView object is created as follows −

view = QtGui.QTableView()
view.setModel(model)
view.setWindowTitle(title)
return view

该 QTableView 对象和两个 QPushButton 窗口小部件将被添加到顶层 QDialog 窗口。add 按钮的 clicked() 信号连接到 addrow()，后者对模型表格执行 insertRow()。

This QTableView object and two QPushButton widgets are added to the top level QDialog window. Clicked() signal of add button is connected to addrow() which performs insertRow() on the model table.

button.clicked.connect(addrow)
def addrow():
   print model.rowCount()
   ret = model.insertRows(model.rowCount(), 1)
   print ret

与删除按钮相关联的槽执行一个删除行的 lambda 函数，该行由用户选择。

The Slot associated with the delete button executes a lambda function that deletes a row, which is selected by the user.

btn1.clicked.connect(lambda: model.removeRow(view1.currentIndex().row()))

完整代码如下所示：

The complete code is as follows −

import sys
from PyQt5.QtSql import *
from PyQt5.QtCore import *
from PyQt5.QtGui import *
from PyQt5.QtWidgets import *

def initializeModel(model):
   model.setTable('sportsmen')
   model.setEditStrategy(QSqlTableModel.OnFieldChange)
   model.select()
   model.setHeaderData(0, Qt.Horizontal, "ID")
   model.setHeaderData(1, Qt.Horizontal, "First name")
   model.setHeaderData(2, Qt.Horizontal, "Last name")

def createView(title, model):
   view = QTableView()
   view.setModel(model)
   view.setWindowTitle(title)
   return view

def addrow():
   print (model.rowCount())
   ret = model.insertRows(model.rowCount(), 1)
   print (ret)

def findrow(i):
   delrow = i.row()

if __name__ == '__main__':
   app = QApplication(sys.argv)
   db = QSqlDatabase.addDatabase('QSQLITE')
   db.setDatabaseName('sportsdatabase.db')
   model = QSqlTableModel()
   delrow = -1
   initializeModel(model)

   view1 = createView("Table Model (View 1)", model)
   view1.clicked.connect(findrow)

   dlg = QDialog()
   layout = QVBoxLayout()
   layout.addWidget(view1)

   button = QPushButton("Add a row")
   button.clicked.connect(addrow)
   layout.addWidget(button)

   btn1 = QPushButton("del a row")
   btn1.clicked.connect(lambda: model.removeRow(view1.currentIndex().row()))
   layout.addWidget(btn1)

   dlg.setLayout(layout)
   dlg.setWindowTitle("Database Demo")
   dlg.show()
   sys.exit(app.exec_())

上述代码生成以下输出 -

The above code produces the following output −

尝试添加和删除一些记录，然后返回 SQLiteStudio 以确认事务。

Try adding and deleting a few records and go back to SQLiteStudio to confirm the transactions.