sqlalchemy async join

Question

I'm trying to implement a basic resource access using SQL Alchemy 1.4 and a PostgreSQL database.

Python code

from sqlalchemy.ext.declarative import DeclarativeMeta, declarative_base

Base: DeclarativeMeta = declarative_base()


class User(Base):
  __tablename__ = 'user'

  id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
  email = Column(String(length=255), index=True, nullable=False)


class Resource(Base):
  __tablename__ = "resource"
  
  id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
  name = Column(String(length=255), index=True, nullable=False)


class UserResourceRole(Base):
  __tablename__ = "user_resource_role"

  user_id = Column(
    UUID(as_uuid=True), 
    ForeignKey("user.id", ondelete="CASCADE"),
    primary_key=True
  )
  resource_id = Column(
    UUID(as_uuid=True),
    ForeignKey("resource.id", ondelete="CASCADE"),
    primary_key=True,
  )
  can_edit = Column(Boolean, default=False, nullable=False)

from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine
from sqlalchemy.ext.asyncio.engine import AsyncEngine
from sqlalchemy.orm import sessionmaker

from the_other_file import User, Resource, UserResourceRole

async def select_all(user: User, db_session: AsyncSession):
  results = await db_session.execute(
    select(Resource, UserResourceRole)
    .join(
      UserResourceRole,
      Resource.id == UserResourceRole.resource_id
    )
    .where(UserResourceRole.user_id == user.id)
  )
  return results.scalars().all()


engine: AsyncEngine = create_async_engine(POSTGRES_URL, future=True)

async_session = sessionmaker(
    bind=engine, class_=AsyncSession, expire_on_commit=False, future=True
)


# ignore the fact that it's not executed in asyncio loop
a_db_session = await async_session()
resources = await select_all(user=a_real_user_is_here, db_session=a_db_session)
print(resources)

I can't retrieve anything from UserResourceRole in my results. It only contains data from Resource. If i swap the objects in the select call, then I can only retrieve data from UserResourceRole.

What I'm expecting

I'm expecting to have the same result of that SQL query :

SELECT *
FROM resource
INNER JOIN user_resource_role
ON resource.id = user_resource_role.resource_id
WHERE user_resource_role.user_id = :a_user_id

The query generated by SQL Alchemy is exactly the same (except the verbosity) :

SELECT resource.id, resource.name, user_resource_role.user_id, user_resource_role.resource_id, user_resource_role.can_edit
FROM resource
JOIN user_resource_role
ON resource.id = user_resource_role.resource_id
WHERE user_resource_role.user_id = :user_id_1

Answer 1

If you try

for entry in results:
  print(entry)

it will show you a list of tuple of (Resource, UserResourceRole). Apparently the call to .scalars().all() only leave the first value. My current solution is to turn results into a list and manually manipulate it.