Python sqlalchemy.alias() Examples

def test_select(self):
        meta = MetaData()

        t1 = Table(
            "t1",
            meta,
            Column("c1", Integer, primary_key=True),
            Column("c2", String(30)),
        )
        t2 = Table(
            "t2",
            meta,
            Column("c1", Integer, primary_key=True),
            Column("c2", String(30)),
        )

        assert t1.select().is_derived_from(t1)
        assert not t2.select().is_derived_from(t1)

        assert select([t1, t2]).is_derived_from(t1)

        assert t1.select().alias("foo").is_derived_from(t1)
        assert select([t1, t2]).alias("foo").is_derived_from(t1)
        assert not t2.select().alias("foo").is_derived_from(t1) 

def test_nested_label_targeting(self):
        """test nested anonymous label generation.

        """
        s1 = table1.select()
        s2 = s1.alias()
        s3 = select([s2], use_labels=True)
        s4 = s3.alias()
        s5 = select([s4], use_labels=True)
        self.assert_compile(
            s5,
            "SELECT anon_1.anon_2_myid AS "
            "anon_1_anon_2_myid, anon_1.anon_2_name AS "
            "anon_1_anon_2_name, anon_1.anon_2_descript"
            "ion AS anon_1_anon_2_description FROM "
            "(SELECT anon_2.myid AS anon_2_myid, "
            "anon_2.name AS anon_2_name, "
            "anon_2.description AS anon_2_description "
            "FROM (SELECT mytable.myid AS myid, "
            "mytable.name AS name, mytable.description "
            "AS description FROM mytable) AS anon_2) "
            "AS anon_1",
        ) 

def test_alias(self):
        meta = MetaData()
        t1 = Table(
            "t1",
            meta,
            Column("c1", Integer, primary_key=True),
            Column("c2", String(30)),
        )
        t2 = Table(
            "t2",
            meta,
            Column("c1", Integer, primary_key=True),
            Column("c2", String(30)),
        )

        assert t1.alias().is_derived_from(t1)
        assert not t2.alias().is_derived_from(t1)
        assert not t1.is_derived_from(t1.alias())
        assert not t1.is_derived_from(t2.alias()) 

def test_proxy_set_iteration_includes_annotated(self):
        from sqlalchemy.schema import Column

        c1 = Column("foo", Integer)

        stmt = select([c1]).alias()
        proxy = stmt.c.foo

        proxy.proxy_set

        # create an annotated of the column
        p2 = proxy._annotate({"weight": 10})

        # now see if our annotated version is in that column's
        # proxy_set, as corresponding_column iterates through proxy_set
        # in this way
        d = {}
        for col in p2._uncached_proxy_set():
            d.update(col._annotations)
        eq_(d, {"weight": 10}) 

def test_alias_alias_samename_init(self):
        a = table("a", column("x"))
        b = table("b", column("y"))
        s1 = select([a, b]).apply_labels().alias()
        s2 = s1.alias()

        s1.c
        s2.c

        q = column("x")
        b.append_column(q)

        assert "_columns" in s2.__dict__

        s2._refresh_for_new_column(q)

        assert "_columns" not in s2.__dict__
        is_(s1.corresponding_column(s2.c.b_x), s1.c.b_x) 

def test_alias_handles_column_context(self):
        # not quite a use case yet but this is expected to become
        # prominent w/ PostgreSQL's tuple functions

        stmt = select([table1.c.col1, table1.c.col2])
        a = stmt.alias("a")

        # TODO: this case is crazy, sending SELECT or FROMCLAUSE has to
        # be figured out - is it a scalar row query?  what kinds of
        # statements go into functions in PG. seems likely select statement,
        # but not alias, subquery or other FROM object
        self.assert_compile(
            select([func.foo(a)]),
            "SELECT foo(SELECT table1.col1, table1.col2 FROM table1) "
            "AS foo_1 FROM "
            "(SELECT table1.col1 AS col1, table1.col2 AS col2 FROM table1) "
            "AS a",
        ) 

def test_join_against_self(self):
        jj = select([table1.c.col1.label("bar_col1")]).subquery()
        jjj = join(table1, jj, table1.c.col1 == jj.c.bar_col1)

        # test column directly against itself

        # joins necessarily have to prefix column names with the name
        # of the selectable, else the same-named columns will overwrite
        # one another.  In this case, we unfortunately have this unfriendly
        # "anonymous" name, whereas before when select() could be a FROM
        # the "bar_col1" label would be directly in the join() object.  However
        # this was a useless join() object because PG and MySQL don't accept
        # unnamed subqueries in joins in any case.
        name = "%s_bar_col1" % (jj.name,)

        assert jjj.corresponding_column(jjj.c.table1_col1) is jjj.c.table1_col1
        assert jjj.corresponding_column(jj.c.bar_col1) is jjj.c[name]

        # test alias of the join

        j2 = jjj.alias("foo")
        assert j2.corresponding_column(table1.c.col1) is j2.c.table1_col1 

def test_join_against_self_implicit_subquery(self):
        jj = select([self.table1.c.col1.label("bar_col1")])
        with testing.expect_deprecated(
            "The SelectBase.c and SelectBase.columns attributes are "
            "deprecated and will be removed",
            "Implicit coercion of SELECT",
        ):
            jjj = join(self.table1, jj, self.table1.c.col1 == jj.c.bar_col1)

        jjj_bar_col1 = jjj.c["%s_bar_col1" % jj._implicit_subquery.name]
        assert jjj_bar_col1 is not None

        # test column directly against itself

        assert jjj.corresponding_column(jjj.c.table1_col1) is jjj.c.table1_col1
        with testing.expect_deprecated(
            "The SelectBase.c and SelectBase.columns attributes are "
            "deprecated and will be removed"
        ):
            assert jjj.corresponding_column(jj.c.bar_col1) is jjj_bar_col1

        # test alias of the join

        j2 = jjj.alias("foo")
        assert j2.corresponding_column(self.table1.c.col1) is j2.c.table1_col1 

def test_keyed_label_gen(self):
        s = select([keyed]).apply_labels()

        assert (
            s.selected_columns.corresponding_column(keyed.c.colx)
            is s.selected_columns.keyed_colx
        )
        assert (
            s.selected_columns.corresponding_column(keyed.c.coly)
            is s.selected_columns.keyed_coly
        )
        assert (
            s.selected_columns.corresponding_column(keyed.c.z)
            is s.selected_columns.keyed_z
        )

        sel2 = s.alias()
        assert sel2.corresponding_column(keyed.c.colx) is sel2.c.keyed_colx
        assert sel2.corresponding_column(keyed.c.coly) is sel2.c.keyed_coly
        assert sel2.corresponding_column(keyed.c.z) is sel2.c.keyed_z 

def test_keyed_gen(self):
        s = select([keyed])
        eq_(s.selected_columns.colx.key, "colx")

        eq_(s.selected_columns.colx.name, "x")

        assert (
            s.selected_columns.corresponding_column(keyed.c.colx)
            is s.selected_columns.colx
        )
        assert (
            s.selected_columns.corresponding_column(keyed.c.coly)
            is s.selected_columns.coly
        )
        assert (
            s.selected_columns.corresponding_column(keyed.c.z)
            is s.selected_columns.z
        )

        sel2 = s.alias()
        assert sel2.corresponding_column(keyed.c.colx) is sel2.c.colx
        assert sel2.corresponding_column(keyed.c.coly) is sel2.c.coly
        assert sel2.corresponding_column(keyed.c.z) is sel2.c.z 

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_anon_aliased_overlapping(self, connection):
        text1 = self.tables.text1

        c1 = text1.c.a.label(None)
        c2 = text1.alias().c.a
        c3 = text1.alias().c.a.label(None)
        c4 = text1.c.a.label(None)

        stmt = text("select a, b, c, d from text1").columns(c1, c2, c3, c4)
        result = connection.execute(stmt)
        row = result.first()

        with testing.expect_deprecated(
            "Retrieving row values using Column objects "
            "with only matching names"
        ):
            eq_(row._mapping[text1.c.a], "a1") 

def test_copy_internals_multiple_nesting(self):
        t = table("t", column("c"))
        stmt = select([t])
        a1 = stmt.alias()
        a2 = a1.alias()

        a3 = a2._clone()
        a3._copy_internals()
        is_(a1.corresponding_column(a3.c.c), a1.c.c) 

def test_join_alias_flat(self):
        a = table("a", column("a"))
        b = table("b", column("b"))
        self.assert_compile(
            a.join(b, a.c.a == b.c.b).alias(flat=True),
            "a AS a_1 JOIN b AS b_1 ON a_1.a = b_1.b",
        ) 

def test_join_standalone_alias_flat(self):
        a = table("a", column("a"))
        b = table("b", column("b"))
        self.assert_compile(
            alias(a.join(b, a.c.a == b.c.b), flat=True),
            "a AS a_1 JOIN b AS b_1 ON a_1.a = b_1.b",
        ) 

def test_attribute_sanity(self):
        assert hasattr(table1, "c")
        assert hasattr(table1.select().subquery(), "c")
        assert not hasattr(table1.c.myid.self_group(), "columns")
        assert not hasattr(table1.c.myid, "columns")
        assert not hasattr(table1.c.myid, "c")
        assert not hasattr(table1.select().subquery().c.myid, "c")
        assert not hasattr(table1.select().subquery().c.myid, "columns")
        assert not hasattr(table1.alias().c.myid, "columns")
        assert not hasattr(table1.alias().c.myid, "c")
        with testing.expect_deprecated(
            "The SelectBase.c and SelectBase.columns attributes are "
            "deprecated"
        ):
            assert hasattr(table1.select(), "c")

        assert_raises_message(
            exc.InvalidRequestError,
            "Scalar Select expression has no "
            "columns; use this object directly within a "
            "column-level expression.",
            getattr,
            select([table1.c.myid]).scalar_subquery().self_group(),
            "columns",
        )

        assert_raises_message(
            exc.InvalidRequestError,
            "Scalar Select expression has no "
            "columns; use this object directly within a "
            "column-level expression.",
            getattr,
            select([table1.c.myid]).scalar_subquery(),
            "columns",
        ) 

def test_select_label_alt_name_table_alias_column(self):
        t = self._fixture()
        x = t.c.x

        ta = t.alias()
        l1, l2 = ta.c.x.label("a"), ta.c.y.label("b")

        s = select([l1, l2])
        mapping = self._mapping(s)
        assert x not in mapping
        assert l1 in mapping
        assert ta.c.x not in mapping 

def test_direct_element_hierarchy(self):
        t = table("t", column("c"))
        a1 = t.alias()
        a2 = a1.alias()
        a3 = a2.alias()

        is_(a1.element, t)
        is_(a2.element, a1)
        is_(a3.element, a2) 

def test_get_children_preserves_multiple_nesting(self):
        t = table("t", column("c"))
        stmt = select([t])
        a1 = stmt.alias()
        a2 = a1.alias()
        eq_(set(a2.get_children(column_collections=False)), {a1}) 

def test_correspondence_multiple_nesting(self):
        t = table("t", column("c"))
        stmt = select([t])
        a1 = stmt.alias()
        a2 = a1.alias()

        is_(a1.corresponding_column(a2.c.c), a1.c.c) 

def test_select_subquery_join(self):
        a = table1.select().alias("a")
        j = join(a, table2)

        criterion = a.c.col1 == table2.c.col2
        self.assert_(criterion.compare(j.onclause)) 

def test_join_standalone_alias(self):
        a = table("a", column("a"))
        b = table("b", column("b"))
        self.assert_compile(
            alias(a.join(b, a.c.a == b.c.b)),
            "SELECT a.a AS a_a, b.b AS b_b FROM a JOIN b ON a.a = b.b",
        ) 

def test_join_alias(self):
        a = table("a", column("a"))
        b = table("b", column("b"))
        self.assert_compile(
            a.join(b, a.c.a == b.c.b).alias(),
            "SELECT a.a AS a_a, b.b AS b_b FROM a JOIN b ON a.a = b.b",
        ) 

def test_flat_ok_on_non_join(self):
        a = table("a", column("a"))
        s = a.select()
        self.assert_compile(
            s.alias(flat=True).select(),
            "SELECT anon_1.a FROM (SELECT a.a AS a FROM a) AS anon_1",
        ) 

def test_aliased_select_irrelevant(self):
        a = table("a", column("x"))
        b = table("b", column("y"))
        c = table("c", column("z"))
        s = select([a, b]).apply_labels().alias()
        s.c
        q = column("x")
        c.append_column(q)
        s._refresh_for_new_column(q)
        assert "c_x" not in s.c 

def test_aliased_select_samename_init(self):
        a = table("a", column("x"))
        b = table("b", column("y"))
        s = select([a, b]).apply_labels().alias()
        s.c
        q = column("x")
        b.append_column(q)
        s._refresh_for_new_column(q)
        assert q in s.c.b_x.proxy_set 

def test_aliased_select_samename_uninit(self):
        a = table("a", column("x"))
        b = table("b", column("y"))
        s = select([a, b]).apply_labels().alias()
        q = column("x")
        b.append_column(q)
        s._refresh_for_new_column(q)
        assert q in s.c.b_x.proxy_set 

def test_no_alias_construct(self):
        a = table("a", column("x"))

        assert_raises_message(
            NotImplementedError,
            "The Alias class is not intended to be constructed directly.  "
            r"Please use the alias\(\) standalone function",
            Alias,
            a,
            "foo",
        ) 

def test_select_alias_labels(self):
        a = table2.select(use_labels=True).alias("a")
        j = join(a, table1)

        criterion = table1.c.col1 == a.c.table2_col2
        self.assert_(criterion.compare(j.onclause)) 

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

        # previously this worked
        assert_raises_message(
            exc.ArgumentError,
            "SELECT construct for inclusion in a UNION or "
            "other set construct expected",
            union,
            s1,
            s2,
        )