A relationship is an association between tables that allows databases to store data in the different tables without redundancy. This allows the tables to run efficiently and simultaneously. There are three common types of relationships in SQLAlchemy. One-to-one is when there is only one instance of each other. For example, an employee has an employee ID. A one-to-many relationship has one instance from one table that corresponds to many instances from another table. doctor sees many patients and each patient sees that doctor. A many-to-many relationship connects one-to-many relationships with a join table to create the many-to-many. A doctor sees many patients through appointments and many patients have appointments and each patient has an appointment. Having relationships allows control of how the objects are queried. to do this we have relationship loading.

There are three categories of loading:

Lazy loading involves returning objects from a query without initially loading their related objects. When a specific object's collection or reference is first accessed, an additional SELECT statement is triggered to load the requested collection.

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

 Base = declarative_base()

 class Country(Base):
     __tablename__ = 'countries'

     id = Column(Integer, primary_key=True)
     name = Column(String)
     capital_id = Column(Integer, ForeignKey('cities.id'))

     # Lazy-loaded relationship with City
     capital = relationship('City', lazy='select')

 class City(Base):
     __tablename__ = 'cities'

     id = Column(Integer, primary_key=True)
     name = Column(String)

 # Create an SQLite in-memory database and bind the engine
 engine = create_engine('sqlite:///:memory:')

 # Create the tables
 Base.metadata.create_all(engine)

 # Create a session to interact with the database
 Session = sessionmaker(bind=engine)
 session = Session()

 # Add some data
 paris = City(name='Paris')
 france = Country(name='France', capital=paris)

 session.add_all([paris, france])
 session.commit()

 # Lazy loading example
 # Accessing the 'capital' attribute triggers a SELECT statement
 loaded_country = session.query(Country).filter_by(name='France').first()
 print("Country Name:", loaded_country.name)
 print("Capital Name:", loaded_country.capital.name)  # Lazy loading occurs here

 # Close the session
 session.close()

In this example, the capital attribute of the Country class is set up for lazy loading with lazy='select'. When accessing loaded_country.capital.name, the related City object is lazily loaded from the database only when needed.

Eager loading involves returning objects from a query with the related collection reference preloaded. This is achieved by the query either enhancing the usual SELECT statement with a JOIN to load related rows simultaneously or by emitting additional SELECT statements after the primary one to load collections references in a single operation.

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

 Base = declarative_base()

 class Country(Base):
     __tablename__ = 'countries'

     id = Column(Integer, primary_key=True)
     name = Column(String)
     capital_id = Column(Integer, ForeignKey('cities.id'))

     # Eager-loaded relationship with City
     capital = relationship('City', lazy='select', uselist=False)

 class City(Base):
     __tablename__ = 'cities'

     id = Column(Integer, primary_key=True)
     name = Column(String)

 # Create an SQLite in-memory database and bind the engine
 engine = create_engine('sqlite:///:memory:')

 # Create the tables
 Base.metadata.create_all(engine)

 # Create a session to interact with the database
 Session = sessionmaker(bind=engine)
 session = Session()

 # Add some data
 paris = City(name='Paris')
 france = Country(name='France', capital=paris)

 session.add_all([paris, france])
 session.commit()

 # Eager loading example
 # The related 'City' object is loaded immediately along with the 'Country' object
 loaded_country = session.query(Country).options(
     relationship('capital', lazy='select')
 ).filter_by(name='France').first()

 print("Country Name:", loaded_country.name)
 print("Capital Name:", loaded_country.capital.name)  # No additional database query needed

 # Close the session
 session.close()

In this example, the relationship is set up with lazy='select', making it eager-loaded. When querying for a Country, the related City object is loaded immediately along with the Country object. This helps avoid additional queries when accessing the related object later on.

"No" loading refers to the intentional disabling of loading on a specific relationship. This can manifest as the attribute remaining empty and never being loaded, or as an error being raised when attempting to access it. This practice serves as a preventive measure against undesired lazy loads.
```
 from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
 from sqlalchemy.orm import declarative_base, relationship, sessionmaker

 Base = declarative_base()

 class Country(Base):
     __tablename__ = 'countries'

     id = Column(Integer, primary_key=True)
     name = Column(String)
     capital_id = Column(Integer, ForeignKey('cities.id'))

     # No loading for the relationship with City
     capital = relationship('City', lazy='noload', uselist=False)

 class City(Base):
     __tablename__ = 'cities'

     id = Column(Integer, primary_key=True)
     name = Column(String)

 # Create an SQLite in-memory database and bind the engine
 engine = create_engine('sqlite:///:memory:')

 # Create the tables
 Base.metadata.create_all(engine)

 # Create a session to interact with the database
 Session = sessionmaker(bind=engine)
 session = Session()

 # Add some data
 paris = City(name='Paris')
 france = Country(name='France', capital=paris)

 session.add_all([paris, france])
 session.commit()

 # No loading example
 # The related 'City' object is not loaded along with the 'Country' object
 loaded_country = session.query(Country).filter_by(name='France').first()

 print("Country Name:", loaded_country.name)
 # Accessing 'capital' does not trigger additional queries
 print("Capital:", loaded_country.capital)  # Returns None

 # Close the session
 session.close()
```
In this example, the relationship between Country and City is set with lazy='noload', which means that the related City object won't be loaded along with the Country object by default. Accessing the capital attribute will not trigger additional queries, and it will return None. This is useful when you want to avoid unnecessary loading of related objects in certain scenarios.

The primary forms of relationship loading are lazy loading, Joined loading, Subquery loading, Selectin loading, dynamic loading, raise loading and no loading.

Lazy loading (True), which can be enabled through either lazy='select' or the lazyload() option, involves emitting a SELECT statement at the time of the attribute access. This method lazily loads a related reference on a single object when used. Joined eager loading, accessible through either lazy='joined' or the joinedload() option, incorporates a JOIN into the rows to be loaded within the same result set.

When employing lazy='dynamic' during a query, a distinct query is generated for related objects. If the same query is used as with 'select', it results in returning an SQLAlchemy object instead of the specific objects. The advantage here lies in the ability to further customize your query by utilizing methods like filter/filter_by_order_by and more.

Subquery eager loading, accessible through lazy='subquery' or the subqueryload() option, involves emitting a second SELECT statement. This statement restates the original query embedded within a subquery. Subsequently, it JOINs that subquery to the related table, facilitating the loading of all members of related collections simultaneously. Notably, lazy='subquery' and lazy='joined' perform the same function, with the key distinction that subquery utilizes a subquery. In terms of query and results, both lazy loading options yield the same outcome. However, there might be performance differences between 'joined' and 'subquery' due to their distinct approaches to joining tables.

Selectin loading, achieved through lazy='selection' or the selectinload() option, triggers an additional SELECT statement (or more). This statement constructs an IN clause using the primary key identifiers of the parent objects. As a result, all members of related collections are loaded at once based on their primary keys. This strategy enhances efficiency in the loading process.

Raise loading(lazy='raise',lazy='raise_on_sql, or raiseload() option), prevents unwanted lazy loads.

'No' loading, with options like lazy='noload' or noload(), transforms the attribute into an empty state(None or []). This means it won't load or having any loading impact. While not commonly used, this strategy is somewhat like an eager loader when objects are loaded, providing an empty attribute or collection. For expired objects, it relies on the default value of the attribute upon access. Though it might be used for a "write-only" attribute, this application is not currently tested or formally supported.

In summary, various loading strategies, such as lazy loading, eager loading, and 'No' loading, offer flexibility in managing how related objects are retrieved. Each strategy comes with its own set of advantages and considerations. Lazy loading optimizes resource usage by loading related objects only when explicitly accessed, while eager loading preloads them for efficiency. 'No' loading provides a way to disable loading on a particular relationship, offering control over when and how related data is fetched. Additionally, specialized loading options like subquery, selectin, and raise loading cater to specific scenarios, providing nuanced solutions. The choice of loading strategy depends on the application's requirements and performance considerations.

Resources:

https://docs.sqlalchemy.org/en/14/orm/loading_relationships.html#lazy-loading

https://medium.com/@ns2586/sqlalchemys-relationship-and-lazy-parameter-4a553257d9ef

https://in.indeed.com/career-advice/career-development/what-is-relationship-in-database#:~:text=To%20answer%20%22What%20is%20a,key%20of%20the%20other%20tables.

Relationship Loading

Subscribe to my newsletter

Shawn Conway

Shawn Conway