How to double-click on a row in a table to search from another table?

Question

Python

Good afternoon.
Can't do a search when double clicking on a row. In Qt Designer, I drew a window with table tableView_1. This table displays data from the database from Table 1.
Table 1 (dbtable_1):
name | quantity | id
Potatoes | 10 | A1
Cucumbers | 5 | A2
There is also Table 2 in the database (dbtable_2):
producer | city ​​| id
Farmer 1 | Moscow | A1
Farmer 2 | St. Petersburg | A2
TableView_1 is set to SelectRows for the selectionBehavior property. When you double-click on a row, the following happens:

def result(mod_inx):
    #Модальное окно
    modal_window = QtWidgets.QWidget(main_window, QtCore.Qt.Window)
    modal_window.setWindowTitle("Модальное окно")
    modal_window.setMinimumSize(700, 500)
    modal_window.setWindowModality(QtCore.Qt.WindowModal)
    modal_window.setAttribute(QtCore.Qt.WA_DeleteOnClose, True)

    #Подключение к БД
    DB_connect = QtSql.QSqlDatabase.addDatabase("QSQLITE")
    DB_connect.setDatabaseName("test.db")
    DB_connect.open()

    #Запрос к БД: из таблицы dbtable_2 выводим все строки
    result_model = QtSql.QSqlQueryModel(parent = None)
    result_model.setQuery("SELECT producer, city, id FROM dbtable_2")

    #Модель
    vbox = QtWidgets.QVBoxLayout()
    table_result = QtWidgets.QTableView()
    table_result.setModel(result_model)
    vbox.addWidget(table_result)
    table_result.resizeColumnsToContents()
    table_result.horizontalHeader().setStretchLastSection(True)
    modal_window.setLayout(vbox)
    modal_window.show()

    #Значения строки, столбца
    row = mod_inx.row()
    column = mod_inx.column()

    #Значение id из dbtable_2
    id_search =  main_window.tableView_1.model().index(row, 2).data()

In this case, double-clicking on a row opens a window with a table in which all rows from dbtable_2 are displayed, and we also get the id value of this row. I want to make sure that when we double-click on a row, we take the id value of this row and in the window display records from dbtable_2 only with this id. Is there any way to do this via "WHERE id LIKE(?)" or "WHERE id = ..."?

Answer 1

1) #The id value from dbtable_2 needs to be figured out before you call it.

#Значения строки, столбца
    row = mod_inx.row()
    column = mod_inx.column()

    #Значение id из dbtable_2
    id_search =  main_window.tableView_1.model().index(row, 2).data()

you need to put it at the beginning of the function and not at the end ...
2) the request is a text, so use the operators as for the text:

query = "SELECT * FROM dbtable_2 WHERE id = " + str(id_search)
result_model.setQuery(query)

3) I suggest using peewee to read and write sqlite tables (there are examples on Habré). It's fast and convenient.