PYQT: QSqlTableModel. How to filter numbers instead of Text

Question

I'm developing a GUI which allows nurses and doctors to view or patients records from a sqlite database. I have been able to design a way to filter the rows using the setFilter fuction. However, here is my dilemma: The discrete fields which search characters data work perfect; however, when I take this same approach using the patient's MRN (id number), it returns a blank screen. Here is my code, [See Section 1a, [#1] below:

from PyQt4 import QtCore, QtGui, QtSql
import sys  
import sqlite3
import time
import Search  #Imported ui.py file
import os 

try:
    from PyQt4.QtCore import QString
except ImportError:
    QString = str

class SearchDatabase(QtGui.QMainWindow, Search.Search_MainWindow):
    def __init__(self, tableName, parent=None):
        super(self.__class__, self).__init__()
        self.setupUi(self)  

        self.model = QtSql.QSqlTableModel(self)
        self.model.setTable('CAUTI')
        self.model.setEditStrategy(QtSql.QSqlTableModel.OnManualSubmit)
        self.model.select()

        self.model.setHeaderData(0, QtCore.Qt.Horizontal, "MRN")
        self.model.setHeaderData(1, QtCore.Qt.Horizontal, "Last Name")
        self.model.setHeaderData(2, QtCore.Qt.Horizontal, "First Name")
        self.model.setHeaderData(3, QtCore.Qt.Horizontal, "Date of Event")
        self.model.setHeaderData(4, QtCore.Qt.Horizontal, "Facility")
        self.model.setHeaderData(5, QtCore.Qt.Horizontal, "Unit")
        self.model.setHeaderData(6, QtCore.Qt.Horizontal, "User")
        self.tableView.setModel(self.model)
        self.setWindowTitle("HAI Table")
        self.tableView.setColumnWidth(0,100)
        self.tableView.setColumnWidth(1,100)
        self.tableView.setColumnWidth(2,100)
        self.tableView.setColumnWidth(3,100)
        self.tableView.setColumnWidth(4,100)
        self.tableView.setColumnWidth(5,100)
        self.tableView.setColumnWidth(6,83)
        self.submitButton.clicked.connect(self.submit)
        self.revertButton.clicked.connect(self.model.revertAll)
        self.quitButton.clicked.connect(self.close)

# Section 1: Signals 

    # {i} Search Fields Button Emitted:
    # [1]
        self.search_MRN_Button.clicked.connect(self.search_MRN_FilterRecord)
    # [2]
        self.search_Lname_Button.clicked.connect(self.search_Lname_FilterRecord)
    # [3]
        self.search_Unit_Button.clicked.connect(self.search_Unit_FilterRecord)

    # {ii} Search Clear Buttons Emitted: 
    # [1]
        self.search_MRN_CancelButton.clicked.connect(self.search_MRN_CancelButton_Clicked)
    # [2]
        self.search_Lname_CancelButton.clicked.connect(self.search_Lname_CancelButton_Clicked)
    # [3]
        self.search_Unit_CancelButton.clicked.connect(self.search_Unit_CancelButton_Clicked)

# Section 1a: Slots from Section 1 [Start Line: 47]

    #{i} Search Field Button Slots:
    #[1]
    def search_MRN_FilterRecord(self):
        text = self.search_MRN.text()
        if len(text) == 0:
            self.model.setFilter("")
        else:
            self.model.setFilter("MRN = '%f'" + text)
    #[2]
    def search_Lname_FilterRecord(self):
        text = self.search_Lname.text()
        if len(text) == 0:
            self.model.setFilter("")
        else:
            self.model.setFilter("Surname = '%s'" % text)
    #[3]
    def search_Unit_FilterRecord(self):
        text = self.search_Unit.text()
        if len(text) == 0:
            self.model.setFilter("")
        else:
            self.model.setFilter("Unit = '%s'" % text)

    #{ii} Search Field Cancel Button Slots:
    #[1]
    def search_MRN_CancelButton_Clicked(self):
        self.model.setFilter("")
        self.search_MRN.setText("MRN Search")
    #[2]
    def search_Lname_CancelButton_Clicked(self):
        self.model.setFilter("")
        self.search_Lname.setText("Last Name Search")
    #[3]
    def search_Unit_CancelButton_Clicked(self):
        self.model.setFilter("")
        self.search_Unit.setText("Unit Search")



    def submit(self):
        self.model.database().transaction()
        if self.model.submitAll():
            self.model.database().commit()
        else:
            self.model.database().rollback()
            QtGui.QMessageBox.warning(self, "HAI Table",
                        "The database reported an error: %s" % self.model.lastError().text())

def main():
    app = QtGui.QApplication(sys.argv) 
    #app.setStyle( "Plastique" )
    db = QtSql.QSqlDatabase.addDatabase('QSQLITE')
    db.setDatabaseName('HAI.db')

    editor = SearchDatabase('CAUTI')
    editor.show()
    app.exec_()  


if __name__ == '__main__':  
    main()  # run the main function

I have already tried the following methods:

self.model.setFilter("MRN = '%f'" + str(text))
self.model.setFilter("MRN = '%f'" + text)
self.model.setFilter(str("MRN = '%f'" % text))
self.model.setFilter(str("MRN = '%s'" % text))
self.model.setFilter((("MRN = '%s'" %) + str(text)))
self.model.setFilter(("MRN = '%s'" + str(text))

What am I doing wrong? Please advise.

score 2 · Answer 1 · answered Jan 28 '17 at 02:37

2

It took me forever to figure this out! But here is the answer.

self.model.setFilter("MRN like'" +self.search_MRN.text()+ "%%'")

answered Jan 28 '17 at 02:37

Jeremy Scott

167
1
3
15

PYQT: QSqlTableModel. How to filter numbers instead of Text

1 Answers1