GrayWizard12345
10th August 2021, 07:11
I am trying to add new rows to QSqlRelationalModel which is represented in QTableView.
I have set proper QSqlRelationalDelegate and proper QSqlRelations in the model. Displaying existing data from the database works fine. Columns with related data change to Comboboxes and I can choose options from the related tables.
However, when I try to create a new record by adding a row to the model, Comboboxes allow me to choose the proper value from the dropdown list, but after choosing it, the value changes to the ID of the related record as if no relational delegate was set.
main.py:
import sys
from PyQt6 import QtCore, QtWidgets
from PyQt6.QtCore import QModelIndex, Qt
from PyQt6.QtSql import QSqlDatabase, QSqlTableModel, QSqlRelationalTableModel, QSqlRelation, QSqlQuery, \
QSqlRelationalDelegate
from PyQt6.QtWidgets import QPushButton
class Ui_main(object):
def setupUi(self, main):
main.setObjectName("main")
main.resize(781, 652)
self.verticalLayoutWidget = QtWidgets.QWidget(main)
self.verticalLayoutWidget.setGeometry(QtCore.QRect (10, 10, 761, 631))
self.verticalLayoutWidget.setObjectName("verticalLayoutWidget")
self.verticalLayout = QtWidgets.QVBoxLayout(self.verticalLayoutWidget)
self.verticalLayout.setContentsMargins(0, 0, 0, 0)
self.verticalLayout.setObjectName("verticalLayout")
# Replace values with your database configurations
database = QSqlDatabase.addDatabase('QSQLITE')
database.setDatabaseName('accounting.db')
database.open()
button_add = QPushButton("AddRow")
button_add.clicked.connect(self.addRow)
self.verticalLayout.addWidget(button_add)
self.tableView = QtWidgets.QTableView(self.verticalLayoutWidget)
self.tableView.setObjectName("tableView")
self.tableView.verticalHeader().setVisible(False)
self.verticalLayout.addWidget(self.tableView)
self.table_model = QSqlRelationalTableModel(main, database)
self.table_model.setJoinMode(QSqlRelationalTableMo del.JoinMode.LeftJoin)
self.table_model.setEditStrategy(QSqlTableModel.Ed itStrategy.OnFieldChange)
self.table_model.setTable('book_of_accounts')
self.table_model.setRelation(4, QSqlRelation('account_type', 'id', 'name'))
self.table_model.setRelation(7, QSqlRelation('subconto1', 'id', 'name'))
self.table_model.setRelation(8, QSqlRelation('subconto2', 'id', 'name'))
self.table_model.setRelation(9, QSqlRelation('subconto3', 'id', 'name'))
self.table_model.select()
self.tableView.setModel(self.table_model)
self.tableView.setItemDelegate(QSqlRelationalDeleg ate(self.tableView))
self.tableView.hideColumn(0)
QtCore.QMetaObject.connectSlotsByName(main)
def addRow(self):
self.tableView.sortByColumn(-1, Qt.SortOrder.AscendingOrder)
count = self.table_model.rowCount(QModelIndex())
self.table_model.insertRows(count, 1)
self.tableView.scrollToBottom()
self.tableView.updateGeometry()
self.tableView.selectRow(count)
if __name__ == '__main__':
app = QtWidgets.QApplication(sys.argv)
main_window = QtWidgets.QWidget()
window = Ui_main()
window.setupUi(main_window)
main_window.show()
sys.exit(app.exec())
Database in SQLite:
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "book_of_accounts" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT,
"code" varchar(4) NOT NULL,
"belongs_to_id" bigint,
"name" varchar(1024) NOT NULL,
"account_type_id" bigint NOT NULL,
"quantitative" boolean NOT NULL,
"monetary" boolean NOT NULL,
"subconto_1_id" bigint,
"subconto_2_id" bigint,
"subconto_3_id" bigint,
CONSTRAINT "book_of_accounts_subconoto_3_id_fkey" FOREIGN KEY("subconto_3_id") REFERENCES "subconto3" on delete cascade,
CONSTRAINT "book_of_accounts_subconoto_2_id_fkey" FOREIGN KEY("subconto_2_id") REFERENCES "subconto2" on delete cascade,
CONSTRAINT "book_of_accounts_subconoto_1_id_fkey" FOREIGN KEY("subconto_1_id") REFERENCES "subconto1" on delete cascade,
CONSTRAINT "book_of_accounts_account_type_id_fkey" FOREIGN KEY("account_type_id") REFERENCES "account_type",
CONSTRAINT "book_of_accounts_belongs_to_id_fkey" FOREIGN KEY("belongs_to_id") REFERENCES "book_of_accounts"
);
CREATE TABLE IF NOT EXISTS "subconto3" (
"id" bigserial,
"name" varchar(1024) NOT NULL,
CONSTRAINT "subconto3_pkey" PRIMARY KEY("id")
);
CREATE TABLE IF NOT EXISTS "subconto2" (
"id" bigserial,
"name" varchar(1024) NOT NULL,
CONSTRAINT "subconto2_pkey" PRIMARY KEY("id")
);
CREATE TABLE IF NOT EXISTS "subconto1" (
"id" bigserial,
"name" varchar(1024) NOT NULL,
CONSTRAINT "subconto1_pkey" PRIMARY KEY("id")
);
CREATE TABLE IF NOT EXISTS "account_type" (
"id" bigserial,
"name" varchar(1024) NOT NULL,
CONSTRAINT "account_type_pkey" PRIMARY KEY("id")
);
INSERT INTO "subconto3" ("id","name") VALUES (1,'< ?????? >');
INSERT INTO "subconto3" ("id","name") VALUES (2,'????');
INSERT INTO "subconto3" ("id","name") VALUES (3,'?????????????');
INSERT INTO "subconto2" ("id","name") VALUES (1,'< ?????? >');
INSERT INTO "subconto2" ("id","name") VALUES (2,'????? ????????');
INSERT INTO "subconto2" ("id","name") VALUES (3,'?????? ??????');
INSERT INTO "subconto2" ("id","name") VALUES (4,'????????');
INSERT INTO "subconto2" ("id","name") VALUES (5,'???? ????????????????? ??????');
INSERT INTO "subconto2" ("id","name") VALUES (6,'???????? ???????? ???????');
INSERT INTO "subconto2" ("id","name") VALUES (7,'???? ???????');
INSERT INTO "subconto1" ("id","name") VALUES (1,'< ?????? >');
INSERT INTO "subconto1" ("id","name") VALUES (2,'???????? ????????');
INSERT INTO "subconto1" ("id","name") VALUES (3,'?????????????? ??????');
INSERT INTO "subconto1" ("id","name") VALUES (4,'??????-???????????? ??????');
INSERT INTO "subconto1" ("id","name") VALUES (5,'?????? ??????');
INSERT INTO "subconto1" ("id","name") VALUES (6,'??????? ??????? ????????');
INSERT INTO "subconto1" ("id","name") VALUES (7,'???????????');
INSERT INTO "subconto1" ("id","name") VALUES (8,'??????????');
INSERT INTO "subconto1" ("id","name") VALUES (9,'?????? ? ??????????');
INSERT INTO "subconto1" ("id","name") VALUES (10,'???????? ???????? ???????');
INSERT INTO "subconto1" ("id","name") VALUES (11,'????????? ????? ????');
INSERT INTO "subconto1" ("id","name") VALUES (12,'??????????? ? ??????????');
INSERT INTO "account_type" ("id","name") VALUES (1,'????????');
INSERT INTO "account_type" ("id","name") VALUES (2,'????????????');
INSERT INTO "account_type" ("id","name") VALUES (3,'?????. ????????');
INSERT INTO "account_type" ("id","name") VALUES (4,'?????. ?????????');
INSERT INTO "account_type" ("id","name") VALUES (5,'?????????');
INSERT INTO "account_type" ("id","name") VALUES (6,'??????????');
INSERT INTO "book_of_accounts" ("id","code","belongs_to_id","name","account_type_id","quantitative","monetary","subconto_1_id","subconto_2_id","subconto_3_id") VALUES (1,'0000',NULL,'???????',5,0,0,1,1,1);
INSERT INTO "book_of_accounts" ("id","code","belongs_to_id","name","account_type_id","quantitative","monetary","subconto_1_id","subconto_2_id","subconto_3_id") VALUES (2,'0100',NULL,'????? ????? ???????? ???????',3,0,0,2,2,1);
INSERT INTO "book_of_accounts" ("id","code","belongs_to_id","name","account_type_id","quantitative","monetary","subconto_1_id","subconto_2_id","subconto_3_id") VALUES (3,'0110',2,'?????',3,0,0,2,2,1);
INSERT INTO "book_of_accounts" ("id","code","belongs_to_id","name","account_type_id","quantitative","monetary","subconto_1_id","subconto_2_id","subconto_3_id") VALUES (4,'0111',2,'??????????????? ?????',1,0,0,2,2,1);
INSERT INTO "book_of_accounts" ("id","code","belongs_to_id","name","account_type_id","quantitative","monetary","subconto_1_id","subconto_2_id","subconto_3_id") VALUES (5,'0112',2,'??????????????? ???????? ???????, ?????????? ?? ???????? ???????????? ??????',1,0,0,2,2,1);
CREATE UNIQUE INDEX IF NOT EXISTS "bookofaccounts_code" ON "book_of_accounts" (
"code"
);
CREATE INDEX IF NOT EXISTS "bookofaccounts_belongs_to_id" ON "book_of_accounts" (
"belongs_to_id"
);
CREATE INDEX IF NOT EXISTS "bookofaccounts_name" ON "book_of_accounts" (
"name"
);
CREATE INDEX IF NOT EXISTS "bookofaccounts_account_type_id" ON "book_of_accounts" (
"account_type_id"
);
CREATE INDEX IF NOT EXISTS "bookofaccounts_subconoto_1_id" ON "book_of_accounts" (
"subconto_1_id"
);
CREATE INDEX IF NOT EXISTS "bookofaccounts_subconoto_2_id" ON "book_of_accounts" (
"subconto_2_id"
);
CREATE INDEX IF NOT EXISTS "bookofaccounts_subconoto_3_id" ON "book_of_accounts" (
"subconto_3_id"
);
CREATE INDEX IF NOT EXISTS "subconto3_name" ON "subconto3" (
"name"
);
CREATE INDEX IF NOT EXISTS "subconto2_name" ON "subconto2" (
"name"
);
CREATE INDEX IF NOT EXISTS "subconto1_name" ON "subconto1" (
"name"
);
CREATE INDEX IF NOT EXISTS "accounttype_name" ON "account_type" (
"name"
);
COMMIT;
The problem is persistent in both pyqt5 and pyqt6. Why does this happen and any hints to fix this?
I have set proper QSqlRelationalDelegate and proper QSqlRelations in the model. Displaying existing data from the database works fine. Columns with related data change to Comboboxes and I can choose options from the related tables.
However, when I try to create a new record by adding a row to the model, Comboboxes allow me to choose the proper value from the dropdown list, but after choosing it, the value changes to the ID of the related record as if no relational delegate was set.
main.py:
import sys
from PyQt6 import QtCore, QtWidgets
from PyQt6.QtCore import QModelIndex, Qt
from PyQt6.QtSql import QSqlDatabase, QSqlTableModel, QSqlRelationalTableModel, QSqlRelation, QSqlQuery, \
QSqlRelationalDelegate
from PyQt6.QtWidgets import QPushButton
class Ui_main(object):
def setupUi(self, main):
main.setObjectName("main")
main.resize(781, 652)
self.verticalLayoutWidget = QtWidgets.QWidget(main)
self.verticalLayoutWidget.setGeometry(QtCore.QRect (10, 10, 761, 631))
self.verticalLayoutWidget.setObjectName("verticalLayoutWidget")
self.verticalLayout = QtWidgets.QVBoxLayout(self.verticalLayoutWidget)
self.verticalLayout.setContentsMargins(0, 0, 0, 0)
self.verticalLayout.setObjectName("verticalLayout")
# Replace values with your database configurations
database = QSqlDatabase.addDatabase('QSQLITE')
database.setDatabaseName('accounting.db')
database.open()
button_add = QPushButton("AddRow")
button_add.clicked.connect(self.addRow)
self.verticalLayout.addWidget(button_add)
self.tableView = QtWidgets.QTableView(self.verticalLayoutWidget)
self.tableView.setObjectName("tableView")
self.tableView.verticalHeader().setVisible(False)
self.verticalLayout.addWidget(self.tableView)
self.table_model = QSqlRelationalTableModel(main, database)
self.table_model.setJoinMode(QSqlRelationalTableMo del.JoinMode.LeftJoin)
self.table_model.setEditStrategy(QSqlTableModel.Ed itStrategy.OnFieldChange)
self.table_model.setTable('book_of_accounts')
self.table_model.setRelation(4, QSqlRelation('account_type', 'id', 'name'))
self.table_model.setRelation(7, QSqlRelation('subconto1', 'id', 'name'))
self.table_model.setRelation(8, QSqlRelation('subconto2', 'id', 'name'))
self.table_model.setRelation(9, QSqlRelation('subconto3', 'id', 'name'))
self.table_model.select()
self.tableView.setModel(self.table_model)
self.tableView.setItemDelegate(QSqlRelationalDeleg ate(self.tableView))
self.tableView.hideColumn(0)
QtCore.QMetaObject.connectSlotsByName(main)
def addRow(self):
self.tableView.sortByColumn(-1, Qt.SortOrder.AscendingOrder)
count = self.table_model.rowCount(QModelIndex())
self.table_model.insertRows(count, 1)
self.tableView.scrollToBottom()
self.tableView.updateGeometry()
self.tableView.selectRow(count)
if __name__ == '__main__':
app = QtWidgets.QApplication(sys.argv)
main_window = QtWidgets.QWidget()
window = Ui_main()
window.setupUi(main_window)
main_window.show()
sys.exit(app.exec())
Database in SQLite:
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "book_of_accounts" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT,
"code" varchar(4) NOT NULL,
"belongs_to_id" bigint,
"name" varchar(1024) NOT NULL,
"account_type_id" bigint NOT NULL,
"quantitative" boolean NOT NULL,
"monetary" boolean NOT NULL,
"subconto_1_id" bigint,
"subconto_2_id" bigint,
"subconto_3_id" bigint,
CONSTRAINT "book_of_accounts_subconoto_3_id_fkey" FOREIGN KEY("subconto_3_id") REFERENCES "subconto3" on delete cascade,
CONSTRAINT "book_of_accounts_subconoto_2_id_fkey" FOREIGN KEY("subconto_2_id") REFERENCES "subconto2" on delete cascade,
CONSTRAINT "book_of_accounts_subconoto_1_id_fkey" FOREIGN KEY("subconto_1_id") REFERENCES "subconto1" on delete cascade,
CONSTRAINT "book_of_accounts_account_type_id_fkey" FOREIGN KEY("account_type_id") REFERENCES "account_type",
CONSTRAINT "book_of_accounts_belongs_to_id_fkey" FOREIGN KEY("belongs_to_id") REFERENCES "book_of_accounts"
);
CREATE TABLE IF NOT EXISTS "subconto3" (
"id" bigserial,
"name" varchar(1024) NOT NULL,
CONSTRAINT "subconto3_pkey" PRIMARY KEY("id")
);
CREATE TABLE IF NOT EXISTS "subconto2" (
"id" bigserial,
"name" varchar(1024) NOT NULL,
CONSTRAINT "subconto2_pkey" PRIMARY KEY("id")
);
CREATE TABLE IF NOT EXISTS "subconto1" (
"id" bigserial,
"name" varchar(1024) NOT NULL,
CONSTRAINT "subconto1_pkey" PRIMARY KEY("id")
);
CREATE TABLE IF NOT EXISTS "account_type" (
"id" bigserial,
"name" varchar(1024) NOT NULL,
CONSTRAINT "account_type_pkey" PRIMARY KEY("id")
);
INSERT INTO "subconto3" ("id","name") VALUES (1,'< ?????? >');
INSERT INTO "subconto3" ("id","name") VALUES (2,'????');
INSERT INTO "subconto3" ("id","name") VALUES (3,'?????????????');
INSERT INTO "subconto2" ("id","name") VALUES (1,'< ?????? >');
INSERT INTO "subconto2" ("id","name") VALUES (2,'????? ????????');
INSERT INTO "subconto2" ("id","name") VALUES (3,'?????? ??????');
INSERT INTO "subconto2" ("id","name") VALUES (4,'????????');
INSERT INTO "subconto2" ("id","name") VALUES (5,'???? ????????????????? ??????');
INSERT INTO "subconto2" ("id","name") VALUES (6,'???????? ???????? ???????');
INSERT INTO "subconto2" ("id","name") VALUES (7,'???? ???????');
INSERT INTO "subconto1" ("id","name") VALUES (1,'< ?????? >');
INSERT INTO "subconto1" ("id","name") VALUES (2,'???????? ????????');
INSERT INTO "subconto1" ("id","name") VALUES (3,'?????????????? ??????');
INSERT INTO "subconto1" ("id","name") VALUES (4,'??????-???????????? ??????');
INSERT INTO "subconto1" ("id","name") VALUES (5,'?????? ??????');
INSERT INTO "subconto1" ("id","name") VALUES (6,'??????? ??????? ????????');
INSERT INTO "subconto1" ("id","name") VALUES (7,'???????????');
INSERT INTO "subconto1" ("id","name") VALUES (8,'??????????');
INSERT INTO "subconto1" ("id","name") VALUES (9,'?????? ? ??????????');
INSERT INTO "subconto1" ("id","name") VALUES (10,'???????? ???????? ???????');
INSERT INTO "subconto1" ("id","name") VALUES (11,'????????? ????? ????');
INSERT INTO "subconto1" ("id","name") VALUES (12,'??????????? ? ??????????');
INSERT INTO "account_type" ("id","name") VALUES (1,'????????');
INSERT INTO "account_type" ("id","name") VALUES (2,'????????????');
INSERT INTO "account_type" ("id","name") VALUES (3,'?????. ????????');
INSERT INTO "account_type" ("id","name") VALUES (4,'?????. ?????????');
INSERT INTO "account_type" ("id","name") VALUES (5,'?????????');
INSERT INTO "account_type" ("id","name") VALUES (6,'??????????');
INSERT INTO "book_of_accounts" ("id","code","belongs_to_id","name","account_type_id","quantitative","monetary","subconto_1_id","subconto_2_id","subconto_3_id") VALUES (1,'0000',NULL,'???????',5,0,0,1,1,1);
INSERT INTO "book_of_accounts" ("id","code","belongs_to_id","name","account_type_id","quantitative","monetary","subconto_1_id","subconto_2_id","subconto_3_id") VALUES (2,'0100',NULL,'????? ????? ???????? ???????',3,0,0,2,2,1);
INSERT INTO "book_of_accounts" ("id","code","belongs_to_id","name","account_type_id","quantitative","monetary","subconto_1_id","subconto_2_id","subconto_3_id") VALUES (3,'0110',2,'?????',3,0,0,2,2,1);
INSERT INTO "book_of_accounts" ("id","code","belongs_to_id","name","account_type_id","quantitative","monetary","subconto_1_id","subconto_2_id","subconto_3_id") VALUES (4,'0111',2,'??????????????? ?????',1,0,0,2,2,1);
INSERT INTO "book_of_accounts" ("id","code","belongs_to_id","name","account_type_id","quantitative","monetary","subconto_1_id","subconto_2_id","subconto_3_id") VALUES (5,'0112',2,'??????????????? ???????? ???????, ?????????? ?? ???????? ???????????? ??????',1,0,0,2,2,1);
CREATE UNIQUE INDEX IF NOT EXISTS "bookofaccounts_code" ON "book_of_accounts" (
"code"
);
CREATE INDEX IF NOT EXISTS "bookofaccounts_belongs_to_id" ON "book_of_accounts" (
"belongs_to_id"
);
CREATE INDEX IF NOT EXISTS "bookofaccounts_name" ON "book_of_accounts" (
"name"
);
CREATE INDEX IF NOT EXISTS "bookofaccounts_account_type_id" ON "book_of_accounts" (
"account_type_id"
);
CREATE INDEX IF NOT EXISTS "bookofaccounts_subconoto_1_id" ON "book_of_accounts" (
"subconto_1_id"
);
CREATE INDEX IF NOT EXISTS "bookofaccounts_subconoto_2_id" ON "book_of_accounts" (
"subconto_2_id"
);
CREATE INDEX IF NOT EXISTS "bookofaccounts_subconoto_3_id" ON "book_of_accounts" (
"subconto_3_id"
);
CREATE INDEX IF NOT EXISTS "subconto3_name" ON "subconto3" (
"name"
);
CREATE INDEX IF NOT EXISTS "subconto2_name" ON "subconto2" (
"name"
);
CREATE INDEX IF NOT EXISTS "subconto1_name" ON "subconto1" (
"name"
);
CREATE INDEX IF NOT EXISTS "accounttype_name" ON "account_type" (
"name"
);
COMMIT;
The problem is persistent in both pyqt5 and pyqt6. Why does this happen and any hints to fix this?