Design of the 'model' for QTableView in PySide + SQLAlchemy

Question

My question is really how to setup a SQLAlchemy-declarative model that can be accessed from PySide's QTableView class.

I'm just trying to essentially implement a front end for the Object Relational tutorial

Unfortunately I have a few points of confusion. I'll try to explain where I'm at.

I have a followed the SQLAlchemy tutorial to the point where I have two related tables and can manipulate/query those with no problems. Trying to establish a QTableView class apparently requires a setData() method with my own model, or using the default model requires a setItem() method.

So the question is how to design the model. I presume this means defining one of those two methods to query/modify the database. I don't know the right way to do this.

The model is supposed to be like User firstname and lastname repeated on a few rows until all the addresses have been shown, then move on to the next User. I can do this with nested for loops to print this at the prompt but I don't think making a big list is the way to go as that seems to defeat the point of having the database in the first place...

I also don't know what will happen when the database grows, does the entire table become instantiated and held in memory or does Qt load rows and columns as they come in to view when the user scrolls?

Lot of text here sorry, but trying to be clear. If there are any additional things I could add please let me know. Or if I'm totally on the wrong track ....

from PySide import QtCore, QtGui
from sqlalchemy import Column, Integer, String, Text, Sequence, ForeignKey, Date, Boolean, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship, backref, aliased
import datetime


engine = create_engine('sqlite:///reminder.db')

Base = declarative_base()

class User(Base):
    __tablename__ = 'users_db'
    id = Column(Integer, Sequence('user_id_seq'), primary_key=True)
    lastname = Column(String)
    firstname = Column(String)
    contact = Column(String)
    history = Column(Text)
    notes = Column(Text)

    addresses = relationship('Address', order_by='Address.id', 
                               backref='user', cascade='all, delete, delete-orphan')


    def __init__(self, firstname, lastname, contact):
        self.firstname = firstname
        self.lastname = lastname
        self.contact = contact

    def __repr__(self):
        return "<User('{0}', '{1}', '{2}')>".format(self.firstname, self.lastname, self.contact)


class Address(Base):
    __tablename__ = 'addresses_db'
    id = Column(Integer, primary_key=True)
    address = Column(String(150))
    date = Column(Date)
    check1 = Column(Boolean)
    check2 = Column(Boolean)

    user_id = Column(Integer, ForeignKey('users_db.id'))

    def __init__(self, address, date):
        self.address = address
        self.date = date
        self.check1 = False
        self.check2 = False

    def __repr__(self):
        return "<Address('{0}', '{1}')>".format(self.address, self.date)

if __name__ == '__main__':
    Base.metadata.create_all(engine)
    Session = sessionmaker(bind=engine)
    session = Session()
    header = [User.firstname, User.lastname, nextaddressfromUser]

>>> for user in session.query(User).all():
...     for addr in user.addresses:
...         print user.firstname, user.lastname, addr.address

Answer 1

First, let us forget about the query, and use the loop you are using. What you are looking for in the UI is a basic thing. I have found from the lack of docs, that it is best to use a QTableWidget (or anything QWhateverWidget) better than QWhateverView for basic things. You don't need to define your own model for simple things. So, I will show you how to do it with a QTableWidget. You need to create a QTableWidgetItem for each cell at (row, column). One problem I encountered, was having to set the number of rows before adding them. Anyway, here:

import sys
from PySide import QtGui, QtCore

class Test(QtGui.QWidget):

    def __init__(self, rows):
        super(Test, self).__init__()

        self.table = QtGui.QTableWidget()
        self.table.setColumnCount(3)
        # Optional, set the labels that show on top
        self.table.setHorizontalHeaderLabels(("First Name", "Last Name", "Address"))

        self.table.setRowCount(len(rows))
        for row, cols in enumerate(rows):
            for col, text in enumerate(cols):
                table_item = QtGui.QTableWidgetItem(text)
                # Optional, but very useful.
                table_item.setData(QtCore.Qt.UserRole+1, user)
                self.table.setItem(row, col, table_item)

        # Also optional. Will fit the cells to its contents.
        self.table.resizeColumnsToContents()

        # Just display the table here.
        layout = QtGui.QHBoxLayout()
        layout.addWidget(self.table)
        self.setLayout(layout)

if __name__ == "__main__":
    # ...
    rows = []
    # Here I have to fill it in an array, because you need to know the number of rows before adding... There might be a better solution though.
    for user in session.query(User).all():
        for addr in user.addresses:
            # These are the columns on each row (firstname, lastname, address)
            rows.append((user.firstname, user.lastname, addr.address))

    app = QtGui.QApplication(sys.argv)
    test = Test(rows)
    test.show()
    app.exec_()

Another thing you might consider using is a QTreeWidget, which supports multiple columns, and you can make it look like a table, but without the editable cells by default, and it might suit your data better here.

Now for the query, you might want to make it one single query to avoid looping through the results and having to make one additional query for each user. Something like:

query = session.query(User.firstname, User.lastname, Address.address).filter(Address.user_id == User.id)
    for row in query.all():
        # firstname, lastname, address = row
        rows.append(row)

For a lot of rows, I think there is a solution, but then you would need to define your own model and use LIMITs in your queries. With the lack of docs and tutorials, it is not that easy...

And as a side note, you don't need to define the __init__ method on your Address and User classes, as you are not doing anything special, SQLAlchemy can do this automatically for you. And you can define default values straight into the Column definition.

UPDATE: for an example of using QTableWidgetItem.setData, let's say we want to delete the user when double-clicked. We'll use the itemDoubleClicked signal.

# in the __init__ function
self.table.itemDoubleClicked.connect(self.onItemDoubleClick)

# in onItemDoubleClicked function
def onItemDoubleClicked(self, item):
    # Every data has a role, you can specify your own (it's an integer) as long as it's greater than UserRole. Others are used internally, like DisplayRole and some others you can find in the QtCore package.
    # You can use data with other widgets also, not just TableWidgets.
    user = item.data(QtCore.Qt.UserRole+1)
    # you get a session however you want, then delete the user. This object is the same as the one you passed earlier when creating the item, it can be whatever you like.
    session.delete(user)
    session.commit()