Python sqlalchemy.sql.util.ClauseAdapter() Examples

def test_label_anonymize_two(self):
        t1a = t1.alias()
        adapter = sql_util.ClauseAdapter(t1a, anonymize_labels=True)

        expr = select([t1.c.col2]).where(t1.c.col3 == 5).label(None)
        expr_adapted = adapter.traverse(expr)

        stmt = select([expr, expr_adapted]).order_by(expr, expr_adapted)
        self.assert_compile(
            stmt,
            "SELECT "
            "(SELECT table1.col2 FROM table1 WHERE table1.col3 = :col3_1) "
            "AS anon_1, "
            "(SELECT table1_1.col2 FROM table1 AS table1_1 "
            "WHERE table1_1.col3 = :col3_2) AS anon_2 "
            "ORDER BY anon_1, anon_2",
        ) 

def test_label_anonymize_one(self):
        t1a = t1.alias()
        adapter = sql_util.ClauseAdapter(t1a, anonymize_labels=True)

        expr = select([t1.c.col2]).where(t1.c.col3 == 5).label("expr")
        expr_adapted = adapter.traverse(expr)

        stmt = select([expr, expr_adapted]).order_by(expr, expr_adapted)
        self.assert_compile(
            stmt,
            "SELECT "
            "(SELECT table1.col2 FROM table1 WHERE table1.col3 = :col3_1) "
            "AS expr, "
            "(SELECT table1_1.col2 FROM table1 AS table1_1 "
            "WHERE table1_1.col3 = :col3_2) AS anon_1 "
            "ORDER BY expr, anon_1",
        ) 

def test_whereclause_adapted(self):
        table1 = table("t1", column("a"))

        s1 = select([table1]).subquery()

        s2 = select([s1]).where(s1.c.a == 5)

        assert s2._whereclause.left.table is s1

        ta = select([table1]).subquery()

        s3 = sql_util.ClauseAdapter(ta).traverse(s2)

        froms = list(s3._iterate_from_elements())

        assert s1 not in froms

        # these are new assumptions with the newer approach that
        # actively swaps out whereclause and others
        assert s3._whereclause.left.table is not s1
        assert s3._whereclause.left.table in froms 

def test_table_to_alias_15(self):
        t1alias = t1.alias("t1alias")
        vis = sql_util.ClauseAdapter(t1alias)
        t2alias = t2.alias("t2alias")
        vis.chain(sql_util.ClauseAdapter(t2alias))
        self.assert_compile(
            select([t1alias, t2alias]).where(
                t1alias.c.col1
                == vis.traverse(
                    select(["*"], t1.c.col1 == t2.c.col2, from_obj=[t1, t2])
                    .correlate(t1)
                    .scalar_subquery()
                )
            ),
            "SELECT t1alias.col1, t1alias.col2, t1alias.col3, "
            "t2alias.col1, t2alias.col2, t2alias.col3 "
            "FROM table1 AS t1alias, table2 AS t2alias "
            "WHERE t1alias.col1 = "
            "(SELECT * FROM table2 AS t2alias "
            "WHERE t1alias.col1 = t2alias.col2)",
        ) 

def test_aliasedselect_to_aliasedselect_join(self):
        s1 = select([t1]).alias("foo")
        s2 = select([s1]).limit(5).offset(10).alias()
        j = s1.outerjoin(t2, s1.c.col1 == t2.c.col1)
        self.assert_compile(
            sql_util.ClauseAdapter(s2).traverse(j).select(),
            "SELECT anon_1.col1, anon_1.col2, "
            "anon_1.col3, table2.col1, table2.col2, "
            "table2.col3 FROM (SELECT foo.col1 AS "
            "col1, foo.col2 AS col2, foo.col3 AS col3 "
            "FROM (SELECT table1.col1 AS col1, "
            "table1.col2 AS col2, table1.col3 AS col3 "
            "FROM table1) AS foo LIMIT :param_1 OFFSET "
            ":param_2) AS anon_1 LEFT OUTER JOIN "
            "table2 ON anon_1.col1 = table2.col1",
            {"param_1": 5, "param_2": 10},
        ) 

def test_this_thing(self):
        s = select([t1]).where(t1.c.col1 == "foo").alias()
        s2 = select([s.c.col1])

        self.assert_compile(
            s2,
            "SELECT anon_1.col1 FROM (SELECT "
            "table1.col1 AS col1, table1.col2 AS col2, "
            "table1.col3 AS col3 FROM table1 WHERE "
            "table1.col1 = :col1_1) AS anon_1",
        )
        t1a = t1.alias()
        s2 = sql_util.ClauseAdapter(t1a).traverse(s2)
        self.assert_compile(
            s2,
            "SELECT anon_1.col1 FROM (SELECT "
            "table1_1.col1 AS col1, table1_1.col2 AS "
            "col2, table1_1.col3 AS col3 FROM table1 "
            "AS table1_1 WHERE table1_1.col1 = "
            ":col1_1) AS anon_1",
        ) 

def test_this_thing_using_setup_joins_one(self):
        s = (
            future_select(t1)
            .join_from(t1, t2, t1.c.col1 == t2.c.col2)
            .subquery()
        )
        s2 = future_select(s.c.col1).join_from(t3, s, t3.c.col2 == s.c.col1)

        self.assert_compile(
            s2,
            "SELECT anon_1.col1 FROM table3 JOIN (SELECT table1.col1 AS "
            "col1, table1.col2 AS col2, table1.col3 AS col3 FROM table1 "
            "JOIN table2 ON table1.col1 = table2.col2) AS anon_1 "
            "ON table3.col2 = anon_1.col1",
        )
        t1a = t1.alias()
        s2 = sql_util.ClauseAdapter(t1a).traverse(s2)
        self.assert_compile(
            s2,
            "SELECT anon_1.col1 FROM table3 JOIN (SELECT table1_1.col1 AS "
            "col1, table1_1.col2 AS col2, table1_1.col3 AS col3 "
            "FROM table1 AS table1_1 JOIN table2 ON table1_1.col1 = "
            "table2.col2) AS anon_1 ON table3.col2 = anon_1.col1",
        ) 

def test_table_to_alias_6(self):
        t1alias = t1.alias("t1alias")
        vis = sql_util.ClauseAdapter(t1alias)
        self.assert_compile(
            select([t1alias, t2]).where(
                t1alias.c.col1
                == vis.traverse(
                    select(
                        [literal_column("*")],
                        t1.c.col1 == t2.c.col2,
                        from_obj=[t1, t2],
                    )
                    .correlate(t2)
                    .scalar_subquery()
                )
            ),
            "SELECT t1alias.col1, t1alias.col2, t1alias.col3, "
            "table2.col1, table2.col2, table2.col3 "
            "FROM table1 AS t1alias, table2 "
            "WHERE t1alias.col1 = "
            "(SELECT * FROM table1 AS t1alias "
            "WHERE t1alias.col1 = table2.col2)",
        ) 

def test_select_setup_joins_adapt_element_two(self):
        s = future_select(literal_column("1")).join_from(
            t1, t2, t1.c.col1 == t2.c.col2
        )

        t1a = t1.alias()

        s2 = sql_util.ClauseAdapter(t1a).traverse(s)

        self.assert_compile(
            s, "SELECT 1 FROM table1 JOIN table2 ON table1.col1 = table2.col2"
        )
        self.assert_compile(
            s2,
            "SELECT 1 FROM table1 AS table1_1 "
            "JOIN table2 ON table1_1.col1 = table2.col2",
        ) 

def test_select_setup_joins_adapt_element_three(self):
        s = future_select(literal_column("1")).join_from(
            t1, t2, t1.c.col1 == t2.c.col2
        )

        t2a = t2.alias()

        s2 = sql_util.ClauseAdapter(t2a).traverse(s)

        self.assert_compile(
            s, "SELECT 1 FROM table1 JOIN table2 ON table1.col1 = table2.col2"
        )
        self.assert_compile(
            s2,
            "SELECT 1 FROM table1 "
            "JOIN table2 AS table2_1 ON table1.col1 = table2_1.col2",
        ) 

def test_joins_dont_adapt(self):
        # adapting to a join, i.e. ClauseAdapter(t1.join(t2)), doesn't
        # make much sense. ClauseAdapter doesn't make any changes if
        # it's against a straight join.

        users = table("users", column("id"))
        addresses = table("addresses", column("id"), column("user_id"))

        ualias = users.alias()

        s = select(
            [func.count(addresses.c.id)], users.c.id == addresses.c.user_id
        ).correlate(users)
        s = sql_util.ClauseAdapter(ualias).traverse(s)

        j1 = addresses.join(ualias, addresses.c.user_id == ualias.c.id)

        self.assert_compile(
            sql_util.ClauseAdapter(j1).traverse(s),
            "SELECT count(addresses.id) AS count_1 "
            "FROM addresses WHERE users_1.id = "
            "addresses.user_id",
        ) 

def test_table_to_alias_5(self):
        t1alias = t1.alias("t1alias")
        vis = sql_util.ClauseAdapter(t1alias)
        self.assert_compile(
            select([t1alias, t2]).where(
                t1alias.c.col1
                == vis.traverse(
                    select(
                        [literal_column("*")],
                        t1.c.col1 == t2.c.col2,
                        from_obj=[t1, t2],
                    )
                    .correlate(t1)
                    .scalar_subquery()
                )
            ),
            "SELECT t1alias.col1, t1alias.col2, t1alias.col3, "
            "table2.col1, table2.col2, table2.col3 "
            "FROM table1 AS t1alias, table2 WHERE t1alias.col1 = "
            "(SELECT * FROM table2 WHERE t1alias.col1 = table2.col2)",
        ) 

def test_table_to_alias_16(self):
        t1alias = t1.alias("t1alias")
        vis = sql_util.ClauseAdapter(t1alias)
        t2alias = t2.alias("t2alias")
        vis.chain(sql_util.ClauseAdapter(t2alias))
        self.assert_compile(
            t2alias.select().where(
                t2alias.c.col2
                == vis.traverse(
                    select(["*"], t1.c.col1 == t2.c.col2, from_obj=[t1, t2])
                    .correlate(t2)
                    .scalar_subquery()
                )
            ),
            "SELECT t2alias.col1, t2alias.col2, t2alias.col3 "
            "FROM table2 AS t2alias WHERE t2alias.col2 = "
            "(SELECT * FROM table1 AS t1alias WHERE "
            "t1alias.col1 = t2alias.col2)",
        ) 

def test_table_to_alias_13(self):
        t1alias = t1.alias("t1alias")
        vis = sql_util.ClauseAdapter(t1alias)
        t2alias = t2.alias("t2alias")
        vis.chain(sql_util.ClauseAdapter(t2alias))
        self.assert_compile(
            vis.traverse(
                select([literal_column("*")], t1.c.col1 == t2.c.col2)
            ),
            "SELECT * FROM table1 AS t1alias, table2 "
            "AS t2alias WHERE t1alias.col1 = "
            "t2alias.col2",
        ) 

def test_adapt_select(self, user_address_fixture):
        users, addresses = user_address_fixture

        stmt = (
            select([users])
            .select_from(
                users.join(
                    addresses, lambda: users.c.id == addresses.c.user_id
                )
            )
            .where(lambda: users.c.name == "ed")
        )

        self.assert_compile(
            stmt,
            "SELECT users.id, users.name FROM users "
            "JOIN addresses ON users.id = addresses.user_id "
            "WHERE users.name = :name_1",
        )

        u1 = users.alias()
        adapter = sql_util.ClauseAdapter(u1)

        s2 = adapter.traverse(stmt)

        self.assert_compile(
            s2,
            "SELECT users_1.id, users_1.name FROM users AS users_1 "
            "JOIN addresses ON users_1.id = addresses.user_id "
            "WHERE users_1.name = :name_1",
        ) 

def test_table_to_alias_14(self):
        t1alias = t1.alias("t1alias")
        vis = sql_util.ClauseAdapter(t1alias)
        t2alias = t2.alias("t2alias")
        vis.chain(sql_util.ClauseAdapter(t2alias))
        self.assert_compile(
            vis.traverse(
                select(["*"], t1.c.col1 == t2.c.col2, from_obj=[t1, t2])
            ),
            "SELECT * FROM table1 AS t1alias, table2 "
            "AS t2alias WHERE t1alias.col1 = "
            "t2alias.col2",
        ) 

def test_recursive_equivalents(self):
        m = MetaData()
        a = Table("a", m, Column("x", Integer), Column("y", Integer))
        b = Table("b", m, Column("x", Integer), Column("y", Integer))
        c = Table("c", m, Column("x", Integer), Column("y", Integer))

        # force a recursion overflow, by linking a.c.x<->c.c.x, and
        # asking for a nonexistent col.  corresponding_column should prevent
        # endless depth.
        adapt = sql_util.ClauseAdapter(
            b, equivalents={a.c.x: set([c.c.x]), c.c.x: set([a.c.x])}
        )
        assert adapt._corresponding_column(a.c.x, False) is None 

def test_multilevel_equivalents(self):
        m = MetaData()
        a = Table("a", m, Column("x", Integer), Column("y", Integer))
        b = Table("b", m, Column("x", Integer), Column("y", Integer))
        c = Table("c", m, Column("x", Integer), Column("y", Integer))

        alias = select([a]).select_from(a.join(b, a.c.x == b.c.x)).alias()

        # two levels of indirection from c.x->b.x->a.x, requires recursive
        # corresponding_column call
        adapt = sql_util.ClauseAdapter(
            alias, equivalents={b.c.x: set([a.c.x]), c.c.x: set([b.c.x])}
        )
        assert adapt._corresponding_column(a.c.x, False) is alias.c.x
        assert adapt._corresponding_column(c.c.x, False) is alias.c.x 

def test_aliasedselect_to_aliasedselect_straight(self):

        # original issue from ticket #904

        s1 = select([t1]).alias("foo")
        s2 = select([s1]).limit(5).offset(10).alias()
        self.assert_compile(
            sql_util.ClauseAdapter(s2).traverse(s1),
            "SELECT foo.col1, foo.col2, foo.col3 FROM "
            "(SELECT table1.col1 AS col1, table1.col2 "
            "AS col2, table1.col3 AS col3 FROM table1) "
            "AS foo LIMIT :param_1 OFFSET :param_2",
            {"param_1": 5, "param_2": 10},
        ) 

def test_functions(self):
        self.assert_compile(
            sql_util.ClauseAdapter(t1.alias()).traverse(func.count(t1.c.col1)),
            "count(table1_1.col1)",
        )
        s = select([func.count(t1.c.col1)])
        self.assert_compile(
            sql_util.ClauseAdapter(t1.alias()).traverse(s),
            "SELECT count(table1_1.col1) AS count_1 "
            "FROM table1 AS table1_1",
        ) 

def test_recursive(self):
        metadata = MetaData()
        a = Table("a", metadata, Column("id", Integer, primary_key=True))
        b = Table(
            "b",
            metadata,
            Column("id", Integer, primary_key=True),
            Column("aid", Integer, ForeignKey("a.id")),
        )
        c = Table(
            "c",
            metadata,
            Column("id", Integer, primary_key=True),
            Column("bid", Integer, ForeignKey("b.id")),
        )

        d = Table(
            "d",
            metadata,
            Column("id", Integer, primary_key=True),
            Column("aid", Integer, ForeignKey("a.id")),
        )

        u = union(
            a.join(b).select().apply_labels(),
            a.join(d).select().apply_labels(),
        ).alias()

        self.assert_compile(
            sql_util.ClauseAdapter(u).traverse(
                select([c.c.bid]).where(c.c.bid == u.c.b_aid)
            ),
            "SELECT c.bid "
            "FROM c, (SELECT a.id AS a_id, b.id AS b_id, b.aid AS b_aid "
            "FROM a JOIN b ON a.id = b.aid UNION SELECT a.id AS a_id, d.id "
            "AS d_id, d.aid AS d_aid "
            "FROM a JOIN d ON a.id = d.aid) AS anon_1 "
            "WHERE c.bid = anon_1.b_aid",
        ) 

def adapt(adapt_with, expression):
    if isinstance(expression.expression, sa.Column):
        cols = get_attrs(adapt_with)
        return getattr(cols, expression.name)
    if not hasattr(adapt_with, 'is_derived_from'):
        adapt_with = sa.inspect(adapt_with).selectable
    return ClauseAdapter(adapt_with).traverse(expression.expression) 

def test_table_to_alias_12(self):
        t1alias = t1.alias("t1alias")
        vis = sql_util.ClauseAdapter(t1alias)
        ff = vis.traverse(func.count(t1.c.col1).label("foo"))
        self.assert_compile(
            select([ff]),
            "SELECT count(t1alias.col1) AS foo FROM " "table1 AS t1alias",
        )
        assert list(_from_objects(ff)) == [t1alias]

    # def test_table_to_alias_2(self):
    # TODO: self.assert_compile(vis.traverse(select([func.count(t1.c
    # .col1).l abel('foo')]), clone=True), "SELECT
    # count(t1alias.col1) AS foo FROM table1 AS t1alias") 

def test_table_to_alias_8(self):
        t1alias = t1.alias("t1alias")
        vis = sql_util.ClauseAdapter(t1alias)
        self.assert_compile(
            vis.traverse(
                case([(5, t1.c.col2)], value=t1.c.col1, else_=t1.c.col1)
            ),
            "CASE t1alias.col1 WHEN :param_1 THEN "
            "t1alias.col2 ELSE t1alias.col1 END",
        ) 

def test_table_to_alias_7(self):
        t1alias = t1.alias("t1alias")
        vis = sql_util.ClauseAdapter(t1alias)
        self.assert_compile(
            vis.traverse(case([(t1.c.col1 == 5, t1.c.col2)], else_=t1.c.col1)),
            "CASE WHEN (t1alias.col1 = :col1_1) THEN "
            "t1alias.col2 ELSE t1alias.col1 END",
        ) 

def test_table_to_alias_4(self):
        t1alias = t1.alias("t1alias")
        vis = sql_util.ClauseAdapter(t1alias)
        self.assert_compile(
            vis.traverse(
                select(
                    [literal_column("*")],
                    t1.c.col1 == t2.c.col2,
                    from_obj=[t1, t2],
                )
            ),
            "SELECT * FROM table1 AS t1alias, table2 "
            "WHERE t1alias.col1 = table2.col2",
        ) 

def test_table_to_alias_2(self):
        t1alias = t1.alias("t1alias")
        vis = sql_util.ClauseAdapter(t1alias)
        self.assert_compile(
            vis.traverse(select([literal_column("*")], from_obj=[t1])),
            "SELECT * FROM table1 AS t1alias",
        ) 

def test_table_to_alias_1(self):
        t1alias = t1.alias("t1alias")

        vis = sql_util.ClauseAdapter(t1alias)
        ff = vis.traverse(func.count(t1.c.col1).label("foo"))
        assert list(_from_objects(ff)) == [t1alias] 

def test_this_thing_using_setup_joins_two(self):
        s = (
            future_select(t1.c.col1)
            .join(t2, t1.c.col1 == t2.c.col2)
            .subquery()
        )
        s2 = future_select(s.c.col1)

        self.assert_compile(
            s2,
            "SELECT anon_1.col1 FROM (SELECT table1.col1 AS col1 "
            "FROM table1 JOIN table2 ON table1.col1 = table2.col2) AS anon_1",
        )

        t1alias = t1.alias("t1alias")
        j = t1.join(t1alias, t1.c.col1 == t1alias.c.col2)

        vis = sql_util.ClauseAdapter(j)

        s2 = vis.traverse(s2)
        self.assert_compile(
            s2,
            "SELECT anon_1.col1 FROM (SELECT table1.col1 AS col1 "
            "FROM table1 JOIN table1 AS t1alias "
            "ON table1.col1 = t1alias.col2 "
            "JOIN table2 ON table1.col1 = table2.col2) AS anon_1",
        ) 

def test_alias(self):
        subq = t2.select().alias("subq")
        s = select(
            [t1.c.col1, subq.c.col1],
            from_obj=[t1, subq, t1.join(subq, t1.c.col1 == subq.c.col2)],
        )
        orig = str(s)
        s2 = CloningVisitor().traverse(s)
        eq_(orig, str(s))
        eq_(str(s), str(s2))

        s4 = CloningVisitor().traverse(s2)
        eq_(orig, str(s))
        eq_(str(s), str(s2))
        eq_(str(s), str(s4))

        s3 = sql_util.ClauseAdapter(table("foo")).traverse(s)
        eq_(orig, str(s))
        eq_(str(s), str(s3))

        s4 = sql_util.ClauseAdapter(table("foo")).traverse(s3)
        eq_(orig, str(s))
        eq_(str(s), str(s3))
        eq_(str(s), str(s4))

        subq = subq.alias("subq")
        s = select(
            [t1.c.col1, subq.c.col1],
            from_obj=[t1, subq, t1.join(subq, t1.c.col1 == subq.c.col2)],
        )
        s5 = CloningVisitor().traverse(s)
        eq_(str(s), str(s5))