SA deferred load possible bug / galaxy simplified example, classical mapping

sa_defer_galaxy.py

from sqlalchemy import (
    create_engine,
    inspect,
    Column,
    ForeignKey,
    Integer,
    MetaData,
    String,
    Table,
)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import (
    defer,
    joinedload,
    mapper,
    relation,
    sessionmaker,
    subqueryload,
)

metadata = MetaData()


class ToolShedRepository:
    pass


class ToolDependency:
    pass


ToolShedRepository.table = Table("tool_shed_repository", metadata,
    Column("id", Integer, primary_key=True),
    Column("name", String(255)),
    Column("metadata", String))
    

ToolDependency.table = Table("tool_dependency", metadata,
    Column("id", Integer, primary_key=True),
    Column("tool_shed_repository_id", Integer, ForeignKey("tool_shed_repository.id")),
    Column("name", String(255)))


mapper(ToolShedRepository, ToolShedRepository.table,
    properties=dict(
        tool_dependencies=relation(ToolDependency,
            primaryjoin=(ToolShedRepository.table.c.id == ToolDependency.table.c.tool_shed_repository_id),
            backref='tool_shed_repository'),
))


mapper(ToolDependency, ToolDependency.table)


def load(session):
    r = ToolShedRepository()
    d = ToolDependency()
    r.tool_dependencies.append(d)
    session.add_all([r, d])


def run(session):

    result = session.query(ToolShedRepository).options(
        defer(ToolShedRepository.metadata),
        joinedload('tool_dependencies').subqueryload('tool_shed_repository').options(
            defer(ToolShedRepository.metadata)
        ),
    ).all()
    
    insp = inspect(result[0])
    print('callables:', insp.callables)
    assert 'metadata' in insp.callables, 'deferred `ToolShedRepository.metadata` should be available as a callable'


engine = create_engine('sqlite:///:memory:', echo=True)
metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()
load(session)
run(session)
session.close()


# # 1.3.23
# SELECT tool_shed_repository.id AS tool_shed_repository_id, tool_shed_repository.name AS tool_shed_repository_name, tool_dependency_1.id AS tool_dependency_1_id, tool_dependency_1.tool_shed_repository_id AS tool_dependency_1_tool_shed_repository_id, tool_dependency_1.name AS tool_dependency_1_name 
# FROM tool_shed_repository
# LEFT OUTER JOIN tool_dependency AS tool_dependency_1 ON tool_shed_repository.id = tool_dependency_1.tool_shed_repository_id
# 
# SELECT tool_shed_repository.id AS tool_shed_repository_id, tool_shed_repository.name AS tool_shed_repository_name, tool_dependency_1.tool_shed_repository_id AS tool_dependency_1_tool_shed_repository_id 
# FROM (SELECT tool_shed_repository.id AS tool_shed_repository_id FROM tool_shed_repository) AS anon_1
# JOIN tool_dependency AS tool_dependency_1 ON anon_1.tool_shed_repository_id = tool_dependency_1.tool_shed_repository_id
# JOIN tool_shed_repository ON tool_shed_repository.id = tool_dependency_1.tool_shed_repository_id
#  
# # 1.4.5
# SELECT tool_shed_repository.id AS tool_shed_repository_id, tool_shed_repository.name AS tool_shed_repository_name, tool_dependency_1.id AS tool_dependency_1_id, tool_dependency_1.tool_shed_repository_id AS tool_dependency_1_tool_shed_repository_id, tool_dependency_1.name AS tool_dependency_1_name 
# FROM tool_shed_repository
# LEFT OUTER JOIN tool_dependency AS tool_dependency_1 ON tool_shed_repository.id = tool_dependency_1.tool_shed_repository_id
# 
# SELECT tool_shed_repository.id AS tool_shed_repository_id, tool_shed_repository.name AS tool_shed_repository_name, tool_shed_repository.metadata AS tool_shed_repository_metadata, tool_dependency_1.tool_shed_repository_id AS tool_dependency_1_tool_shed_repository_id 
# FROM (SELECT tool_shed_repository.id AS tool_shed_repository_id 
# FROM tool_shed_repository) AS anon_1
# JOIN tool_dependency AS tool_dependency_1 ON anon_1.tool_shed_repository_id = tool_dependency_1.tool_shed_repository_id
# JOIN tool_shed_repository ON tool_shed_repository.id = tool_dependency_1.tool_shed_repository_id