PyQt5工单编辑QComboBox无法正确设置指定关联值问题

问题描述 #

开发PyQt5工单编辑窗体时，尝试将order_type_combo QComboBox的当前值设置为wo_type表中ID为29的选项「Complaint: Damage caused by sewer blockages」，但实际显示的却是ID为2的默认选项「Complaint: Sewerage Blockage」。运行代码时控制台输出「Error: Invalid order type」，打印出的order_type值为3。工单数据存储于t_wo_workorders表，下拉选项关联wo_type表。

报错信息 #

Error: Invalid order type
3
[Finished in 5.3s]

完整代码 #

from PyQt5 import QtWidgets, QtGui
import sys
from PyQt5.QtWidgets import QApplication, QWidget, QLabel, QPushButton, QLineEdit, QVBoxLayout, QDateEdit, QComboBox, QStyleFactory, QTableView, QMessageBox, QSizePolicy, QTextEdit
from PyQt5.QtCore import Qt
from PyQt5.QtGui import QFont, QStandardItemModel, QStandardItem, QPalette
import psycopg2
import datetime
from PyQt5.QtCore import QDate
from PyQt5.QtWidgets import QDateEdit
from PyQt5.QtCore import QTimer


current_date = QDate.currentDate()

# Connect to the database and create a cursor object
conn = psycopg2.connect(
    host="localhost",
    database="mms",
    user="postgres",
    password="postgres"
)
cur = conn.cursor()

# Retrieve the last work order from the t_wo_workorders table
cur.execute("SELECT * FROM t_wo_workorders ORDER BY order_id DESC LIMIT 1")
work_order = cur.fetchone()

# Extract the values for each field in the work order
work_order_id = work_order[0]
defined = work_order[1]
org = work_order[2]
order_type = work_order[3]
scheduled = work_order[4]
status = work_order[5]
request = work_order[6]
address = work_order[7]
customer = work_order[8]
tel_no = work_order[9]


class EditWorkOrderForm(QWidget):

    def populate_org_combo(self):
        self.cur.execute("SELECT org_unit, id FROM org_units")
        org_units = self.cur.fetchall()
        for org_unit in org_units:
            self.org_combo.addItem(str(org_unit[0]))
            self.org_units_dict[org_unit[1]] = org_unit[0]

    def populate_order_type_combo(self, order_type):
        self.order_type = order_type
        # Retrieve the wo_type records from the database
        self.cur.execute("SELECT type_description_en, id FROM wo_type")
        wo_type = self.cur.fetchall()

        # Create a dictionary with the id values as keys and the type_description_en values as values
        self.order_types_dict = {}
        for id, type_description_en in wo_type:
            self.order_types_dict[id] = type_description_en
            self.order_type_combo.addItem(str(type_description_en))

        # Check if the order_type value is present in the order_types_dict dictionary
        if self.order_type not in self.order_types_dict:
            print("Error: Invalid order type")
            return

        # Get the index of the order_type value in the order_types_dict keys and set the combo box's current index to that value
        default_index = list(self.order_types_dict.keys()).index(self.order_type)
        self.order_type_combo.setCurrentIndex(default_index)

    def populate_status_combo(self):
        self.cur.execute("SELECT status_descr_en, id FROM wo_status")
        wo_status = self.cur.fetchall()
        for status in wo_status:
            self.status_combo.addItem(str(status[0]))
            self.status_dict[status[1]] = status[0]

    def __init__(self, conn, cur, order_id, defined, org, order_type, scheduled, status, request, address, customer, tel_no):
        super().__init__()
        self.cur = cur  # Assign the cur parameter to the self.cur attribute
        self.order_types_dict = {}
        self.order_type_combo = QComboBox(self)
        self.populate_order_type_combo(order_type)
        
        print(order_type)
        self.conn = conn
        self.org_units_dict = {}
        self.status_dict = {}
        self.order_id = order_id
        self.org_combo = QComboBox(self)
        self.populate_org_combo()
        if str(org) in self.org_units_dict:
            org_value = self.org_units_dict[int(org)]
        else:
            org_value = ""
        try:
            org_value = self.org_units_dict[int(org)]
        except KeyError:
            pass
        org_index = self.org_combo.findText(org_value)
        order_type_value = [v for k, v in self.order_types_dict.items() if k == order_type]
        if order_type_value:
            order_type_value = order_type_value[0]
        else:
            order_type_value = ""
        order_type_index = self.order_type_combo.findText(order_type_value)
        self.order_type_combo.setCurrentIndex(order_type_index)
        self.status_combo = QComboBox(self) 
        self.populate_status_combo()
        conn = psycopg2.connect(              
            host="localhost",
            database="mms",
            user="postgres",
            password="postgres"
        )

        self.cur.execute(
            "SELECT * FROM t_wo_workorders WHERE order_id = %s",
            (self.order_id,)
        )
        work_order = self.cur.fetchone()
        self.defined_label = QLabel("Defined:")
        self.defined_edit = QDateEdit()
        self.defined_edit.setDate(defined)

        self.org_label = QLabel("Org. Unit:")
        self.org_combo.setGeometry(100, 100, 200, 25)
        self.org_combo.setCurrentIndex(int(org))
        self.org_combo.setCurrentIndex(org_index)

        self.order_type_label = QLabel("Order Type:")   
        self.order_type_combo.setGeometry(100, 100, 200, 25) 
        self.order_type_combo.setCurrentIndex(int(order_type))
        self.order_type_combo.setCurrentIndex(order_type_index)

        self.scheduled_label = QLabel("Scheduled:")
        self.scheduled_edit = QDateEdit()
        self.scheduled_edit.setDate(scheduled)
        self.status_label = QLabel("Status:")
        self.status_combo = QComboBox()
        self.order_type_combo.setCurrentIndex(int(status))
        status_value = self.status_dict.get(org, "")
        status_index = self.status_combo.findText(status_value)
        self.request_label = QLabel("Request:")
        self.request_edit = QLineEdit()
        self.request_edit.setText(request)
        self.address_label = QLabel("Address:")
        self.address_edit = QLineEdit()
        self.address_edit.setText(address)
        self.customer_label = QLabel("Customer:")
        self.customer_edit = QLineEdit()
        self.customer_edit.setText(customer)
        self.tel_no_label = QLabel("Tel. No:")
        self.tel_no_edit = QLineEdit()
        self.tel_no_edit.setText(tel_no)
        self.request_gps_label = QLabel("Request GPS:") 

        layout = QVBoxLayout()  
        layout.addWidget(self.defined_label)
        layout.addWidget(self.defined_edit)
        layout.addWidget(self.org_label)
        layout.addWidget(self.org_combo)
        layout.addWidget(self.order_type_label) 
        layout.addWidget(self.order_type_combo) 
        layout.addWidget(self.scheduled_label)
        layout.addWidget(self.scheduled_edit)
        layout.addWidget(self.status_label)
        layout.addWidget(self.status_combo)
        layout.addWidget(self.request_label)
        layout.addWidget(self.request_edit)
        layout.addWidget(self.address_label)
        layout.addWidget(self.address_edit)
        layout.addWidget(self.customer_label)
        layout.addWidget(self.customer_edit)
        layout.addWidget(self.tel_no_label)
        layout.addWidget(self.tel_no_edit)
        layout.addWidget(self.request_gps_label)

        self.setLayout(layout)
        self.show()

        def edit_work_order(self, work_order_id):
            form = EditWorkOrderForm(work_order_id)
            form.show()

        def create_edit_work_order_form(work_order_id, defined, org, order_type, scheduled, status, request, address, customer, tel_no):
            form = EditWorkOrderForm(conn, cur, work_order_id, defined, org, order_type, scheduled, status, request, address, customer, tel_no)
            return form


app = QApplication(sys.argv)
edit_work_order_form = EditWorkOrderForm(conn, cur, work_order_id, defined, org, order_type, scheduled, status, request, address, customer, tel_no)
edit_work_order_form.show()
sys.exit(app.exec_())

问题分析 #

1. 数据库查询字段顺序不匹配：populate_order_type_combo中执行SELECT type_description_en, id FROM wo_type，返回的结果集是(描述文本, ID)，但循环时使用for id, type_description_en in wo_type，导致字典order_types_dict的键值完全颠倒——把描述文本当作ID存为键，ID当作描述存为值。这直接导致后续判断order_type是否在字典中时失败，输出「Error: Invalid order type」。
2. 重复设置ComboBox索引：__init__方法中，populate_order_type_combo已经完成了索引设置，但后续又执行了self.order_type_combo.setCurrentIndex(int(order_type))和self.order_type_combo.setCurrentIndex(order_type_index)，覆盖了正确的索引值。
3. 冗余数据库连接：__init__中重新创建了数据库连接，完全可以复用传入的conn和cur，避免资源浪费。
4. 数据值不符：打印的order_type值为3，但预期是29，需要核对t_wo_workorders表中对应工单的order_type字段值是否正确。

修复方案 #

1. 修正查询结果循环变量顺序 #

修改populate_order_type_combo方法中的循环，匹配查询返回的字段顺序：

def populate_order_type_combo(self, order_type):
    self.order_type = order_type
    self.cur.execute("SELECT type_description_en, id FROM wo_type")
    wo_type = self.cur.fetchall()

    self.order_types_dict = {}
    # 修正循环变量顺序，匹配查询结果的(type_description_en, id)
    for type_description_en, id in wo_type:
        self.order_types_dict[id] = type_description_en
        self.order_type_combo.addItem(str(type_description_en))

    if self.order_type not in self.order_types_dict:
        print("Error: Invalid order type")
        return

    default_index = list(self.order_types_dict.keys()).index(self.order_type)
    self.order_type_combo.setCurrentIndex(default_index)

2. 删除重复的ComboBox索引设置代码 #

在__init__方法中，删除以下重复设置order_type_combo索引的代码：

# 移除以下两行
self.order_type_combo.setCurrentIndex(int(order_type))
self.order_type_combo.setCurrentIndex(order_type_index)

3. 移除冗余数据库连接 #

删除__init__中重新创建数据库连接的代码：

# 移除这段代码
conn = psycopg2.connect(              
    host="localhost",
    database="mms",
    user="postgres",
    password="postgres"
)

4. 核对数据库数据 #

检查t_wo_workorders表中最后一条工单的order_type字段值，确认是否确实为29。如果当前值是3，说明数据本身和预期不符，需要修正数据库中的数据。

修复后效果 #

修正后，order_types_dict会正确存储ID到描述的映射，populate_order_type_combo能正确找到对应ID的索引并设置，同时不会被后续冗余代码覆盖设置值，即可正常显示指定的工单类型选项。

内容的提问来源于stack exchange，提问作者Gent Bytyqi