How to convert string to date format in QDataWidgetMapper?

Question

Below is the example code:

import sys
from PyQt5 import QtCore, QtWidgets, QtSql, uic


class FilterProxyModel(QtCore.QSortFilterProxyModel):
    def __init__(self, parent=None):
        super().__init__(parent)
        self._filter_value = None

    @property
    def filter_value(self):
        return self._filter_value

    @filter_value.setter
    def filter_value(self, value):
        self._filter_value = value
        self.invalidateFilter()

    def filterAcceptsRow(self, sourceRow, sourceParent):
        if self.filter_value is None:
            return super().filterAcceptsRow(sourceRow, sourceParent)
        if self.filterKeyColumn() >= 0:
            value = (
                self.sourceModel()
                .index(sourceRow, self.filterKeyColumn(), sourceParent)
                .data(self.filterRole())
            )
            return value == self.filter_value

        for column in range(self.columnCount()):
            value = (
                self.sourceModel()
                .index(sourceRow, column, sourceParent)
                .data(self.filterRole())
            )
            if value == self.filter_value:
                return True
        return False

    def setFilterRegExp(self, filter):
        self.filter_value = None
        super().setFilterRegExp(filter)


class UI(QtWidgets.QMainWindow):
    def __init__(self):
        super(UI, self).__init__()
        uic.loadUi("tableview.ui", self)
        self.tableView.setSelectionBehavior(QtWidgets.QAbstractItemView.SelectRows)

        db = QtSql.QSqlDatabase.addDatabase("QSQLITE")
        db.setDatabaseName("book.db")
        db.open()

        self.model = QtSql.QSqlTableModel(self)
        self.model.setTable("card")
        self.model.select()

        self.proxy = FilterProxyModel(self)
        self.proxy.setSourceModel(self.model)

        self.tableView.setModel(self.proxy)
        self.model.select()

        self.edit.clicked.connect(self.edit_items)
        self.refresh.clicked.connect(self.refresh_table)

        r = self.model.record()
        column_names = [r.field(i).name().title() for i in range(r.count())]

        self.comboBox.addItems([x for x in column_names])

        self.horizontalHeader = self.tableView.horizontalHeader()
        self.horizontalHeader.sectionClicked.connect(
            self.tableView_horizontalHeader_sectionClicked
            )
        self.lineEdit.textChanged.connect(self.lineEdit_textChanged)

    def tableView_horizontalHeader_sectionClicked(self, logicalIndex):
        menu = QtWidgets.QMenu(self)

        values = []

        for row in range(self.model.rowCount()):
            value = self.model.index(row, logicalIndex).data(self.proxy.filterRole())
            values.append(value)

        action_all = QtWidgets.QAction("All", self)
        action_all.setData(None)
        menu.addAction(action_all)
        menu.addSeparator()

        for value in sorted(list(set(values))):
            action = QtWidgets.QAction(str(value), self)
            action.setData(value)
            menu.addAction(action)

        headerPos = self.tableView.mapToGlobal(self.horizontalHeader.pos())
        posY = headerPos.y() + self.horizontalHeader.height()
        posX = headerPos.x() + self.horizontalHeader.sectionPosition(logicalIndex)

        action = menu.exec_(QtCore.QPoint(posX, posY))

        if action is not None:
            self.proxy.setFilterKeyColumn(logicalIndex)
            self.proxy.filter_value = action.data()

    def lineEdit_textChanged(self):
        search = QtCore.QRegExp(
            self.lineEdit.text(), QtCore.Qt.CaseInsensitive, QtCore.QRegExp.RegExp
        )
        self.proxy.setFilterKeyColumn(self.comboBox.currentIndex())
        self.proxy.setFilterRegExp(search)

    def edit_items(self):

        if not self.model.rowCount():
            return
        index = self.tableView.currentIndex()
        if index.isValid():
            row = index.row()
        else:
            row = 0

        name_line = QtWidgets.QLineEdit(readOnly=True)
        age_edit = QtWidgets.QSpinBox()
        gender_combo = QtWidgets.QComboBox()
        genders = "M", "F"
        gender_combo.addItems(genders)

        date_of_birth = QtWidgets.QDateEdit()
        date_of_birth.setDisplayFormat("d-MMM-yyyy")
        
        updateButton = QtWidgets.QPushButton("Update")

        mapper = QtWidgets.QDataWidgetMapper()
        mapper.setSubmitPolicy(QtWidgets.QDataWidgetMapper.ManualSubmit)
        mapper.setModel(self.tableView.model())
        mapper.addMapping(name_line, 0)
        mapper.addMapping(age_edit, 1)
        mapper.addMapping(gender_combo, 2)
        mapper.addMapping(date_of_birth, 3)
        
        mapper.setCurrentIndex(row)

        dialog = QtWidgets.QDialog()
        dialog.setWindowTitle("Edit Window")

        layout = QtWidgets.QVBoxLayout(dialog)

        formLayout = QtWidgets.QFormLayout()
        layout.addLayout(formLayout)
        formLayout.addRow("Name", name_line)
        formLayout.addRow("Age", age_edit)
        formLayout.addRow("Gender", gender_combo)
        formLayout.addRow("Date of Birth", date_of_birth)
        
        layout.addWidget(updateButton)
        updateButton.clicked.connect(dialog.accept)

        if dialog.exec_():
            mapper.submit()

    def refresh_table(self):
        print("refresh")


def main():
    app = QtWidgets.QApplication(sys.argv)
    w = UI()
    w.show()
    sys.exit(app.exec_())


if __name__ == "__main__":
    main()

I am trying to edit row data from Qsqltablemodel using with QDataWidgetMapper(). The date column is in text format in My database table, I want to use date format as "d-MMM-yyyy". When i am trying to edit the row, the date column is setting the default date format as "1-1-2000". How to do this? Below is the example Image:

Answer 1

By default QtSql can handle string columns with a specific format such as QDate and QDateTime (as indicated in the sqlite docs), but in this case it does not comply with those formats so Qt does not know how to interpret them and displays them as text. So you must convert that text into QDate, and vice versa, using a delegate:

class ItemDelegate(QtWidgets.QItemDelegate):
    def setEditorData(self, editor, index):
        if index.column() == 3 and isinstance(editor, QtWidgets.QDateEdit):
            text = index.data()
            date = QtCore.QDate.fromString(text, "d-MMM-yyyy")
            editor.setDate(date)
            return
        super().setEditorData(editor, index)

    def setModelData(self, editor, model, index):
        if index.column() == 3 and isinstance(editor, QtWidgets.QDateEdit):
            text = editor.date().toString("d-MMM-yyyy")
            model.setData(index, text)
            return
        super().setModelData(editor, model, index)

# ...
mapper = QtWidgets.QDataWidgetMapper()
delegate = ItemDelegate(mapper)
mapper.setItemDelegate(delegate)
# ...