33. Databases

Working with databases in Qt is supported by the QtSql module, which divides its APIs into three layers:

The driver layer acts as an intermediary between your Python code and the specific database engine. Note that while SQLite (QSQLITE) is almost always available out of the box, drivers for other databases may require additional setup.
The SQL API layer gives you direct access to the database, allowing you to execute queries, and manipulate data using classes like QSqlDatabase and QSqlQuery.
The user interface layer lets you connect database data to widgets using models such as QSqlTableModel or QSqlQueryModel with views like QTableView to display and edit query results in your GUI.

Here’s a table of supported Qt SQL drivers:

Driver	Database	Description
`QDB2`	IBM DB2	Enterprise database system from IBM
`QIBASE`	Borland InterBase	Lightweight, embeddable relational database
`QMYSQL`	MySQL	Popular open-source relational database
`QOCI`	Oracle Call Interface	Oracle database driver
`QODBC`	Open Database Connectivity (ODBC)	Generic driver supporting any ODBC-compliant database
`QPSQL`	PostgreSQL	Advanced open-source relational database
`QSQLITE`	SQLite	Lightweight, file-based database (no server required)
`QMIMER`	Mimer SQL	High-performance relational database

QSQLITE is typically included by default with Qt installations and is usefull for getting started, as it requires no separate database server installation (with some caveats).

You can easily list available database drivers with the following code:

 1 from PySide6.QtCore import QCoreApplication
 2 from PySide6.QtSql import QSqlDatabase
 3 
 4 app = QCoreApplication()
 5 
 6 # Get list of available drivers
 7 available_drivers = QSqlDatabase.drivers()
 8 
 9 print("Available SQL drivers:")
10 for driver in available_drivers:
11     print(f"  - {driver}")

In practice, working with databases in PySide6 follows a simple pattern:

Create a connection with QSqlDatabase,
Execute queries using QSqlQuery (or higher-level models),
Display/edit the results via Qt’s views and models.

In the examples we will be using a small SQLite database that you can generate from this SQL script:

 1 -- Drop tables if they exist to reset the database
 2 DROP TABLE IF EXISTS Transactions;
 3 DROP TABLE IF EXISTS Categories;
 4 DROP TABLE IF EXISTS Accounts;
 5 DROP TABLE IF EXISTS Users;
 6 
 7 -- Create Users table
 8 CREATE TABLE Users (
 9     user_id INTEGER PRIMARY KEY AUTOINCREMENT,
10     username TEXT NOT NULL,
11     email TEXT UNIQUE NOT NULL
12 );
13 
14 -- Create Accounts table
15 CREATE TABLE Accounts (
16     account_id INTEGER PRIMARY KEY AUTOINCREMENT,
17     user_id INTEGER NOT NULL,
18     account_name TEXT NOT NULL,
19     balance REAL DEFAULT 0.0,
20     FOREIGN KEY (user_id) REFERENCES Users(user_id)
21 );
22 
23 -- Create Categories table
24 CREATE TABLE Categories (
25     category_id INTEGER PRIMARY KEY AUTOINCREMENT,
26     category_name TEXT NOT NULL UNIQUE,
27     description TEXT
28 );
29 
30 -- Create Transactions table
31 CREATE TABLE Transactions (
32     transaction_id INTEGER PRIMARY KEY AUTOINCREMENT,
33     account_id INTEGER NOT NULL,
34     category_id INTEGER NOT NULL,
35     amount REAL NOT NULL,
36     transaction_date DATE NOT NULL,
37     description TEXT,
38     FOREIGN KEY (account_id) REFERENCES Accounts(account_id),
39     FOREIGN KEY (category_id) REFERENCES Categories(category_id)
40 );
41 
42 -- Populate Users
43 INSERT INTO Users (username, email) VALUES ('john_doe', 'john@example.com');
44 INSERT INTO Users (username, email) VALUES ('jane_smith', 'jane@example.com');
45 
46 -- Populate Accounts
47 INSERT INTO Accounts (user_id, account_name, balance) VALUES (1, 'Checking Account', 1500.00);
48 INSERT INTO Accounts (user_id, account_name, balance) VALUES (1, 'Savings Account', 5000.00);
49 INSERT INTO Accounts (user_id, account_name, balance) VALUES (2, 'Business Account', 10000.00);
50 
51 -- Populate Categories
52 INSERT INTO Categories (category_name, description) VALUES ('Income', 'Salary or other earnings');
53 INSERT INTO Categories (category_name, description) VALUES ('Expenses', 'Daily spending');
54 INSERT INTO Categories (category_name, description) VALUES ('Investments', 'Stock or asset purchases');
55 INSERT INTO Categories (category_name, description) VALUES ('Transfers', 'Internal transfers');
56 
57 -- Populate Transactions
58 INSERT INTO Transactions (account_id, category_id, amount, transaction_date, description) VALUES (1, 1, 2000.00, '2025-09-01', 'Salary deposit');
59 INSERT INTO Transactions (account_id, category_id, amount, transaction_date, description) VALUES (1, 2, -500.00, '2025-09-05', 'Groceries');
60 INSERT INTO Transactions (account_id, category_id, amount, transaction_date, description) VALUES (2, 3, -1000.00, '2025-09-10', 'Stock purchase');
61 INSERT INTO Transactions (account_id, category_id, amount, transaction_date, description) VALUES (3, 1, 5000.00, '2025-09-15', 'Client payment');
62 INSERT INTO Transactions (account_id, category_id, amount, transaction_date, description) VALUES (1, 4, -200.00, '2025-09-20', 'Transfer to savings');
63 INSERT INTO Transactions (account_id, category_id, amount, transaction_date, description) VALUES (2, 4, 200.00, '2025-09-20', 'Transfer from checking');

using this simple PySide6 application:

If the execution is successfull, the database will be contained in a file named finance_demo.sqlite. All the examples in this chapter expect this file to be located in the same directory in which they reside. You can verify that the database is generated properly using an open source GUI application named DB Browser for SQLite¹. You can also use this to reacreate the database and load the initial data after experimenting.

33.1 Database Connections

If you have experience working with databases, you may have noticed that many Python database APIs implement PEP 249 where a database connection is represented with a Connection object. This is not the case with the PySide6 QtSql module - you establish a connection using the QSqlDatabase.open() method, setting the connection options before calling it. Unlike PEP 249, where your code holds an explicit reference to the connection object, QtSql maintains a global registry of named connections, allowing you to retrieve a connection by name from anywhere in your application, with an implicit default connection available when no name is specified².

You are building a budgeting app where users can view their financial data stored in a local database. Implement a connection to the SQLite database and display basic connection details like the database name and available tables.

To connect to a SQLite database:

Create a QSqlDatabase object and set the connection options. In the main window __init__(), create a database object by calling the addDatabase() factory method, passing it the database driver name (QSQLITE) and, optionally, the connection name. If the connection name is not specified, like in our case, the connection becomes the default connection for the application. addDatabase() returns a QSqlDatabase object and we assign it to the self.db instance member. Set the database name to finance_demo.sqlite and the database as read-only so that SQLite does not create an empty database file if it doesn’t exist.
Use QSqlDatabase.open() to connect to the database. open() returns true if the connection is opened successfully and false otherwise. You can get the error information using QSqlDatabase.lastError().
Print the database information. To confirm that opening the connection was successful, print the database name, the driver name and the list of the tables. The output should be:

1 Connected!
2 Name:  finance_demo.sqlite
3 Driver:  QSQLITE
4 Tables: 
5     Users
6     sqlite_sequence
7     Accounts
8     Categories
9     Transactions

You can get the list of all connections using connectionNames(); you can also check if a connection is in the list using the QSqlDatabase.contains() static method.

33.2 Executing Simple Queries

With a connection established, you can retrieve the data using a QSqlQuery object³.

In your expense tracking software, users need to retrieve lists of records. Add a simple query execution feature to fetch and display data from selected tables.

To execute a simple SQL query:

Connect to the database the same way as in the previous example.
Populate the combobox with table names. Use QSqlDatabase.tables() to populate a combobox with all the user tables from the database (excluding the sqlite_sequence table).
Execute a query and process the results. When the user selects a table from the combobox, the query string is updated to select all its records. When the user presses the Execute query button, create a QSqlQuery object and call its exec() method with the current query string. If exec() returns True, iterate through the returned records calling next() and each record’s field values. FOr each record, retreive a QSqlRecord object by calling query.record(). This gives you the field fount via count(), and the value of each field by index using query.value(i).

Now, when you start the application a window is shown:

and when you press the Execute query button query results are printed in the terminal:

1 Current query: Select * From Users
2 1   john_doe    john@example.com    
3 2   jane_smith  jane@example.com

33.3 Inserting Data

Inserting, updating, and deleting data are common database operations. You can perform then using QSqlQuery with parameterized queries to handle data supplied by users.

For your banking dashboard app, users should be able to add new accounts easily. Develop an insert operation to add records to the database using basic SQL commands.

To insert data in a SQLite database:

Connect to the database. In the first two examples the we needed only read-only access so we used the QSQLITE_OPEN_READONLY connection option. To insert data we need read/write access and still don’t want an empty database file to be created if one doesn’t exist already. Unfortunately, the SQLite Qt driver does not support the SQLITE_OPEN_READWRITE option so we open the database file as an URI with the rw mode as a workaround.
Get and validate user-entered data. The user needs to enter a username and an email so we add twoQLineEdit widgets to the main window. When the user presses the Add user button, ensure neither field is empty. If the data is valid:
- Create a QSqlQuery object.
- Prepare an insert query with two parameters: username and email.
- Bind the values from the line edits to the query parameters.
Execute the query and display the result. If the query executed successfully, print a message, otherwise print the execution error. For instance, an error will occur if the user enters a duplicate email.

You would follow the same pattern when updating and deleting data. While QSqlQuery offers flexibility when working with databases, Qt’s SQL module also provides several model classes for use with the model-view framework, reducing the mount of code you need to write, covered in the following sections:

QSqlTableModel
QSqlRelationalTableModel
QSqlQueryModel

33.4 Displaying and Editing Data with `QSqlTableModel`

The QSqlTableModel class provides an editable data model for a single database table⁴. It is a QAbstractItemModel subclass which which means that you can use it with Qt’s view classes.

You are designing a view for account summaries in your finance app. Use QSqlTableModel to display editable table data in a QTableView for quick overviews.

To use QSqlTableModel in your application:

Connect to the database. As the data needs to be editable we open the database as a URI as we did in the previous section. Also turn on SQlite foreign key support by executing PRAGMA foreign_keys = ON;. Without this, SQlite would allow you to enter User ID values that do not exist in the Users table.
Create the model. Instantiate a QSqlTableModel object, set its table to ‘Accounts’, its edit strategy to OnManualSubmit, and set the horizontal header labels for each column. Call select() to load data from the database into the model.
Create the view and set its model. Instantiate a QTableView and assign the model to it. Connect the selection model’s currentChanged() signal to the save_changes() slot so that edits are submitted whenever the user moves to a different cell.
Save the changes to the database when the current cell changes. In the slot, return early if the previous index is invalid (meaning no cell was previously selected). Otherwise check if the model has unsaved changes via model.isDirty() and if so submit them with model.submitAll(). On success display a confirmation label. On error display the error text and revert all pending chages with revertAll().

33.5 Handling Relational Data with `QSqlRelationalTableModel`

The QSqlRelationalTableModel class provides an editable data model for a single database table with foreign key support. To provide a combobox editor for foreign key fields, use QSqlRelationalDelegate with this model⁵.

For a relational view in your budgeting tool, show transactions with linked account and category names. Employ QSqlRelationalTableModel to handle foreign keys and provide combo-box editing.

To use QRelationalTableModel in your application:

Create a QSqlRelationalTableModel subclass. Here we create a class named TransactionsTableModel, set its table name to ‘Transactions’, and define two relations, to the ‘Accounts’ and ‘Categories’ tables, using QSqlRelation objects, each constructed with the related table name, its join column, and the column whose value should be displayed. We also override flags() to make the primary key column read-only.

Create a model instance, set its header data, and call model.select() to load data from the database.
Create a QTableView instance and assign the model to it.
Provide combobox editors for foreign key columns. Instantiate a QSqlRelationalDelegate and set it as the view’s delegate - this automatically adds comboboxes to the ‘Account’ and ‘Category’ columns, displaying names instead of raw IDs.

Curiously, if you inspect the SQL statement the model generates to join the Transactions, Accounts, and Categories tables (via model.query().lastQuery()), you will find it uses an implicit (“old-style”) join, a hint at the age of the underlying API.

33.6 Custom Queries with `QSqlQueryModel`

The QSqlQueryModel class provides a read-only data model for SQL result sets⁶. It is a high-level interface for executing SQL statements, built on top of the lower-level QSqlQuery API, and lets you execute arbitrary SQL queries like this one, for instance:

 1 -- Account Summary Report (September 2025)
 2 SELECT 
 3     a.account_id, 
 4     a.account_name, 
 5     u.username AS owner,
 6     COALESCE(c.category_name, 'N/A') AS category_name,
 7     COUNT(t.transaction_id) AS transaction_count,
 8     COALESCE(SUM(t.amount), 0) AS total_amount,
 9     COALESCE(AVG(t.amount), 0) AS avg_transaction,
10     COALESCE((
11         SELECT SUM(t2.amount)
12         FROM Transactions t2
13         WHERE t2.account_id = a.account_id
14           AND t2.transaction_date LIKE '2025-09%'
15     ), 0) AS net_balance
16 FROM 
17     Accounts a
18     INNER JOIN Users u ON a.user_id = u.user_id
19     LEFT JOIN Transactions t ON a.account_id = t.account_id 
20         AND t.transaction_date LIKE '2025-09%'
21     LEFT JOIN Categories c ON t.category_id = c.category_id
22 GROUP BY 
23     a.account_id, a.account_name, u.username, c.category_name
24 
25 UNION ALL
26 
27 -- Per-account totals
28 SELECT 
29     a.account_id, 
30     a.account_name, 
31     u.username AS owner,
32     'Total' AS category_name,
33     COUNT(t.transaction_id) AS transaction_count,
34     COALESCE(SUM(t.amount), 0) AS total_amount,
35     COALESCE(AVG(t.amount), 0) AS avg_transaction,
36     COALESCE(SUM(t.amount), 0) AS net_balance
37 FROM 
38     Accounts a
39     INNER JOIN Users u ON a.user_id = u.user_id
40     LEFT JOIN Transactions t ON a.account_id = t.account_id 
41         AND t.transaction_date LIKE '2025-09%'
42 GROUP BY 
43     a.account_id, a.account_name, u.username
44 
45 UNION ALL
46 
47 -- Grand total
48 SELECT 
49     NULL AS account_id, 
50     'Grand Total' AS account_name,
51     NULL AS owner, 
52     NULL AS category_name,
53     COUNT(t.transaction_id) AS transaction_count,
54     COALESCE(SUM(t.amount), 0) AS total_amount,
55     COALESCE(AVG(t.amount), 0) AS avg_transaction,
56     COALESCE(SUM(t.amount), 0) AS net_balance
57 FROM 
58     Transactions t
59 WHERE 
60     t.transaction_date LIKE '2025-09%'
61 
62 ORDER BY 
63     account_id, category_name;

The above query is a bit involved and shows a September 2025 spending report that shows, for each account, a breakdown by category, followed by a subtotal for that account, with a grand total at the end.

In your financial reporting app, generate custom summaries like monthly totals. Utilize QSqlQueryModel for complex, read-only queries with joins and aggregations.

To use a QSqlQueryModel in your application:

Get the SQL query text. Here, we load it from an external ‘.sql’ file using a standard open() Python call.
Create the model and query the database. Instantiate a QSqlQueryModel and pass the query string to setQuery() to execute the query. Check model.query().lastError() to datect any errors, then set the horizontal header labels for each column.
Create the view and assign the model to it. Instantiate a QTableView, set its model, and enablealternating row colors for readability.

QSqlQueryModel objects are read-only by default - to make it read-write, you need to subclass it and reimplement setData() and flags() methods.

33.7 The Single Record Form

A single record form form displays one database record at a time, allowing the user to navigate between the records, edit fields, and add or delete new records.

Your finance app needs an interface for managing categories one record at a time. You are asked to implement a reusable single record form with navigation controls.

To create a single record form:

Create a QSqlTableModel subclass. CategoriesTableModel overrides flags() to make the primary key column read-only, and overrides record() to exclude the primary key so SQLite can generate it automatically on insert.

Create the navigation toolbar. NavBar is a QToolBar subclass that lets the user navigate to the first, previous, next and last records. It also lets the user save the current record changes and create new records.

Create the single record form. SingleRecordForm accepts a model and column to label mappings, and dynamically creates a QLineEdit for each mapped column. Set up a QDataWidgetmapper, map each line edit to its corresponding model column, and connect the NavBar signals to the mapper’s navigation slots.
Save and add records. The save_data() slot calls mapper.submit() to push line edit values into the model, then model.submitAll() to write them to the database, refreshing via select() on success. The add_new_record() slot submits any pending changes, appends a new row to the model, and navigates the mapper to it.

Add the form to the main window. Instantiate CategoriesTableModel, define the column mappings, and pass both to DetailForm.

The SingleRecordForm class is generic - it accepts any QSqlTableModel subclass and a column mapping, making it reusable. The final section builds on this, implementing the classic master-detail pattern.

33.8 The Master-Detail Form

The master-detail pattern links two forms so that the detail form always shows records related to the currently selected master record.

Your finance app needs to display transactions grouped by category. You decide to build a master-detail form that links a categories form to a filtered transactions view using QSqlRelationalTableModel.

In this example, selecting a category in the master form filters the transactions shown in the detail form. The example reuses CategoriesTableModel, NavBar and SingleRecordForm from the previous section and introduces two new classes: DateDelegate and TransactionForm.

To create a master-detail form:

Create a custom date delegate. DateDelegate is a QStyledItemDelegate subclass that provides a QDateEdit editor with a calendar popup for date columns. It overrides createEditor() to return the configured QDateEdit, setEditorData() to parse the stored date string into a QDate, and setModelData() to write the selected date back to the model as a formatted string. SQLite has no native date type - it stores dates as plain strings. Without DateDelegate, the default editor for that column would be a plain line edit, leaving the user to type dates manually in the correct format.
Build the transactions form. TransactionsForm displays transactions in a table view. It hides the primary key and category ID columns (columns 0 and 2), sets single-row selection, and applies both a QSqlRelationalDelegate for the foreign key columns and the DateDelegate for the date column.

Implement navigation. Unlike SingleRecordForm, TransactionsForm is a tabular view - all records are visible simultaneously and the view itself manages which row is current (there is nothing to map), so the navigation slots are moved from the nav bar to the form.
Handle new records. The add_new_record() slot inserts a new row at the end of the model and sets the category ID column to category_id passed to the form’s constructor, ensuring new transactions are always linked to the current category.

Put the master and the detail forms together. In the main window, connect the master form’s currentIndexChanged signal to a slot that reads the selected category ID and passes it to the transactions model as a filter via setFilter(), then calls select() to refresh the view.

Note that you can easily make TransactionsForm generic by replacing its hardcoded values with constructor parameters: a list of columns indices to hide, a dictionary of column-to-delegate mappings for custom editors like DateDelegate, and a dictionary of column-to-default-value pairs to fill on new rows. This would give you a reusable tabular form class you could pair with SingleRecordForm in your application.

If you don’t implement __init__() in your class at all, the parent class gets initalized automatically.↩︎
https://doc.qt.io/qtforpython-6/↩︎
https://doc.qt.io/↩︎
https://doc.qt.io/qt-6/qobject.html↩︎
https://doc.qt.io/qt-6/qcolordialog.html↩︎
https://doc.qt.io/qt-6/qfontdialog.html↩︎

Up next

34. Processes