Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct
Get SQLite pragma table result into QSqlTableModel
how could we load table result of
pragma <tableName>into a
QSQLtableModelusing PyQt5/PySide2? The command generates table like:
cid name type notnull dflt_value pk ------------------------------------------------------- 0 ID INTEGER 1 1 1 EName VARCHAR (50) 1 0 2 IsGroup bit 1 1 0 ... ... ... ... ... ...
The usual way I use is something like this:
model.setTable(TableName) model.setEditStrategy(QtSql.QSqlTableModel.OnFieldChange) model.select() for i, ColName in enumerate(ColumnList): model.setHeaderData(i, QtCore.Qt.Horizontal, ColName)
But that won't work with
pragma <tableName>command. Obviously, there would be no editing. Do I have to use another model?
QSqlQueryModel? ...that would change the implementation with
QTableView, I guess :-(, so I'd use
QSQLtableModelif possible. I couldn't find any relevant info on this issue, could anyone point me or advise, please?
You want the table schema? Then take a look at QSqlDatabase::record() - it returns all information you shown above.
@Christian-Ehrlicher Hmm, that makes sense. I'll investigate it, thank you very much for suggestion.
OK, so I am studying the documentation (no harm, I'm learning) and playing whole day with the models and stuff and came as close as this:
self.modelFieldsTable = QtSql.QSqlTableModel() # create model # ... load data to model... (skipped) qr = QtSql.QSqlRecord() # create record qr = self.modelFieldsTable.record(0) # get first record QVariant.typeToName(qr.field(i).type()) # get type of i-th field and get its name
The issue I'm having is, that some data types are somehow "messed". I mean it's not probably an error, but how it works. Incorrect types:
DateTime --> QString Date --> QString Time --> QString Bit --> QString SmallInt --> QString
Int (int), BLOB (QByteArray), varchar (QString), numeric (double) are basically correct. I also didn't find a way to retrieve string (var/char) length. I wonder, did I miss anything?
I found a similar question and there was no solution.
If there's no way, I'll probably have to do it using:
query = QSqlQuery () query.exec_ ("PRAGMA table_info(tableName)")
SQLite only has some datatypes - see https://www.sqlite.org/datatype3.html
Are you sure smallint is mapped to QString? The rest looks fine
@Christian-Ehrlicher Ah, that is absolutely correct, thank you very much. Furthermore, I accidentally (by habbit) set the boolean field to
BITinstead of correct version in SQLite
BOOLEAN. SQLite permits that and it wasn't apparent until you pointed it out. Once, corrected, it's also correctly interpreted by Qt as
However, that leaves me with more issues. I need to be sure the data types are correct and I also need other database field properties. The proposed solution via
QSqlRecordwould be very elegant, because it would work with both testing & demo SQLite database and the production PostgreSQL database. But I'm not sure if it can be completed with the rest of field information.
@Christian-Ehrlicher Ah, yes, thank you for pointing out! I missed them, because I was primarily looking (in the QSqlField docs) for length (and misunderstood
lenghtpurpose for content string length). Now I see those properties are there and once looking again more carefully I can see there's
precisionfor numeric fields and the
lengthis really field length.
I'm pretty sure this will be the best solution and once I test it, I'll mark it solved. Thank you very much, Christian.