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.