从QSqlQuery返回列名

Question

我正在尝试创建一个小程序，它应该显示一个基于sqlite表的表视图，其中存储日期。传递有效性时，单元格显示红色以表示有效性已过期。我有一个方法"get_date_diff“，它检索电子邮件和过期日期，并将它们存储在列表中，以便稍后使用这些变量作为批处理发送邮件的变量。例如:电子邮件(var)的证书(var)在日期(Var)已过期。我的if声明给了我过期的日期和listes_pilotes和mail_pilotes中的电子邮件，我需要在第三份清单中填写罪魁祸首证书:是在certificate1、certificate2栏中，还是两者都填写：

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

​

​

表视图所基于的DB：

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

        # ...

Answer 1

这样做的目的是通过将日期存储在列表中来获取日期并进行比较，如果该列表至少有一个项，则保存其他数据：

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_())