SQL Books Example

Shows how to use Qt SQL classes with a model/view framework.

The Books example shows how Qt’s SQL classes can be used with the model/view framework to create rich user interfaces for information stored in a database.

main.py

import sys
from PySide6.QtWidgets import QApplication
from bookwindow import BookWindow
import rc_books

if __name__ == "__main__":
    app = QApplication([])

    window = BookWindow()
    window.resize(800, 600)
    window.show()

    sys.exit(app.exec())

bookdelegate.py

import copy
from PySide6.QtSql import QSqlRelationalDelegate
from PySide6.QtWidgets import QSpinBox, QStyle
from PySide6.QtGui import QPixmap, QPalette
from PySide6.QtCore import QEvent, QSize, Qt


class BookDelegate(QSqlRelationalDelegate):
    """Books delegate to rate the books"""

    def __init__(self, parent=None):
        QSqlRelationalDelegate.__init__(self, parent)
        self.star = QPixmap(":/images/star.png")

    def paint(self, painter, option, index):
        """ Paint the items in the table.

            If the item referred to by <index> is a StarRating, we
            handle the painting ourselves. For the other items, we
            let the base class handle the painting as usual.

            In a polished application, we'd use a better check than
            the column number to find out if we needed to paint the
            stars, but it works for the purposes of this example.
        """
        if index.column() != 5:
            # Since we draw the grid ourselves:
            opt = copy.copy(option)
            opt.rect = option.rect.adjusted(0, 0, -1, -1)
            QSqlRelationalDelegate.paint(self, painter, opt, index)
        else:
            model = index.model()
            if option.state & QStyle.State_Enabled:
                if option.state & QStyle.State_Active:
                    color_group = QPalette.Normal
                else:
                    color_group = QPalette.Inactive
            else:
                color_group = QPalette.Disabled

            if option.state & QStyle.State_Selected:
                painter.fillRect(option.rect,
                    option.palette.color(color_group, QPalette.Highlight))
            rating = model.data(index, Qt.DisplayRole)
            width = self.star.width()
            height = self.star.height()
            x = option.rect.x()
            y = option.rect.y() + (option.rect.height() / 2) - (height / 2)
            for i in range(rating):
                painter.drawPixmap(x, y, self.star)
                x += width

            # Since we draw the grid ourselves:
            self.drawFocus(painter, option, option.rect.adjusted(0, 0, -1, -1))

        pen = painter.pen()
        painter.setPen(option.palette.color(QPalette.Mid))
        painter.drawLine(option.rect.bottomLeft(), option.rect.bottomRight())
        painter.drawLine(option.rect.topRight(), option.rect.bottomRight())
        painter.setPen(pen)

    def sizeHint(self, option, index):
        """ Returns the size needed to display the item in a QSize object. """
        if index.column() == 5:
            size_hint = QSize(5 * self.star.width(), self.star.height()) + QSize(1, 1)
            return size_hint
        # Since we draw the grid ourselves:
        return QSqlRelationalDelegate.sizeHint(self, option, index) + QSize(1, 1)

    def editorEvent(self, event, model, option, index):
        if index.column() != 5:
            return False

        if event.type() == QEvent.MouseButtonPress:
            mouse_pos = event.position()
            new_stars = int(0.7 + (mouse_pos.x() - option.rect.x()) / self.star.width())
            stars = max(0, min(new_stars, 5))
            model.setData(index, stars)
            # So that the selection can change
            return False

        return True

    def createEditor(self, parent, option, index):
        if index.column() != 4:
            return QSqlRelationalDelegate.createEditor(self, parent, option, index)

        # For editing the year, return a spinbox with a range from -1000 to 2100.
        spinbox = QSpinBox(parent)
        spinbox.setFrame(False)
        spinbox.setMaximum(2100)
        spinbox.setMinimum(-1000)
        return spinbox

bookwindow.py

from PySide6.QtWidgets import (QAbstractItemView, QDataWidgetMapper,
    QHeaderView, QMainWindow, QMessageBox)
from PySide6.QtGui import QKeySequence
from PySide6.QtSql import QSqlRelation, QSqlRelationalTableModel, QSqlTableModel
from PySide6.QtCore import Qt, Slot
import createdb
from ui_bookwindow import Ui_BookWindow
from bookdelegate import BookDelegate


class BookWindow(QMainWindow, Ui_BookWindow):
    """A window to show the books available"""

    def __init__(self):
        super().__init__()
        self.setupUi(self)

        # Initialize db
        createdb.init_db()

        model = QSqlRelationalTableModel(self.bookTable)
        model.setEditStrategy(QSqlTableModel.OnManualSubmit)
        model.setTable("books")

        # Remember the indexes of the columns:
        author_idx = model.fieldIndex("author")
        genre_idx = model.fieldIndex("genre")

        # Set the relations to the other database tables:
        model.setRelation(author_idx, QSqlRelation("authors", "id", "name"))
        model.setRelation(genre_idx, QSqlRelation("genres", "id", "name"))

        # Set the localized header captions:
        model.setHeaderData(author_idx, Qt.Horizontal, self.tr("Author Name"))
        model.setHeaderData(genre_idx, Qt.Horizontal, self.tr("Genre"))
        model.setHeaderData(model.fieldIndex("title"), Qt.Horizontal, self.tr("Title"))
        model.setHeaderData(model.fieldIndex("year"), Qt.Horizontal, self.tr("Year"))
        model.setHeaderData(model.fieldIndex("rating"), Qt.Horizontal, self.tr("Rating"))

        if not model.select():
            print(model.lastError())

        # Set the model and hide the ID column:
        self.bookTable.setModel(model)
        self.bookTable.setItemDelegate(BookDelegate(self.bookTable))
        self.bookTable.setColumnHidden(model.fieldIndex("id"), True)
        self.bookTable.setSelectionMode(QAbstractItemView.SingleSelection)

        # Initialize the Author combo box:
        self.authorEdit.setModel(model.relationModel(author_idx))
        self.authorEdit.setModelColumn(model.relationModel(author_idx).fieldIndex("name"))

        self.genreEdit.setModel(model.relationModel(genre_idx))
        self.genreEdit.setModelColumn(model.relationModel(genre_idx).fieldIndex("name"))

        # Lock and prohibit resizing of the width of the rating column:
        self.bookTable.horizontalHeader().setSectionResizeMode(model.fieldIndex("rating"),
            QHeaderView.ResizeToContents)

        mapper = QDataWidgetMapper(self)
        mapper.setModel(model)
        mapper.setItemDelegate(BookDelegate(self))
        mapper.addMapping(self.titleEdit, model.fieldIndex("title"))
        mapper.addMapping(self.yearEdit, model.fieldIndex("year"))
        mapper.addMapping(self.authorEdit, author_idx)
        mapper.addMapping(self.genreEdit, genre_idx)
        mapper.addMapping(self.ratingEdit, model.fieldIndex("rating"))

        selection_model = self.bookTable.selectionModel()
        selection_model.currentRowChanged.connect(mapper.setCurrentModelIndex)

        self.bookTable.setCurrentIndex(model.index(0, 0))
        self.create_menubar()

    def showError(err):
        QMessageBox.critical(self, "Unable to initialize Database",
                    f"Error initializing database: {err.text()}")

    def create_menubar(self):
        file_menu = self.menuBar().addMenu(self.tr("&File"))
        quit_action = file_menu.addAction(self.tr("&Quit"))
        quit_action.triggered.connect(qApp.quit)

        help_menu = self.menuBar().addMenu(self.tr("&Help"))
        about_action = help_menu.addAction(self.tr("&About"))
        about_action.setShortcut(QKeySequence.HelpContents)
        about_action.triggered.connect(self.about)
        aboutQt_action = help_menu.addAction("&About Qt")
        aboutQt_action.triggered.connect(qApp.aboutQt)

    @Slot()
    def about(self):
        QMessageBox.about(self, self.tr("About Books"),
            self.tr("<p>The <b>Books</b> example shows how to use Qt SQL classes "
                "with a model/view framework."))

createdb.py

from PySide6.QtSql import QSqlDatabase, QSqlQuery
from datetime import date


def add_book(q, title, year, authorId, genreId, rating):
    q.addBindValue(title)
    q.addBindValue(year)
    q.addBindValue(authorId)
    q.addBindValue(genreId)
    q.addBindValue(rating)
    q.exec()


def add_genre(q, name):
    q.addBindValue(name)
    q.exec()
    return q.lastInsertId()


def add_author(q, name, birthdate):
    q.addBindValue(name)
    q.addBindValue(str(birthdate))
    q.exec()
    return q.lastInsertId()


BOOKS_SQL = """
    create table books(id integer primary key, title varchar, author integer,
                       genre integer, year integer, rating integer)
    """
AUTHORS_SQL = """
    create table authors(id integer primary key, name varchar, birthdate text)
    """
GENRES_SQL = """
    create table genres(id integer primary key, name varchar)
    """
INSERT_AUTHOR_SQL = """
    insert into authors(name, birthdate) values(?, ?)
    """
INSERT_GENRE_SQL = """
    insert into genres(name) values(?)
    """
INSERT_BOOK_SQL = """
    insert into books(title, year, author, genre, rating)
                values(?, ?, ?, ?, ?)
    """


def init_db():
    """
    init_db()
    Initializes the database.
    If tables "books" and "authors" are already in the database, do nothing.
    Return value: None or raises ValueError
    The error value is the QtSql error instance.
    """
    def check(func, *args):
        if not func(*args):
            raise ValueError(func.__self__.lastError())
    db = QSqlDatabase.addDatabase("QSQLITE")
    db.setDatabaseName(":memory:")

    check(db.open)

    q = QSqlQuery()
    check(q.exec, BOOKS_SQL)
    check(q.exec, AUTHORS_SQL)
    check(q.exec, GENRES_SQL)
    check(q.prepare, INSERT_AUTHOR_SQL)

    asimovId = add_author(q, "Isaac Asimov", date(1920, 2, 1))
    greeneId = add_author(q, "Graham Greene", date(1904, 10, 2))
    pratchettId = add_author(q, "Terry Pratchett", date(1948, 4, 28))

    check(q.prepare, INSERT_GENRE_SQL)
    sfiction = add_genre(q, "Science Fiction")
    fiction = add_genre(q, "Fiction")
    fantasy = add_genre(q, "Fantasy")

    check(q.prepare, INSERT_BOOK_SQL)
    add_book(q, "Foundation", 1951, asimovId, sfiction, 3)
    add_book(q, "Foundation and Empire", 1952, asimovId, sfiction, 4)
    add_book(q, "Second Foundation", 1953, asimovId, sfiction, 3)
    add_book(q, "Foundation's Edge", 1982, asimovId, sfiction, 3)
    add_book(q, "Foundation and Earth", 1986, asimovId, sfiction, 4)
    add_book(q, "Prelude to Foundation", 1988, asimovId, sfiction, 3)
    add_book(q, "Forward the Foundation", 1993, asimovId, sfiction, 3)
    add_book(q, "The Power and the Glory", 1940, greeneId, fiction, 4)
    add_book(q, "The Third Man", 1950, greeneId, fiction, 5)
    add_book(q, "Our Man in Havana", 1958, greeneId, fiction, 4)
    add_book(q, "Guards! Guards!", 1989, pratchettId, fantasy, 3)
    add_book(q, "Night Watch", 2002, pratchettId, fantasy, 3)
    add_book(q, "Going Postal", 2004, pratchettId, fantasy, 3)

books.qrc

<!DOCTYPE RCC><RCC version="1.0">
<qresource>
  <file>images/star.png</file>
</qresource>
</RCC>

bookwindow.ui

<?xml version="1.0" encoding="UTF-8"?>
<ui version="4.0">
 <class>BookWindow</class>
 <widget class="QMainWindow" name="BookWindow">
  <property name="geometry">
   <rect>
    <x>0</x>
    <y>0</y>
    <width>601</width>
    <height>420</height>
   </rect>
  </property>
  <property name="windowTitle">
   <string>Books</string>
  </property>
  <widget class="QWidget" name="centralWidget">
   <layout class="QVBoxLayout">
    <property name="spacing">
     <number>6</number>
    </property>
    <property name="leftMargin">
     <number>9</number>
    </property>
    <property name="topMargin">
     <number>9</number>
    </property>
    <property name="rightMargin">
     <number>9</number>
    </property>
    <property name="bottomMargin">
     <number>9</number>
    </property>
    <item>
     <widget class="QGroupBox" name="groupBox">
      <property name="title">
       <string/>
      </property>
      <layout class="QVBoxLayout">
       <property name="spacing">
        <number>6</number>
       </property>
       <property name="leftMargin">
        <number>9</number>
       </property>
       <property name="topMargin">
        <number>9</number>
       </property>
       <property name="rightMargin">
        <number>9</number>
       </property>
       <property name="bottomMargin">
        <number>9</number>
       </property>
       <item>
        <widget class="QTableView" name="bookTable">
         <property name="selectionBehavior">
          <enum>QAbstractItemView::SelectRows</enum>
         </property>
        </widget>
       </item>
       <item>
        <widget class="QGroupBox" name="groupBox_2">
         <property name="title">
          <string>Details</string>
         </property>
         <layout class="QFormLayout">
          <item row="0" column="0">
           <widget class="QLabel" name="label_5">
            <property name="text">
             <string>&lt;b&gt;Title:&lt;/b&gt;</string>
            </property>
           </widget>
          </item>
          <item row="0" column="1">
           <widget class="QLineEdit" name="titleEdit">
            <property name="enabled">
             <bool>true</bool>
            </property>
           </widget>
          </item>
          <item row="1" column="0">
           <widget class="QLabel" name="label_2">
            <property name="text">
             <string>&lt;b&gt;Author: &lt;/b&gt;</string>
            </property>
           </widget>
          </item>
          <item row="1" column="1">
           <widget class="QComboBox" name="authorEdit">
            <property name="enabled">
             <bool>true</bool>
            </property>
           </widget>
          </item>
          <item row="2" column="0">
           <widget class="QLabel" name="label_3">
            <property name="text">
             <string>&lt;b&gt;Genre:&lt;/b&gt;</string>
            </property>
           </widget>
          </item>
          <item row="2" column="1">
           <widget class="QComboBox" name="genreEdit">
            <property name="enabled">
             <bool>true</bool>
            </property>
           </widget>
          </item>
          <item row="3" column="0">
           <widget class="QLabel" name="label_4">
            <property name="text">
             <string>&lt;b&gt;Year:&lt;/b&gt;</string>
            </property>
           </widget>
          </item>
          <item row="3" column="1">
           <widget class="QSpinBox" name="yearEdit">
            <property name="enabled">
             <bool>true</bool>
            </property>
            <property name="prefix">
             <string/>
            </property>
            <property name="minimum">
             <number>-1000</number>
            </property>
            <property name="maximum">
             <number>2100</number>
            </property>
           </widget>
          </item>
          <item row="4" column="0">
           <widget class="QLabel" name="label">
            <property name="text">
             <string>&lt;b&gt;Rating:&lt;/b&gt;</string>
            </property>
           </widget>
          </item>
          <item row="4" column="1">
           <widget class="QSpinBox" name="ratingEdit">
            <property name="maximum">
             <number>5</number>
            </property>
           </widget>
          </item>
         </layout>
        </widget>
       </item>
      </layout>
     </widget>
    </item>
   </layout>
  </widget>
 </widget>
 <tabstops>
  <tabstop>bookTable</tabstop>
  <tabstop>titleEdit</tabstop>
  <tabstop>authorEdit</tabstop>
  <tabstop>genreEdit</tabstop>
  <tabstop>yearEdit</tabstop>
 </tabstops>
 <resources/>
 <connections/>
</ui>