第1章：initDb.h 到 createDb.py¶
首先，移植创建SQLite数据库和表并向其中添加数据的C++代码。在这种情况下，所有与此相关的C++代码都位于initdb.h中。此头文件中的代码分为以下几个部分：
initDb- 创建一个数据库和必要的表addBooks- 向books表中添加数据。addAuthor- 向authors表中添加数据。addGenre- 向genres表添加数据。
首先，在createdb.py的开头添加以下import语句：
1from __future__ import annotations
2
3from PySide6.QtSql import QSqlDatabase, QSqlError, QSqlQuery
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
2
3def add_book(q, title, year, authorId, genreId, rating):
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
12def add_genre(q, name):
13 q.addBindValue(name)
14 q.exec_()
15 return q.lastInsertId()
16
17
18def add_author(q, name, birthdate):
19 q.addBindValue(name)
20 q.addBindValue(str(birthdate))
21 q.exec_()
22 return q.lastInsertId()
现在辅助函数已经就位，移植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 return q.lastInsertId()
2
3BOOKS_SQL = """
4 create table books(id integer primary key, title varchar, author integer,
5 genre integer, year integer, rating integer)
6 """
7AUTHORS_SQL = """
8 create table authors(id integer primary key, name varchar, birthdate text)
9 """
10GENRES_SQL = """
11 create table genres(id integer primary key, name varchar)
12 """
13INSERT_AUTHOR_SQL = """
14 insert into authors(name, birthdate) values(?, ?)
15 """
16INSERT_GENRE_SQL = """
17 insert into genres(name) values(?)
18 """
19INSERT_BOOK_SQL = """
20 insert into books(title, year, author, genre, rating)
21 values(?, ?, ?, ?, ?)
22 """
23
24def init_db():
25 """
26 init_db()
27 Initializes the database.
28 If tables "books" and "authors" are already in the database, do nothing.
29 Return value: None or raises ValueError
30 The error value is the QtSql error instance.
31 """
32 def check(func, *args):
33 if not func(*args):
34 raise ValueError(func.__self__.lastError())
35 db = QSqlDatabase.addDatabase("QSQLITE")
36 db.setDatabaseName(":memory:")
37
38 check(db.open)
39
40 q = QSqlQuery()
41 check(q.exec_, BOOKS_SQL)
42 check(q.exec_, AUTHORS_SQL)
43 check(q.exec_, GENRES_SQL)
44 check(q.prepare, INSERT_AUTHOR_SQL)
45
46 asimovId = add_author(q, "Isaac Asimov", date(1920, 2, 1))
47 greeneId = add_author(q, "Graham Greene", date(1904, 10, 2))
48 pratchettId = add_author(q, "Terry Pratchett", date(1948, 4, 28))
49
50 check(q.prepare,INSERT_GENRE_SQL)
51 sfiction = add_genre(q, "Science Fiction")
52 fiction = add_genre(q, "Fiction")
53 fantasy = add_genre(q, "Fantasy")
54
55 check(q.prepare,INSERT_BOOK_SQL)
56 add_book(q, "Foundation", 1951, asimovId, sfiction, 3)
57 add_book(q, "Foundation and Empire", 1952, asimovId, sfiction, 4)
58 add_book(q, "Second Foundation", 1953, asimovId, sfiction, 3)
59 add_book(q, "Foundation's Edge", 1982, asimovId, sfiction, 3)
60 add_book(q, "Foundation and Earth", 1986, asimovId, sfiction, 4)
61 add_book(q, "Prelude to Foundation", 1988, asimovId, sfiction, 3)
62 add_book(q, "Forward the Foundation", 1993, asimovId, sfiction, 3)
63 add_book(q, "The Power and the Glory", 1940, greeneId, fiction, 4)
64 add_book(q, "The Third Man", 1950, greeneId, fiction, 5)
65 add_book(q, "Our Man in Havana", 1958, greeneId, fiction, 4)
66 add_book(q, "Guards! Guards!", 1989, pratchettId, fantasy, 3)
67 add_book(q, "Night Watch", 2002, pratchettId, fantasy, 3)
68 add_book(q, "Going Postal", 2004, pratchettId, fantasy, 3)
注意
Python 版本使用 check 函数来执行 SQL 语句，而不是像 C++ 版本那样使用 if...else 块。虽然这两种方法都是有效的，但前一种方法生成的代码看起来更简洁和简短。
您的Python代码已准备好设置数据库。要测试它，请将以下代码添加到main.py中并运行它：
1from __future__ import annotations
2
3import sys
4
5from PySide6.QtSql import QSqlQueryModel
6from PySide6.QtWidgets import QTableView, QApplication
7
8import createdb
9
10if __name__ == "__main__":
11 app = QApplication()
12 createdb.init_db()
13
14 model = QSqlQueryModel()
15 model.setQuery("select * from books")
16
17 table_view = QTableView()
18 table_view.setModel(model)
19 table_view.resize(800, 600)
20 table_view.show()
21 sys.exit(app.exec())
使用以下命令从提示符运行：
python main.py
您的表格将如下所示：
尝试修改main.py中的SQL语句，以从genres或authors表中获取数据。