[PyQt] QSqlTableModel doesn't know submitted records any more with PostgreSQL database

[Sibylle Koczian]

No answer up to now, why? Is this mailing list the wrong place? This 
might be a Qt problem, should I try the Qt forum?

Or did I leave out important information? If yes, what?

I'll put the code at the end of this mail in case there were problems 
with the attachment.

Am 11.02.2015 um 20:26 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 appliction 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.
>
> I've attached a small example which uses the following tables:
>
> CREATE TABLE states
> (
>    cc character(2) NOT NULL,
>    name character varying(50) NOT NULL,
>    CONSTRAINT states_pkey PRIMARY KEY (cc)
> )
>
> CREATE TABLE towns
> (
>    id serial NOT NULL,
>    name character varying(50) NOT NULL,
>    cc character(2) NOT NULL,
>    CONSTRAINT towns_pkey PRIMARY KEY (id),
>    CONSTRAINT towns_cc_fkey FOREIGN KEY (cc)
>        REFERENCES states (cc) MATCH SIMPLE
>        ON UPDATE NO ACTION ON DELETE NO ACTION,
>    CONSTRAINT towns_name_cc_key UNIQUE (name, cc)
> )
>
> Running the application on Windows 7, using Python 3.4.2, PyQt 5.4 gives
> the following output:
>
> Result using SQLite (5 states, 7 towns before starting):
>
> d:\home\...\pyqt5_sql>statoids_model_cons.py lite
> New state, example: HE Hessen: NI Niedersachsen
> DEBUG:__main__:Before submit, new record in row 6: Niedersachsen (NI)
> DEBUG:__main__:After submit, new record in row 6: Niedersachsen (NI)
> New town in NI: Düsseldorf
> DEBUG:__main__:Before submit, new record in row 8: Düsseldorf in NI, PK 0
> DEBUG:__main__:After submit, new record in row 8: Düsseldorf in NI, PK 9
> BW: Baden-Württemberg
> ...
> NI: Niedersachsen
> 1: Frankfurt in HE
> ...
> 9: Düsseldorf in NI
> DEBUG:__main__:Model data created and shown
>
> Everything as expected.
>
> Result using PostgreSQL 9.4 (1 state, 1 town before starting):
>
> d:\home\...\pyqt5_sql>statoids_model_cons.py pg
> New state, example: HE Hessen: NI Niedersachsen
> DEBUG:__main__:Before submit, new record in row 1: Niedersachsen (NI)
> DEBUG:__main__:After submit, new record in row 1: Niedersachsen (NI)
> New town in NI: Düsseldorf
> DEBUG:__main__:Before submit, new record in row 1: Düsseldorf in NI, PK 0
> DEBUG:__main__:After submit, new record in row 1: None in None, PK None ***
> HE: Hessen
> NI: Niedersachsen
> 1: Frankfurt in HE
> None: None in None ***
> DEBUG:__main__:Model data created and shown
>
> Lines with *** show the problem, I think. The new record gets into the
> database, but it can be important to see it in the application.
>
> What can I do? Can this be a bug in QPSQL?
>

Here is the code:

#!/usr/bin/env python3
# statoids_model_cons.py
# QTableView and QDataWidgetMapper
# Model for all parts of the application, using PostgreSQL

import os
import sys
import logging
from PyQt5 import QtCore
from PyQt5 import QtSql

modlog = logging.getLogger(__name__)

def createSQLiteConnection():
     filename = os.path.join(os.path.dirname(__file__), "statoids.db")
     db = QtSql.QSqlDatabase.addDatabase("QSQLITE")
     db.setDatabaseName(filename)
     ok = db.open()
     msg = "Database Error: {}".format(db.lastError().text())
     if ok:
         query = QtSql.QSqlQuery()
         okp = query.exec_("""PRAGMA foreign_keys = ON""")
         if not okp:
             modlog.error("Pragma: {}".format(db.lastError().text()))
     else:
         modlog.error(msg)
     return (ok, msg)

def createPGConnection():
     db = QtSql.QSqlDatabase.addDatabase("QPSQL")
     db.setDatabaseName("...")
     db.setHostName("localhost")
     db.setUserName("...")
     db.setPassword("...")
     ok = db.open()
     msg = "Database Error: {}".format(db.lastError().text())
     return (ok, msg)


class StatesModel(QtSql.QSqlTableModel):

     def __init__(self, parent=None):
         super().__init__(parent)
         self.setTable('states')
         self.setEditStrategy(QtSql.QSqlTableModel.OnRowChange)
         #self.setSort(1, QtCore.Qt.AscendingOrder)
         for (col, header) in enumerate(["CC", "Name"]):
             self.setHeaderData(col, QtCore.Qt.Horizontal, header)
         self.select()

     def addNew(self, newcc, newstate):
         zz = self.rowCount()
         self.insertRow(zz)
         self.setData(self.index(zz, 0), newcc)
         self.setData(self.index(zz, 1), newstate)
         modlog.debug("Before submit, new record in row %d: %s (%s)",
                      zz, self.data(self.index(zz, 1)),
                      self.data(self.index(zz, 0)))
         self.submit()
         modlog.debug("After submit, new record in row %d: %s (%s)",
                      zz, self.data(self.index(zz, 1)),
                      self.data(self.index(zz, 0)))

     def showData(self):
         for row in range(self.rowCount()):
             rec = self.record(row)
             print("{0}: {1}".format(rec.value(0), rec.value(1)))


class TownsModel(QtSql.QSqlTableModel):

     def __init__(self, parent=None):
         super().__init__(parent)
         self.setTable('towns')
         self.setEditStrategy(QtSql.QSqlTableModel.OnRowChange)
         #self.setSort(1, QtCore.Qt.AscendingOrder)
         for (col, header) in enumerate(["ID", "Name", "CC"]):
             self.setHeaderData(col, QtCore.Qt.Horizontal, header)
         self.select()

     def addNew(self, newcc, newtown):
         zz = self.rowCount()
         self.insertRow(zz)
         self.setData(self.index(zz, 1), newtown)
         self.setData(self.index(zz, 2), newcc)
         modlog.debug("Before submit, new record in row %d: %s in %s, PK 
%s",
                      zz, self.data(self.index(zz, 1)),
                      self.data(self.index(zz, 2)), 
self.data(self.index(zz, 0)))
         self.submit()
         modlog.debug("After submit, new record in row %d: %s in %s, PK 
%s",
                      zz, self.data(self.index(zz, 1)),
                      self.data(self.index(zz, 2)), 
self.data(self.index(zz, 0)))

     def showData(self):
         for row in range(self.rowCount()):
             rec = self.record(row)
             print("{0}: {1} in {2}".format(rec.value(0), rec.value(1),
                                            rec.value(2)))


def addData(statesmodel, townsmodel):
     newdata = input("New state, example: HE Hessen: ")
     (newcc, newstate) = newdata.split()
     statesmodel.addNew(newcc, newstate)
     newtown = input("New town in {}: ".format(newcc))
     townsmodel.addNew(newcc, newtown)
     statesmodel.showData()
     townsmodel.showData()

def choose_dbms(args):
     dbs_ok = False
     if args:
         dbs = args.pop()
         dbs_ok = dbs in ("pg", "lite")
     if not dbs_ok:
         dbs = input("Which DBMS (pg - PostgreSQL, lite - SQLite)? ")
     return (dbs, args)

if __name__ == "__main__":
     logging.basicConfig(level=logging.DEBUG)
     (dbs, args) = choose_dbms(sys.argv[1:])
     app = QtCore.QCoreApplication(args)
     (ok, msg) = (createPGConnection() if dbs == "pg"
                  else createSQLiteConnection())
     if ok:
         addData(StatesModel(), TownsModel())
         modlog.debug("Model data created and shown")
     else:
         modlog.error(msg)