Design a “model” for QTableView in PySide + SQLAlchemy.

Question

My question is how to configure the SQLAlchemy declarative model, which can be accessed from the PySide QTableView class.

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

Unfortunately, I have a few confusions. I will try to explain where I am.

I followed the SQLAlchemy tutorial to such an extent that I have two related tables and I can manipulate / query those who have no problems. Attempting to set the QTableView class apparently requires the setData () method with my own model or using the default model, the setItem () method is required.

So the question is how to create a model. I assume this means defining one of these two methods for querying / modifying the database. I don’t know how to do it right.

It is assumed that the model will look like a username and surname repeating on several lines until all addresses are displayed, and then go to the next user. I can do this with nested loops to print this in a tooltip, but I don't think creating a large list is a way that seems to win the database first ...

I also don’t know what will happen when the database grows, the whole table becomes instance and is stored in memory, or does Qt load rows and columns when they come in to see when the user scrolls?

Sorry a lot of text here, but trying to be clear. If there are any additional things that I could add, please let me know. Or if I'm completely wrong ...

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's forget about the query and use the loop that you use. What you are looking for in the user interface is the main thing. I found, due to the lack of documents, that it is better to use a QTableWidget (or something QWhateverWidget ) better than QWhateverView for basic things. You do not need to define your own model for simple things. So, I will show you how to do this with QTableWidget. You need to create a QTableWidgetItem for each cell in (row, column). One of the problems I ran into was setting 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 can use is QTreeWidget , which supports multiple columns, and you can make it look like a table, but without editable cells by default, and this may suit your data better here.

Now for a query, you can do this with one single query to avoid scrolling through the results and make one additional query for each user. Sort of:

 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 large number of rows, I think there is a solution, but then you will need to define your own model and use LIMIT in your queries. With the lack of documents and textbooks, it is not so simple ...

And as a note, you do not need to define the __init__ method in your Address and user classes, since you are not doing anything, SQLAlchemy can do this automatically for you. And you can define default values ​​directly in the column definition.

UPDATE : for an example using QTableWidgetItem.setData , let's say we want to remove the user when double-clicked. We will 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 an integer) as long as it 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()