Nsryjdtyk

I am trying to create a small program which should display a tableview based on sqlite table where dates are stored. When a validity is passed the cell shows red to indicate the validity has expired. I have a method "get_date_diff" which retrieves the emails and dates expired and stores them in lists to later use those as variable for mails to be sent in batch i.e: Email(var) has the certificate (var) expired on date (var). My if statement gets me the expired dates and the emails in the listes_pilotes and mail_pilotes i need the third list to be filled with the culprit certificate : was it in column certificate1, certificate2 or possibly both:

def get_date_diff(self):

    '''method to retrieve from DB expired certificates '''

    query = QSqlQuery("SELECT pilot_1,certificate1,certificate2,pilot_mail FROM Pilots")

    liste_pilotes = 

    mail_pilotes = 

    #certificate_expired =  needs to be filled

    append_new = liste_pilotes.append

    append_new_mail = mail_pilotes.append

    while query_time.next():

        pilot_1 = query.value(0)

        date1 = query.value(1)

        date2 = query.value(2)

        pilot_mail = query.value(3)

        alter_certif1 = datetime.strptime(date1,"%Y-%m-%d")

        alter_certif2 = datetime.strptime(date2,"%Y-%m-%d")

        if alter_date1  < datetime.now() or alter_date2 < datetime.now():

            append_new(pilot_1)

            append_new_mail(pilot_mail)

    return liste_pilotes,mail_pilotes

the DB on which the tableview is based:

from PyQt5.QtSql import *



class LmtDataBase():

    def __init__(self):

        self.db = QSqlDatabase.addDatabase("QSQLITE")

        self.db.setDatabaseName("LmtPilots.db")



        self.db.open()



        query = QSqlQuery()

        query.exec_('''CREATE TABLE Pilots(id INTEGER PRIMARY KEY,pilot_1 TEXT, datetime1 TEXT, datetime2 TEXT,pilot_mail TEXT)''')



        self.db.commit()

        self.db.close()



        # ...

The idea is to get the dates and compare by storing them in a list, and if that list has at least one item save the other data:

from PyQt5 import QtCore, QtGui, QtWidgets, QtSql

import random

import string



def createConnection():

    db = QtSql.QSqlDatabase.addDatabase('QSQLITE')

    db.setDatabaseName(':memory:')

    if not db.open():

        return False



    query = QtSql.QSqlQuery()

    query.exec_('''CREATE TABLE Pilots(

        id INTEGER PRIMARY KEY, 

        pilot_1 TEXT, 

        certificate1 TEXT, 

        certificate2 TEXT,

        certificate3 TEXT,

        pilot_mail TEXT

        )''')



    for i in range(100):

        query.prepare("insert into Pilots values (?, ?, ?, ?, ?, ?)")

        query.addBindValue(i)

        query.addBindValue("".join(random.sample(string.ascii_letters, 15)))

        for j in range(3):

            days = random.randint(-10*365, 10*365)

            query.addBindValue(QtCore.QDate.currentDate().addDays(days))

        query.addBindValue("".join(random.sample(string.ascii_letters, 4) + ["@mail.com"]))

        if not query.exec_():

            print("error: ", query.lastError().text())

    return True



class DateDelegate(QtWidgets.QStyledItemDelegate):

    def initStyleOption(self, option, index):

        super(DateDelegate, self).initStyleOption(option, index)

        t = QtCore.QDate.fromString(index.data(), "yyyy-MM-dd")

        if t < QtCore.QDate.currentDate():

            option.backgroundBrush = QtGui.QBrush(QtGui.QColor("red"))







class MainWindow(QtWidgets.QMainWindow):

    def __init__(self, parent=None):

        super(MainWindow, self).__init__(parent)

        tableview = QtWidgets.QTableView()

        self.setCentralWidget(tableview)

        query = QtSql.QSqlQuery()

        query.exec_('''SELECT pilot_1, certificate1, certificate2, pilot_mail FROM Pilots''')

        model = QtSql.QSqlQueryModel(self)

        model.setQuery(query)

        tableview.setModel(model)

        for name in ('certificate1', 'certificate2'):

            ix = query.record().indexOf(name)

            delegate = DateDelegate(tableview)

            tableview.setItemDelegateForColumn(ix, delegate)

        print(self.get_date_diff())



    def get_date_diff(self):

        query = QtSql.QSqlQuery("SELECT pilot_1, certificate1, certificate2, pilot_mail FROM Pilots")

        rec = query.record()

        cols = [rec.indexOf(name) for name in  ("certificate1", "certificate2")]

        results = 

        while query.next():

            pilot_1 = query.value(rec.indexOf("pilot_1"))

            pilot_mail = query.value(rec.indexOf("pilot_mail"))

            dates = [QtCore.QDate.fromString(query.value(col), "yyyy-MM-dd") for col in cols]

            filter_columns = [col for col, date in zip(cols, dates) if date < QtCore.QDate.currentDate()]

            if filter_columns:

                v = [pilot_1, pilot_mail, filter_columns]

                results.append(v)

        return results



if __name__ == '__main__':

    import sys

    app = QtWidgets.QApplication(sys.argv)

    if not createConnection():

        sys.exit(-1)

    w = MainWindow()

    w.show()

    sys.exit(app.exec_())