Pyqt 简明教程

PyQt - Database Handling

PyQt API 包含一个精密的类系统，用于与许多基于 SQL 的数据库通信。其 QSqlDatabase 通过连接对象提供访问权限。以下是当前可用的 SQL 驱动程序的列表：

PyQt API contains 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 数据库的连接是使用静态方法建立的：

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 查询。类中最重要的一个方法是 exec_(), 它将一个包含要执行的 SQL 语句的字符串作为参数。

QSqlQuery class has the functionality to execute and manipulate SQL commands. Both DDL and DML type of SQL queries can be executed. The most important method in the class is 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.

from PyQt4 import QtSql, QtGui

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

   if not db.open():
      QtGui.QMessageBox.critical(None, QtGui.qApp.tr("Cannot open database"),
         QtGui.qApp.tr("Unable to establish a database connection.\n"
            "This example needs SQLite support. Please read "
            "the Qt SQL driver documentation for information "
            "how to build it.\n\n" "Click Cancel to exit."),
         QtGui.QMessageBox.Cancel)

      return False

   query = QtSql.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__':
   import sys

   app = QtGui.QApplication(sys.argv)
   createDB()

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.

一个 QTableModel 对象按如下方式声明 −

A QTableModel 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 PyQt4 import QtCore, QtGui, QtSql
import sportsconnection

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

def createView(title, model):
   view = QtGui.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 = QtGui.QApplication(sys.argv)
   db = QtSql.QSqlDatabase.addDatabase('QSQLITE')
   db.setDatabaseName('sports.db')
   model = QtSql.QSqlTableModel()
   delrow = -1
   initializeModel(model)

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

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

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

   btn1 = QtGui.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 −

database handling output