QSqlTableModel.setTable() doesn't work with PostgreSQL 12

skoczian

After upgrading to PostgreSQL 12 I can't use QSqlTableModel any more with PostgreSQL. While QSqlQueries work as expected, creating a usable QSqlTableModel fails:

def create_stamm_model():
    model = QtSql.QSqlTableModel()
    logging.debug("Message by model: %s", model.lastError().text())
    db = model.database()
    logging.debug("Data base tables: %s", db.tables())  # <-- correct answer
    model.setTable("test_table")  # <-- name is contained in the list from db.tables()
    logging.debug("Message by model.setTable: %s",
                  model.lastError().text())  # <-- error, see below
...

output:

DEBUG:root:Message by model:  
DEBUG:root:Data base tables: ['t_manip', 't_spiel', 't_stamm', 't_ewtab', 't_pps', 't_tab']
DEBUG:root:Message by model.setTable:  Unable to find table t_ewtab

I'm using PyQt5, but this looks more like a Qt bug, I think. I found the very similar QTBUG-79033, but that talks about model.select() failing, while setTable() fails even earlier.

Denni 0

Okay I do not quite understand you are using PostgreSQL to handle your database -- but you are importing entire tables into your code? Can I ask why if that is what you are doing?

skoczian

As far as I know the Qt model classes don't necessarily import all the data they work with into the application and I doubt the QSqlTableModel does.

But that's definitely not the point here. The point is that this class doesn't work with the current version of PostgreSQL - and that the first thing that goes wrong isn't the select() method as described in the bug report I cited, but the setTable() method which must be called first. And if you don't pepper your code with logging statements or the like it goes wrong silently. You just wonder about your completely empty QTableView components.

I know that the QtSql classes are not the only way to use databases with a Qt (or PyQt) GUI. But, again, that can't mean they don't need to work correctly.

Denni 0

Well I can concur with your synopsis on the aspect of a module needing to work correctly but my statement was more of a you have no need to use it since the entire database connectivity ought to be done in just pure python without the use of any pyqt at all since pyqt is all about rendering the View (GUI) and that should be completely divorced from working with the Model (Data Source in this case Database) if you merge the two you are taking a major step backwards rather than any kind of step forward.

Of course I have seen this same approach used over and over again -- and in ever case it eventually fails for one reason or another. Then folks dive into how to make a better broken machine rather than use a better machine (as its already out there) and it only gets worse. So from my perspective it is best not to even dabble in it since to me its a bad design to begin with and I leave it at that.

SGaist

Hi,

Based on the but report, you would need to build the PostgreSQL plugin yourself with the patch applied (taking into account the remark about breaking the backward compatibility) with the Qt version using for the release of PyQt5 you are using.

Christian Ehrlicher

The best is to not upgrade to PostgreSQL 12 until the fix is in - or do you really need any feature from PostgreSQL 12?

skoczian

I don't see why using QtSql should destroy the separation of GUI and business logic. The controls in the GUI get their data from a QAbstractItemModel subclass. They don't need to know what sort of a model that is: QSqlQueryModel or one of its subclasses or a custom model working with data from a Python database module.

Of course, using QStandardItemModel or a custom model not even the model needs to know about the origin of the data. It could be imported to the model and exported from it in the form of a list of tuples, namedtuples, dicts or whatever other pure Python data structure suggests itself.

I think I will look first if psycopg2 works normally with PostgreSQL 12. If yes, I might change my application to use it. If not, perhaps really return to version 11 for a time.

Denni 0

@skoczian if you read the documentation on Qt concerning their QModels you will find that they come right out and say they are tying the Model to the View by combining the Controller (aka the actual separator) into the View so instead of M to C to V you have just M to V so this does in fact destroy that separation

What I do not understand is the lazy mentality that is required to use a tool such as the QModel to tie the View directly to the database especially when coding a pure python class to handle all Database connectivity is so just pathetically simple to render that doing otherwise just does not make any sense. The benefits to doing so are profoundly better -- basically coding that database class in python is a win-win scenario and not doing so is a lose-lose scenario And even worse doing this with a QModel is not even as easy as coding it in pure python it seems

terma.aba

This is giving us a problem, part of our system uses the ability of QSqlTableModel to tell us the columns in a table which we match with properties of objects we are saving. This problem means our customers can't upgrade to PSQL 12. It worked up until PSQL 11.

Unsolved QSqlTableModel.setTable() doesn't work with PostgreSQL 12