Python sqlalchemy.outerjoin() Examples

The following are 25 code examples for showing how to use sqlalchemy.outerjoin(). These examples are extracted from open source projects. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example.

You may check out the related API usage on the sidebar.

You may also want to check out all available functions/classes of the module sqlalchemy , or try the search function .

Example 1
Project: jbox   Author: jpush   File: selectable.py    License: MIT License 6 votes vote down vote up
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) 
Example 2
Project: Fluid-Designer   Author: Microvellum   File: selectable.py    License: GNU General Public License v3.0 6 votes vote down vote up
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) 
Example 3
Project: planespotter   Author: yfauser   File: selectable.py    License: MIT License 6 votes vote down vote up
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) 
Example 4
Project: pyRevit   Author: eirannejad   File: selectable.py    License: GNU General Public License v3.0 6 votes vote down vote up
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) 
Example 5
Project: stdm   Author: gltn   File: selectable.py    License: GNU General Public License v2.0 6 votes vote down vote up
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) 
Example 6
Project: sqlalchemy   Author: sqlalchemy   File: selectable.py    License: MIT License 6 votes vote down vote up
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) 
Example 7
Project: sqlalchemy   Author: sqlalchemy   File: test_assorted_eager.py    License: MIT License 6 votes vote down vote up
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"]) 
Example 8
Project: jarvis   Author: haynieresearch   File: selectable.py    License: GNU General Public License v2.0 6 votes vote down vote up
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) 
Example 9
Project: moviegrabber   Author: binhex   File: selectable.py    License: GNU General Public License v3.0 6 votes vote down vote up
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) 
Example 10
Project: android_universal   Author: bkerler   File: selectable.py    License: MIT License 6 votes vote down vote up
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) 
Example 11
Project: jbox   Author: jpush   File: selectable.py    License: MIT License 5 votes vote down vote up
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) 
Example 12
Project: Fluid-Designer   Author: Microvellum   File: selectable.py    License: GNU General Public License v3.0 5 votes vote down vote up
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) 
Example 13
Project: stdm   Author: gltn   File: selectable.py    License: GNU General Public License v2.0 5 votes vote down vote up
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) 
Example 14
Project: sqlalchemy   Author: sqlalchemy   File: selectable.py    License: MIT License 5 votes vote down vote up
def outerjoin(self, *arg, **kw):
        return self._implicit_subquery.outerjoin(*arg, **kw) 
Example 15
Project: sqlalchemy   Author: sqlalchemy   File: test_selectable.py    License: MIT License 5 votes vote down vote up
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 
Example 16
Project: sqlalchemy   Author: sqlalchemy   File: test_assorted_eager.py    License: MIT License 5 votes vote down vote up
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"]) 
Example 17
Project: sqlalchemy   Author: sqlalchemy   File: test_assorted_eager.py    License: MIT License 5 votes vote down vote up
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"]) 
Example 18
Project: sqlalchemy   Author: sqlalchemy   File: test_assorted_eager.py    License: MIT License 5 votes vote down vote up
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),
        ) 
Example 19
Project: moviegrabber   Author: binhex   File: selectable.py    License: GNU General Public License v3.0 5 votes vote down vote up
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) 
Example 20
Project: planespotter   Author: yfauser   File: selectable.py    License: MIT License 4 votes vote down vote up
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) 
Example 21
Project: pyRevit   Author: eirannejad   File: selectable.py    License: GNU General Public License v3.0 4 votes vote down vote up
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) 
Example 22
Project: sqlalchemy   Author: sqlalchemy   File: selectable.py    License: MIT License 4 votes vote down vote up
def outerjoin(self, right, onclause=None, full=False):
        """Return a :class:`_expression.Join` from this
        :class:`_expression.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:`_expression.FromClause` object such as a
         :class:`_schema.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:`_expression.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:`_expression.FromClause.join`

            :class:`_expression.Join`

        """

        return Join(self, right, onclause, True, full) 
Example 23
Project: sqlalchemy   Author: sqlalchemy   File: test_selectable.py    License: MIT License 4 votes vote down vote up
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]),
        ) 
Example 24
Project: jarvis   Author: haynieresearch   File: selectable.py    License: GNU General Public License v2.0 4 votes vote down vote up
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) 
Example 25
Project: android_universal   Author: bkerler   File: selectable.py    License: MIT License 4 votes vote down vote up
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)