Querying joined tables in SQLAlchemy and displaying in an ObjectListView

Question

I have an ObjectListView that displays information retrieved from an SQLite DB with SQLAlchemy.

def setupOLV(self):
    self.loanResultsOlv.SetEmptyListMsg("No Loan Records Found")
    self.loanResultsOlv.SetColumns([
    ColumnDefn("Date Issued", "left", 100, "date_issued",
                stringConverter="%d-%m-%y"),
    ColumnDefn("Card Number", "left", 100, "card_id"),
    ColumnDefn("Student Number", "left", 100, "person_id"),
    ColumnDefn("Forename", "left", 150, "person_fname"),
    ColumnDefn("Surname", "left", 150, "person_sname"),
    ColumnDefn("Reason", "left", 150, "issue_reason"),
    ColumnDefn("Date Due", "left", 100, "date_due", 
                stringConverter="%d-%m-%y"),
    ColumnDefn("Date Returned", "left", 100, "date_returned", 
                stringConverter="%d-%m-%y")
])

I also have three models, Loan:

class Loan(DeclarativeBase):
    """
    Loan model
    """
    __tablename__ = "loans"

    id = Column(Integer, primary_key=True)
    card_id = Column(Unicode, ForeignKey("cards.id"))
    person_id = Column(Unicode, ForeignKey("people.id"))
    date_issued = Column(Date)
    date_due = Column(Date)
    date_returned = Column(Date)
    issue_reason = Column(Unicode(50))
    person = relation("Person", backref="loans", cascade_backrefs=False)
    card = relation("Card", backref="loans", cascade_backrefs=False)

Person:

class Person(DeclarativeBase):
    """
    Person model
    """
    __tablename__ = "people"

    id = Column(Unicode(50), primary_key=True)
    fname = Column(Unicode(50))
    sname = Column(Unicode(50))

and Card:

class Card(DeclarativeBase):
    """
    Card model
    """
    __tablename__ = "cards"

    id = Column(Unicode(50), primary_key=True)
    active = Column(Boolean)

I am trying to join the tables (loans and people) in order to retrieve and display the information in my ObjectListView. Here is my query method:

def getQueriedRecords(session, filterChoice, keyword):
    """
    Searches the database based on the filter chosen and the keyword
    given by the user
    """
    qry = session.query(Loan)
    if filterChoice == "person":
        result = qry.join(Person).filter(Loan.person_id=='%s' % keyword).all()
    elif filterChoice == "card":
        result = qry.join(Person).filter(Loan.card_id=='%s' % keyword).all()
    return result

I can retrieve and display every field stored in the loans table but forename and surname (should be drawn from people table and joined on person.id) are blank in my ObjectListView. I have SQL output on so I can see the query and it is not selecting at all from the people table.

How can I modify my query/ObjectListView to retrieve and display this information. ?

UPDATE: I have created an example script that is runnable here.

Answer 1

I admit that I am pretty new to SQLAlchemy myself, but I thought I would share what I use to display results from my queries. I have a program that uses a SQLite DB with 4+ tables and I pull data from 2-3 of them in a single query and display this information in an ObjectListView. I owe Mike Driscoll for his in depth tutorials, particularly wxPython and SqlAlchemy: An Intro to MVC and CRUD.

Here is what I would possibly add/change in your code.

In your model section add a "display" class such as:

def OlvDisplay(object):
    def __init__(self, date_issued, card_id, person_id, fname, sname,
                 issue_reason, date_due, date_returned):
        self.date_issued   = date_issued
        self.card_id       = card_id
        self.person_id     = person_id
        self.person_fname  = fname
        self.person_sname  = sname
        self.issue_reason  = issue_reason
        self.date_due      = date_due
        self.date_returned = date_returned

This display class is used in the convertResults definition below and assists with making sure the data is formatted properly for the ObjectListView.

The adjustment to your existing query function:

def getQueriedRecords(session, filterChoice, keyword):
    """
    Searches the database based on the filter chosen and the keyword
    given by the user
    """
    qry = session.query(Loan)
    if filterChoice == "person":
        result = qry.join(Person).filter(Loan.person_id=='%s' % keyword).all()
    elif filterChoice == "card":
        result = qry.join(Person).filter(Loan.card_id=='%s' % keyword).all()
    convertedResults = convertResults(result)
    return convertedResults

What we're doing here is creating a local variable that is essentially running the conversion definition and storing the results for the next line, which returns those results.

And the "Convertor" function:

def convertResults(results):
    finalResults = []
    for record in results:
        result = OlvDisplay(
                        record.date_issued,
                        record.card_id,
                        record.person_id,
                        record.person.fname,
                        record.person.sname,
                        record.issue_reason,
                        record.date_due,
                        record.date_returned
                           )
        finalResults.append(result)
    return finalResults

The important part here are the 2 lines:

record.person.fname
record.person.sname

Since we are wanting to pull information from another table using the established relationship it is important to refer to that relationship to actually see the data.

And to populate the ObjectListView Widget:

theOutput = getQueriedRecords(session, filterChoice, keyword)
self.setupOLV.SetObjects(theOutput)

Hope this helps you out.

-MikeS

Answer 2

You're only querying for a Loan (qry = session.query(Loan)). Why do you expect something else to be in the results besides what's in the SELECT statement?