Python sqlalchemy.union_all() Examples

The following are 5 code examples of sqlalchemy.union_all(). 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 also want to check out all available functions/classes of the module sqlalchemy , or try the search function .
Example #1
Source File: test_query.py    From sqlalchemy with MIT License 6 votes vote down vote up 
def test_union_all_lightweight(self, connection):
        """like test_union_all, but breaks the sub-union into
        a subquery with an explicit column reference on the outside,
        more palatable to a wider variety of engines.

        """

        u = union(select([t1.c.col3]), select([t1.c.col3])).alias()

        e = union_all(select([t1.c.col3]), select([u.c.col3]))

        wanted = [("aaa",), ("aaa",), ("bbb",), ("bbb",), ("ccc",), ("ccc",)]
        found1 = self._fetchall_sorted(connection.execute(e))
        eq_(found1, wanted)

        found2 = self._fetchall_sorted(
            connection.execute(e.alias("foo").select())
        )
        eq_(found2, wanted)
Example #2
Source File: alchemy.py    From ibis with Apache License 2.0 5 votes vote down vote up 
def compile(self):
        def reduce_union(left, right, distincts=iter(self.distincts)):
            distinct = next(distincts)
            sa_func = sa.union if distinct else sa.union_all
            return sa_func(left, right)

        context = self.context
        selects = []

        for table in self.tables:
            table_set = context.get_compiled_expr(table)
            selects.append(table_set.cte().select())

        return functools.reduce(reduce_union, selects)
Example #3
Source File: test_query.py    From sqlalchemy with MIT License 5 votes vote down vote up 
def test_union_all(self, connection):
        e = union_all(
            select([t1.c.col3]),
            union(select([t1.c.col3]), select([t1.c.col3])),
        )

        wanted = [("aaa",), ("aaa",), ("bbb",), ("bbb",), ("ccc",), ("ccc",)]
        found1 = self._fetchall_sorted(connection.execute(e))
        eq_(found1, wanted)

        found2 = self._fetchall_sorted(
            connection.execute(e.alias("foo").select())
        )
        eq_(found2, wanted)
Example #4
Source File: test_eager_relations.py    From sqlalchemy with MIT License 4 votes vote down vote up 
def test_distinct(self):
        Address, addresses, users, User = (
            self.classes.Address,
            self.tables.addresses,
            self.tables.users,
            self.classes.User,
        )

        # this is an involved 3x union of the users table to get a lot of rows.
        # then see if the "distinct" works its way out.  you actually get
        # the same result with or without the distinct, just via less or
        # more rows.
        u2 = users.alias("u2")
        s = sa.union_all(
            u2.select(use_labels=True),
            u2.select(use_labels=True),
            u2.select(use_labels=True),
        ).alias("u")

        mapper(
            User,
            users,
            properties={
                "addresses": relationship(
                    mapper(Address, addresses),
                    lazy="joined",
                    order_by=addresses.c.id,
                )
            },
        )

        sess = create_session()
        q = sess.query(User)

        def go():
            result = (
                q.filter(s.c.u2_id == User.id)
                .distinct()
                .order_by(User.id)
                .all()
            )
            eq_(self.static.user_address_result, result)

        self.assert_sql_count(testing.db, go, 1)
Example #5
Source File: test_lazy_relations.py    From sqlalchemy with MIT License 4 votes vote down vote up 
def test_distinct(self):
        (
            users,
            items,
            order_items,
            orders,
            Item,
            User,
            Address,
            Order,
            addresses,
        ) = (
            self.tables.users,
            self.tables.items,
            self.tables.order_items,
            self.tables.orders,
            self.classes.Item,
            self.classes.User,
            self.classes.Address,
            self.classes.Order,
            self.tables.addresses,
        )

        mapper(Item, items)
        mapper(
            Order,
            orders,
            properties={
                "items": relationship(
                    Item, secondary=order_items, lazy="select"
                )
            },
        )
        mapper(
            User,
            users,
            properties={
                "addresses": relationship(
                    mapper(Address, addresses), lazy="select"
                ),
                "orders": relationship(Order, lazy="select"),
            },
        )

        sess = create_session()
        q = sess.query(User)

        # use a union all to get a lot of rows to join against
        u2 = users.alias("u2")
        s = sa.union_all(
            u2.select(use_labels=True),
            u2.select(use_labels=True),
            u2.select(use_labels=True),
        ).alias("u")
        result = (
            q.filter(s.c.u2_id == User.id).order_by(User.id).distinct().all()
        )
        eq_(self.static.user_all_result, result)