Chapter 1: initDb.h to createDb.py

To begin with, port the C++ code that creates an SQLite database and tables, and adds data to them. In this case, all C++ code related to this lives in initdb.h. The code in this header file is divided into following parts:

  • initDb - Creates a db and the necessary tables

  • addBooks - Adds data to the books table.

  • addAuthor - Adds data to the authors table.

  • addGenre - Adds data to the genres table.

To start with, add these following import statements at the beginning of createdb.py:

1from __future__ import annotations
2
3from PySide6.QtSql import QSqlDatabase, QSqlError, QSqlQuery

The initDb function does most of the work needed to set up the database, but it depends on the addAuthor, addGenre, and addBook helper functions to populate the tables. Port these helper functions first. Here is how the C++ and Python versions of these functions look like:

C++ version

 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 version

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

Now that the helper functions are in place, port initDb. Here is how the C++ and Python versions of this function looks like:

C++ version (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 version (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)

Note

The Python version uses the check function to execute the SQL statements instead of the if...else block like in the C++ version. Although both are valid approaches, the earlier one produces code that looks cleaner and shorter.

Your Python code to set up the database is ready now. To test it, add the following code to main.py and run it:

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

Use the following command from the prompt to run:

python main.py

Your table will look like this:

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

Try modifying the SQL statment in main.py to get data from the genres or authors table.