CODEWITHSUNDEEP
pythonqtsqlitepyqtqtableview
Jeremy Scott
Jeremy Scott

Reputation: 177

PYQT: QTableView. Filter Between Dates

I've created a GUI that allows users to modify, filter update and delete from a sqlite database. Everything is working great except the date range filter. When I run the code, I don't get an error, I only get a blank filtered screen with no data. Does anyone see what's wrong with my code? [Section 1a, subgroup{v} is what I am seeking help on]. Thanks!

from PyQt4 import QtCore, QtGui, QtSql
import sys  
import sqlite3
import time
import Search  #imported ui.py MainWindow file
import os 
try:
    from PyQt4.QtCore import QString
except ImportError:
    QString = str

class TableEditor(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)

        current = QtCore.QDateTime.currentDateTime()
        self.startDate.setDate(current.date())
        self.endDate.setDate(current.date())
        self.startDate.setDisplayFormat("M/dd/yyyy")
        self.endDate.setDisplayFormat("M/dd/yyyy")


# 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)

    # {iii} Search Fields Button Emitted:
    # [1]
        self.search_MRN.selectionChanged.connect(self.search_MRN_Edit)
    # [2]
        self.search_Lname.selectionChanged.connect(self.search_Lname_Edit)
    # [3]
        self.search_Unit.selectionChanged.connect(self.search_Unit_Edit)
    # {iv} Search Fields Button Emitted:
    # [1]
        self.search_MRN.returnPressed.connect(self.search_MRN_Enter)
    # [2]
        self.search_Lname.returnPressed.connect(self.search_Lname_Enter)
    # [3]
        self.search_Unit.returnPressed.connect(self.search_Unit_Enter)

    #{v} Search Between 2 Dates
        self.btnSubmit.clicked.connect(self.FilterBetweenDates)

# Section 1a: Slots from Section 1. 

    #{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 like'" +self.search_MRN.text()+ "%%'")

            #self.model.setFilter("MRN = '%s'" % text)
    #[2]
    def search_Lname_FilterRecord(self):
        text = self.search_Lname.text()
        if len(text) == 0:
            self.model.setFilter("")
        else:
            self.model.setFilter("Surname like'" +self.search_Lname.text()+ "%'")
            #self.model.setFilter("Surname = '%s'" % text) #This line of code will only pull exact matches.
    #[3]
    def search_Unit_FilterRecord(self):
        text = self.search_Unit.text()
        if len(text) == 0:
            self.model.setFilter("")
        else:
            self.model.setFilter("Unit like'" +self.search_Unit.text()+ "%'")
            #self.model.setFilter("Unit = '%s'" % text) #This line of code will only pull exact matches.

    #{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")

    #{iii} Search Text Edited Slots:
    #[1]    
    def search_MRN_Edit(self):
        self.search_MRN.setText("")
    #[2]
    def search_Lname_Edit(self):
        self.search_Lname.setText("") 
    #[3]      
    def search_Unit_Edit(self):
        self.search_Unit.setText("")

    #{iv} Search Text Return Pressed (Enter) Slots:
    #[1]    
    def search_MRN_Enter(self):
        self.search_MRN_FilterRecord()
    #[2]
    def search_Lname_Enter(self):
        self.search_Lname_FilterRecord()
    #[3]      
    def search_Unit_Enter(self):
        self.search_Unit_FilterRecord()

    #{v} Filter Between Dates, Slot:
    def FilterBetweenDates(self):
        start = str(self.startDate.text())
        finish = str(self.endDate.text())
        self.model.setFilter("EventDate BETWEEN'" + start and finish)

    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 = TableEditor('CAUTI')
    editor.show()
    app.exec_()  


if __name__ == '__main__':

Upvotes: 1

Views: 1655

Answers (1)

titusjan
titusjan

Reputation: 5546

The and in your filter text should part of the filter string. Now you are taking the logical-and of two strings (which will be the last string unless the first is empty) and append that to the filter string.

So change 

self.model.setFilter("EventDate BETWEEN'" + start and finish)

to

filter = "EventDate BETWEEN '{}' AND '{}'".format(start, finish)
print(filter) # for debugging
self.model.setFilter(filter)

During debugging, it's probably a good idea to print the filter string and try it out in an SQL query (directly in the sqlite3 tool) so that you can see it works.

Upvotes: 1

Related Questions