Results 1 to 2 of 2

Thread: QSqlRelationalDelegate displays foreign_key of the related record instead of name

  1. #1
    Join Date
    Aug 2021
    Posts
    3
    Qt products
    Qt5
    Platforms
    Unix/X11 Windows

    Default QSqlRelationalDelegate displays foreign_key of the related record instead of name

    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:

    Qt Code:
    1. import sys
    2.  
    3. from PyQt6 import QtCore, QtWidgets
    4. from PyQt6.QtCore import QModelIndex, Qt
    5. from PyQt6.QtWidgets import QPushButton
    6.  
    7. class Ui_main(object):
    8. def setupUi(self, main):
    9. main.setObjectName("main")
    10. main.resize(781, 652)
    11.  
    12. self.verticalLayoutWidget = QtWidgets.QWidget(main)
    13. self.verticalLayoutWidget.setGeometry(QtCore.QRect(10, 10, 761, 631))
    14. self.verticalLayoutWidget.setObjectName("verticalLayoutWidget")
    15. self.verticalLayout = QtWidgets.QVBoxLayout(self.verticalLayoutWidget)
    16. self.verticalLayout.setContentsMargins(0, 0, 0, 0)
    17. self.verticalLayout.setObjectName("verticalLayout")
    18.  
    19. # Replace values with your database configurations
    20. database = QSqlDatabase.addDatabase('QSQLITE')
    21. database.setDatabaseName('accounting.db')
    22. database.open()
    23.  
    24. button_add = QPushButton("AddRow")
    25. button_add.clicked.connect(self.addRow)
    26. self.verticalLayout.addWidget(button_add)
    27.  
    28. self.tableView = QtWidgets.QTableView(self.verticalLayoutWidget)
    29. self.tableView.setObjectName("tableView")
    30. self.tableView.verticalHeader().setVisible(False)
    31. self.verticalLayout.addWidget(self.tableView)
    32.  
    33. self.table_model = QSqlRelationalTableModel(main, database)
    34. self.table_model.setJoinMode(QSqlRelationalTableModel.JoinMode.LeftJoin)
    35. self.table_model.setEditStrategy(QSqlTableModel.EditStrategy.OnFieldChange)
    36.  
    37. self.table_model.setTable('book_of_accounts')
    38.  
    39. self.table_model.setRelation(4, QSqlRelation('account_type', 'id', 'name'))
    40. self.table_model.setRelation(7, QSqlRelation('subconto1', 'id', 'name'))
    41. self.table_model.setRelation(8, QSqlRelation('subconto2', 'id', 'name'))
    42. self.table_model.setRelation(9, QSqlRelation('subconto3', 'id', 'name'))
    43.  
    44. self.table_model.select()
    45.  
    46. self.tableView.setModel(self.table_model)
    47. self.tableView.setItemDelegate(QSqlRelationalDelegate(self.tableView))
    48. self.tableView.hideColumn(0)
    49. QtCore.QMetaObject.connectSlotsByName(main)
    50.  
    51. def addRow(self):
    52. self.tableView.sortByColumn(-1, Qt.SortOrder.AscendingOrder)
    53. count = self.table_model.rowCount(QModelIndex())
    54. self.table_model.insertRows(count, 1)
    55. self.tableView.scrollToBottom()
    56.  
    57. self.tableView.updateGeometry()
    58. self.tableView.selectRow(count)
    59.  
    60.  
    61. if __name__ == '__main__':
    62. app = QtWidgets.QApplication(sys.argv)
    63. main_window = QtWidgets.QWidget()
    64. window = Ui_main()
    65. window.setupUi(main_window)
    66. main_window.show()
    67. sys.exit(app.exec())
    To copy to clipboard, switch view to plain text mode 
    Database in SQLite:

    Qt Code:
    1. BEGIN TRANSACTION;
    2. CREATE TABLE IF NOT EXISTS "book_of_accounts" (
    3. "id" INTEGER PRIMARY KEY AUTOINCREMENT,
    4. "code" varchar(4) NOT NULL,
    5. "belongs_to_id" bigint,
    6. "name" varchar(1024) NOT NULL,
    7. "account_type_id" bigint NOT NULL,
    8. "quantitative" boolean NOT NULL,
    9. "monetary" boolean NOT NULL,
    10. "subconto_1_id" bigint,
    11. "subconto_2_id" bigint,
    12. "subconto_3_id" bigint,
    13. CONSTRAINT "book_of_accounts_subconoto_3_id_fkey" FOREIGN KEY("subconto_3_id") REFERENCES "subconto3" on delete cascade,
    14. CONSTRAINT "book_of_accounts_subconoto_2_id_fkey" FOREIGN KEY("subconto_2_id") REFERENCES "subconto2" on delete cascade,
    15. CONSTRAINT "book_of_accounts_subconoto_1_id_fkey" FOREIGN KEY("subconto_1_id") REFERENCES "subconto1" on delete cascade,
    16. CONSTRAINT "book_of_accounts_account_type_id_fkey" FOREIGN KEY("account_type_id") REFERENCES "account_type",
    17. CONSTRAINT "book_of_accounts_belongs_to_id_fkey" FOREIGN KEY("belongs_to_id") REFERENCES "book_of_accounts"
    18. );
    19. CREATE TABLE IF NOT EXISTS "subconto3" (
    20. "id" bigserial,
    21. "name" varchar(1024) NOT NULL,
    22. CONSTRAINT "subconto3_pkey" PRIMARY KEY("id")
    23. );
    24. CREATE TABLE IF NOT EXISTS "subconto2" (
    25. "id" bigserial,
    26. "name" varchar(1024) NOT NULL,
    27. CONSTRAINT "subconto2_pkey" PRIMARY KEY("id")
    28. );
    29. CREATE TABLE IF NOT EXISTS "subconto1" (
    30. "id" bigserial,
    31. "name" varchar(1024) NOT NULL,
    32. CONSTRAINT "subconto1_pkey" PRIMARY KEY("id")
    33. );
    34. CREATE TABLE IF NOT EXISTS "account_type" (
    35. "id" bigserial,
    36. "name" varchar(1024) NOT NULL,
    37. CONSTRAINT "account_type_pkey" PRIMARY KEY("id")
    38. );
    39.  
    40. INSERT INTO "subconto3" ("id","name") VALUES (1,'< ?????? >');
    41. INSERT INTO "subconto3" ("id","name") VALUES (2,'????');
    42. INSERT INTO "subconto3" ("id","name") VALUES (3,'?????????????');
    43. INSERT INTO "subconto2" ("id","name") VALUES (1,'< ?????? >');
    44. INSERT INTO "subconto2" ("id","name") VALUES (2,'????? ????????');
    45. INSERT INTO "subconto2" ("id","name") VALUES (3,'?????? ??????');
    46. INSERT INTO "subconto2" ("id","name") VALUES (4,'????????');
    47. INSERT INTO "subconto2" ("id","name") VALUES (5,'???? ????????????????? ??????');
    48. INSERT INTO "subconto2" ("id","name") VALUES (6,'???????? ???????? ???????');
    49. INSERT INTO "subconto2" ("id","name") VALUES (7,'???? ???????');
    50. INSERT INTO "subconto1" ("id","name") VALUES (1,'< ?????? >');
    51. INSERT INTO "subconto1" ("id","name") VALUES (2,'???????? ????????');
    52. INSERT INTO "subconto1" ("id","name") VALUES (3,'?????????????? ??????');
    53. INSERT INTO "subconto1" ("id","name") VALUES (4,'??????-???????????? ??????');
    54. INSERT INTO "subconto1" ("id","name") VALUES (5,'?????? ??????');
    55. INSERT INTO "subconto1" ("id","name") VALUES (6,'??????? ??????? ????????');
    56. INSERT INTO "subconto1" ("id","name") VALUES (7,'???????????');
    57. INSERT INTO "subconto1" ("id","name") VALUES (8,'??????????');
    58. INSERT INTO "subconto1" ("id","name") VALUES (9,'?????? ? ??????????');
    59. INSERT INTO "subconto1" ("id","name") VALUES (10,'???????? ???????? ???????');
    60. INSERT INTO "subconto1" ("id","name") VALUES (11,'????????? ????? ????');
    61. INSERT INTO "subconto1" ("id","name") VALUES (12,'??????????? ? ??????????');
    62. INSERT INTO "account_type" ("id","name") VALUES (1,'????????');
    63. INSERT INTO "account_type" ("id","name") VALUES (2,'????????????');
    64. INSERT INTO "account_type" ("id","name") VALUES (3,'?????. ????????');
    65. INSERT INTO "account_type" ("id","name") VALUES (4,'?????. ?????????');
    66. INSERT INTO "account_type" ("id","name") VALUES (5,'?????????');
    67. INSERT INTO "account_type" ("id","name") VALUES (6,'??????????');
    68.  
    69. 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);
    70. 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);
    71. 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);
    72. 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);
    73. 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);
    74.  
    75. CREATE UNIQUE INDEX IF NOT EXISTS "bookofaccounts_code" ON "book_of_accounts" (
    76. "code"
    77. );
    78. CREATE INDEX IF NOT EXISTS "bookofaccounts_belongs_to_id" ON "book_of_accounts" (
    79. "belongs_to_id"
    80. );
    81. CREATE INDEX IF NOT EXISTS "bookofaccounts_name" ON "book_of_accounts" (
    82. "name"
    83. );
    84. CREATE INDEX IF NOT EXISTS "bookofaccounts_account_type_id" ON "book_of_accounts" (
    85. "account_type_id"
    86. );
    87. CREATE INDEX IF NOT EXISTS "bookofaccounts_subconoto_1_id" ON "book_of_accounts" (
    88. "subconto_1_id"
    89. );
    90. CREATE INDEX IF NOT EXISTS "bookofaccounts_subconoto_2_id" ON "book_of_accounts" (
    91. "subconto_2_id"
    92. );
    93. CREATE INDEX IF NOT EXISTS "bookofaccounts_subconoto_3_id" ON "book_of_accounts" (
    94. "subconto_3_id"
    95. );
    96. CREATE INDEX IF NOT EXISTS "subconto3_name" ON "subconto3" (
    97. "name"
    98. );
    99. CREATE INDEX IF NOT EXISTS "subconto2_name" ON "subconto2" (
    100. "name"
    101. );
    102. CREATE INDEX IF NOT EXISTS "subconto1_name" ON "subconto1" (
    103. "name"
    104. );
    105. CREATE INDEX IF NOT EXISTS "accounttype_name" ON "account_type" (
    106. "name"
    107. );
    108. COMMIT;
    To copy to clipboard, switch view to plain text mode 

    The problem is persistent in both pyqt5 and pyqt6. Why does this happen and any hints to fix this?

  2. #2
    Join Date
    Aug 2021
    Posts
    3
    Qt products
    Qt5
    Platforms
    Unix/X11 Windows

    Default Re: QSqlRelationalDelegate displays foreign_key of the related record instead of name

    I just noticed that Values in sql are being displayed as question marks here. It's a Russian text (no unicode in this platform?), so you might want to replace them with whatever is suitable for you.

Similar Threads

  1. Replies: 1
    Last Post: 24th December 2023, 06:13
  2. Replies: 7
    Last Post: 27th November 2010, 16:55
  3. QSqlRelationalDelegate
    By pippo42 in forum Qt Programming
    Replies: 1
    Last Post: 2nd May 2010, 13:05
  4. Replies: 3
    Last Post: 26th March 2010, 05:32
  5. Replies: 1
    Last Post: 20th January 2010, 23:59

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Digia, Qt and their respective logos are trademarks of Digia Plc in Finland and/or other countries worldwide.