Retrieve date (INTEGER) from SQLite database and insert it to QDateEdit field

Question

I have a simple test form with 2 fields: Name, Birthdate. They both get values from an underlying SQLite database. The birthdate field in the database is of type INTEGER (UNIX time). I can get the name on the form alright but I cannot get the birthdate (I get 1/1/2000 instead). I suspect my problem is related with type conversion from INTEGER to QDate but I can't figure out how to fix it.

Below is the code for this simplified form version. I have included the bare minimum to recreate the problem. If you save this code in a directory as a .py file and run it from there it will create the database in that directory and show the form. I am using the View / Model approach and any answers would be especially appreciated if they could work within the existing code.

Thanks a lot in advance

import sys
import os.path
import sqlite3
from PyQt5 import QtWidgets as qtw
from PyQt5 import QtGui as qtg
from PyQt5 import QtCore as qtc
from PyQt5 import QtSql as qts


DIR_PATH = os.path.dirname(os.path.abspath(__file__))
DB_NAME = 'test.db'
target_db = os.path.join(DIR_PATH, DB_NAME)


 # create database 'test.db', add schema, then close it
cnn = sqlite3.connect(target_db)
c = cnn.cursor()
c.executescript("""
                            
                    DROP TABLE IF EXISTS clients;
                    CREATE TABLE clients
                    (
                        id INTEGER PRIMARY KEY,
                        name STRING,
                        birthdate INTEGER
                    );

                    INSERT INTO clients VALUES (1, 'Error Flynn', '12/30/1980');
                            
                """)
cnn.commit()
cnn.close()


class Model(qtc.QObject):

    connection_error = qtc.pyqtSignal(str)
    
    def start_db(self):

        connection_error = ''
        # create a db connection:
        self.db = qts.QSqlDatabase.addDatabase('QSQLITE')
        self.db.setDatabaseName(target_db)
        # test the connection for errors:    
        if not self.db.open():
            connection_error = f'{self.db.lastError().text()}'
            sys.exit(1)

        if connection_error:
            self.error.emit(connection_error)


    def create_model(self):

        self.clients_model = qts.QSqlRelationalTableModel()
        self.clients_model.setTable('clients')

        # query once the model to populate it
        self.clients_model.select()

        return self.clients_model


# create the Clients Form
class View(qtw.QWidget):

    error = qtc.pyqtSignal(str)
    
    def __init__(self, a_clients_model):

        super().__init__()
        self.setLayout(qtw.QFormLayout())
        
        # The 2 Client Fields
        
        self.client_name = qtw.QLineEdit()
        self.layout().addRow('Name: ', self.client_name)

        self.client_bdate = qtw.QDateEdit()
        self.layout().addRow('Birthdate: ', self.client_bdate)
        self.client_bdate.setCalendarPopup(True)
    

        # create a mapper and point it to the clients_model 
        self.this_clients_model = a_clients_model
        self.mapper = qtw.QDataWidgetMapper(self)
        self.mapper.setModel(self.this_clients_model)
        self.mapper.setItemDelegate(
            qts.QSqlRelationalDelegate(self))

        # map the Client Name field  
        self.mapper.addMapping(
            self.client_name,
            self.this_clients_model.fieldIndex('name')
        )

        # map the Client Birthdate field  
        self.mapper.addMapping(
            self.client_bdate,
            self.this_clients_model.fieldIndex('birthdate')
            # client_birthdate is declared INTEGER in the SQLite database,
            # to be converted to a QDateEdit object before it can be used!
        )

        # this will show the first record in the tbl_clients
        self.mapper.setCurrentIndex(0)
       
        self.show()


    # display error message originating in Model class
    def show_connection_error(self, error):
        qtw.QMessageBox.critical(
            None,
            'DB Connection Error',
            'Could not open database file: ' 
            f'{error}')
        sys.exit(1)


class MainWindow(qtw.QMainWindow):

    def __init__(self):
        """MainWindow constructor.
        """
        super().__init__()
        # Main UI code goes here

        self.stack = qtw.QStackedWidget()
        self.setCentralWidget(self.stack)

        self.model = Model()
        self.model.start_db()
               
        self.view = View(self.model.create_model())
        self.stack.addWidget(self.view)
        
        self.model.connection_error.connect(self.view.show_connection_error)

        # End main UI code
        self.show()


if __name__ == '__main__':
    app = qtw.QApplication(sys.argv)
    # it's required to save a reference to MainWindow.
    # if it goes out of scope, it will be destroyed.
    mw = MainWindow()
    sys.exit(app.exec())

Answer 1

The problem is that there is no conversion between the string "12/30/1980" and a default QDate. The solution in this case is to use a delegate to do that conversion. On the other hand I see it unnecessary to use QSqlRelationalDelegate as a delegate.

class Delegate(qtw.QItemDelegate):
    def setEditorData(self, editor, index):
        if isinstance(editor, qtw.QDateEdit):
            dt = qtc.QDate.fromString(index.data(), "MM/dd/yyyy")
            editor.setDate(dt)
        else:
            super().setEditorData(editor, index)

    def setModelData(self, editor, model, index):
        if isinstance(editor, qtw.QDateEdit):
            data = editor.date().toString("MM/dd/yyyy")
            model.setData(index, data)
        else:
            super().setModelData(editor, model, index)

# create a mapper and point it to the clients_model
self.this_clients_model = a_clients_model
self.mapper = qtw.QDataWidgetMapper(self)
self.mapper.setModel(self.this_clients_model)
# self.mapper.setItemDelegate(qts.QSqlRelationalDelegate(self))
self.mapper.setItemDelegate(Delegate(self))