Python sqlalchemy.orm.foreign() Examples

def _join_fixture_remote_local_multiple_ref(self, **kw):
        def fn(a, b):
            return (a == b) | (b == a)

        return relationships.JoinCondition(
            self.selfref,
            self.selfref,
            self.selfref,
            self.selfref,
            support_sync=False,
            primaryjoin=fn(
                # we're putting a do-nothing annotation on
                # "a" so that the left/right is preserved;
                # annotation vs. non seems to affect __eq__ behavior
                self.selfref.c.sid._annotate({"foo": "bar"}),
                foreign(remote(self.selfref.c.sid)),
            ),
        ) 

def test_write_cascade_disallowed_w_viewonly(self):

        Order = self.classes.Order

        assert_raises_message(
            sa_exc.ArgumentError,
            'Cascade settings "delete, delete-orphan, merge, save-update" '
            "apply to persistence operations",
            relationship,
            Order,
            primaryjoin=(
                self.tables.users.c.id == foreign(self.tables.orders.c.user_id)
            ),
            cascade="all, delete, delete-orphan",
            viewonly=True,
        ) 

def _assert_raises_no_equality(
        self, fn, expr, relname, primary, *arg, **kw
    ):
        assert_raises_message(
            sa.exc.ArgumentError,
            "Could not locate any simple equality expressions "
            "involving locally mapped foreign key columns for %s join "
            "condition '%s' on relationship %s.  "
            "Ensure that referencing columns are associated with a "
            "ForeignKey or ForeignKeyConstraint, or are annotated in "
            r"the join condition with the foreign\(\) annotation. "
            "To allow comparison operators other than '==', "
            "the relationship can be marked as viewonly=True."
            % (primary, expr, relname),
            fn,
            *arg,
            **kw
        ) 

def test_pj_deannotates(self):
        from sqlalchemy.ext.declarative import declarative_base

        Base = declarative_base()

        class A(Base):
            __tablename__ = "a"
            id = Column(Integer, primary_key=True)

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

        eq_(
            B.a.property.primaryjoin.left._annotations,
            {"parentmapper": A.__mapper__, "remote": True},
        )
        eq_(
            B.a.property.primaryjoin.right._annotations,
            {"foreign": True, "local": True, "parentmapper": B.__mapper__},
        ) 

def test_determine_join_ambiguous_fks_o2m(self):
        assert_raises_message(
            exc.AmbiguousForeignKeysError,
            "Could not determine join condition between "
            "parent/child tables on relationship None - "
            "there are multiple foreign key paths linking "
            "the tables.  Specify the 'foreign_keys' argument, "
            "providing a list of those columns which "
            "should be counted as containing a foreign "
            "key reference to the parent table.",
            relationships.JoinCondition,
            self.left,
            self.right_multi_fk,
            self.left,
            self.right_multi_fk,
        ) 

def _descendants_fixture(self, data=True):
        Entity = self.classes.Entity
        entity = self.tables.entity

        m = mapper(
            Entity,
            entity,
            properties={
                "descendants": relationship(
                    Entity,
                    primaryjoin=remote(foreign(entity.c.path)).like(
                        entity.c.path.concat("/%")
                    ),
                    viewonly=True,
                    order_by=entity.c.path,
                )
            },
        )
        configure_mappers()
        assert m.get_property("descendants").direction is ONETOMANY
        if data:
            return self._fixture() 

def _assert_raises_no_equality(
        self, fn, expr, relname, primary, *arg, **kw
    ):
        assert_raises_message(
            exc.ArgumentError,
            "Could not locate any simple equality expressions "
            "involving locally mapped foreign key columns for %s join "
            "condition '%s' on relationship %s.  "
            "Ensure that referencing columns are associated with a "
            "ForeignKey or ForeignKeyConstraint, or are annotated in "
            r"the join condition with the foreign\(\) annotation. "
            "To allow comparison operators other than '==', "
            "the relationship can be marked as viewonly=True."
            % (primary, expr, relname),
            fn,
            *arg,
            **kw
        ) 

def _anscestors_fixture(self, data=True):
        Entity = self.classes.Entity
        entity = self.tables.entity

        m = mapper(
            Entity,
            entity,
            properties={
                "anscestors": relationship(
                    Entity,
                    primaryjoin=entity.c.path.like(
                        remote(foreign(entity.c.path)).concat("/%")
                    ),
                    viewonly=True,
                    order_by=entity.c.path,
                )
            },
        )
        configure_mappers()
        assert m.get_property("anscestors").direction is ONETOMANY
        if data:
            return self._fixture() 

def _test_no_overwrite(self, sess, expect_failure):
        # test [ticket:3230]

        Employee, Company = self.classes.Employee, self.classes.Company

        c1 = sess.query(Company).filter_by(name="c1").one()
        e3 = sess.query(Employee).filter_by(name="emp3").one()
        e3.reports_to = None

        if expect_failure:
            # if foreign() isn't applied specifically to
            # employee_t.c.reports_to_id only, then
            # employee_t.c.company_id goes foreign as well and then
            # this happens
            assert_raises_message(
                AssertionError,
                "Dependency rule tried to blank-out primary key column "
                "'employee_t.company_id'",
                sess.flush,
            )
        else:
            sess.flush()
            eq_(e3.company, c1) 

def setup_classes(cls):
        Base = cls.DeclarativeBasic

        class Venue(Base):
            __tablename__ = "venue"
            id = Column(Integer, primary_key=True)
            name = Column(String)

            descendants = relationship(
                "Venue",
                primaryjoin=func.instr(
                    remote(foreign(name)), name + "/"
                ).as_comparison(1, 2)
                == 1,
                viewonly=True,
                order_by=name,
            ) 

def test_viewonly_warning(self, flag, value):
        Order = self.classes.Order

        with testing.expect_warnings(
            r"Setting %s on relationship\(\) while also setting "
            "viewonly=True does not make sense" % flag
        ):
            kw = {
                "viewonly": True,
                "primaryjoin": self.tables.users.c.id
                == foreign(self.tables.orders.c.user_id),
            }
            kw[flag] = value
            rel = relationship(Order, **kw)

            eq_(getattr(rel, flag), value) 

def setup_classes(cls):
        Base = cls.DeclarativeBasic

        class Network(fixtures.ComparableEntity, Base):
            __tablename__ = "network"

            id = Column(
                sa.Integer, primary_key=True, test_needs_autoincrement=True
            )
            ip_net_addr = Column(Integer)
            ip_broadcast_addr = Column(Integer)

            addresses = relationship(
                "Address",
                primaryjoin="remote(foreign(Address.ip_addr)).between("
                "Network.ip_net_addr,"
                "Network.ip_broadcast_addr)",
                viewonly=True,
            )

        class Address(fixtures.ComparableEntity, Base):
            __tablename__ = "address"

            ip_addr = Column(Integer, primary_key=True) 

def test_onetomany_funcfk_annotated(self):
        T2, T1, t2, t1 = (
            self.classes.T2,
            self.classes.T1,
            self.tables.t2,
            self.tables.t1,
        )

        # use annotation
        mapper(
            T1,
            t1,
            properties={
                "t2s": relationship(
                    T2,
                    primaryjoin=t1.c.id == foreign(sa.func.lower(t2.c.t1id)),
                )
            },
        )
        mapper(T2, t2)
        self._test_onetomany() 

def test_join_on_custom_op(self):
        class A(fixtures.BasicEntity):
            pass

        class B(fixtures.BasicEntity):
            pass

        mapper(
            A,
            self.tables.a,
            properties={
                "bs": relationship(
                    B,
                    primaryjoin=self.tables.a.c.foo.op(
                        "&*", is_comparison=True
                    )(foreign(self.tables.b.c.foo)),
                    viewonly=True,
                )
            },
        )
        mapper(B, self.tables.b)
        self.assert_compile(
            Session().query(A).join(A.bs),
            "SELECT a.id AS a_id, a.foo AS a_foo "
            "FROM a JOIN b ON a.foo &* b.foo",
        ) 

def test_annotated_no_overwriting(self):
        Employee, Company, employee_t, company_t = (
            self.classes.Employee,
            self.classes.Company,
            self.tables.employee_t,
            self.tables.company_t,
        )

        mapper(Company, company_t)
        mapper(
            Employee,
            employee_t,
            properties={
                "company": relationship(Company, backref="employees"),
                "reports_to": relationship(
                    Employee,
                    primaryjoin=sa.and_(
                        remote(employee_t.c.emp_id)
                        == foreign(employee_t.c.reports_to_id),
                        remote(employee_t.c.company_id)
                        == employee_t.c.company_id,
                    ),
                    backref=backref("employees"),
                ),
            },
        )

        self._assert_lazy_clauses()
        self._test_no_warning() 

def test_write_cascades(self, setting, settings_that_warn):
        Order = self.classes.Order

        assert_raises_message(
            sa_exc.ArgumentError,
            'Cascade settings "%s" apply to persistence '
            "operations" % (", ".join(sorted(settings_that_warn))),
            relationship,
            Order,
            primaryjoin=(
                self.tables.users.c.id == foreign(self.tables.orders.c.user_id)
            ),
            cascade=", ".join(sorted(setting)),
            viewonly=True,
        ) 

def test_with_annotated_pj_o2m(self):
        A, B = self.classes.A, self.classes.B
        a, b = self.tables.a, self.tables.b
        mapper(
            A,
            a,
            properties={
                "bs": relationship(B, primaryjoin=a.c.id == foreign(b.c.aid_1))
            },
        )
        mapper(B, b)
        sa.orm.configure_mappers()
        assert A.bs.property.primaryjoin.compare(a.c.id == b.c.aid_1)
        eq_(A.bs.property._calculated_foreign_keys, set([b.c.aid_1])) 

def _resolver(cls, prop):
    import sqlalchemy
    from sqlalchemy.orm import foreign, remote

    fallback = sqlalchemy.__dict__.copy()
    fallback.update({'foreign': foreign, 'remote': remote})

    def resolve_arg(arg):
        return _class_resolver(cls, prop, fallback, arg)
    return resolve_arg 

def _assert_raises_ambiguous_direction(self, fn, relname, *arg, **kw):
        assert_raises_message(
            sa.exc.ArgumentError,
            "Can't determine relationship"
            " direction for relationship '%s' - foreign "
            "key columns within the join condition are present "
            "in both the parent and the child's mapped tables.  "
            "Ensure that only those columns referring to a parent column "
            r"are marked as foreign, either via the foreign\(\) annotation or "
            "via the foreign_keys argument." % relname,
            fn,
            *arg,
            **kw
        ) 

def _assert_raises_ambig_join(
        self, fn, relname, secondary_arg, *arg, **kw
    ):
        if secondary_arg is not None:
            assert_raises_message(
                exc.ArgumentError,
                "Could not determine join condition between "
                "parent/child tables on relationship %s - "
                "there are multiple foreign key paths linking the "
                "tables via secondary table '%s'.  "
                "Specify the 'foreign_keys' argument, providing a list "
                "of those columns which should be counted as "
                "containing a foreign key reference from the "
                "secondary table to each of the parent and child tables."
                % (relname, secondary_arg),
                fn,
                *arg,
                **kw
            )
        else:
            assert_raises_message(
                exc.ArgumentError,
                "Could not determine join "
                "condition between parent/child tables on "
                "relationship %s - there are multiple foreign key "
                "paths linking the tables.  Specify the "
                "'foreign_keys' argument, providing a list of those "
                "columns which should be counted as containing a "
                "foreign key reference to the parent table." % (relname,),
                fn,
                *arg,
                **kw
            ) 

def _resolver(cls, prop):
    import sqlalchemy
    from sqlalchemy.orm import foreign, remote

    fallback = sqlalchemy.__dict__.copy()
    fallback.update({'foreign': foreign, 'remote': remote})

    def resolve_arg(arg):
        return _class_resolver(cls, prop, fallback, arg)
    return resolve_arg 

def _assert_raises_no_relevant_fks(
        self, fn, expr, relname, primary, *arg, **kw
    ):
        assert_raises_message(
            sa.exc.ArgumentError,
            "Could not locate any relevant foreign key columns "
            "for %s join condition '%s' on relationship %s.  "
            "Ensure that referencing columns are associated with "
            "a ForeignKey or ForeignKeyConstraint, or are annotated "
            r"in the join condition with the foreign\(\) annotation."
            % (primary, expr, relname),
            fn,
            *arg,
            **kw
        ) 

def test_dont_send_neverset_to_get_w_relationship(self):
        # test issue #3647
        CompositePk, composite_pk_table = (
            self.classes.CompositePk,
            self.tables.composite_pk_table,
        )
        User, users = (self.classes.User, self.tables.users)
        mapper(
            User,
            users,
            properties={
                "elements": relationship(
                    CompositePk,
                    primaryjoin=users.c.id == foreign(composite_pk_table.c.i),
                )
            },
        )
        mapper(CompositePk, composite_pk_table)

        u1 = User(id=5, name="some user")
        cp1 = CompositePk(j=1, k=1)
        u1.elements.append(cp1)
        sess = Session()

        rec = []

        def go():
            rec.append(sess.merge(u1))

        self.assert_sql_count(testing.db, go, 1)
        u2 = rec[0]
        sess.commit()
        eq_(u2.elements[0].i, 5)
        eq_(u2.id, 5) 

def _resolver(cls, prop):
    import sqlalchemy
    from sqlalchemy.orm import foreign, remote

    fallback = sqlalchemy.__dict__.copy()
    fallback.update({'foreign': foreign, 'remote': remote})

    def resolve_arg(arg):
        return _class_resolver(cls, prop, fallback, arg)
    return resolve_arg 

def _join_fixture_purely_single_m2o(self, **kw):
        return relationships.JoinCondition(
            self.purely_single_col,
            self.purely_single_col,
            self.purely_single_col,
            self.purely_single_col,
            support_sync=False,
            primaryjoin=remote(self.purely_single_col.c.path).like(
                foreign(self.purely_single_col.c.path.concat("%"))
            ),
        ) 

def _resolver(cls, prop):
    import sqlalchemy
    from sqlalchemy.orm import foreign, remote

    fallback = sqlalchemy.__dict__.copy()
    fallback.update({'foreign': foreign, 'remote': remote})

    def resolve_arg(arg):
        return _class_resolver(cls, prop, fallback, arg)
    return resolve_arg 

def _resolver(cls, prop):
    import sqlalchemy
    from sqlalchemy.orm import foreign, remote

    fallback = sqlalchemy.__dict__.copy()
    fallback.update({'foreign': foreign, 'remote': remote})

    def resolve_arg(arg):
        return _class_resolver(cls, prop, fallback, arg)
    return resolve_arg 

def _resolver(cls, prop):
    import sqlalchemy
    from sqlalchemy.orm import foreign, remote

    fallback = sqlalchemy.__dict__.copy()
    fallback.update({'foreign': foreign, 'remote': remote})

    def resolve_arg(arg):
        return _class_resolver(cls, prop, fallback, arg)
    return resolve_arg 

def _resolver(cls, prop):
    import sqlalchemy
    from sqlalchemy.orm import foreign, remote

    fallback = sqlalchemy.__dict__.copy()
    fallback.update({'foreign': foreign, 'remote': remote})

    def resolve_arg(arg):
        return _class_resolver(cls, prop, fallback, arg)
    return resolve_arg 

def setup_listener(mapper, class_):
    name = class_.__name__
    discriminator = name.lower()
    class_.addresses = relationship(
        Address,
        primaryjoin=and_(
            class_.id == foreign(remote(Address.parent_id)),
            Address.discriminator == discriminator,
        ),
        backref=backref(
            "parent_%s" % discriminator,
            primaryjoin=remote(class_.id) == foreign(Address.parent_id),
        ),
    )

    @event.listens_for(class_.addresses, "append")
    def append_address(target, value, initiator):
        value.discriminator = discriminator