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.
# Copyright (C) 2022 The Qt Company Ltd.
# SPDX-License-Identifier: LicenseRef-Qt-Commercial OR BSD-3-Clause
from __future__ import annotations
import sys
from PySide6.QtWidgets import QApplication
from bookwindow import BookWindow
import rc_books # noqa: F401
if __name__ == "__main__":
app = QApplication([])
window = BookWindow()
window.resize(800, 600)
window.show()
sys.exit(app.exec())
# Copyright (C) 2022 The Qt Company Ltd.
# SPDX-License-Identifier: LicenseRef-Qt-Commercial OR BSD-3-Clause
from __future__ import annotations
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.svg")
self.star_filled = QPixmap(":/images/star-filled.svg")
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.ItemDataRole.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(5):
if i < rating:
painter.drawPixmap(x, y, self.star_filled)
else:
painter.drawPixmap(x, y, self.star)
x += width
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
# Copyright (C) 2022 The Qt Company Ltd.
# SPDX-License-Identifier: LicenseRef-Qt-Commercial OR BSD-3-Clause
from __future__ import annotations
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.Orientation.Horizontal, self.tr("Author Name"))
model.setHeaderData(genre_idx, Qt.Orientation.Horizontal, self.tr("Genre"))
model.setHeaderData(model.fieldIndex("title"), Qt.Orientation.Horizontal, self.tr("Title"))
model.setHeaderData(model.fieldIndex("year"), Qt.Orientation.Horizontal, self.tr("Year"))
model.setHeaderData(model.fieldIndex("rating"),
Qt.Orientation.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(self, 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) # noqa: F821
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) # noqa: F821
@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."))
# Copyright (C) 2022 The Qt Company Ltd.
# SPDX-License-Identifier: LicenseRef-Qt-Commercial OR BSD-3-Clause
from __future__ import annotations
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)
<!DOCTYPE RCC><RCC version="1.0">
<qresource>
<file>images/star.svg</file>
<file>images/star-filled.svg</file>
</qresource>
</RCC>
<?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::SelectionBehavior::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><b>Title:</b></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><b>Author: </b></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><b>Genre:</b></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><b>Year:</b></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><b>Rating:</b></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>