Storing dates and other data types in database models

[JonB]

I have a nasty problem with what can and cannot be stored in model data. My issue arises partially because I have to use Python/PyQt, but I think this is a generic question applicable to C++ too. I really need/politely request a Qt data expert for the answers to the questions below.

My back-end database is MySQL, using QMYSQL driver. I have some columns which are dates. Unlike C++, Python does have a native type for dates, datetime.date. That is what I store/retrieve.

I have gotten used to the fact that in places to do with models I have to override data(), setData() & e.g. QSortFilterProxyModel.lessThan() to handle this. But I have now discovered that e.g. QSqlTableModel.submitAll() errors when trying to add/update rows with a datetime.date column. Presumably because it is willing to deal with QDate columns only.

This is going to be a major rewrite headache across tens of thousands of lines of code :( What am I asking for? I think what I need to know is:

1. Where in the documentation can I see a list of precisely what types the model<->QMYSQL accepts/works correctly with? Not a "hand-waving" answer, an actual documented list?

2. Where in the documentation is it stated/explained how the values I have stored in the model are fetched from the model in order to be passed to the QMYSQL driver? For example, I store my data via setData(index, value, Qt.EditRole). How is this data read by Qt/QMYSQL in order to pass onto the backend, e.g. when submitAll()? Is it done via data() with some role? Is it an internal call? Or what? What if I use QSqlTableModel::setRecord(), is that different, does that go via setData(role)? Any other ways? Does QSqlTableModel::record(row) go via data(role)?

3. I assume I cannot influence e.g. QSqlTableModel.submitAll() in how it fetches the data from my records?

4. Are we saying that while you can store your own data types in a model and use data()/setData/lessThan() to do some conversion if necessary, you must never do so if you intend to update the database from the model?

I think that's it for now. Really hoping for some explanation/help here, please! :)

[VRonin]

I can only reply on number 3:
You need to subclass QSqlTableModel and reimplement updateRowInTable/insertRowIntoTable. In there you need to iterate the fields of the record argument and convert those that are of type "date" to type string and store the ISO representation of the date (yyyy-MM-dd)

[JonB]

@VRonin
Hmm, that's interesting, I had assumed you wouldn't get access at that level.

The problem is that, quite understandably, updateRowInTable/insertRowIntoTable take a const QSqlRecord &values. Since I'm obviously not going to want to actually write SQL statements to do the update etc., I assume I'm going to have to: create a new, blank QSqlRecord and copy across the fields into it, changing where I find a Python datetime.date to a QDate, and possibly having to deal correctly with the "generated" flag on the columns in the new QSqlRecord. Then pass this temporary record on, assuming nothing cares it isn't actually in any table. Maybe this is all fine, but it's a bit more hairy than I had in mind... ! :(

[VRonin]

QSqlRecord has a copy constructor. In C++ (and using QDate) it would look something like:

class MySqlTableModel : public QSqlTableModel {
    Q_OBJECT
    Q_DISABLE_COPY(MySqlTableModel)
public:
    MySqlTableModel(QObject *parent = Q_NULLPTR, QSqlDatabase db = QSqlDatabase())
        :QSqlTableModel(parent,db)
    {}
protected:
    bool updateRowInTable(int row, const QSqlRecord &values){
        QSqlRecord newvalues(values);
        for(int i=0;i<newvalues.count();++i){
            const QVariant oldVariant = newvalues.value(i);
            if(oldVariant.userType() == QMetaType::QDate)
                newvalues.setValue(i, oldVariant.toDate().toString(Qt::ISODate));
        }
        return QSqlTableModel::updateRowInTable(row,newvalues);
    }
};

[JonB]

@VRonin
I do understand what you're saying. All I need to do, however, is: there are perfectly good Python (PyQt) functions for converting between Python datetime <-> C++/Qt QDate. I was simply intending to have newValues.setValue(pyDateToQDate(oldPyDateValue)), and then pass it onto the base updateRowInTable() so it's a QDate, and that does whatever it likes with it.

This is possible, but I'd have to consider any & all other methods in case they are affected. I was hoping not to have go down that route.

I should still appreciate any reader's further comments on the other 3 questions. If I can understand those, I will be in a position to understand the consequences for my possible solutions and decide accordingly. Thanks.

[VRonin]

@JonB said in Storing dates and other data types in database models:

so it's a QDate, and that does whatever it likes with it.

Some drivers don't support QDates either and you have to manually set it to string like I did