How do I filter queries from my database by Python? Using mysql

Question

I am making a system of "inventory", in it we have in the second screen a search screen and the third the screen that shows the results of the search. This search is a database search, but for now when I hit search it pops up all the records from the database, I wanted to know how to do a search filter. Follow the images and the code

[![telapesquisa][1]][1] [! [tellarearesultado][2]][2]

from PyQt5 import uic, QtWidgets, QtCore
from telamain import *
import mysql.connector

#Connect to mysql server
mydb = mysql.connector.connect(
    host="localhost", 
    user="root", 
    passwd="",
    database="inventario"
)

def chama_telapesquisa():
     
    nome_usuario = telamain.lineEdit.text() 
    senha = telamain.lineEdit_2.text()
    if nome_usuario == "user" and senha == "123" :
        telamain.close()
        telapesquisa.show()
    

def telapesquisa():
    idprodutosc = telapesquisa.lineEdit.text()
    categoriasc = telapesquisa.lineEdit_2.text()
    marcasc = telapesquisa.lineEdit_3.text()
    modelosc = telapesquisa.lineEdit_4.text()
    numseriesc = telapesquisa.lineEdit_5.text()
    localsc = telapesquisa.lineEdit_6.text()
  

def chama_telaresultados():

    
    telapesquisa.close()
    telaresultados.show() 



    cursor = mydb.cursor()
    comando_sql = "SELECT * FROM cpd_room"
    cursor.execute(comando_sql)
    dados_lidos = cursor.fetchall()

    telaresultados.tableWidget.setRowCount(len(dados_lidos))
    telaresultados.tableWidget.setColumnCount(8)

    for i in range(0, len(dados_lidos)):
        for j in range(0,8):
            telaresultados.tableWidget.setItem(i,j,QtWidgets.QTableWidgetItem(str(dados_lidos[i][j])))

def volta_telapesquisa():

    telaresultados.close()
    telapesquisa.show()

app=QtWidgets.QApplication([])
telamain=uic.loadUi("telamain.ui")
telapesquisa = uic.loadUi("telapesquisa.ui")
telaresultados = uic.loadUi("telaresultados.ui")
telapesquisa.pushButton.clicked.connect(chama_telaresultados)
telaresultados.pushButton.clicked.connect(volta_telapesquisa)
telamain.pushButton.clicked.connect(chama_telapesquisa)
telamain.lineEdit_2.setEchoMode(QtWidgets.QLineEdit.Password)


telamain.show()
app.exec()


  [1]: https://i.stack.imgur.com/A36Wh.png
  [2]: https://i.stack.imgur.com/UVkcZ.png

Answer 1

Here’s a possibility to use Python to search the database.

Note that your code makes use of command "SELECT * FROM cpd_room" that asks to return all data recorded in cdp_room. It is necessary to study a little more the sql language to be able to do more interesting things.

Given the great possibilities of answers, it is only this example for you to break your head a little in how it can improve your code, according to your needs.

def chama_telaresultados():

    idprodutosc = telapesquisa.lineEdit.text()
    telapesquisa.close()
    telaresultados.show() 



    cursor = mydb.cursor()
    comando_sql = (f"SELECT {idprodutosc} FROM cpd_room;")
    cursor.execute(comando_sql)
    dados_lidos = cursor.fetchall()