Geek Answers Handbook

How to return results from both tables in SQLAlchemy JOIN?

I have two tables defined in my ORM, like:

Base = declarative_base() class GeneralLedger(Base): __tablename__ = 'generalledgers' id = Column(Integer, primary_key=True) invoiceId = Column(Integer) .. class ConsolidatedLedger(Base): __tablename__ = 'consolidatedledgers' id = Column(Integer, primary_key = True) invoiceId = Column(Integer)

..

I have no relationship between two tables. I do the following:

 records = DBSession.query(GeneralLedger).join(ConsolidatedLedger, GeneralLedger.invoiceId == ConsolidatedLedger.invoiceId).all()

I also tried:

 records = DBSession.query(GeneralLedger).filter(GeneralLedger.invoiceId == ConsolidatedLedger.invoiceId).all()

In both cases, when I show the results in my view, only the entries from the GeneralLedger table are displayed. How to get results from both tables in one result set? I tried this:

 records = DBSession.query(GeneralLedger, ConsolidatedLedger).join(ConsolidatedLedger, GeneralLedger.invoiceId == ConsolidatedLedger.invoiceId).all()

But for some reason, when I repeat the results in my template (Jinja2), the column values ​​are empty for each individual row. Also, when count:

 total = DBSession.query(GeneralLedger).join(ConsolidatedLedger, GeneralLedger.invoiceId == ConsolidatedLedger.invoiceId).count()

Shared rows are the sum of matching records from two tables. I use webhelpers.paginate to handle swap:

 query = DBSession.query(GeneralLedger).join(ConsolidatedLedger, GeneralLedger.invoiceId == ConsolidatedLedger.invoiceId) records = paginate.Page(query, current_page, url=page_url)

and the result set sent to the template looks as if all the results where there are, but those that are in the ConslidatedLedger table are deleted. For example, I have a shared page with 20 entries. If there are entries from ConslidatedLedger on this page, the page is truncated, but only entries from GeneralLedger, but the paging is not broken.

Any thoughts? Thanks!

+7
python pyramid sqlalchemy
source share
1 answer
 records = DBSession.query(GeneralLedger, ConsolidatedLedger).join(ConsolidatedLedger, GeneralLedger.invoiceId == ConsolidatedLedger.invoiceId).all()

should work, but I think that when working with a set of records you need to access them through records.GeneralLedger and records.ConsolidatedLedger :

 for record in records: print record.GeneralLedger print record.ConsolidatedLedger print record.GeneralLedger.foo # ...etc
+9
source share

More articles:


All Articles