2 posts tagged with "sqlalchemy"

In SQLAlchemy, both the relationship loading option (.options(joinedload(...))) and the query builder method (.join(TableClass)) result in a SQL JOIN clause. However, they serve fundamentally different purposes and lead to distinct results in the ORM (Object Relational Mapper) layer.

Understanding this difference is crucial for avoiding the common "N+1 problem" and correctly shaping the data returned by your queries.

This pattern is often used for:

1. Legacy Databases: Working with existing schemas that lack proper constraints.
2. Performance: Avoiding the overhead of transactional foreign key checks.
3. Data Warehousing: Dealing with schemas where relationships are semantic, not structural.

The key to achieving this is the relationship() function combined with the primaryjoin argument. This allows SQLAlchemy to define the join condition required for the relationship, enabling essential features like eager loading (joinedload, selectinload).