Return joined tables in JSON format with SQLAlchemy and Flask jsonify

By declaring category = relationship(Category) in Item, instances of Item have a category attribute that corresponds to the correct row in the database. In the background, this will fetch the row from the database if necessary. You should be careful about this when handling collections of items as it may result in calling the database once for each item - this is called the n+1 problem.

So to answer the question "How do I include self.category within the item serializable?", you can literally just write:

class Item(Base):
    ...

    @property
    def serializable(self):
        return {
            'id': self.id,
            'name': self.name,
            ...
            'category': self.category.serializable
        }

But this is probably not a good idea as you might accidentally cause an extra database call when writing item.serializable.

In any case we really want to list all the items in a category, so we need to use the foreign key relationship in the other direction. This is done by adding the backref argument to the relationship:

category = relationship(Category, backref='items')

and now Category instances will have an items attribute. Then here is how to write getCatalog:

def getCatalog():
    categories = Session().query(Category).options(joinedload(Category.items)).all()
    return dict(Catalog=[dict(c.serializable, items=[i.serializable
                                                     for i in c.items])
                         for c in categories])

Here .options(joinedload(Category.items)) performs a SQL JOIN to fetch the items in advance so that c.items doesn't cause extra database queries. (Thanks Ilja)

Here is the full code for a complete demo:

from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship, joinedload

engine = create_engine('sqlite://', echo=True)

Session = sessionmaker(bind=engine)

Base = declarative_base()


class Category(Base):
    __tablename__ = 'category'
    id = Column(Integer, primary_key=True)
    name = Column(String(32), nullable=False)

    @property
    def serializable(self):
        return {'id': self.id, 'name': self.name}


class Item(Base):
    __tablename__ = 'item'
    id = Column(Integer, primary_key=True)
    name = Column(String(32), nullable=False)
    category_id = Column(Integer, ForeignKey('category.id'))
    category = relationship(Category, backref='items')

    @property
    def serializable(self):
        return {'id': self.id, 'name': self.name}


Base.metadata.create_all(engine)

category1 = Category(id=1, name='fruit')
category2 = Category(id=2, name='clothes')
session = Session()
session.add_all([category1, category2,
                 Item(id=1, name='apple', category=category1),
                 Item(id=2, name='orange', category=category1),
                 Item(id=3, name='shirt', category=category2),
                 Item(id=4, name='pants', category=category2)])
session.commit()


def getCatalog():
    categories = Session().query(Category).options(joinedload(Category.items)).all()
    return dict(Catalog=[dict(c.serializable, items=[i.serializable
                                                     for i in c.items])
                         for c in categories])


from pprint import pprint

pprint(getCatalog())

The echoed SQL shows that only one SELECT is sent to the database. The actual output is:

{'Catalog': [{'id': 1,
              'items': [{'id': 1, 'name': 'apple'},
                        {'id': 2, 'name': 'orange'}],
              'name': 'fruit'},
             {'id': 2,
              'items': [{'id': 3, 'name': 'shirt'}, {'id': 4, 'name': 'pants'}],
              'name': 'clothes'}]}