CODEWITHSUNDEEP
pythonsqlalchemy
Who Cares
Who Cares

Reputation: 1

Way to defer/load_only in SQLAlchemy 2.0

Is there any any way to incorporate defer() or load_only() types of methods in SQL 2.0 types of queries, except adding deffered() to the columns in tables?

What i.m trying to get is smthg like:

class Signals(Base):

    __tablename__ = 'signals'

    id = sa.Column(sa.BigInteger(), primary_key=True)
    value_type_id = sa.Column(sa.Integer(), sa.ForeignKey('value_types.id'))
    sources_id = sa.Column(sa.BigInteger(), sa.ForeignKey('sources.id'))
    acl = sa.Column(JSONB())


stmt = select(Signals).join(Signals.value_types).defer(acl)

session = Session(engine)

# get all fields except acl
result = await session.execute(stmt)

Upvotes: 0

Views: 1392

Answers (1)

soundstripe
soundstripe

Reputation: 1474

I've expanded/modified your example a bit to have a full workable example.

In short, you can use .options(defer('field_name')) to prevent loading specific fields. However, implicitly loading those fields later (as the name "defer" implies) will not work.

Documentation link: https://docs.sqlalchemy.org/en/14/orm/loading_columns.html#deferred

import asyncio

import sqlalchemy as sa
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
from sqlalchemy.orm import declarative_base, relationship, defer, sessionmaker

meta = sa.MetaData()
Base = declarative_base(metadata=meta)


class ValueType(Base):
    __tablename__ = 'value_types'

    id = sa.Column(sa.Integer(), primary_key=True, autoincrement=True)


class Signals(Base):
    __tablename__ = 'signals'

    id = sa.Column(sa.Integer(), primary_key=True, autoincrement=True)
    value_type_id = sa.Column(sa.Integer(), sa.ForeignKey('value_types.id'))
    acl = sa.Column(sa.String())

    value_type = relationship(ValueType)


async def async_main():
    engine = create_async_engine(
        "sqlite+aiosqlite:///", echo=True,
    )

    async_session = sessionmaker(
        engine, expire_on_commit=False, class_=AsyncSession
    )

    async with engine.begin() as conn:
        await conn.run_sync(meta.drop_all)
        await conn.run_sync(meta.create_all)

    async with async_session() as session:
        async with session.begin():
            v = ValueType()
            session.add_all([
                Signals(acl='acl1', value_type=v),
                Signals(acl='acl2', value_type=v),
            ])

        stmt = sa.select(Signals).join(Signals.value_type).options(defer('acl'))
        results = await session.execute(stmt)
        for r in results.scalars():
            print(r)
            # await session.refresh(r, ['acl'])  # <-- uncomment to make the following line work
            print(r.acl)  # <-- error here, deferred loads will not work as the cursor is not awaited

    await engine.dispose()


if __name__ == '__main__':
    asyncio.run(async_main())

Upvotes: 1

Related Questions