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

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