Storing dates and other data types in database models
-
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 adatetime.date
column. Presumably because it is willing to deal withQDate
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:
-
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? -
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 viasetData(index, value, Qt.EditRole)
. How is this data read by Qt/QMYSQL
in order to pass onto the backend, e.g. whensubmitAll()
? Is it done viadata()
with some role? Is it an internal call? Or what? What if I useQSqlTableModel::setRecord()
, is that different, does that go viasetData(role)
? Any other ways? DoesQSqlTableModel::record(row)
go viadata(role)
? -
I assume I cannot influence e.g.
QSqlTableModel.submitAll()
in how it fetches the data from my records? -
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! :)
-
-
I can only reply on number 3:
You need to subclassQSqlTableModel
and reimplementupdateRowInTable
/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) -
@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 aconst 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, blankQSqlRecord
and copy across the fields into it, changing where I find a Pythondatetime.date
to aQDate
, and possibly having to deal correctly with the "generated" flag on the columns in the newQSqlRecord
. 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... ! :( -
QSqlRecord
has a copy constructor. In C++ (and usingQDate
) 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); } };
-
@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 Pythondatetime
<-> C++/QtQDate
. I was simply intending to havenewValues.setValue(pyDateToQDate(oldPyDateValue))
, and then pass it onto the baseupdateRowInTable()
so it's aQDate
, 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.