QSqlRelationalTableModel and Sqlite

[viandante]

Hi,

I am trying to create a small database with 2 related table. I've been able to show the tables, but I can't manage to edit them.
How do you edit in a QSqlRelationalTableModel?

Here is the code:

import os
import sys
from PyQt4.QtCore import *
from PyQt4.QtGui import *
from PyQt4.QtSql import *
import form,  form2
 
#pyuic4 window.ui -o windowUi.py
 
class MainForm(QDialog):
 
    def __init__(self):
        super(MainForm, self).__init__()
 
        self.memberModel=QSqlRelationalTableModel(self)
        self.memberModel.setEditStrategy(self.memberModel.OnRowChange)
        self.memberModel.setTable("members")
        self.memberModel.select()
 
        self.form=form.Ui_Dialog()
        self.form.setupUi(self)
 
        self.form.tableView.setModel(self.memberModel)
 
        self.paymentModel=QSqlRelationalTableModel(self)
        self.paymentModel.setTable("payment")
        self.paymentModel.setRelation(1,
                QSqlRelation("members", "id", "name"))
        self.paymentModel.select()
 
        self.form.tableView_2.setModel(self.paymentModel)
 
        #Signals
        self.connect(self.form.tableView.selectionModel(),
                SIGNAL("currentRowChanged(QModelIndex,QModelIndex)"),
                self.assetChanged)
        self.connect(self.form.AddPayment, SIGNAL("clicked()"),
                self.addAction)
 
    def assetChanged(self, index):
        if index.isValid():
            record = self.memberModel.record(index.row())
            id = record.value("id").toInt()[0]
            self.paymentModel.setFilter(QString("membersID = %1").arg(id))
        else:
            self.paymentModel.setFilter("membersID = -1")
        self.paymentModel.reset() # workaround for Qt <= 4.3.3/SQLite bug
        self.paymentModel.select()
        self.form.tableView_2.horizontalHeader().setVisible(
                self.paymentModel.rowCount() > 0)
        #if PYQT_VERSION_STR < "4.1.0":
           # self.form.tableView_2.setColumnHidden(ID, True)
            #self.form.tableView_2.setColumnHidden(ASSETID, True)
 
    def addAction(self):
        form = form2.Ui_Dlg_addPayment()
        form.show()
        """
        index = self.form.tableView.currentIndex()
        if not index.isValid():
            return
        QSqlDatabase.database().transaction()
        record = self.memberModel.record(index.row())
        memberID = record.value(0).toInt()[0]
        print memberID
        row = self.paymentModel.rowCount()
        self.paymentModel.insertRow(row)
        self.paymentModel.setData(self.paymentModel.index(row, memberID ),
                              QVariant(memberID))
        self.paymentModel.setData(self.paymentModel.index(row, 2),
                              QVariant(QDate.currentDate()))
        QSqlDatabase.database().commit()
        """
 
 
def createFakeData():
    import random
    query = QSqlQuery()
    QApplication.processEvents()
 
    print "Creating tables..."
    query.exec_("""CREATE TABLE members (
                id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,
                name TEXT NOT NULL,
                surname TEXT NOT NULL,
                mem_notes)""")
    query.exec_("""CREATE TABLE payment (
                id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,
                membersID INTEGER NOT NULL,
                date TEXT NOT NULL,
                amount TEXT NOT NULL,
                pay_notes TEXT,
                FOREIGN KEY (membersID) REFERENCES members)""")
    print "Populating tables..."
    query.exec_("INSERT INTO members (name, surname) "
                "VALUES ('Giovanni', 'Sticazzi')")
    var = QVariant("id")
    print var
    query.prepare("INSERT INTO payment (membersID, date, amount) "
                "VALUES (:membersID, '17', 'Prova')")
    query.bindValue(":membersID", "123")
    query.exec_()
 
def main():
    app = QApplication(sys.argv)
 
    filename = os.path.join(os.path.dirname(__file__), "assets.db")
    create = not QFile.exists(filename)
    db = QSqlDatabase.addDatabase("QSQLITE")
    db.setDatabaseName(filename)
    if not db.open():
        QMessageBox.warning(None, "Asset Manager",
            QString("Database Error: %1").arg(db.lastError().text()))
        sys.exit(1)
 
    splash = None
    if create:
        app.setOverrideCursor(QCursor(Qt.WaitCursor))
        splash = QLabel()
        pixmap = QPixmap(":/assetmanagersplash.png")
        splash.setPixmap(pixmap)
        splash.setMask(pixmap.createHeuristicMask())
        splash.setWindowFlags(Qt.SplashScreen)
        rect = app.desktop().availableGeometry()
        splash.move((rect.width() - pixmap.width()) / 2,
                    (rect.height() - pixmap.height()) / 2)
        splash.show()
        app.processEvents()
        createFakeData()
 
    form = MainForm()
    form.show()
    if create:
        splash.close()
        app.processEvents()
        app.restoreOverrideCursor()
    app.exec_()
    del form
    del db
 
 
main()

import os
import sys
from PyQt4.QtCore import *
from PyQt4.QtGui import *
from PyQt4.QtSql import *
import form,  form2

#pyuic4 window.ui -o windowUi.py

class MainForm(QDialog):

    def __init__(self):
        super(MainForm, self).__init__()
        
        self.memberModel=QSqlRelationalTableModel(self)
        self.memberModel.setEditStrategy(self.memberModel.OnRowChange)
        self.memberModel.setTable("members")
        self.memberModel.select()
        
        self.form=form.Ui_Dialog()
        self.form.setupUi(self)
        
        self.form.tableView.setModel(self.memberModel)
        
        self.paymentModel=QSqlRelationalTableModel(self)
        self.paymentModel.setTable("payment")
        self.paymentModel.setRelation(1,
                QSqlRelation("members", "id", "name"))
        self.paymentModel.select()
        
        self.form.tableView_2.setModel(self.paymentModel)
        
        #Signals
        self.connect(self.form.tableView.selectionModel(),
                SIGNAL("currentRowChanged(QModelIndex,QModelIndex)"),
                self.assetChanged)
        self.connect(self.form.AddPayment, SIGNAL("clicked()"),
                self.addAction)

    def assetChanged(self, index):
        if index.isValid():
            record = self.memberModel.record(index.row())
            id = record.value("id").toInt()[0]
            self.paymentModel.setFilter(QString("membersID = %1").arg(id))
        else:
            self.paymentModel.setFilter("membersID = -1")
        self.paymentModel.reset() # workaround for Qt <= 4.3.3/SQLite bug
        self.paymentModel.select()
        self.form.tableView_2.horizontalHeader().setVisible(
                self.paymentModel.rowCount() > 0)
        #if PYQT_VERSION_STR < "4.1.0":
           # self.form.tableView_2.setColumnHidden(ID, True)
            #self.form.tableView_2.setColumnHidden(ASSETID, True)
    
    def addAction(self):
        form = form2.Ui_Dlg_addPayment()
        form.show()
        """
        index = self.form.tableView.currentIndex()
        if not index.isValid():
            return
        QSqlDatabase.database().transaction()
        record = self.memberModel.record(index.row())
        memberID = record.value(0).toInt()[0]
        print memberID
        row = self.paymentModel.rowCount()
        self.paymentModel.insertRow(row)
        self.paymentModel.setData(self.paymentModel.index(row, memberID ),
                              QVariant(memberID))
        self.paymentModel.setData(self.paymentModel.index(row, 2),
                              QVariant(QDate.currentDate()))
        QSqlDatabase.database().commit()
        """


def createFakeData():
    import random
    query = QSqlQuery()
    QApplication.processEvents()

    print "Creating tables..."
    query.exec_("""CREATE TABLE members (
                id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,
                name TEXT NOT NULL,
                surname TEXT NOT NULL,
                mem_notes)""")
    query.exec_("""CREATE TABLE payment (
                id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,
                membersID INTEGER NOT NULL,
                date TEXT NOT NULL,
                amount TEXT NOT NULL,
                pay_notes TEXT,
                FOREIGN KEY (membersID) REFERENCES members)""")
    print "Populating tables..."
    query.exec_("INSERT INTO members (name, surname) "
                "VALUES ('Giovanni', 'Sticazzi')")
    var = QVariant("id")
    print var
    query.prepare("INSERT INTO payment (membersID, date, amount) "
                "VALUES (:membersID, '17', 'Prova')")
    query.bindValue(":membersID", "123")
    query.exec_()
    
def main():
    app = QApplication(sys.argv)

    filename = os.path.join(os.path.dirname(__file__), "assets.db")
    create = not QFile.exists(filename)
    db = QSqlDatabase.addDatabase("QSQLITE")
    db.setDatabaseName(filename)
    if not db.open():
        QMessageBox.warning(None, "Asset Manager",
            QString("Database Error: %1").arg(db.lastError().text()))
        sys.exit(1)

    splash = None
    if create:
        app.setOverrideCursor(QCursor(Qt.WaitCursor))
        splash = QLabel()
        pixmap = QPixmap(":/assetmanagersplash.png")
        splash.setPixmap(pixmap)
        splash.setMask(pixmap.createHeuristicMask())
        splash.setWindowFlags(Qt.SplashScreen)
        rect = app.desktop().availableGeometry()
        splash.move((rect.width() - pixmap.width()) / 2,
                    (rect.height() - pixmap.height()) / 2)
        splash.show()
        app.processEvents()
        createFakeData()

    form = MainForm()
    form.show()
    if create:
        splash.close()
        app.processEvents()
        app.restoreOverrideCursor()
    app.exec_()
    del form
    del db


main()

To copy to clipboard, switch view to plain text mode 

[tbscope]

In your case, whenever you change data in your table and select another row, the data in the database should be updated.

[viandante]

Yes, but this is not happening. Any idea why?