pyqt连接Oracle数据库,PyQt数据库处理

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 −

Driver Type

Description

QDB2

IBM DB2

QIBASE

Borland InterBase Driver

QMYSQL

MySQL Driver

QOCI

Oracle Call Interface Driver

QODBC

ODBC Driver (includes Microsoft SQL Server)

QPSQL

PostgreSQL Driver

QSQLITE

SQLite version 3 or above

QSQLITE2

SQLite version 2

Example

A connection with a SQLite database is established using the static method −

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

db.setDatabaseName("sports.db")

Other methods of QSqlDatabase class are as follows −

S.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()

设置连接对象的密码(如果有)

5

承诺()

提交事务并成功返回true

6

回滚()

回滚数据库事务

7

关()

关闭连接

QSqlQuery类具有执行和操作SQL命令的功能。可以执行DDL和DML类型的SQL查询。该类中最重要的方法是exec_()，它将一个包含要执行的SQL语句的字符string作为参数。

query = QtSql.QSqlQuery()

query.exec_("create table sportsmen(id int primary key,

" "firstname varchar(20), lastname varchar(20))")

以下脚本创建一个带有五个记录的sportsperson表的SQLite数据库sports.db。

fromPyQt4importQtSql,QtGuidefcreateDB():db=QtSql.QSqlDatabase.addDatabase("QSQLITE")db.setDatabaseName("sports.db")ifnotdb.open():QtGui.QMessageBox.critical(None,QtGui.qApp.tr("Cannot open database"),QtGui.qApp.tr("Unable to establish a database connection.

""This example needs SQLite support. Please read ""the Qt SQL driver documentation for information ""how to build it.

""Click Cancel to exit."),QtGui.QMessageBox.Cancel)returnFalsequery=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")")returnTrueif__name__=="__main__":importsys

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

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

以下列方式声明QTableModel对象：

model = QtSql.QSqlTableModel()

其编辑策略可以设置为以下任何一种 -

QSqlTableModel.OnFieldChange

所有更改将立即应用

QSqlTableModel.OnRowChange

当用户选择不同的行时，将应用更改

QSqlTableModel.OnManualSubmit

所有更改将被缓存，直到submitAll()或revertAll()被调用

例

在以下示例中，运动员表用作模型，策略设置为 -

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

QTableView类是PyQt中Model / View框架的一部分。QTableView对象创建如下 -

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

这个QTableView对象和两个QPushButton小部件被添加到顶级QDialog窗口。添加按钮的clicked()信号连接到在模型表上执行insertRow()的addrow()。

button.clicked.connect(addrow)defaddrow():printmodel.rowCount()ret=model.insertRows(model.rowCount(),1)printret

与删除按钮相关联的插槽执行一个lambda功能，删除用户选择的行。

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

完整的代码如下 -

importsysfromPyQt4importQtCore,QtGui,QtSqlimportsportsconnectiondefinitializeModel(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")defcreateView(title,model):view=QtGui.QTableView()view.setModel(model)view.setWindowTitle(title)returnviewdefaddrow():printmodel.rowCount()ret=model.insertRows(model.rowCount(),1)printretdeffindrow(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=-1initializeModel(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_())

上述代码产生以下输出 -