PDA

View Full Version : QSqlRelationalTableModel and Sqlite



viandante
30th September 2010, 09:34
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().setVisibl e(
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()

tbscope
30th September 2010, 10:09
In your case, whenever you change data in your table and select another row, the data in the database should be updated.

viandante
1st October 2010, 07:03
Yes, but this is not happening. Any idea why?