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



  • 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?


  • Lifetime Qt Champion

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

  • Lifetime Qt Champion

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


  • Lifetime Qt Champion

    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'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.


Log in to reply