[PyQt] QSqlTableModel, PostgreSQL: Model forgets data after submitting

[Sibylle Koczian]

What is wrong with this question?

Am 17.08.2015 um 17:41 schrieb Sibylle Koczian:
> Hello,
>
> when I insert data into a PostgreSQL database table using a
> QSqlTableModel they are written to the database, but I can't see them in
> the application after submitting. This happens with tables whose primary
> key has type SERIAL, it doesn't happen if the primary key is inserted by
> hand.
>
> This doesn't happen with SQLite databases and it doesn't happen if I use
> PyQt4. That's why I think it might be a bug in QPSQL. If that's true,
> what should I do? I've next to no knowledge of C++, so I don't think I
> could do a proper bug report.
>
> After calling model.select() the data are visible again, but the
> selection in a QTableView or similar is lost.
>
I've asked about this some months before in this list and then, because 
I didn't get any answer, on Stackoverflow. No answer there as well. I've 
shortened the example, but that doesn't seem to help.

Isn't this list the right place for the question? Is using 
QSqlTableModel not the right way to write database applications with 
PyQt5? If it's that, what would be better?

I've looked at the Qt bug tracker but didn't find anything resembling my 
problem.

What can I do to resolve this? Go back to PyQt4?

> I've attached a small example which uses the following table:
>
> CREATE TABLE person (idp SERIAL PRIMARY KEY, fullname VARCHAR(50))
>
> This should run (after adapting createPGConnection, of course) and show
> the problem. The table can be created by the script (uncomment the call
> to createTable() in main()).
>
> #!/usr/bin/env python3
> # newrecords_test.py
> # Adding new records to a QSqlTableModel connected to a PostgreSQL
> database.
>
> import os
> import sys
> import logging
> from PyQt5 import QtCore
> from PyQt5 import QtSql
>
> modlog = logging.getLogger(__name__)
>
> def createPGConnection():
>      db = QtSql.QSqlDatabase.addDatabase("QPSQL")
>      db.setDatabaseName("xxx")
>      db.setHostName("localhost")
>      db.setUserName("xxx")
>      db.setPassword("xxx")
>      return db
>
> def createTable():
>      createstmt = """CREATE TABLE person (idp SERIAL PRIMARY KEY,
>      fullname VARCHAR(50))"""
>      query = QtSql.QSqlQuery()
>      ok = query.exec_(createstmt)
>      if ok:
>          modlog.debug("Successfully created table 'person'")
>      else:
>          modlog.error(query.lastError().text())
>
> def setupModel(tablename):
>      model = QtSql.QSqlTableModel()
>      model.setTable(tablename)
>      model.setEditStrategy(QtSql.QSqlTableModel.OnRowChange)
>      model.setHeaderData(0, QtCore.Qt.Horizontal, "ID")
>      model.setHeaderData(1, QtCore.Qt.Horizontal, "Full name")
>      model.select()
>      return model
>
> def addNewName(newname, model):
>      zz = model.rowCount()
>      ok = model.insertRow(zz)
>      if not ok:
>          modlog.error(model.lastError().text())
>      ok = model.setData(model.index(zz, 1), newname)
>      if not ok:
>          modlog.error(model.lastError().text())
>      return zz
>
> def showRow(msg, row, model):
>      print("{}, new record in row {}: {} ({})".format(msg, row,
>          model.data(model.index(row, 1)),
>          model.data(model.index(row, 0))))
>
> def main():
>      personModel = setupModel("person")
>      row = addNewName("John B. Doe", personModel)
>      showRow("Before submit", row, personModel)
>      ok = personModel.submit()
>      if not ok:
>          modlog.error(personModel.lastError().text())
>      showRow("After submit", row, personModel)
>      personModel.select()
>      showRow("After select", row, personModel)
>
> if __name__ == '__main__':
>      logging.basicConfig(level=logging.DEBUG)
>      app = QtCore.QCoreApplication(sys.argv[1:])
>      db = createPGConnection()
>      ok = db.open()
>      if not ok:
>          modlog.error(db.lastError().text())
>          sys.exit(1)
>      # if desired, uncomment for first run:
>      # createTable()
>      main()
>
> If I run this, using Windows 7, 64 bit, with Python 3.4.3 and PyQt 5.5,
> I get this output:
>
> DEBUG:__main__:Before submit, new record in row 2: John B. Doe (0)
> DEBUG:__main__:After submit, new record in row 2: None (None)   <---- ???
> DEBUG:__main__:After select, new record in row 2: John B. Doe (3)
>