Python sqlalchemy.outerjoin方法代碼示例

# 需要導入模塊: import sqlalchemy [as 別名]
# 或者: from sqlalchemy import outerjoin [as 別名]
def _create_outerjoin(cls, left, right, onclause=None):
        """Return an ``OUTER JOIN`` clause element.

        The returned object is an instance of :class:`.Join`.

        Similar functionality is also available via the
        :meth:`~.FromClause.outerjoin()` method on any
        :class:`.FromClause`.

        :param left: The left side of the join.

        :param right: The right side of the join.

        :param onclause:  Optional criterion for the ``ON`` clause, is
          derived from foreign key relationships established between
          left and right otherwise.

        To chain joins together, use the :meth:`.FromClause.join` or
        :meth:`.FromClause.outerjoin` methods on the resulting
        :class:`.Join` object.

        """
        return cls(left, right, onclause, isouter=True) 

# 需要導入模塊: import sqlalchemy [as 別名]
# 或者: from sqlalchemy import outerjoin [as 別名]
def _create_outerjoin(cls, left, right, onclause=None, full=False):
        """Return an ``OUTER JOIN`` clause element.

        The returned object is an instance of :class:`.Join`.

        Similar functionality is also available via the
        :meth:`~.FromClause.outerjoin()` method on any
        :class:`.FromClause`.

        :param left: The left side of the join.

        :param right: The right side of the join.

        :param onclause:  Optional criterion for the ``ON`` clause, is
          derived from foreign key relationships established between
          left and right otherwise.

        To chain joins together, use the :meth:`.FromClause.join` or
        :meth:`.FromClause.outerjoin` methods on the resulting
        :class:`.Join` object.

        """
        return cls(left, right, onclause, isouter=True, full=full) 

# 需要導入模塊: import sqlalchemy [as 別名]
# 或者: from sqlalchemy import outerjoin [as 別名]
def _create_outerjoin(cls, left, right, onclause=None, full=False):
        """Return an ``OUTER JOIN`` clause element.

        The returned object is an instance of :class:`_expression.Join`.

        Similar functionality is also available via the
        :meth:`_expression.FromClause.outerjoin` method on any
        :class:`_expression.FromClause`.

        :param left: The left side of the join.

        :param right: The right side of the join.

        :param onclause:  Optional criterion for the ``ON`` clause, is
          derived from foreign key relationships established between
          left and right otherwise.

        To chain joins together, use the :meth:`_expression.FromClause.join`
        or
        :meth:`_expression.FromClause.outerjoin` methods on the resulting
        :class:`_expression.Join` object.

        """
        return cls(left, right, onclause, isouter=True, full=full) 

# 需要導入模塊: import sqlalchemy [as 別名]
# 或者: from sqlalchemy import outerjoin [as 別名]
def test_dslish(self):
        """test the same as withjoinedload except using generative"""

        Thing, tests, options = (
            self.classes.Thing,
            self.tables.tests,
            self.tables.options,
        )

        s = create_session()
        q = s.query(Thing).options(sa.orm.joinedload("category"))
        result = q.filter(
            sa.and_(
                tests.c.owner_id == 1,
                sa.or_(
                    options.c.someoption == None,
                    options.c.someoption == False,  # noqa
                ),
            )
        ).outerjoin("owner_option")

        result_str = ["%d %s" % (t.id, t.category.name) for t in result]
        eq_(result_str, ["1 Some Category", "3 Some Category"]) 

# 需要導入模塊: import sqlalchemy [as 別名]
# 或者: from sqlalchemy import outerjoin [as 別名]
def test_nested_joins(self):
        task, Task_Type, Joined, task_type, msg = (
            self.tables.task,
            self.classes.Task_Type,
            self.classes.Joined,
            self.tables.task_type,
            self.tables.msg,
        )

        # this is testing some subtle column resolution stuff,
        # concerning corresponding_column() being extremely accurate
        # as well as how mapper sets up its column properties

        mapper(Task_Type, task_type)

        j = sa.outerjoin(task, msg, task.c.id == msg.c.task_id)
        jj = sa.select(
            [
                task.c.id.label("task_id"),
                sa.func.count(msg.c.id).label("props_cnt"),
            ],
            from_obj=[j],
            group_by=[task.c.id],
        ).alias("prop_c_s")
        jjj = sa.join(task, jj, task.c.id == jj.c.task_id)

        mapper(
            Joined,
            jjj,
            properties=dict(type=relationship(Task_Type, lazy="joined")),
        )

        session = create_session()

        eq_(
            session.query(Joined).limit(10).offset(0).one(),
            Joined(id=1, title="task 1", props_cnt=0),
        ) 

# 需要導入模塊: import sqlalchemy [as 別名]
# 或者: from sqlalchemy import outerjoin [as 別名]
def outerjoin(self, right, onclause=None):
        """Return a :class:`.Join` from this :class:`.FromClause`
        to another :class:`FromClause`, with the "isouter" flag set to
        True.

        E.g.::

            from sqlalchemy import outerjoin

            j = user_table.outerjoin(address_table,
                            user_table.c.id == address_table.c.user_id)

        The above is equivalent to::

            j = user_table.join(
                address_table,
                user_table.c.id == address_table.c.user_id,
                isouter=True)

        :param right: the right side of the join; this is any
         :class:`.FromClause` object such as a :class:`.Table` object, and
         may also be a selectable-compatible object such as an ORM-mapped
         class.

        :param onclause: a SQL expression representing the ON clause of the
         join.  If left at ``None``, :meth:`.FromClause.join` will attempt to
         join the two tables based on a foreign key relationship.

        .. seealso::

            :meth:`.FromClause.join`

            :class:`.Join`

        """

        return Join(self, right, onclause, True) 

# 需要導入模塊: import sqlalchemy [as 別名]
# 或者: from sqlalchemy import outerjoin [as 別名]
def outerjoin(self, *arg, **kw):
        return self._implicit_subquery.outerjoin(*arg, **kw) 

# 需要導入模塊: import sqlalchemy [as 別名]
# 或者: from sqlalchemy import outerjoin [as 別名]
def test_join_against_join(self):
        j = outerjoin(table1, table2, table1.c.col1 == table2.c.col2)
        jj = select([table1.c.col1.label("bar_col1")], from_obj=[j]).alias(
            "foo"
        )
        jjj = join(table1, jj, table1.c.col1 == jj.c.bar_col1)
        assert jjj.corresponding_column(jjj.c.table1_col1) is jjj.c.table1_col1
        j2 = jjj.alias("foo")
        assert j2.corresponding_column(jjj.c.table1_col1) is j2.c.table1_col1
        assert jjj.corresponding_column(jj.c.bar_col1) is jj.c.bar_col1 

# 需要導入模塊: import sqlalchemy [as 別名]
# 或者: from sqlalchemy import outerjoin [as 別名]
def test_withoutjoinedload(self):
        Thing, tests, options = (
            self.classes.Thing,
            self.tables.tests,
            self.tables.options,
        )

        s = create_session()
        result = (
            s.query(Thing)
            .select_from(
                tests.outerjoin(
                    options,
                    sa.and_(
                        tests.c.id == options.c.test_id,
                        tests.c.owner_id == options.c.owner_id,
                    ),
                )
            )
            .filter(
                sa.and_(
                    tests.c.owner_id == 1,
                    sa.or_(
                        options.c.someoption == None,  # noqa
                        options.c.someoption == False,
                    ),
                )
            )
        )

        result_str = ["%d %s" % (t.id, t.category.name) for t in result]
        eq_(result_str, ["1 Some Category", "3 Some Category"]) 

# 需要導入模塊: import sqlalchemy [as 別名]
# 或者: from sqlalchemy import outerjoin [as 別名]
def test_withjoinedload(self):
        """
        Test that an joinedload locates the correct "from" clause with which to
        attach to, when presented with a query that already has a complicated
        from clause.

        """

        Thing, tests, options = (
            self.classes.Thing,
            self.tables.tests,
            self.tables.options,
        )

        s = create_session()
        q = s.query(Thing).options(sa.orm.joinedload("category"))

        result = q.select_from(
            tests.outerjoin(
                options,
                sa.and_(
                    tests.c.id == options.c.test_id,
                    tests.c.owner_id == options.c.owner_id,
                ),
            )
        ).filter(
            sa.and_(
                tests.c.owner_id == 1,
                sa.or_(
                    options.c.someoption == None,
                    options.c.someoption == False,  # noqa
                ),
            )
        )

        result_str = ["%d %s" % (t.id, t.category.name) for t in result]
        eq_(result_str, ["1 Some Category", "3 Some Category"]) 

# 需要導入模塊: import sqlalchemy [as 別名]
# 或者: from sqlalchemy import outerjoin [as 別名]
def outerjoin(self, right, onclause=None):
        """Return a :class:`.Join` from this :class:`.FromClause`
        to another :class:`FromClause`, with the "isouter" flag set to
        True.

        E.g.::

            from sqlalchemy import outerjoin

            j = user_table.outerjoin(address_table,
                            user_table.c.id == address_table.c.user_id)

        The above is equivalent to::

            j = user_table.join(address_table,
                            user_table.c.id == address_table.c.user_id, isouter=True)

        :param right: the right side of the join; this is any :class:`.FromClause`
         object such as a :class:`.Table` object, and may also be a selectable-compatible
         object such as an ORM-mapped class.

        :param onclause: a SQL expression representing the ON clause of the
         join.  If left at ``None``, :meth:`.FromClause.join` will attempt to
         join the two tables based on a foreign key relationship.

        .. seealso::

            :meth:`.FromClause.join`

            :class:`.Join`

        """

        return Join(self, right, onclause, True) 

# 需要導入模塊: import sqlalchemy [as 別名]
# 或者: from sqlalchemy import outerjoin [as 別名]
def outerjoin(self, right, onclause=None, full=False):
        """Return a :class:`.Join` from this :class:`.FromClause`
        to another :class:`FromClause`, with the "isouter" flag set to
        True.

        E.g.::

            from sqlalchemy import outerjoin

            j = user_table.outerjoin(address_table,
                            user_table.c.id == address_table.c.user_id)

        The above is equivalent to::

            j = user_table.join(
                address_table,
                user_table.c.id == address_table.c.user_id,
                isouter=True)

        :param right: the right side of the join; this is any
         :class:`.FromClause` object such as a :class:`.Table` object, and
         may also be a selectable-compatible object such as an ORM-mapped
         class.

        :param onclause: a SQL expression representing the ON clause of the
         join.  If left at ``None``, :meth:`.FromClause.join` will attempt to
         join the two tables based on a foreign key relationship.

        :param full: if True, render a FULL OUTER JOIN, instead of
         LEFT OUTER JOIN.

         .. versionadded:: 1.1

        .. seealso::

            :meth:`.FromClause.join`

            :class:`.Join`

        """

        return Join(self, right, onclause, True, full) 

# 需要導入模塊: import sqlalchemy [as 別名]
# 或者: from sqlalchemy import outerjoin [as 別名]
def test_reduce_aliased_join(self):
        metadata = MetaData()
        people = Table(
            "people",
            metadata,
            Column(
                "person_id",
                Integer,
                Sequence("person_id_seq", optional=True),
                primary_key=True,
            ),
            Column("name", String(50)),
            Column("type", String(30)),
        )
        engineers = Table(
            "engineers",
            metadata,
            Column(
                "person_id",
                Integer,
                ForeignKey("people.person_id"),
                primary_key=True,
            ),
            Column("status", String(30)),
            Column("engineer_name", String(50)),
            Column("primary_language", String(50)),
        )
        managers = Table(
            "managers",
            metadata,
            Column(
                "person_id",
                Integer,
                ForeignKey("people.person_id"),
                primary_key=True,
            ),
            Column("status", String(30)),
            Column("manager_name", String(50)),
        )
        pjoin = (
            people.outerjoin(engineers)
            .outerjoin(managers)
            .select()
            .apply_labels()
            .alias("pjoin")
        )
        eq_(
            util.column_set(
                sql_util.reduce_columns(
                    [
                        pjoin.c.people_person_id,
                        pjoin.c.engineers_person_id,
                        pjoin.c.managers_person_id,
                    ]
                )
            ),
            util.column_set([pjoin.c.people_person_id]),
        )