《快速掌握PyQt5》第二十六章 数据库

第二十六章 数据库

26.1 数据库连接和关闭

26.2 SQL语句使用

26.3 使用模型和视图

26.4 小结

---

《快速掌握PyQt5》专栏已整理成书出版，书名为《PyQt编程快速上手》，详情请见该链接。感谢大家一直以来的支持！祝大家PyQt用得越来越顺！

如果小伙伴还不了解数据库可以先去快速学习下，也可以选择先跳过本章，后续章节阅读并不会受到影响。

PyQt5提供了一些数据库驱动以方便我们来连接不同的数据库：

驱动类型	支持的数据库
QDB2	IBM DB2
QIBASE	Borland InterBase
QMYSQL	MySQL
QOCI	Oracle调用接口
QODBC	ODBC(包括微软SQL服务器)
QPSQL	PostgreSQL
QSQLITE	SQLite3或更高版本
QSQLITE2	SQLite2
QTDS	Sybase自适应服务器

本章笔者将会使用QSQLITE驱动和QMYSQL驱动来进行演示并讲解如何在PyQt5中进行数据库应用(连接和使用数据库操作其实都是类似的)。请读者确保已经安装并配置好MySQL数据库(SQLite数据库无需额外下载和配置，我们可直接在PyQt5中使用)。

26.1 数据库连接和关闭

在用到数据库的程序中，我们通常把数据库连接操作放在程序应用开始时(因为数据库无法连接的话，程序的功能就会收到影响了，所以要先确保数据库连接成功)。首先来看一下连接SQLite数据库：

import sys
from PyQt5.QtSql import QSqlDatabase
from PyQt5.QtWidgets import QApplication, QWidget, QMessageBox


class Demo(QWidget):
    def __init__(self):
        super(Demo, self).__init__()
        self.db = None
        self.db_connect()

    def db_connect(self):
        self.db = QSqlDatabase.addDatabase('QSQLITE')    # 1
        self.db.setDatabaseName('./test.db')             # 2
        if not self.db.open():                           # 3
            QMessageBox.critical(self, 'Database Connection', self.db.lastError().text())

    def closeEvent(self, QCloseEvent):                   # 4
        self.db.close()


if __name__ == '__main__':
    app = QApplication(sys.argv)
    demo = Demo()
    demo.show()
    sys.exit(app.exec_())

1. 通过调用QSqlDatabase类的addDatabase()方法来创建一个数据库连接，因为要连接SQLite数据库，所以这里传入的是QSQLite参数；

2. 调用setDatabaseName()设置要使用的数据库名称，只需要写入一个路径，文件名以.db结尾即可(若该数据库已经存在，则使用该数据库；若不存在则会新建一个)；

3. 调用open()方法打开数据库，若打开成功则返回True，失败则返回False。在这里我们用消息框来提示用户数据库打开失败，lastErrot().text()方法可以获取数据库打开失败的原因；

4. 在窗口关闭事件中通过self.db.close()方法来关闭数据库。

运行成功后我们会在当前目录下多出一个test.db的文件。

其他类型的数据库连接要多几行代码，这里以MySQL为例：

import sys
from PyQt5.QtSql import QSqlDatabase
from PyQt5.QtWidgets import QApplication, QWidget, QMessageBox


class Demo(QWidget):
    def __init__(self):
        super(Demo, self).__init__()
        self.db = None
        self.db_connect()

    def db_connect(self):
        self.db = QSqlDatabase.addDatabase('QMYSQL')     # 1
        self.db.setHostName('localhost')                 # 2
        self.db.setDatabaseName('test_db')               # 3
        self.db.setUserName('root')                      # 4
        self.db.setPassword('password')                    # 5
        if not self.db.open():
            QMessageBox.critical(self, 'Database Connection', self.db.lastError().text())

    def closeEvent(self, QCloseEvent):
        self.db.close()


if __name__ == '__main__':
    app = QApplication(sys.argv)
    demo = Demo()
    demo.show()
    sys.exit(app.exec_())

1. 要连接MySQL数据库那就要使用QMYSQL驱动；

2. 调用setHostName()方法设置主机名，因为是本地的，所以直接写localhost；

3. 调用setDatabaseName()设置要使用的数据库名称，请注意笔者之前已经在MySQL上新建了一个名为test_db的数据库，使用某数据库名前，请确保相应的数据库存在；

4-5. 调用setUserName()和setPassword()来分别输入数据库的用户名和密码。

如果出现“Driver not loaded”错误，请参考：PyQt5连接MySQL数据库Driver not loaded问题解决！！！

26.2 SQL语句使用

在PyQt5中，我们可以通过QSqlQuery类来执行SQL语句(以下均以操作MySQL数据库为例)：

import sys
from PyQt5.QtSql import QSqlDatabase, QSqlQuery
from PyQt5.QtWidgets import QApplication, QWidget, QMessageBox


class Demo(QWidget):
    def __init__(self):
        super(Demo, self).__init__()
        self.db = None
        self.db_connect()
        self.sql_exec()

    def db_connect(self):
        self.db = QSqlDatabase.addDatabase('QMYSQL')
        self.db.setHostName('localhost')
        self.db.setDatabaseName('test_db')
        self.db.setUserName('root')
        self.db.setPassword('password')
        if not self.db.open():
            QMessageBox.critical(self, 'Database Connection', self.db.lastError().text())

    def closeEvent(self, QCloseEvent):
        self.db.close()

    def sql_exec(self):
        query = QSqlQuery()  # 1
        query.exec_("CREATE TABLE students "                                  # 2
                    "(id INT(11) PRIMARY KEY, class VARCHAR(4) NOT NULL, "
                    "name VARCHAR(25) NOT NULL, score FLOAT)")

        query.exec_("INSERT INTO students (id, class, name, score) "          # 3
                    "VALUES (2018010401, '0104', 'Louis', 59.5)")
        query.exec_("INSERT INTO students (id, class, name, score) "
                    "VALUES (2018011603, '0116', 'Chris', 99.5)")

        query.exec_("SELECT name, class, score FROM students")                # 4
        while query.next():
            stu_name = query.value(0)
            stu_class = query.value(1)
            stu_score = query.value(2)
            print(stu_name, stu_class, stu_score)


if __name__ == '__main__':
    app = QApplication(sys.argv)
    demo = Demo()
    demo.show()
    sys.exit(app.exec_())

1. 实例化一个QSqlQuery对象，之后只用对该对象调用exec_()方法就可以执行SQL语句了；

2. 这里我们新建了一个students表，一共有四个字段id, class, name, score；

3. 这里插入两条数据。这里使用的是直接插入数据的方式，当然我们还可以使用占位符进行插入，这时就需要用到prepare()方法。一共有两种插入风格，首先是Oracle风格：

query.prepare("INSERT INTO students (id, class, name, score) "          
              "VALUES (:id, :class, :name, :score)")
query.bindValue(':id', 2018010401)
query.bindValue(':class', '0104')
query.bindValue(':name', 'Louis')
query.bindValue(':score', 59.5)
query.exec_()

还有一种是ODBC风格：

query.prepare("INSERT INTO students (id, class, name, score) "
              "VALUES (?, ?, ?, ?)")
query.addBindValue(2018011603)
query.addBindValue('0116')
query.addBindValue('Chris')
query.addBindValue(99.5)
query.exec_()

虽然麻烦点，但是可以避免将int型的数值转化为字符串。

4. 这里进行查询操作，执行完毕后调用next()方法就可以将记录指针定位到返回结果中的第一条。调用value()方法传入相应的索引值就可以返回指定的字段数据，最后控制台会输出我们的查询结果：

读者朋友可以自行下载相应的数据库可视化工具来查看表中的数据。

26.3 使用模型和视图

PyQt5提供了相比QSqlQuery之下更高级的接口：QSqlQueryModel、QSqlTableModel和QSqlRelationalTableModel。这些模型类让我们不必生硬地在代码中使用原始的SQL语句，而且提供了许多便捷的方法。对应的视图我们通常会选择表格视图QTableView(因为数据库中是用表来存储数据的嘛)。我们主要来讲下QSqlQueryModel和QSqlTableModel这两种模型，(QSqlRelationalTableModel用于有外键的表，大部分用法和QSqlTableModel类似，我们这里就简单讲一下如何建立表之间的联系并显示数据)。

QSqlQueryModel类为SQL结果集提供了只读的数据模型。它基于低级的QSqlQuery类，用于执行SQL语句和遍历结果集。接下来我们就直接使用在26.2中使用的数据表进行操作即可：

import sys
from PyQt5.QtCore import Qt
from PyQt5.QtSql import QSqlDatabase, QSqlQueryModel
from PyQt5.QtWidgets import QApplication, QMessageBox, QTableView


class Demo(QTableView):
    def __init__(self):
        super(Demo, self).__init__()
        self.db = None
        self.db_connect()
        self.sql_exec()

    def db_connect(self):
        self.db = QSqlDatabase.addDatabase('QMYSQL')
        self.db.setHostName('localhost')
        self.db.setDatabaseName('test_db')
        self.db.setUserName('root')
        self.db.setPassword('password')
        if not self.db.open():
            QMessageBox.critical(self, 'Database Connection', self.db.lastError().text())

    def closeEvent(self, QCloseEvent):
        self.db.close()

    def sql_exec(self):
        model = QSqlQueryModel()                        # 1
        model.setQuery("SELECT id, name, class, score FROM students")
        model.setHeaderData(0, Qt.Horizontal, 'ID')
        model.setHeaderData(1, Qt.Horizontal, 'Name')
        model.setHeaderData(2, Qt.Horizontal, 'Class')
        model.setHeaderData(3, Qt.Horizontal, ' Score')

        self.setModel(model)                            # 2

        for i in range(model.rowCount()):               # 3
            id = model.record(i).value('id')
            name = model.record(i).value(1)
            print(id, name)

        print('---------------------')

        for i in range(model.rowCount()):               # 4
            id = model.data(model.index(i, 0))
            name = model.data(model.index(i, 1))
            print(id, name)


if __name__ == '__main__':
    app = QApplication(sys.argv)
    demo = Demo()
    demo.show()
    sys.exit(app.exec_())

1. 实例化一个QSqlQueryModel模型，并调用setQuery()方法来执行一个SQL查询语句。setHeaderData()方法用来设置表格标题，若不使用该方法的话，程序则会默认使用数据表中的字段名作为标题；

2. 调用setModel()方法来设置视图所使用的模型(注意这里程序直接继承QTableView，所以直接使用self)；

3. 在使用QSqlQuery类执行查询语句后，我们是通过next()方法循环遍历结果集，并使用value()方法来获取数据。其实我们还可以使用record()方法，传入相应的行索引，我们就可以获取到相对应的行记录。

首先我们调用rowCount()方法获取行总数，接下来进行循环，将索引值传入record()中并调用value()方法传入字段名(也可以传入索引)获取到id和name。

4. 除了第3点中涉及的方法，我们还可以调用data()来获取数据，需要传入的是ModelIndex值。为获取该值，我们调用index()传入行列索引值即可。

运行截图如下：

QSqlQueryModel模型只提供只读操作，如果要同时获取写入操作的话，那就需要继承QSqlQueryModel并重新实现setData()和flags()方法。另一种选择是使用QSqlTableModel，该模型可读可写：

def sql_exec(self):
    model = QSqlTableModel()                            # 1
    model.setTable('students')
    model.setEditStrategy(QSqlTableModel.OnFieldChange)
    model.setHeaderData(0, Qt.Horizontal, 'ID')
    model.setHeaderData(1, Qt.Horizontal, 'Class')
    model.setHeaderData(2, Qt.Horizontal, 'Name')
    model.setHeaderData(3, Qt.Horizontal, 'Score')
    model.select()

    self.setModel(model)

其他地方代码不变，我们只需要改下sql_exe()函数即可。

1. 实例化一个QSqlTableModel模型，调用setTable()来选择要进行操作的数据表。setEditStrategy()用来设置模型的编辑策略，一共有以下三种：

QSqlTableModel::OnFieldChange	0	所有变更立即更新到数据库中
QSqlTableModel::OnRowChange	1	当用户对某行数据操作后，点击其他行时再更新数据库
QSqlTableModel::OnManualSubmit	2	只有在调用submitAll()或者reverAll()后才会更新数据库

最后调用select()方法选择该表中的所有数据，相应的视图也会显示出全部的数据；

此时运行截图如下：

我们把第三行Chris的分数改成100，由于编辑策略是OnFieldChange，所以此时数据库中的值也会立即改变：

我们关掉程序，然后再次运行就会发现数据库中的值确实改变了，分数已经变成了100。

以下是插入操作：

def sql_exec(self):
    model = QSqlTableModel()
    model.setTable('students')
    model.setEditStrategy(QSqlTableModel.OnFieldChange)
    model.setHeaderData(0, Qt.Horizontal, 'ID')
    model.setHeaderData(1, Qt.Horizontal, 'Class')
    model.setHeaderData(2, Qt.Horizontal, 'Name')
    model.setHeaderData(3, Qt.Horizontal, 'Score')
    model.select()

    model.insertRow(0)                # 1
    model.setData(model.index(0, 0), 201801010111)
    model.setData(model.index(0, 1), '0101')
    model.setData(model.index(0, 2), 'Who Cares')
    model.setData(model.index(0, 3), 0.5)
    model.submit()

    self.setModel(model)

1. 要进行插入操作的话，只需要调用insertRow()方法传入索引值来确定要插入的位置。这里我们传入0表示在第一行插入。setData()方法可以用来插入或更新值，需要两个参数，第一个是位置，第二个是插入的数据。最后调用submit()方法来提交我们对数据库所做的更改。

此时运行截图如下：

过滤操作也非常简单：

def sql_exec(self):
    model = QSqlTableModel()
    model.setTable('students')
    model.setEditStrategy(QSqlTableModel.OnFieldChange)
    model.setHeaderData(0, Qt.Horizontal, 'ID')
    model.setHeaderData(1, Qt.Horizontal, 'Class')
    model.setHeaderData(2, Qt.Horizontal, 'Name')
    model.setHeaderData(3, Qt.Horizontal, 'Score')

    model.insertRow(0)
    model.setData(model.index(0, 0), 2018010111)
    model.setData(model.index(0, 1), '0101')
    model.setData(model.index(0, 2), 'Who Cares')
    model.setData(model.index(0, 3), 0.5)
    model.submit()

    model.setFilter('score < 60')           # 1
    model.select()

    self.setModel(model)

1. 调用setFilter()方法可以进行过滤操作。在这里我们选择了分数不及格的同学进行显示。这等价于下面的SQL语句：

SELECT * FROM students WHERE score < 60

此时运行截图如下：

再来看一下如何删除行：

def sql_exec(self):
    model = QSqlTableModel()
    model.setTable('students')
    model.setEditStrategy(QSqlTableModel.OnFieldChange)
    model.setHeaderData(0, Qt.Horizontal, 'ID')
    model.setHeaderData(1, Qt.Horizontal, 'Class')
    model.setHeaderData(2, Qt.Horizontal, 'Name')
    model.setHeaderData(3, Qt.Horizontal, 'Score')

    model.insertRow(0)
    model.setData(model.index(0, 0), 2018010111)
    model.setData(model.index(0, 1), '0101')
    model.setData(model.index(0, 2), 'Who Cares')
    model.setData(model.index(0, 3), 0.5)
    model.submit()

    model.setFilter('score < 60')
    model.select()

    model.removeRow(0)  # 1
    model.submit()

    self.setModel(model)

1. 调用removeRow()方法传入索引值即可。

此时运行截图如下：

如果想获取单独数据的话，用上面讲过的record()+value()方法或者data()方法即可。

最后是QSqlRelationalTableModel，该模型用来建立表之间的联系(即外键)，大部分用法和QSqlTableModel类似，所以笔者这里就主要讲解下如何建立联系并显示。同样我们会用到上面的students表，不过还需要再新建另一个teachers表：

然后插入两条数据：

def sql_exec(self):
    model = QSqlRelationalTableModel()
    model.setTable('students')

    # 1
    model.setRelation(1, QSqlRelation('teachers', 'class', 'name'))

    # 2
    model.setHeaderData(0, Qt.Horizontal, 'ID')
    model.setHeaderData(1, Qt.Horizontal, 'Teacher')
    model.setHeaderData(2, Qt.Horizontal, 'Student')
    model.setHeaderData(3, Qt.Horizontal, 'Score')
    model.select()

    self.setModel(model)

1. 要建立表之间的联系，就要调用setRelation()方法。它一共传入两个参数，第一个是作为外键的索引，第二个是QSqlRelation对象。可以知道笔者这里是想把class字段作为外键的，而在students表中class外键的索引为1。QSqlRelation对象实例化需要三个参数：外键关系对应的表、外键字段名以及要进行显示的字段。外键关系对应的表很明显就是teachers了，teachers表中主键名class就是联系时用的外键字段名称，最后个teachers中的name字段就是用于显示的。

简单来说，这行代码就是把两个表建立了联系，并且把students中class字段下的值换成teachers表中name字段下的值用于显示；

2. 设置表格标题，因为显示的不是班级而是老师的名字，所以把Class改成了Teacher。

运行截图如下：

26.4 小结

1. 使用数据库前当然是要先建立连接，别忘了在程序关闭时要同时关闭数据库哦～

2. 如果是单纯的想执行SQL语句，那我们可以使用QSqlQuery类；

3. 数据模型类为我们提供了很多便捷的方法，非常适合在大型复杂的项目中使用；

4. QSqlQueryModel为只读模型，如果想要读写的话，建议使用QSqlTableModel，这个也是我们平常使用最多的模型了。

欢迎关注我的微信公众号，发现更多有趣内容：