Get SQLite pragma table result into QSqlTableModel

Oak77

Hello,

how could we load table result of pragma <tableName> into a QSQLtableModel 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 with QTableView, I guess :-(, so I'd use QSQLtableModel if possible. I couldn't find any relevant info on this issue, could anyone point me or advise, please?

Christian Ehrlicher

You want the table schema? Then take a look at QSqlDatabase::record() - it returns all information you shown above.

Oak77

@Christian-Ehrlicher Hmm, that makes sense. I'll investigate it, thank you very much for suggestion.

Oak77

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)")

Christian Ehrlicher

SQLite only has some datatypes - see https://www.sqlite.org/datatype3.html
Are you sure smallint is mapped to QString? The rest looks fine

Oak77

@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 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 bool.

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.

Christian Ehrlicher

The pk can be treived via QSqlDatabase::primaryIndex(), notnull can be mapped to QSqlField::requiredStatus() and the default value with QSqlField::defaultValue()

Oak77

@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's precision for numeric fields and the length 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.