QSqlQuery::value: not positioned on a valid record

Question

After setting up a table model like this:

from PyQt4.QtSql import *
from PyQt4.QtCore import *
from PyQt4.QtGui import *
import sys


def createConnection(driverType, databaseName, hostName=None, userName=None, password=None):
    db = QSqlDatabase.addDatabase(driverType);
    db.setDatabaseName(databaseName)
    db.setHostName(hostName);
    db.setUserName(userName);
    db.setPassword(password)
    if (db.open() == False):
        QMessageBox.critical(None, "Database Error", db.lastError().text())
        return False
    return db


if __name__ == '__main__':

    app = QApplication(sys.argv)
    con = createConnection('QSQLITE', "testdatabase.db")

    qry = QSqlQuery()
    qry.setForwardOnly(True)

    qry.prepare("CREATE TABLE IF NOT EXISTS names (id INTEGER UNIQUE PRIMARY KEY, firstname VARCHAR(30), lastname VARCHAR(30))");
    if (qry.exec_() ==False):
        print(qry.lastError().text())
    else:
        print("Table createdFalse==");

    qry.prepare("INSERT INTO names (id, firstname, lastname) VALUES (1, 'John', 'Doe')");
    if (qry.exec_() ==False):
        print(qry.lastError().text())
    else:
        print("Inserted!");

    qry.prepare("INSERT INTO names (id, firstname, lastname) VALUES (2, 'Jane', 'Doe')");
    if (qry.exec_() ==False):
        print(qry.lastError().text())
    else:
        print("Inserted!");

    qry.prepare("INSERT INTO names (id, firstname, lastname) VALUES (3, 'James', 'Doe')");
    if (qry.exec_() ==False):
        print(qry.lastError().text())
    else:
        print("Inserted!");

    qry.prepare("INSERT INTO names (id, firstname, lastname) VALUES (4, 'Judy', 'Doe')");
    if (qry.exec_() ==False):
        print(qry.lastError().text())
    else:
        print("Inserted!");

    qry.prepare("INSERT INTO names (id, firstname, lastname) VALUES (5, 'Richard', 'Roe')");
    if (qry.exec_() ==False):
        print(qry.lastError().text())
    else:
        print("Inserted!");

    qry.prepare("INSERT INTO names (id, firstname, lastname) VALUES (6, 'Jane', 'Roe')");
    if (qry.exec_() ==False):
        print(qry.lastError().text())
    else:
        print("Inserted!");

    qry.prepare("INSERT INTO names (id, firstname, lastname) VALUES (7, 'John', 'Noakes')");
    if (qry.exec_() ==False):
        print(qry.lastError().text())
    else:
        print("Inserted!");

    qry.prepare("INSERT INTO names (id, firstname, lastname) VALUES (8, 'Donna', 'Doe')");
    if (qry.exec_() ==False):
        print(qry.lastError().text())
    else:
        print("Inserted!");

    qry.prepare("INSERT INTO names (id, firstname, lastname) VALUES (9, 'Ralph', 'Roe')");
    if (qry.exec_() ==False):
        print(qry.lastError().text())
    else:
        print("Inserted!");

    qry.prepare("SELECT id, firstname, lastname FROM names");
    if (qry.exec_() ==False):
        print(qry.lastError().text())
    else:
        print("SelectedFalse==");

    print(con.tables(), con.primaryIndex('names').name(), con.record('names'))  # #########################

    while (qry.next()):
#        print(qry.boundValues())
        country = qry.value(1)
        print(country)

    rec = qry.record()
    cols = rec.count();

    for c in range(cols):
        print("Column {}: {}" .format(c, rec.fieldName(c)))

    qry.prepare("SELECT firstname, lastname FROM names WHERE lastname = 'Roe'" );
    if (qry.exec_()==False ):
        print(qry.lastError().text())
    else:
        print("SelectedFalse==" );

    qry.prepare("INSERT INTO names (id, firstname, lastname) VALUES (:id, :firstname, :lastname)" );
    qry.bindValue(":id", 9 );
    qry.bindValue(":firstname", "Ralph" );
    qry.bindValue(":lastname", "Roe" );
    if(False==qry.exec_()):
        print(qry.lastError().text())
    else:
      print("Inserted!" );

    qry.prepare("CREATE TABLE IF NOT EXISTS salaries (id INTEGER UNIQUE PRIMARY KEY, annual INTEGER)" );
    if(False==qry.exec_()):
      print(qry.lastError())
    else:
      print("Inserted!" );

    qry.prepare("INSERT INTO salaries (id, annual) VALUES (1, 1000)" );
    if(False==qry.exec_()):
      print(qry.lastError())
    else:
      print("Inserted!" );

    qry.prepare("INSERT INTO salaries (id, annual) VALUES (2, 900)" );
    if(False==qry.exec_()):
      print(qry.lastError())
    else:
      print("Inserted!" );

    qry.prepare("INSERT INTO salaries (id, annual) VALUES (3, 900)" );
    if(False==qry.exec_()):
      print(qry.lastError())
    else:
      print("Inserted!" );

    qry.prepare("INSERT INTO salaries (id, annual) VALUES (5, 1100)" );
    if(False==qry.exec_()):
      print(qry.lastError())
    else:
      print("Inserted!" );

    qry.prepare("INSERT INTO salaries (id, annual) VALUES (6, 1000)" );
    if(False==qry.exec_()):
      print(qry.lastError())
    else:
      print("Inserted!" );

    qry.prepare("INSERT INTO salaries (id, annual) VALUES (8, 1200)" );
    if(False==qry.exec_()):
      print(qry.lastError())
    else:
      print("Inserted!" );

    qry.prepare("INSERT INTO salaries (id, annual) VALUES (9, 1200)" );
    if(False==qry.exec_()):
      print(qry.lastError())
    else:
      print("Inserted!" );

    qry.prepare("SELECT * FROM salaries" );
    if(False==qry.exec_()):
      print(qry.lastError())
    else:

      print("SelectedFalse==" );



#    con.close()

#    model = QSqlQueryModel();
#    model.setQuery( "SELECT firstname, lastname FROM names" );


    model = QSqlRelationalTableModel()
    model.setTable( "names" );
    model.setRelation( 0, QSqlRelation( "salaries", "id", "annual" ) );
    model.select();
#    model.setHeaderData( 0, Qt.Horizontal, ("Annual Pay") );
#    model.setHeaderData( 0, Qt.Horizontal, ("Annual Pay") );
#    model.setHeaderData( 1, Qt.Horizontal, ("First Name") );
#    model.setHeaderData( 2, Qt.Horizontal, ("Last Name") );
#    


#    model = QSqlTableModel()
    print(model.editStrategy() )
#    model.setTable( "names" );
#    model.setFilter( "lastname = 'Doe'" );
#    model.select();
#    model.removeColumn( 0 );

    view =  QTableView();
    view.setModel( model );
    view.show();

    sys.exit(app.exec_())

the content is displayed properly, but after I edit the data item then press enter ,this error occurred :

QSqlQuery::value: not positioned on a valid record

when I close the application and restart it again ,I found the editing I did last time is not stored into the database ,anyone can fix my problem here .

Note:I did not use

model->removeColumn(0)

here.

Answer 1

These lines are the offending:

qry.prepare("INSERT INTO names (id, firstname, lastname) VALUES (:id, :firstname, :lastname)" )
qry.bindValue(":id", 9 );
qry.bindValue(":firstname", "Ralph" );
qry.bindValue(":lastname", "Roe" );

You basically insert the same again what you inserted before:

qry.prepare("INSERT INTO names (id, firstname, lastname) VALUES (9, 'Ralph', 'Roe')");
if (qry.exec_() ==False):
    print(qry.lastError().text())
else:
    print("Inserted!");

The most interesting duplication is the id in here, and that is why you are getting the following error on the command line:

UNIQUE constraint failed: names.id Unable to fetch row

It is by design that it rejects the database entries with the same unique identifier, otherwise it would not quite be unique anymore, right?

The reasonable solution would be to increment the id 9 to 10 in your code as you seem to have reserved the id from 1-9. Once, I make the change to your code, it does not produce the aforementioned error anymore.

Please also note that you are calling the createConnection function without specifying the hostname will lead to python Type Error, such as: "TypeError: QSqlDatabase.setHostName(QString): argument 1 has unexpected type 'NoneType'".

You would probably need to handle that scenario in a more user friendly manner.