Python sqlalchemy.union() Examples

def test_intersect_unions_2(self, connection):
        u = intersect(
            union(
                select([t1.c.col3, t1.c.col4]), select([t3.c.col3, t3.c.col4])
            )
            .alias()
            .select(),
            union(
                select([t2.c.col3, t2.c.col4]), select([t3.c.col3, t3.c.col4])
            )
            .alias()
            .select(),
        )
        wanted = [("aaa", "ccc"), ("bbb", "aaa"), ("ccc", "bbb")]
        found = self._fetchall_sorted(connection.execute(u))

        eq_(found, wanted) 

def test_union_literal_expressions_compile(self):
        """test that column expressions translate during
            the _from_statement() portion of union(), others"""

        User = self.classes.User

        s = Session()
        q1 = s.query(User, literal("x"))
        q2 = s.query(User, literal_column("'y'"))
        q3 = q1.union(q2)

        self.assert_compile(
            q3,
            "SELECT anon_1.users_id AS anon_1_users_id, "
            "anon_1.users_name AS anon_1_users_name, "
            "anon_1.anon_2 AS anon_1_anon_2 FROM "
            "(SELECT users.id AS users_id, users.name AS users_name, "
            ":param_1 AS anon_2 FROM users "
            "UNION SELECT users.id AS users_id, users.name AS users_name, "
            "'y' FROM users) AS anon_1",
        ) 

def activity_conversion(s, source_ids, null):
    # convert the query that gives any source id and select either those that are activities directly,
    # or activities associated with a topic (eg user entered activity notes)

    # for most queries, we have some source IDs and we want to know if they are activityjournal ids
    # (which we pass through) or activitytopicjournal ids (in which case we convert to activityjournal).
    source_ids = source_ids.cte()
    q_direct = s.query(ActivityJournal.id). \
        filter(ActivityJournal.id.in_(source_ids))
    q_via_topic = s.query(ActivityJournal.id). \
        join(FileHash). \
        join(ActivityTopicJournal). \
        filter(ActivityTopicJournal.id.in_(source_ids))
    q = aliased(union(q_direct, q_via_topic)).select()

    if null:
        # for 'is null' queries we are really asking if the data are missing (since values are not null constrained)
        # so we find what does exist and then invert it.  that inversion has to happen avter conversion
        return s.query(ActivityJournal.id).filter(not_(ActivityJournal.id.in_(q)))
    else:
        return q 

def test_union_mapped_colnames_preserved_across_subquery(self):
        User = self.classes.User

        s = Session()
        q1 = s.query(User.name)
        q2 = s.query(User.name)

        # the label names in the subquery are the typical anonymized ones
        self.assert_compile(
            q1.union(q2),
            "SELECT anon_1.users_name AS anon_1_users_name "
            "FROM (SELECT users.name AS users_name FROM users "
            "UNION SELECT users.name AS users_name FROM users) AS anon_1",
        )

        # but in the returned named tuples,
        # due to [ticket:1942], this should be 'name', not 'users_name'
        eq_([x["name"] for x in q1.union(q2).column_descriptions], ["name"]) 

def test_eager_load(self):
        User, Address = self.classes.User, self.classes.Address

        s = create_session()

        fred = s.query(User).filter(User.name == "fred")
        ed = s.query(User).filter(User.name == "ed")

        def go():
            eq_(
                fred.union(ed)
                .order_by(User.name)
                .options(joinedload(User.addresses))
                .all(),
                [
                    User(
                        name="ed", addresses=[Address(), Address(), Address()]
                    ),
                    User(name="fred", addresses=[Address()]),
                ],
            )

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

def test_unique_binds_union(self):
        """bindparams used in the 'parent' query are unique"""
        User, Address = self.classes.User, self.classes.Address

        sess = Session()
        u1, u2 = sess.query(User).order_by(User.id)[0:2]

        q1 = sess.query(Address).with_parent(u1, "addresses")
        q2 = sess.query(Address).with_parent(u2, "addresses")

        self.assert_compile(
            q1.union(q2),
            "SELECT anon_1.addresses_id AS anon_1_addresses_id, "
            "anon_1.addresses_user_id AS anon_1_addresses_user_id, "
            "anon_1.addresses_email_address AS "
            "anon_1_addresses_email_address FROM (SELECT addresses.id AS "
            "addresses_id, addresses.user_id AS addresses_user_id, "
            "addresses.email_address AS addresses_email_address FROM "
            "addresses WHERE :param_1 = addresses.user_id UNION SELECT "
            "addresses.id AS addresses_id, addresses.user_id AS "
            "addresses_user_id, addresses.email_address "
            "AS addresses_email_address "
            "FROM addresses WHERE :param_2 = addresses.user_id) AS anon_1",
            checkparams={"param_1": 7, "param_2": 8},
        ) 

def build_comparisons(s, ast, with_conversion):
    qname, op, value = ast
    owner, name, group = StatisticName.parse(qname, default_activity_group=UNDEF)
    if value is None:
        if op == '=':
            return get_source_ids_for_null(s, owner, name, group, with_conversion), True
        else:
            return aliased(union(*[get_source_ids(s, owner, name, op, value, group, type)
                                   for type in StatisticJournalType
                                   if type != StatisticJournalType.STATISTIC])).select(), False
    elif isinstance(value, str):
        return get_source_ids(s, owner, name, op, value, group, StatisticJournalType.TEXT), False
    elif isinstance(value, dt.datetime):
        return get_source_ids(s, owner, name, op, value, group, StatisticJournalType.TIMESTAMP), False
    else:
        qint = get_source_ids(s, owner, name, op, value, group, StatisticJournalType.INTEGER)
        qfloat = get_source_ids(s, owner, name, op, value, group, StatisticJournalType.FLOAT)
        return aliased(union(qint, qfloat)).select(), False 

def test_chained_node(self):
        '''
        we can move one step along the chain by looking for nodes whose inputs will be deleted.
        '''
        q_counts = self.session.query(Node.id.label('id'), count(Connect.input_id).label('count')). \
            outerjoin(Connect, Node.id == Connect.output_id). \
            group_by(Node.id).order_by(Node.id).subquery()
        q_missing = self.session.query(Node.id.label('id')). \
            join(q_counts, q_counts.c.id == Node.id). \
            filter(Node.n_input != q_counts.c.count)
        q_chained = self.session.query(Node.id). \
            join(Connect, Node.id == Connect.output_id). \
            filter(Connect.input_id.in_(q_missing))
        q_all = union(q_missing, q_chained)
        print('\nchained node\n%s\n' % q_all.select())
        self.assertEqual([(5,), (7,)],
                         self.session.query(Node.id).filter(Node.id.in_(q_all.select())).order_by(Node.id).all()) 

def test_statement_labels(self):
        """test that label conflicts don't occur with joins etc."""

        User, Address = self.classes.User, self.classes.Address

        s = create_session()
        q1 = (
            s.query(User, Address)
            .join(User.addresses)
            .filter(Address.email_address == "ed@wood.com")
        )
        q2 = (
            s.query(User, Address)
            .join(User.addresses)
            .filter(Address.email_address == "jack@bean.com")
        )
        q3 = q1.union(q2).order_by(User.name)

        eq_(
            q3.all(),
            [
                (User(name="ed"), Address(email_address="ed@wood.com")),
                (User(name="jack"), Address(email_address="jack@bean.com")),
            ],
        ) 

def test_direct_correspondence_on_labels(self):
        # this test depends on labels being part
        # of the proxy set to get the right result

        l1, l2 = table1.c.col1.label("foo"), table1.c.col1.label("bar")
        sel = select([l1, l2])

        sel2 = sel.alias()
        assert sel2.corresponding_column(l1) is sel2.c.foo
        assert sel2.corresponding_column(l2) is sel2.c.bar

        sel2 = select([table1.c.col1.label("foo"), table1.c.col2.label("bar")])

        sel3 = sel.union(sel2).alias()
        assert sel3.corresponding_column(l1) is sel3.c.foo
        assert sel3.corresponding_column(l2) is sel3.c.bar 

def test_union(self):
        User = self.classes.User

        s = create_session()

        fred = s.query(User).filter(User.name == "fred")
        ed = s.query(User).filter(User.name == "ed")
        jack = s.query(User).filter(User.name == "jack")

        eq_(
            fred.union(ed).order_by(User.name).all(),
            [User(name="ed"), User(name="fred")],
        )

        eq_(
            fred.union(ed, jack).order_by(User.name).all(),
            [User(name="ed"), User(name="fred"), User(name="jack")],
        )

        eq_(
            fred.union(ed).union(jack).order_by(User.name).all(),
            [User(name="ed"), User(name="fred"), User(name="jack")],
        ) 

def test_foo(self):
        s1 = select([table1.c.col1, table1.c.col2])
        s2 = select([table1.c.col2, table1.c.col1])

        u1 = union(s1, s2).subquery()
        assert u1.corresponding_column(table1.c.col2) is u1.c.col2

        metadata = MetaData()
        table1_new = Table(
            "table1",
            metadata,
            Column("col1", Integer, primary_key=True),
            Column("col2", String(20)),
            Column("col3", Integer),
            Column("colx", Integer),
        )
        # table1_new = table1

        s1 = select([table1_new.c.col1, table1_new.c.col2])
        s2 = select([table1_new.c.col2, table1_new.c.col1])
        u1 = union(s1, s2).subquery()

        # TODO: failing due to proxy_set not correct
        assert u1.corresponding_column(table1_new.c.col2) is u1.c.col2 

def test_anonymous_expression_newstyle(self):
        from sqlalchemy.sql import column

        c1, c2 = column("c1"), column("c2")
        q1 = future_select(c1, c2).where(c1 == "dog")
        q2 = future_select(c1, c2).where(c1 == "cat")
        subq = q1.union(q2).subquery()
        q3 = future_select(subq).apply_labels()

        self.assert_compile(
            q3.order_by(subq.c.c1),
            "SELECT anon_1.c1 AS anon_1_c1, anon_1.c2 "
            "AS anon_1_c2 FROM (SELECT c1, c2 WHERE "
            "c1 = :c1_1 UNION SELECT c1, c2 "
            "WHERE c1 = :c1_2) AS anon_1 ORDER BY anon_1.c1",
        ) 

def test_anonymous_expression_oldstyle(self):
        # relies upon _orm_only_from_obj_alias setting
        from sqlalchemy.sql import column

        sess = create_session()
        c1, c2 = column("c1"), column("c2")
        q1 = sess.query(c1, c2).filter(c1 == "dog")
        q2 = sess.query(c1, c2).filter(c1 == "cat")
        q3 = q1.union(q2)
        self.assert_compile(
            q3.order_by(c1),
            "SELECT anon_1.c1 AS anon_1_c1, anon_1.c2 "
            "AS anon_1_c2 FROM (SELECT c1, c2 WHERE "
            "c1 = :c1_1 UNION SELECT c1, c2 "
            "WHERE c1 = :c1_2) AS anon_1 ORDER BY anon_1.c1",
        ) 

def test_correlate_to_union_newstyle(self):
        User = self.classes.User

        q = future_select(User).apply_labels()

        q = future_select(User).union(q).apply_labels().subquery()

        u_alias = aliased(User)

        raw_subq = exists().where(u_alias.id > q.c[0])

        self.assert_compile(
            future_select(q, raw_subq).apply_labels(),
            "SELECT anon_1.users_id AS anon_1_users_id, "
            "anon_1.users_name AS anon_1_users_name, "
            "EXISTS (SELECT * FROM users AS users_1 "
            "WHERE users_1.id > anon_1.users_id) AS anon_2 "
            "FROM ("
            "SELECT users.id AS users_id, users.name AS users_name FROM users "
            "UNION SELECT users.id AS users_id, users.name AS users_name "
            "FROM users) AS anon_1",
        ) 

def test_annotate_varied_annot_same_col(self):
        """test two instances of the same column with different annotations
        preserving them when deep_annotate is run on them.

        """
        t1 = table("table1", column("col1"), column("col2"))
        s = select([t1.c.col1._annotate({"foo": "bar"})])
        s2 = select([t1.c.col1._annotate({"bat": "hoho"})])
        s3 = s.union(s2)
        sel = sql_util._deep_annotate(s3, {"new": "thing"})

        eq_(
            sel.selects[0]._raw_columns[0]._annotations,
            {"foo": "bar", "new": "thing"},
        )

        eq_(
            sel.selects[1]._raw_columns[0]._annotations,
            {"bat": "hoho", "new": "thing"},
        ) 

def test_label_conflict_union(self):
        t1 = Table(
            "t1", MetaData(), Column("a", Integer), Column("b", Integer)
        )
        t2 = Table("t2", MetaData(), Column("t1_a", Integer))
        union = select([t2]).union(select([t2])).alias()

        t1_alias = t1.alias()
        stmt = (
            select([t1, t1_alias])
            .select_from(t1.join(union, t1.c.a == union.c.t1_a))
            .apply_labels()
        )
        comp = stmt.compile()
        eq_(
            set(comp._create_result_map()),
            set(["t1_1_b", "t1_1_a", "t1_a", "t1_b"]),
        )
        is_(comp._create_result_map()["t1_a"][1][2], t1.c.a) 

def test_union_ordered(self, connection):
        (s1, s2) = (
            select(
                [t1.c.col3.label("col3"), t1.c.col4.label("col4")],
                t1.c.col2.in_(["t1col2r1", "t1col2r2"]),
            ),
            select(
                [t2.c.col3.label("col3"), t2.c.col4.label("col4")],
                t2.c.col2.in_(["t2col2r2", "t2col2r3"]),
            ),
        )
        u = union(s1, s2, order_by=["col3", "col4"])

        wanted = [
            ("aaa", "aaa"),
            ("bbb", "bbb"),
            ("bbb", "ccc"),
            ("ccc", "aaa"),
        ]
        eq_(connection.execute(u).fetchall(), wanted) 

def test_union_ordered_alias(self, connection):
        (s1, s2) = (
            select(
                [t1.c.col3.label("col3"), t1.c.col4.label("col4")],
                t1.c.col2.in_(["t1col2r1", "t1col2r2"]),
            ),
            select(
                [t2.c.col3.label("col3"), t2.c.col4.label("col4")],
                t2.c.col2.in_(["t2col2r2", "t2col2r3"]),
            ),
        )
        u = union(s1, s2, order_by=["col3", "col4"])

        wanted = [
            ("aaa", "aaa"),
            ("bbb", "bbb"),
            ("bbb", "ccc"),
            ("ccc", "aaa"),
        ]
        eq_(connection.execute(u.alias("bar").select()).fetchall(), wanted) 

def test_union_precedence(self):
        # conflicting column correspondence should be resolved based on
        # the order of the select()s in the union

        s1 = select([table1.c.col1, table1.c.col2])
        s2 = select([table1.c.col2, table1.c.col1])
        s3 = select([table1.c.col3, table1.c.colx])
        s4 = select([table1.c.colx, table1.c.col3])

        u1 = union(s1, s2).subquery()
        assert u1.corresponding_column(table1.c.col1) is u1.c.col1
        assert u1.corresponding_column(table1.c.col2) is u1.c.col2

        u1 = union(s1, s2, s3, s4).subquery()
        assert u1.corresponding_column(table1.c.col1) is u1.c.col1
        assert u1.corresponding_column(table1.c.col2) is u1.c.col2
        assert u1.corresponding_column(table1.c.colx) is u1.c.col2
        assert u1.corresponding_column(table1.c.col3) is u1.c.col1 

def test_except_style1(self, connection):
        e = except_(
            union(
                select([t1.c.col3, t1.c.col4]),
                select([t2.c.col3, t2.c.col4]),
                select([t3.c.col3, t3.c.col4]),
            ),
            select([t2.c.col3, t2.c.col4]),
        )

        wanted = [
            ("aaa", "aaa"),
            ("aaa", "ccc"),
            ("bbb", "aaa"),
            ("bbb", "bbb"),
            ("ccc", "bbb"),
            ("ccc", "ccc"),
        ]

        found = self._fetchall_sorted(connection.execute(e.alias().select()))
        eq_(found, wanted) 

def test_unique_binds_join_cond(self):
        """test that binds used when the lazyclause is used in criterion are
        unique"""

        User, Address = self.classes.User, self.classes.Address
        sess = Session()
        a1, a2 = sess.query(Address).order_by(Address.id)[0:2]
        self.assert_compile(
            sess.query(User)
            .filter(User.addresses.contains(a1))
            .union(sess.query(User).filter(User.addresses.contains(a2))),
            "SELECT anon_1.users_id AS anon_1_users_id, anon_1.users_name AS "
            "anon_1_users_name FROM (SELECT users.id AS users_id, "
            "users.name AS users_name FROM users WHERE users.id = :param_1 "
            "UNION SELECT users.id AS users_id, users.name AS users_name "
            "FROM users WHERE users.id = :param_2) AS anon_1",
            checkparams={"param_1": 7, "param_2": 8},
        ) 

def test_distinct_selectable_in_unions(self):
        table = self.tables.some_table
        s1 = select([table]).where(table.c.id == 2).\
            distinct()
        s2 = select([table]).where(table.c.id == 3).\
            distinct()

        u1 = union(s1, s2).limit(2)
        self._assert_result(
            u1.order_by(u1.c.id),
            [(2, 2, 3), (3, 3, 4)]
        ) 

def test_intersect_unions_3(self, connection):
        u = intersect(
            select([t2.c.col3, t2.c.col4]),
            union(
                select([t1.c.col3, t1.c.col4]),
                select([t2.c.col3, t2.c.col4]),
                select([t3.c.col3, t3.c.col4]),
            )
            .alias()
            .select(),
        )
        wanted = [("aaa", "bbb"), ("bbb", "ccc"), ("ccc", "aaa")]
        found = self._fetchall_sorted(connection.execute(u))

        eq_(found, wanted) 

def test_limit_offset_aliased_selectable_in_unions(self):
        table = self.tables.some_table
        s1 = select([table]).where(table.c.id == 2).\
            limit(1).order_by(table.c.id).alias().select()
        s2 = select([table]).where(table.c.id == 3).\
            limit(1).order_by(table.c.id).alias().select()

        u1 = union(s1, s2).limit(2)
        self._assert_result(
            u1.order_by(u1.c.id),
            [(2, 2, 3), (3, 3, 4)]
        ) 

def test_limit_offset_compound_select_literal_binds(self):
        stmt = select([1]).union(select([2])).limit(5).offset(6)
        self.assert_compile(
            stmt,
            "SELECT 1 UNION SELECT 2 LIMIT 5 OFFSET 6",
            literal_binds=True,
        ) 

def test_compound_populates(self):
        t = Table("t", MetaData(), Column("a", Integer), Column("b", Integer))
        stmt = select([t]).union(select([t]))
        comp = stmt.compile()
        eq_(
            comp._create_result_map(),
            {
                "a": ("a", (t.c.a, "a", "a", "t_a"), t.c.a.type),
                "b": ("b", (t.c.b, "b", "b", "t_b"), t.c.b.type),
            },
        ) 

def test_compound_not_toplevel_doesnt_populate(self):
        t = Table("t", MetaData(), Column("a", Integer), Column("b", Integer))
        subq = select([t]).union(select([t])).subquery()
        stmt = select([t.c.a]).select_from(t.join(subq, t.c.a == subq.c.a))
        comp = stmt.compile()
        eq_(
            comp._create_result_map(),
            {"a": ("a", (t.c.a, "a", "a", "t_a"), t.c.a.type)},
        ) 

def test_limit_offset_selectable_in_unions(self):
        table = self.tables.some_table
        s1 = select([table]).where(table.c.id == 2).\
            limit(1).order_by(table.c.id)
        s2 = select([table]).where(table.c.id == 3).\
            limit(1).order_by(table.c.id)

        u1 = union(s1, s2).limit(2)
        self._assert_result(
            u1.order_by(u1.c.id),
            [(2, 2, 3), (3, 3, 4)]
        ) 

def test_dupe_cols_hey_we_can_union(self):
        """test the original inspiration for [ticket:4753]."""

        s1 = select([table1, table1.c.myid]).where(table1.c.myid == 5)
        s2 = select([table1, table2.c.otherid]).where(
            table1.c.myid == table2.c.otherid
        )
        self.assert_compile(
            union(s1, s2).order_by(s1.selected_columns.myid),
            "SELECT mytable.myid, mytable.name, mytable.description, "
            "mytable.myid FROM mytable WHERE mytable.myid = :myid_1 "
            "UNION SELECT mytable.myid, mytable.name, mytable.description, "
            "myothertable.otherid FROM mytable, myothertable "
            "WHERE mytable.myid = myothertable.otherid ORDER BY myid",
        )