Solved Get SQLite pragma table result into QSqlTableModel
-
Hello,
how could we load table result of
pragma <tableName>
into aQSQLtableModel
using 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 withQTableView
, I guess :-(, so I'd useQSQLtableModel
if 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
BIT
instead of correct version in SQLiteBOOLEAN
. SQLite permits that and it wasn't apparent until you pointed it out. Once, corrected, it's also correctly interpreted by Qt asbool
.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
QSqlRecord
would 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. -
The pk can be treived via QSqlDatabase::primaryIndex(), notnull can be mapped to QSqlField::requiredStatus() and the default value with QSqlField::defaultValue()
-
@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
lenght
purpose for content string length). Now I see those properties are there and once looking again more carefully I can see there'sprecision
for numeric fields and thelength
is 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.