第1章：initDb.h到createDb.py#

首先，将创建SQLite数据库和表的C++代码迁移过来。在这种情况下，所有与此相关的C++代码都在initdb.h中。该头文件中的代码分为以下部分

  • initDb - 创建数据库和必要的表

  • addBooks - 向books表添加数据。

  • addAuthor - 向authors表添加数据。

  • addGenre - 向genres表添加数据。

首先，在createdb.py的开始添加以下import语句

1
2from PySide6.QtSql import QSqlDatabase, QSqlError, QSqlQuery
3from datetime import date

initDb函数完成了大多数设置数据库所需的工作，但它依赖于addAuthor、addGenre和addBook辅助函数来填充表。首先迁移这些辅助函数。以下是这些函数的C++和Python版本的示例

C++版本#

 1void addBook(QSqlQuery &q, const QString &title, int year, const QVariant &authorId,
 2             const QVariant &genreId, int rating)
 3{
 4    q.addBindValue(title);
 5    q.addBindValue(year);
 6    q.addBindValue(authorId);
 7    q.addBindValue(genreId);
 8    q.addBindValue(rating);
 9    q.exec();
10}
11
12QVariant addGenre(QSqlQuery &q, const QString &name)
13{
14    q.addBindValue(name);
15    q.exec();
16    return q.lastInsertId();
17}
18
19QVariant addAuthor(QSqlQuery &q, const QString &name, const QDate &birthdate)
20{
21    q.addBindValue(name);
22    q.addBindValue(birthdate);
23    q.exec();
24    return q.lastInsertId();
25}

Python 版本#

 1
 2def add_book(q, title, year, authorId, genreId, rating):
 3    q.addBindValue(title)
 4    q.addBindValue(year)
 5    q.addBindValue(authorId)
 6    q.addBindValue(genreId)
 7    q.addBindValue(rating)
 8    q.exec_()
 9
10
11def add_genre(q, name):
12    q.addBindValue(name)
13    q.exec_()
14    return q.lastInsertId()
15
16
17def add_author(q, name, birthdate):
18    q.addBindValue(name)
19    q.addBindValue(str(birthdate))
20    q.exec_()
21    return q.lastInsertId()
22

辅助函数已经就绪，现在对 initDb 进行端口。以下是这个函数的 C++ 和 Python 版本

C++ 版本（initDb）#

 1const auto BOOKS_SQL = QLatin1String(R"(
 2    create table books(id integer primary key, title varchar, author integer,
 3                       genre integer, year integer, rating integer)
 4    )");
 5
 6const auto AUTHORS_SQL =  QLatin1String(R"(
 7    create table authors(id integer primary key, name varchar, birthdate date)
 8    )");
 9
10const auto GENRES_SQL = QLatin1String(R"(
11    create table genres(id integer primary key, name varchar)
12    )");
13
14const auto INSERT_AUTHOR_SQL = QLatin1String(R"(
15    insert into authors(name, birthdate) values(?, ?)
16    )");
17
18const auto INSERT_BOOK_SQL = QLatin1String(R"(
19    insert into books(title, year, author, genre, rating)
20                      values(?, ?, ?, ?, ?)
21    )");
22
23const auto INSERT_GENRE_SQL = QLatin1String(R"(
24    insert into genres(name) values(?)
25    )");
26
27QSqlError initDb()
28{
29    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
30    db.setDatabaseName(":memory:");
31
32    if (!db.open())
33        return db.lastError();
34
35    QStringList tables = db.tables();
36    if (tables.contains("books", Qt::CaseInsensitive)
37        && tables.contains("authors", Qt::CaseInsensitive))
38        return QSqlError();
39
40    QSqlQuery q;
41    if (!q.exec(BOOKS_SQL))
42        return q.lastError();
43    if (!q.exec(AUTHORS_SQL))
44        return q.lastError();
45    if (!q.exec(GENRES_SQL))
46        return q.lastError();
47
48    if (!q.prepare(INSERT_AUTHOR_SQL))
49        return q.lastError();
50    QVariant asimovId = addAuthor(q, QLatin1String("Isaac Asimov"), QDate(1920, 2, 1));
51    QVariant greeneId = addAuthor(q, QLatin1String("Graham Greene"), QDate(1904, 10, 2));
52    QVariant pratchettId = addAuthor(q, QLatin1String("Terry Pratchett"), QDate(1948, 4, 28));
53
54    if (!q.prepare(INSERT_GENRE_SQL))
55        return q.lastError();
56    QVariant sfiction = addGenre(q, QLatin1String("Science Fiction"));
57    QVariant fiction = addGenre(q, QLatin1String("Fiction"));
58    QVariant fantasy = addGenre(q, QLatin1String("Fantasy"));
59
60    if (!q.prepare(INSERT_BOOK_SQL))
61        return q.lastError();
62    addBook(q, QLatin1String("Foundation"), 1951, asimovId, sfiction, 3);
63    addBook(q, QLatin1String("Foundation and Empire"), 1952, asimovId, sfiction, 4);
64    addBook(q, QLatin1String("Second Foundation"), 1953, asimovId, sfiction, 3);
65    addBook(q, QLatin1String("Foundation's Edge"), 1982, asimovId, sfiction, 3);
66    addBook(q, QLatin1String("Foundation and Earth"), 1986, asimovId, sfiction, 4);
67    addBook(q, QLatin1String("Prelude to Foundation"), 1988, asimovId, sfiction, 3);
68    addBook(q, QLatin1String("Forward the Foundation"), 1993, asimovId, sfiction, 3);
69    addBook(q, QLatin1String("The Power and the Glory"), 1940, greeneId, fiction, 4);
70    addBook(q, QLatin1String("The Third Man"), 1950, greeneId, fiction, 5);
71    addBook(q, QLatin1String("Our Man in Havana"), 1958, greeneId, fiction, 4);
72    addBook(q, QLatin1String("Guards! Guards!"), 1989, pratchettId, fantasy, 3);
73    addBook(q, QLatin1String("Night Watch"), 2002, pratchettId, fantasy, 3);
74    addBook(q, QLatin1String("Going Postal"), 2004, pratchettId, fantasy, 3);
75
76    return QSqlError();
77}

Python 版本（init_db）#

 1
 2BOOKS_SQL = """
 3    create table books(id integer primary key, title varchar, author integer,
 4                       genre integer, year integer, rating integer)
 5    """
 6AUTHORS_SQL = """
 7    create table authors(id integer primary key, name varchar, birthdate text)
 8    """
 9GENRES_SQL = """
10    create table genres(id integer primary key, name varchar)
11    """
12INSERT_AUTHOR_SQL = """
13    insert into authors(name, birthdate) values(?, ?)
14    """
15INSERT_GENRE_SQL = """
16    insert into genres(name) values(?)
17    """
18INSERT_BOOK_SQL = """
19    insert into books(title, year, author, genre, rating)
20                values(?, ?, ?, ?, ?)
21    """
22
23def init_db():
24    """
25    init_db()
26    Initializes the database.
27    If tables "books" and "authors" are already in the database, do nothing.
28    Return value: None or raises ValueError
29    The error value is the QtSql error instance.
30    """
31    def check(func, *args):
32        if not func(*args):
33            raise ValueError(func.__self__.lastError())
34    db = QSqlDatabase.addDatabase("QSQLITE")
35    db.setDatabaseName(":memory:")
36
37    check(db.open)
38
39    q = QSqlQuery()
40    check(q.exec_, BOOKS_SQL)
41    check(q.exec_, AUTHORS_SQL)
42    check(q.exec_, GENRES_SQL)
43    check(q.prepare, INSERT_AUTHOR_SQL)
44
45    asimovId = add_author(q, "Isaac Asimov", date(1920, 2, 1))
46    greeneId = add_author(q, "Graham Greene", date(1904, 10, 2))
47    pratchettId = add_author(q, "Terry Pratchett", date(1948, 4, 28))
48
49    check(q.prepare,INSERT_GENRE_SQL)
50    sfiction = add_genre(q, "Science Fiction")
51    fiction = add_genre(q, "Fiction")
52    fantasy = add_genre(q, "Fantasy")
53
54    check(q.prepare,INSERT_BOOK_SQL)
55    add_book(q, "Foundation", 1951, asimovId, sfiction, 3)
56    add_book(q, "Foundation and Empire", 1952, asimovId, sfiction, 4)
57    add_book(q, "Second Foundation", 1953, asimovId, sfiction, 3)
58    add_book(q, "Foundation's Edge", 1982, asimovId, sfiction, 3)
59    add_book(q, "Foundation and Earth", 1986, asimovId, sfiction, 4)
60    add_book(q, "Prelude to Foundation", 1988, asimovId, sfiction, 3)
61    add_book(q, "Forward the Foundation", 1993, asimovId, sfiction, 3)
62    add_book(q, "The Power and the Glory", 1940, greeneId, fiction, 4)
63    add_book(q, "The Third Man", 1950, greeneId, fiction, 5)
64    add_book(q, "Our Man in Havana", 1958, greeneId, fiction, 4)
65    add_book(q, "Guards! Guards!", 1989, pratchettId, fantasy, 3)
66    add_book(q, "Night Watch", 2002, pratchettId, fantasy, 3)
67    add_book(q, "Going Postal", 2004, pratchettId, fantasy, 3)

注意

Python 版本使用 check 函数执行 SQL 语句，而不是像 C++ 版本那样使用 if...else 块。虽然两种方法都是有效的，但前者生成的代码看起来更干净、更简洁。

您的数据库设置 Python 代码现在已经准备好了。要测试它，将以下代码添加到 main.py 并运行

 1
 2import sys
 3
 4from PySide6.QtSql import QSqlQueryModel
 5from PySide6.QtWidgets import QTableView, QApplication
 6
 7import createdb
 8
 9if __name__ == "__main__":
10    app = QApplication()
11    createdb.init_db()
12
13    model = QSqlQueryModel()
14    model.setQuery("select * from books")
15
16    table_view = QTableView()
17    table_view.setModel(model)
18    table_view.resize(800, 600)
19    table_view.show()
20    sys.exit(app.exec())

使用以下命令从提示符运行

python main.py

您的表将如下所示

../../../_images/chapter1_books.png

尝试修改 main.py 中的 SQL 语句，以从 genres 或 authors 表中获取数据。