How to preload relationship attributes to access outside of session?

[SamEdwardes]

First Check

• I added a very descriptive title to this issue.
• I used the GitHub search to find a similar issue and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

# Data base set up. Copied from:
# https://sqlmodel.tiangolo.com/tutorial/relationship-attributes/back-populates/

from typing import List, Optional

from sqlmodel import Field, Relationship, Session, SQLModel, create_engine, select


class Team(SQLModel, table=True):
 id: Optional[int] = Field(default=None, primary_key=True)
 name: str
 headquarters: str

 heroes: List["Hero"] = Relationship(back_populates="team")


class Hero(SQLModel, table=True):
 id: Optional[int] = Field(default=None, primary_key=True)
 name: str
 secret_name: str
 age: Optional[int] = None

 team_id: Optional[int] = Field(default=None, foreign_key="team.id")
 team: Optional[Team] = Relationship(back_populates="heroes")


sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"

engine = create_engine(sqlite_url, echo=False)


def create_db_and_tables():
 SQLModel.metadata.create_all(engine)


def create_heroes():
 with Session(engine) as session:
 team_preventers = Team(name="Preventers", headquarters="Sharp Tower")
 team_z_force = Team(name="Z-Force", headquarters="Sister Margaret’s Bar")

 hero_deadpond = Hero(
 name="Deadpond", secret_name="Dive Wilson", team=team_z_force
 )
 hero_rusty_man = Hero(
 name="Rusty-Man", secret_name="Tommy Sharp", age=48, team=team_preventers
 )
 hero_spider_boy = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
 session.add(hero_deadpond)
 session.add(hero_rusty_man)
 session.add(hero_spider_boy)
 session.commit()

 session.refresh(hero_deadpond)
 session.refresh(hero_rusty_man)
 session.refresh(hero_spider_boy)


 hero_spider_boy.team = team_preventers
 session.add(hero_spider_boy)
 session.commit()
 session.refresh(hero_spider_boy)


 hero_black_lion = Hero(name="Black Lion", secret_name="Trevor Challa", age=35)
 hero_sure_e = Hero(name="Princess Sure-E", secret_name="Sure-E")
 team_wakaland = Team(
 name="Wakaland",
 headquarters="Wakaland Capital City",
 heroes=[hero_black_lion, hero_sure_e],
 )
 session.add(team_wakaland)
 session.commit()
 session.refresh(team_wakaland)

 hero_tarantula = Hero(name="Tarantula", secret_name="Natalia Roman-on", age=32)
 hero_dr_weird = Hero(name="Dr. Weird", secret_name="Steve Weird", age=36)
 hero_cap = Hero(
 name="Captain North America", secret_name="Esteban Rogelios", age=93
 )

 team_preventers.heroes.append(hero_tarantula)
 team_preventers.heroes.append(hero_dr_weird)
 team_preventers.heroes.append(hero_cap)
 session.add(team_preventers)
 session.commit()
 session.refresh(hero_tarantula)
 session.refresh(hero_dr_weird)
 session.refresh(hero_cap)


def main():
 create_db_and_tables()
 create_heroes()


main()


# Within session I can access heroes.
with Session(engine) as session:
 team = session.exec(select(Team)).first()
 print(team.heroes)

# [Hero(id=1, age=None, name='Deadpond', secret_name='Dive Wilson', team_id=1)]


# Outside of session I cannot.
with Session(engine) as session:
 team = session.exec(select(Team)).first()
 
print(team.heroes)

# ---------------------------------------------------------------------------
# DetachedInstanceError Traceback (most recent call last)
# /var/folders/38/ccm_21tj43v1ntn9ks9vyy740000gn/T/ipykernel_7846/3037874887.py in <module>
# 3 team = session.exec(select(Team)).first()
# 4 
# ----> 5 print(team.heroes)
# 
# ~/Library/Caches/pypoetry/virtualenvs/flask-webapp-VRI2aZnU-py3.9/lib/python3.9/site-packages/sqlalchemy/orm/attributes.py in __get__(self, instance, owner)
# 479 replace_context=err,
# 480 )
# --> 481 return self.impl.get(state, dict_)
# 482 
# 483 
# 
# ~/Library/Caches/pypoetry/virtualenvs/flask-webapp-VRI2aZnU-py3.9/lib/python3.9/site-packages/sqlalchemy/orm/attributes.py in get(self, state, dict_, passive)
# 924 return PASSIVE_NO_RESULT
# 925 
# --> 926 value = self._fire_loader_callables(state, key, passive)
# 927 
# 928 if value is PASSIVE_NO_RESULT or value is NO_VALUE:
# 
# ~/Library/Caches/pypoetry/virtualenvs/flask-webapp-VRI2aZnU-py3.9/lib/python3.9/site-packages/sqlalchemy/orm/attributes.py in _fire_loader_callables(self, state, key, # passive)
# 960 return callable_(state, passive)
# 961 elif self.callable_:
# --> 962 return self.callable_(state, passive)
# 963 else:
# 964 return ATTR_EMPTY
# 
# ~/Library/Caches/pypoetry/virtualenvs/flask-webapp-VRI2aZnU-py3.9/lib/python3.9/site-packages/sqlalchemy/orm/strategies.py in _load_for_state(self, state, passive, loadopt, # extra_criteria)
# 841 return attributes.PASSIVE_NO_RESULT
# 842 
# --> 843 raise orm_exc.DetachedInstanceError(
# 844 "Parent instance %s is not bound to a Session; "
# 845 "lazy load operation of attribute '%s' cannot proceed"
# 
# DetachedInstanceError: Parent instance <Team at 0x1162a8400> is not bound to a Session; lazy load operation of attribute 'heroes' cannot proceed (Background on this error at: https://sqlalche.me/e/14/bhk3)

Description

I am using sqlmodel with flask. I would like to pre-load the relationship attributes for a given object before passing that object into a jinja2 template. The challenge is I can't figure out how to pre-load the attributes.

In my example code, how can I get the last line to execute without throwing an error?

# Outside of session I cannot.
with Session(engine) as session:
 team = session.exec(select(Team)).first()
 
print(team.heroes)

Operating System

macOS

Operating System Details

macOS Big Sur 11.3.1

SQLModel Version

0.0.4

Python Version

3.9.4

Additional Context

In reference to my tweet :) https://twitter.com/TheReaLSamlam/status/1447779469221974016

[lukesmurray]

sqlalchemy by default lazy loads relationships. So when you print
team.heroes sqlalchemy will try to issue a select call to the database
to get the heroes which have the associated team id
SELECT * FROM hero WHERE team_id = ?
this is why you are havin the problem
what you want to do is tell sqlalchemy to eager load the relationship
there are several ways to do this
https://docs.sqlalchemy.org/en/14/orm/loading_relationships.html
because this is a 1-N relationship you probably want to
use either a subquery load or a selectin load
if you have multiple levels of nesting use a selectin load

from sqlalchemy.orm import selectinload
with Session(engine) as session:
 team = session.exec(select(Team).options(selectinload(Team.heroes))).first()
print(team.heroes)

[SamEdwardes]

Thank @lukesmurray I will give this a try and report back!

[SamEdwardes]

Confirmed this is exactly what I was looking for. Thank you!

[RamonGiovane]

sqlalchemy by default lazy loads relationships. So when you print team.heroes sqlalchemy will try to issue a select call to the database to get the heroes which have the associated team id SELECT * FROM hero WHERE team_id = ? this is why you are havin the problem what you want to do is tell sqlalchemy to eager load the relationship there are several ways to do this https://docs.sqlalchemy.org/en/14/orm/loading_relationships.html because this is a 1-N relationship you probably want to use either a subquery load or a selectin load if you have multiple levels of nesting use a selectin load

from sqlalchemy.orm import selectinload
with Session(engine) as session:
 team = session.exec(select(Team).options(selectinload(Team.heroes))).first()
print(team.heroes)

I got AttributeError: type object 'User' has no attribute 'user_role'. Did you mean: 'user_role_id'?

In my case User has one UserRole and UserRole has many Users

[SamEdwardes]

@RamonGiovane I am not sure I follow your question. In the example above (#130 (comment)) there are only two classes / tables (Team and Hero). Where is User coming from?

[GriffinWood]

@SamEdwardes I believe @RamonGiovane is using the same structure, but has changed the table names.

Currently when I add:

from sqlalchemy.orm import selectinload
with Session(engine) as session:
 team = session.exec(select(Team).options(selectinload(Team.heroes))).first()
print(team.heroes)

I get AttributeError: type object 'Team' has no attribute 'heroes'

Though to even get to this point I have to put this before the create_heroes() as if I do not I get the following error:

Traceback (most recent call last):
 File "summoner_service/create_heros.py", line 100, in <module>
 main()
 File "summoner_service/create_heros.py", line 92, in main
 create_heroes()
 File "summoner_service/create_heros.py", line 80, in create_heroes
 team_preventers.heroes.append(hero_tarantula)
AttributeError: 'Team' object has no attribute 'heroes'

[GriffinWood]

I took a look at the existing issues and I found an open issue about this. #322

It looks like SQLAlchemy version 1.4.36 broke this behavior. I have confirmed that I get the expected results when I use SQLAlchemy version 1.4.35.