CODEWITHSUNDEEP
pythonsqlalchemy
Luiz Geron
Luiz Geron

Reputation: 1387

How do I load only the specified columns and still get a model object? (without defer)

I want to:

  1. Load only the fields I want, from multiple tables/models.
  2. Get back "model objects", not the namedtuple-like objects I would from get passing the columns to session.query. This should span relationships, so for instance employee.company.name, where company is a relationship field in the Employee model.
  3. Prevent other fields from being inadvertently loaded after the initial select query. I could use defer or .load_only('field_name') but then someone else could access an attribute of the model I didn't specify and that would cause another query to run. Ideally, accessing a field that wasn't specified in the query would raise an AttributeError, even if that field is defined in the model.

Is this possible using mechanisms provided by SqlAlchemy? Would this even be a good idea?

I wrote the following function that does what I want, but it seems someone else must've met these needs in a better and more standard way.

class Attributable(object):
    pass


def spread_result(row, columns):
    """
        :type row: sqlalchemy.util._collections.KeyedTuple
        :type columns: tuple
        Usage:
        >>> result = session.query(Model.field, AnotherModel.other_field).first()
        >>> obj = spread_result(result, ('field', 'another_model.other_field'))
        >>> obj.field
        'field_value'
        >>> obj.another_model.other_field
        'other_field_value'
        >>> obj.another_mapped_field
        AttributeError: 'Attributable' object has no attribute 'another_mapped_field'
    """
    root = Attributable()
    for column, value in zip(columns, row):
        obj = root
        parts = column.split('.')
        for i, attr in enumerate(parts):
            if i == len(parts) - 1:
                setattr(obj, attr, value)
            else:
                setattr(obj, attr, Attributable())
                obj = getattr(obj, attr)

    return root

Upvotes: 1

Views: 1158

Answers (1)

davidism
davidism

Reputation: 127180

The easiest way would be to create a "public" model that maps to the same table but only contains the columns and other properties that you want loadable/accessible.

The relevant documentation says:

the include_properties or exclude_properties arguments can specify that only a subset of columns should be mapped

If you have a Person model which users should just see id and name from, the "public" class would look like this:

class PublicPerson(Base):
    __table__ = Person.__table__

    __mapper_args__ = {
        'include_properties': ['id', 'name']
    }

Here is a simple runnable example:

from datetime import datetime
from sqlalchemy import create_engine, Column, Integer, String, DateTime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session

engine = create_engine('sqlite://', echo=True)
session = Session(bind=engine)
Base = declarative_base(bind=engine)


class Person(Base):
    __tablename__ = 'person'

    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    birthday = Column(DateTime, nullable=False)


class PublicPerson(Base):
    __table__ = Person.__table__

    __mapper_args__ = {
        'include_properties': ['id', 'name']
    }


Base.metadata.create_all()

session.add(Person(name='Jan', birthday=datetime(2001, 1, 1)))

# query the full person, prints birthday
print(session.query(Person.birthday).scalar())

# query the "public" person, raises exception on birthday
print(session.query(PublicPerson.birthday).scalar())

Upvotes: 1

Related Questions