QComboBox using QQueryModel, getting the id field (not display) from the clicked

Question

PyQt5 - QComboBox populated with QQueryModel including Id; Name; ..etc fields. Using Name as QComboBox.modelColumn. Hope to retrive id field from the clicked item.

I shifted the ModelColumn back and forth without doing any good. Also I accessed the QQueryModel.record to find that it is on the first record always, not on the current one.

import MySQL_Connector
import sys
from PyQt5.QtCore import QVariant, Qt
from PyQt5.QtSql import QSqlDatabase, QSqlQueryModel,QSqlQuery , QSqlTableModel, QSqlError, QSqlQueryModel, QSqlQuery
from PyQt5.QtWidgets import QApplication, QMainWindow
from MainUI import Ui_MainWindow


class QConnectionError(Exception):
    pass


class MainWindow(QMainWindow, Ui_MainWindow):


    def __init__(self):
        super(MainWindow, self).__init__()
        self.setupUi(self)

        #setup Qsql databaase objects
        cnn = MySQL_Connector.MysqlConnection('config.ini')
        con_string = cnn.read_db_config()[1]
        try:
            db = QSqlDatabase.addDatabase("QMYSQL")
            db.setHostName(con_string['host'])
            db.setUserName(con_string['user'])
            db.setDatabaseName(con_string['database'])
            db.setPassword(con_string['password'])
            ok = db.open()
            if not ok:
                raise QConnectionError("Connection failed--- Is the server running?")
        except QConnectionError as err:
            print("You'll have to wait until a connection is established")
            return
        finally:
            if db.isOpen():
                db.close()
        self.qdb = db
        self.qdb.open()
        # set combobox
        self.comboQuery = combo_query(self.qdb)
        self.comboModel = QSqlQueryModel()
        self.comboModel.setQuery(self.comboQuery)

        self.comboBox.setModel(self.comboModel)
        self.comboBox.setModelColumn(1)
        self.comboBox.activated[int].connect(self.do_action)

       #populate textView
        self.query = test(self.qdb)
        self.model = QSqlQueryModel()
        self.model.setQuery(self.query)
        self.model.setHeaderData(0,Qt.Horizontal, "ID")
        self.model.setHeaderData(1, Qt.Horizontal, "Nombre")

        self.tableView.rowHeight(2)
        self.tableView.fontMetrics()
        self.tableView.setModel(self.model)
        self.show()
        if self.qdb.isOpen():
            self.qdb.close()

    def do_action(self,  str): #Experimenting 
        print(str, type(str))
        self.tableView.selectRow(5)



def main():
    app = QApplication(sys.argv)
    window = MainWindow()
    app.exec()

Need advise on how to go forward. All the database I'm using is based on ID fields with I need to further query for whatever the reason. Maybe a tools change? A different approach.

Answer 1

QCombobox has two signals suitable for your needs: activated() is sent when the user chooses an item, even if the choice is not changed, see QComboBox.activated() currentIndexChanged() is sent if the current index is changed, either by users choice or programmatically, see QComboBox.currentIndexChanged()

Both signals pass the current index. Using this index you get the needed data by QSqlQuery.data().

Here a simple example using sqlite3, i think you can adapt it to your database:

import sqlite3
from PyQt5 import QtWidgets, QtSql

class MyWidget(QtWidgets.QWidget): 
    def __init__(self): 
        QtWidgets.QWidget.__init__(self) 

        self.database = QtSql.QSqlDatabase('QSQLITE')
        self.database.setDatabaseName('tc.db')
        self.database.open()

        self.dataModel = QtSql.QSqlQueryModel()
        self.dataModel.setQuery('select id, name from items', self.database)

        self.comboBox = QtWidgets.QComboBox(self)
        self.comboBox.setModel(self.dataModel)
        self.comboBox.setModelColumn(1)

        self.comboBox.currentIndexChanged.connect(self.do_action)
        # self.comboBox.activated.connect(self.do_action)

    def do_action(self, i):
        id = self.dataModel.data(self.dataModel.index(i, 0))        # self.dataModel.index(row, column)
        name = self.dataModel.data(self.dataModel.index(i, 1))
        print(i, 'id: ', id, 'name: ', name)

qApp = QtWidgets.QApplication([])    
widget = MyWidget() 
widget.show()
qApp.exec_()

here the dump of the database tc.db:

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "items" (
    "id" INTEGER PRIMARY KEY NOT NULL,
    "name" TEXT NOT NULL
);
INSERT INTO items VALUES(0,'abcde');
INSERT INTO items VALUES(1,'fghijk');
INSERT INTO items VALUES(2,'lmnop');
INSERT INTO items VALUES(3,'qrstuv');
COMMIT;