Python sqlalchemy.orm.deferred() Examples

def test_add_deferred(self):
        class Person(Base, fixtures.ComparableEntity):

            __tablename__ = "people"
            id = Column(
                "id", Integer, primary_key=True, test_needs_autoincrement=True
            )

        Person.name = deferred(Column(String(10)))
        Base.metadata.create_all()
        sess = create_session()
        p = Person(name="ratbert")
        sess.add(p)
        sess.flush()
        sess.expunge_all()
        eq_(sess.query(Person).all(), [Person(name="ratbert")])
        sess.expunge_all()
        person = sess.query(Person).filter(Person.name == "ratbert").one()
        assert "name" not in person.__dict__ 

def test_deferred(self):
        class User(Base, fixtures.ComparableEntity):

            __tablename__ = "users"
            id = Column(
                Integer, primary_key=True, test_needs_autoincrement=True
            )
            name = sa.orm.deferred(Column(String(50)))

        Base.metadata.create_all()
        sess = create_session()
        sess.add(User(name="u1"))
        sess.flush()
        sess.expunge_all()
        u1 = sess.query(User).filter(User.name == "u1").one()
        assert "name" not in u1.__dict__

        def go():
            eq_(u1.name, "u1")

        self.assert_sql_count(testing.db, go, 1) 

def setup_classes(cls):
        Base = cls.DeclarativeBasic

        class A(Base):
            __tablename__ = "a"

            id = Column(Integer, primary_key=True)
            bs = relationship("B")

        class B(Base):
            __tablename__ = "b"
            id = Column(Integer, primary_key=True)
            a_id = Column(ForeignKey("a.id"))

        A.b_count = deferred(
            select([func.count(1)]).where(A.id == B.a_id).scalar_subquery()
        ) 

def test_deferred_notfound(self):
        users, User = self.tables.users, self.classes.User

        mapper(User, users, properties={"name": deferred(users.c.name)})
        s = create_session(autocommit=False)
        u = s.query(User).get(10)

        assert "name" not in u.__dict__
        s.execute(users.delete().where(User.id == 10))
        assert_raises_message(
            sa.orm.exc.ObjectDeletedError,
            "Instance '<User at .*?>' has been "
            "deleted, or its row is otherwise not present.",
            getattr,
            u,
            "name",
        ) 

def test_unsaved(self):
        """Deferred loading does not kick in when just PK cols are set."""

        Order, orders = self.classes.Order, self.tables.orders

        mapper(
            Order,
            orders,
            properties={"description": deferred(orders.c.description)},
        )

        sess = create_session()
        o = Order()
        sess.add(o)
        o.id = 7

        def go():
            o.description = "some description"

        self.sql_count_(0, go) 

def setup_mappers(cls):
        global WeatherLocation, Report

        class WeatherLocation(object):
            def __init__(self, continent, city):
                self.continent = continent
                self.city = city

        class Report(object):
            def __init__(self, temperature, id_=None):
                self.temperature = temperature
                if id_:
                    self.id = id_

        mapper(
            WeatherLocation,
            weather_locations,
            properties={
                "reports": relationship(Report, backref="location"),
                "city": deferred(weather_locations.c.city),
            },
        )

        mapper(Report, weather_reports) 

def test_unsaved_group(self):
        """Deferred loading doesn't kick in when just PK cols are set"""

        orders, Order = self.tables.orders, self.classes.Order

        mapper(
            Order,
            orders,
            properties=dict(
                description=deferred(orders.c.description, group="primary"),
                opened=deferred(orders.c.isopen, group="primary"),
            ),
        )

        sess = create_session()
        o = Order()
        sess.add(o)
        o.id = 7

        def go():
            o.description = "some description"

        self.sql_count_(0, go) 

def test_unsaved_group_2(self):
        orders, Order = self.tables.orders, self.classes.Order

        mapper(
            Order,
            orders,
            properties=dict(
                description=deferred(orders.c.description, group="primary"),
                opened=deferred(orders.c.isopen, group="primary"),
            ),
        )

        sess = create_session()
        o = Order()
        sess.add(o)

        def go():
            o.description = "some description"

        self.sql_count_(0, go) 

def test_load_only_w_deferred(self):
        orders, Order = self.tables.orders, self.classes.Order

        mapper(
            Order,
            orders,
            properties={"description": deferred(orders.c.description)},
        )

        sess = create_session()
        q = sess.query(Order).options(
            load_only("isopen", "description"), undefer("user_id")
        )
        self.assert_compile(
            q,
            "SELECT orders.description AS orders_description, "
            "orders.id AS orders_id, "
            "orders.user_id AS orders_user_id, "
            "orders.isopen AS orders_isopen FROM orders",
        ) 

def test_raise_on_col_rowproc_only(self):
        orders, Order = self.tables.orders, self.classes.Order

        mapper(
            Order,
            orders,
            properties={
                "description": deferred(orders.c.description, raiseload=True)
            },
        )

        sess = create_session()
        stmt = sa.select([Order]).order_by(Order.id)
        o1 = (sess.query(Order).from_statement(stmt).all())[0]

        assert_raises_message(
            sa.exc.InvalidRequestError,
            "'Order.description' is not available due to raiseload=True",
            getattr,
            o1,
            "description",
        ) 

def setup_mappers(cls):
        A, B = cls.classes("A", "B")
        a, b = cls.tables("a", "b")

        mapper(A, a)
        mapper(
            B,
            b,
            inherits=A,
            properties={
                "data": deferred(b.c.data),
                "expr": column_property(b.c.data + "q", deferred=True),
            },
        ) 

def add_geometry_column(cls):
        if not hasattr(cls, 'geom'):
            from geoalchemy2 import Geometry
            cls.geom = deferred(Column(Geometry('MULTILINESTRING'))) 

def test_state_deferred_to_col(self):
        """Behavioral test to verify the current activity of loader callables
        """

        users, User = self.tables.users, self.classes.User

        mapper(User, users, properties={"name": deferred(users.c.name)})

        sess = create_session()
        u1 = sess.query(User).options(undefer(User.name)).first()
        assert "name" not in attributes.instance_state(u1).callables

        # mass expire, the attribute was loaded,
        # the attribute gets the callable
        sess.expire(u1)
        assert "name" in attributes.instance_state(u1).expired_attributes
        assert "name" not in attributes.instance_state(u1).callables

        # load it
        u1.name
        assert "name" not in attributes.instance_state(u1).expired_attributes
        assert "name" not in attributes.instance_state(u1).callables

        # mass expire, attribute was loaded but then deleted,
        # the callable goes away - the state wants to flip
        # it back to its "deferred" loader.
        sess.expunge_all()
        u1 = sess.query(User).options(undefer(User.name)).first()
        del u1.name
        sess.expire(u1)
        assert "name" in attributes.instance_state(u1).expired_attributes
        assert "name" not in attributes.instance_state(u1).callables

        # single attribute expire, the attribute gets the callable
        sess.expunge_all()
        u1 = sess.query(User).options(undefer(User.name)).first()
        sess.expire(u1, ["name"])

        # the expire cancels the undefer
        assert "name" in attributes.instance_state(u1).expired_attributes
        assert "name" not in attributes.instance_state(u1).callables 

def test_deferred_expire_w_transient_to_detached(self):
        orders, Order = self.tables.orders, self.classes.Order
        mapper(
            Order,
            orders,
            properties={"description": deferred(orders.c.description)},
        )

        s = Session()
        item = Order(id=1)

        make_transient_to_detached(item)
        s.add(item)
        item.isopen
        assert "description" not in item.__dict__ 

def test_deferred_expire_normally(self):
        orders, Order = self.tables.orders, self.classes.Order
        mapper(
            Order,
            orders,
            properties={"description": deferred(orders.c.description)},
        )

        s = Session()

        item = s.query(Order).first()
        s.expire(item)
        item.isopen
        assert "description" not in item.__dict__ 

def setup_mappers(cls):
        mapper(cls.classes.Data, cls.tables.data)
        mapper(cls.classes.DataFetched, cls.tables.data_fetched)
        mapper(
            cls.classes.DataDefer,
            cls.tables.data_defer,
            properties={"data": deferred(cls.tables.data_defer.c.data)},
        ) 

def test_deferred_cols_missing_in_load_state_reset(self):
        Data = self.classes.DataDefer

        sess = create_session()

        d1 = Data(data="d1")
        sess.add(d1)
        sess.flush()
        sess.close()

        sess = create_session()
        d1 = (
            sess.query(Data)
            .from_statement(select([Data.id]))
            .options(undefer(Data.data))
            .first()
        )
        d1.data = "d2"

        # the deferred loader has to clear out any state
        # on the col, including that 'd2' here
        d1 = sess.query(Data).populate_existing().first()

        def go():
            eq_(d1.data, "d1")

        self.assert_sql_count(testing.db, go, 1) 

def _deferred_config_fixture(self):
        User, Address = self.classes.User, self.classes.Address
        mapper(
            User,
            self.tables.users,
            properties={
                "name": deferred(self.tables.users.c.name),
                "addresses": relationship(Address, lazy="subquery"),
            },
        )
        mapper(Address, self.tables.addresses)
        sess = Session(autoflush=False)
        return User, Address, sess 

def setup_mappers(cls):
        thing, human = cls.tables.thing, cls.tables.human

        class Human(cls.Basic):
            pass

        class Thing(cls.Basic):
            pass

        mapper(Human, human, properties={"thing": relationship(Thing)})
        mapper(Thing, thing, properties={"name": deferred(thing.c.name)}) 

def setup_classes(cls):
        class A(cls.DeclarativeBasic):
            __tablename__ = "a"
            id = Column(Integer, primary_key=True)
            unloaded = deferred(Column(String(50)))
            bs = relationship("B", lazy="joined")

        class B(cls.DeclarativeBasic):
            __tablename__ = "b"
            id = Column(Integer, primary_key=True)
            a_id = Column(ForeignKey("a.id")) 

def _deferred_config_fixture(self):
        User, Address = self.classes.User, self.classes.Address
        mapper(
            User,
            self.tables.users,
            properties={
                "name": deferred(self.tables.users.c.name),
                "addresses": relationship(Address, lazy="selectin"),
            },
        )
        mapper(Address, self.tables.addresses)
        sess = Session(autoflush=False)
        return User, Address, sess 

def test_use_join_parent_criteria_degrade_on_defer(self):
        A, B = self.classes("A", "B")
        s = Session()
        q = (
            s.query(A)
            .filter(A.id.in_([1, 3]))
            .options(defer(A.b_id), selectinload(A.b))
            .order_by(A.id)
        )
        results = self.assert_sql_execution(
            testing.db,
            q.all,
            CompiledSQL(
                "SELECT a.id AS a_id, a.q AS a_q "
                "FROM a WHERE a.id IN ([POSTCOMPILE_id_1]) ORDER BY a.id",
                [{"id_1": [1, 3]}],
            ),
            # in the very unlikely case that the the FK col on parent is
            # deferred, we degrade to the JOIN version so that we don't need to
            # emit either for each parent object individually, or as a second
            # query for them.
            CompiledSQL(
                "SELECT a_1.id AS a_1_id, b.id AS b_id, b.x AS b_x, "
                "b.y AS b_y "
                "FROM a AS a_1 JOIN b ON b.id = a_1.b_id "
                "WHERE a_1.id IN ([POSTCOMPILE_primary_keys])",
                [{"primary_keys": [1, 3]}],
            ),
        )

        eq_(
            results,
            [A(id=1, b=B(id=1, x=5, y=9)), A(id=3, b=B(id=2, x=10, y=8))],
        ) 

def test_use_join_parent_degrade_on_defer(self):
        A, B = self.classes("A", "B")
        s = Session()
        q = s.query(A).options(defer(A.b_id), selectinload(A.b)).order_by(A.id)
        results = self.assert_sql_execution(
            testing.db,
            q.all,
            CompiledSQL(
                "SELECT a.id AS a_id, a.q AS a_q " "FROM a ORDER BY a.id", [{}]
            ),
            # in the very unlikely case that the the FK col on parent is
            # deferred, we degrade to the JOIN version so that we don't need to
            # emit either for each parent object individually, or as a second
            # query for them.
            CompiledSQL(
                "SELECT a_1.id AS a_1_id, b.id AS b_id, b.x AS b_x, "
                "b.y AS b_y "
                "FROM a AS a_1 JOIN b ON b.id = a_1.b_id "
                "WHERE a_1.id IN ([POSTCOMPILE_primary_keys])",
                [{"primary_keys": [1, 2, 3, 4, 5]}],
            ),
        )

        b1, b2 = B(id=1, x=5, y=9), B(id=2, x=10, y=8)
        eq_(
            results,
            [
                A(id=1, b=b1),
                A(id=2, b=b2),
                A(id=3, b=b2),
                A(id=4, b=None),
                A(id=5, b=b1),
            ],
        ) 

def setup_classes(cls):
        Base = cls.DeclarativeBasic

        class A(Base):
            __tablename__ = "a"
            id = Column(Integer, primary_key=True)
            data = Column(String(30))
            deferred_data = deferred(Column(String(30))) 

def test_not_allowed(self):
        class MyMixin:
            foo = Column(Integer, ForeignKey("bar.id"))

        def go():
            class MyModel(Base, MyMixin):
                __tablename__ = "foo"

        assert_raises(sa.exc.InvalidRequestError, go)

        class MyRelMixin:
            foo = relationship("Bar")

        def go():
            class MyModel(Base, MyRelMixin):

                __tablename__ = "foo"

        assert_raises(sa.exc.InvalidRequestError, go)

        class MyDefMixin:
            foo = deferred(Column("foo", String))

        def go():
            class MyModel(Base, MyDefMixin):
                __tablename__ = "foo"

        assert_raises(sa.exc.InvalidRequestError, go)

        class MyCPropMixin:
            foo = column_property(Column("foo", String))

        def go():
            class MyModel(Base, MyCPropMixin):
                __tablename__ = "foo"

        assert_raises(sa.exc.InvalidRequestError, go) 

def test_deferred_reflection_default_error(self):
        class MyExt(object):
            @classmethod
            def prepare(cls):
                "sample prepare method"
                to_map = _DeferredMapperConfig.classes_for_base(cls)
                for thingy in to_map:
                    thingy.map()

            @classmethod
            def _sa_decl_prepare(cls):
                pass

        class User(MyExt, Base):
            __tablename__ = "user"
            id = Column(Integer, primary_key=True)

        assert_raises_message(
            orm_exc.UnmappedClassError,
            "Class test.ext.declarative.test_basic.User has a deferred "
            "mapping on it.  It is not yet usable as a mapped class.",
            Session().query,
            User,
        )

        User.prepare()

        self.assert_compile(
            Session().query(User), 'SELECT "user".id AS user_id FROM "user"'
        ) 

def test_add_property(self):
        users = self.tables.users

        assert_col = []

        class User(fixtures.ComparableEntity):
            def _get_name(self):
                assert_col.append(("get", self._name))
                return self._name

            def _set_name(self, name):
                assert_col.append(("set", name))
                self._name = name

            name = property(_get_name, _set_name)

        m = mapper(User, users)

        m.add_property("_name", deferred(users.c.name))
        m.add_property("name", synonym("_name"))

        sess = create_session(autocommit=False)
        assert sess.query(User).get(7)

        u = sess.query(User).filter_by(name="jack").one()

        def go():
            eq_(u.name, "jack")
            eq_(assert_col, [("get", "jack")], str(assert_col))

        self.sql_count_(1, go) 

def make_geom_lazy(cls):
        from sqlalchemy.orm import deferred
        try:
            cls.__mapper__.add_property('geom', deferred(cls.__table__.c.geom))
        except Exception as e:
            log.warning(e) 

def add_geometry_column(cls):
        if not hasattr(cls, 'geom'):
            cls.geom = deferred(Column(Geometry(geometry_type='LINESTRING', srid=config.SRID))) 

def test_deferred(self):
        """test versioning of unloaded, deferred columns."""

        class SomeClass(Versioned, self.Base, ComparableEntity):
            __tablename__ = "sometable"

            id = Column(Integer, primary_key=True)
            name = Column(String(50))
            data = deferred(Column(String(25)))

        self.create_tables()
        sess = self.session
        sc = SomeClass(name="sc1", data="somedata")
        sess.add(sc)
        sess.commit()
        sess.close()

        sc = sess.query(SomeClass).first()
        assert "data" not in sc.__dict__

        sc.name = "sc1modified"
        sess.commit()

        assert sc.version == 2

        SomeClassHistory = SomeClass.__history_mapper__.class_

        eq_(
            sess.query(SomeClassHistory)
            .filter(SomeClassHistory.version == 1)
            .all(),
            [SomeClassHistory(version=1, name="sc1", data="somedata")],
        )