How to return join objects in a nested list using SQLAlchemy

Viewed 124

I'm using Python and SQLAlchemy to manage a small database. This database has the purpose of controlling some products of defined categories. I made a small representation of the code to show the current schema I created:

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

engine = create_engine("sqlite:///products.db", echo=False)
_Session = sessionmaker(bind=engine)
session: Session = _Session()
Base = declarative_base()

class Category(Base):
    __tablename__ = "categories"
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(60), nullable=False)
    products = relationship("Product", backref="category")

class Product(Base):
    __tablename__ = "products"
    id = Column(Integer, primary_key=True, autoincrement=True)
    description = Column(String(60), nullable=False)
    category_id = Column(Integer, ForeignKey("categories.id"), nullable=False)

cat1 = Category(name="School")
cat2 = Category(name="Foods")

school1 = Product(description="Eraser", category=cat1)
school2 = Product(description="Pencil", category=cat1)
school3 = Product(description="Ruler", category=cat1)
food1 = Product(description="Apple", category=cat2)
food2 = Product(description="Grape", category=cat2)
food3 = Product(description="Chocolate", category=cat2)

Base.metadata.create_all(engine)

session.add_all([cat1, cat2, school1, school2, school3, food1, food2, food3])
session.commit()

So, basically I have 2 categories, each of then containing 3 products. What I want is to retrieve these 2 categories with the 3 products nested inside, instead of a expanded table.

I don't want to do session.query(Category, Product).all() because it'll bring me an expanded table.

qry = session.query(...)...? # It's what I need for help
print(qry)

[
    {"id": 1, "name": "School", 'products': [
        {'id': 1, 'description': 'Eraser'},
        {'id': 2, 'description': 'Pencil'},
        {'id': 3, 'description': 'Ruler'}
    ]},
    {"id": 2, "name": "Foods", 'products': [
        {'id': 1, 'description': 'Apple'},
        {'id': 2, 'description': 'Grape'},
        {'id': 3, 'description': 'Chocolate'}
    ]}
]

(I represented the objects using JSON format to better explain my needs, but I want the objects itself)

I know I can query for the categories and loop through each of then getting the cat1.products variable. But if I can get all the data with a single SQL query call, I think it'll be more performant.

I appreciate for help.

0 Answers
Related