I'm using PySide6 with Python 3.10 to create an app that is using an SQLite DB to record some data points.
I'm having difficulties with the QtSql module, in particular regarding the QTableView-QSqlTableModel interaction and the ability to create/edit/delete records present in the DB.
This is a minimum reproducible example of what I'm trying to achieve:
import logging
import sys
from PySide6.QtSql import QSqlDatabase, QSqlQuery, QSqlTableModel
from PySide6.QtWidgets import (
QApplication,
QHBoxLayout,
QMainWindow,
QPushButton,
QTableView,
QVBoxLayout,
QWidget,
)
class CustomSqlModel(QSqlTableModel):
def __init__(self, parent=None):
QSqlTableModel.__init__(self, parent=parent)
self.setTable("records")
self.setEditStrategy(QSqlTableModel.OnFieldChange)
self.select()
# Subclass QMainWindow to customize your application's main window
class MainWindow(QMainWindow):
def __init__(self):
super().__init__()
main_layout = QVBoxLayout()
buttons_layout = QHBoxLayout()
self.view = QTableView()
main_layout.addWidget(self.view)
main_layout.addLayout(buttons_layout)
self.add_button = QPushButton("+")
self.add_button.pressed.connect(self.add_action)
self.remove_button = QPushButton("-")
self.remove_button.pressed.connect(self.remove_action)
buttons_layout.addWidget(self.add_button)
buttons_layout.addWidget(self.remove_button)
widget = QWidget()
widget.setLayout(main_layout)
self.setCentralWidget(widget)
self.connect_to_db()
def add_action(self):
new_record = self.model.record()
new_record.setValue("point", 0)
self.model.insertRecord(self.model.rowCount(), new_record)
self.model.submitAll()
self.model.select()
def remove_action(self):
selected = self.view.currentIndex()
self.model.removeRow(selected.row())
self.model.submitAll()
self.model.select()
def connect_to_db(self):
self.database = QSqlDatabase.database()
if not self.database.isValid():
self.database = QSqlDatabase.addDatabase("QSQLITE")
self.database.setDatabaseName(f"test.db")
if not self.database.open():
logging.error("Failed to open db")
self.database = None
return
self.database.open()
if "records" not in self.database.tables():
query = QSqlQuery()
if not query.exec(
"""
CREATE TABLE IF NOT EXISTS records(
point REAL
)
"""
):
logging.error("Failed to query db")
if not query.exec(
"""
INSERT INTO records VALUES (0);
"""
):
logging.error("Failed to create initial data in db")
self.model = CustomSqlModel()
self.view.setModel(self.model)
app = QApplication(sys.argv)
window = MainWindow()
window.show()
app.exec()
When the application is started I look for an existing SQLite DB and load it. If it's not present I create it and add one table records to it (one single column that holds a floting point value).
The QTableView widget in the middle of the interface shows the loaded DB and it's possible to directly edit the records from there.
At the bottom of the widget two buttons are present: add_button and remove_button, which are used to add one row to the records table and to remove the currently selected one respectivly.
My issues arise form the behavior of the QTableView when creating/removing rows with my UI: adding rows seems to work fine at first but removing one of the newly created rows deletes all the rows instead of just the one selected. Moreover editing one of the rows and then deleting any of them results in no deletion whatsoever and all the rows getting the same value of the edited one.
The problem seems to be related to my implementation since if I edit the table with an SQL editor such as DBeaver or DB Browser for Sqlite the created/edited rows are correctly displayed in thr application.
What am I doing wrong?
The issue here is with the structure of your database. Although it's valid to create a database with no primary key, this will usually cause problems, because it may force Qt to try to identify rows using a combination all their values when editing an SQL model via a view. In cases where each row does not have a unique combination of values, this is almost certain to produce unpredictable behaviour.
In your example, every row is initialised with the value 0. If that remains unchanged, deleting any row with that value will delete them all, since (as far as Qt is concerned) they all effectively have the same key.
To fix your example, you should therefore simply add a primary key, like so:
class MainWindow(QMainWindow):
...
def connect_to_db(self):
...
if "records" not in self.database.tables():
query = QSqlQuery()
if not query.exec(
"""
CREATE TABLE IF NOT EXISTS records(
ID INTEGER PRIMARY KEY,
point REAL
)
"""
):
logging.error("Failed to query db")
if not query.exec(
"""
INSERT INTO records VALUES (NULL,0);
"""
):
logging.error("Failed to create initial data in db")