sqlalchemy.select.subquery

Here are the examples of the python api sqlalchemy.select.subquery taken from open source projects. By voting up you can indicate which examples are most useful and appropriate.

51 Examples 7

3 Source : test_compiler.py
with MIT License
from sqlalchemy

    def test_subquery(self):
        t = table("sometable", column("col1"), column("col2"))
        s = select(t).subquery()
        s = select(s.c.col1, s.c.col2)

        self.assert_compile(
            s,
            "SELECT anon_1.col1, anon_1.col2 FROM (SELECT "
            "sometable.col1 AS col1, sometable.col2 "
            "AS col2 FROM sometable) anon_1",
        )

    def test_bindparam_quote(self):

3 Source : test_types.py
with MIT License
from sqlalchemy

    def test_rowid(self, metadata, connection):
        t = Table("t1", metadata, Column("x", Integer))

        t.create(connection)
        connection.execute(t.insert(), {"x": 5})
        s1 = select(t).subquery()
        s2 = select(column("rowid")).select_from(s1)
        rowid = connection.scalar(s2)

        # the ROWID type is not really needed here,
        # as cx_oracle just treats it as a string,
        # but we want to make sure the ROWID works...
        rowid_col = column("rowid", oracle.ROWID)
        s3 = select(t.c.x, rowid_col).where(
            rowid_col == cast(rowid, oracle.ROWID)
        )
        eq_(connection.execute(s3).fetchall(), [(5, rowid)])

    def test_interval(self, metadata, connection):

3 Source : test_core_compilation.py
with MIT License
from sqlalchemy

    def test_non_deferred_col_prop_targetable_in_subq(
        self, non_deferred_fixture
    ):
        """test for #6661"""
        User = non_deferred_fixture

        subq = select(User).subquery()

        assert hasattr(subq.c, "name_upper")

    def test_recursive_cte_render_on_deferred(self, deferred_fixture):

3 Source : test_relationship_criteria.py
with MIT License
from sqlalchemy

    def test_select_count_subquery_global_criteria(self, user_address_fixture):
        User, Address = user_address_fixture

        stmt = select(User).subquery()

        stmt = (
            select(sql.func.count())
            .select_from(stmt)
            .options(with_loader_criteria(User, User.id != 8))
        )

        self.assert_compile(
            stmt,
            "SELECT count(*) AS count_1 FROM (SELECT users.id AS id, "
            "users.name AS name FROM users WHERE users.id != :id_1) AS anon_1",
        )

    def test_query_outerjoin_global_criteria(self, user_address_fixture):

3 Source : test_compiler.py
with MIT License
from sqlalchemy

    def test_alias_nesting_subquery(self):
        stmt = select(table1).subquery()
        self.assert_compile(
            select(stmt.alias("foo").alias("bar").alias("bat")),
            "SELECT bat.myid, bat.name, bat.description FROM "
            "(SELECT mytable.myid AS myid, mytable.name AS name, "
            "mytable.description AS description FROM mytable) AS bat",
        )

        self.assert_compile(
            select(stmt.alias("foo").alias(None).alias(None)),
            "SELECT anon_1.myid, anon_1.name, anon_1.description FROM "
            "(SELECT mytable.myid AS myid, mytable.name AS name, "
            "mytable.description AS description FROM mytable) AS anon_1",
        )

    def test_prefix(self):

3 Source : test_deprecations.py
with MIT License
from sqlalchemy

    def test_as_scalar_from_subquery(self):
        with testing.expect_deprecated(
            r"The Subquery.as_scalar\(\) method, which was previously "
            r"``Alias.as_scalar\(\)`` prior to version 1.4"
        ):
            stmt = select(self.table1.c.myid).subquery().as_scalar()

        is_true(stmt.compare(select(self.table1.c.myid).scalar_subquery()))

    def test_fromclause_subquery(self):

3 Source : test_external_traversal.py
with MIT License
from sqlalchemy

    def test_aliased_cloned_column_adapt_exported(self):
        clause = select(t1.c.col1, func.foo(t1.c.col2).label("foo")).subquery()

        aliased1 = select(clause.c.col1, clause.c.foo).subquery()
        aliased2 = clause
        aliased2.c.col1, aliased2.c.foo
        aliased3 = cloned_traverse(aliased2, {}, {})

        # also fixed by [ticket:2419].  When we look at the
        # *outside* columns of aliased3, they previously did not
        # have an _is_clone_of pointer.   But we now modified _make_proxy
        # to assign this.
        adapter = sql_util.ColumnAdapter(aliased1)
        f1 = select(*[adapter.columns[c] for c in aliased2.c])
        f2 = select(*[adapter.columns[c] for c in aliased3.c])
        eq_(str(f1), str(f2))

    def test_aliased_cloned_schema_column_adapt_exported(self):

3 Source : test_external_traversal.py
with MIT License
from sqlalchemy

    def test_aliased_cloned_schema_column_adapt_exported(self):
        clause = select(t3.c.col1, func.foo(t3.c.col2).label("foo")).subquery()

        aliased1 = select(clause.c.col1, clause.c.foo).subquery()
        aliased2 = clause
        aliased2.c.col1, aliased2.c.foo
        aliased3 = cloned_traverse(aliased2, {}, {})

        # also fixed by [ticket:2419].  When we look at the
        # *outside* columns of aliased3, they previously did not
        # have an _is_clone_of pointer.   But we now modified _make_proxy
        # to assign this.
        adapter = sql_util.ColumnAdapter(aliased1)
        f1 = select(*[adapter.columns[c] for c in aliased2.c])
        f2 = select(*[adapter.columns[c] for c in aliased3.c])
        eq_(str(f1), str(f2))

    def test_labeled_expression_adapt(self):

3 Source : test_functions.py
with MIT License
from sqlalchemy

    def test_column_valued_subquery(self):
        x = func.generate_series(1, 2).column_valued("x")
        y = func.generate_series(3, 4).column_valued("y")
        subq = select(x, y).subquery()
        stmt = select(subq).where(subq.c.x > 2)

        self.assert_compile(
            stmt,
            "SELECT anon_1.x, anon_1.y FROM "
            "(SELECT x, y FROM "
            "generate_series(:generate_series_1, :generate_series_2) AS x, "
            "generate_series(:generate_series_3, :generate_series_4) AS y"
            ") AS anon_1 "
            "WHERE anon_1.x > :x_1",
        )

    @testing.combinations((True,), (False,))

3 Source : test_resultset.py
with MIT License
from sqlalchemy

    def _adapt_result_columns_fixture_three(self):
        keyed1 = self.tables.keyed1
        stmt = select(keyed1.c.b, keyed1.c.q.label("foo")).subquery()

        return select(stmt.c.b, stmt.c.foo)

    def _adapt_result_columns_fixture_four(self):

3 Source : test_roles.py
with MIT License
from sqlalchemy

    def test_strict_from_clause_role(self):
        stmt = select(t).subquery()
        is_true(
            expect(roles.StrictFromClauseRole, stmt).compare(
                select(t).subquery()
            )
        )

    def test_strict_from_clause_role_disallow_select(self):

3 Source : test_roles.py
with MIT License
from sqlalchemy

    def test_anonymized_from_clause_role(self):
        is_true(expect(roles.AnonymizedFromClauseRole, t).compare(t.alias()))

        # note the compare for subquery().alias(), even if it is two
        # plain Alias objects (which it won't be once we introduce the
        # Subquery class), still compares based on alias() being present
        # twice, that is, alias().alias() builds an alias of an alias, rather
        # than just replacing the outer alias.
        is_true(
            expect(
                roles.AnonymizedFromClauseRole, select(t).subquery()
            ).compare(select(t).subquery().alias())
        )

    def test_statement_coercion_sequence(self):

3 Source : test_select.py
with MIT License
from sqlalchemy

    def test_select_tuple_subquery(self):
        subq = select(
            table1.c.name, tuple_(table1.c.myid, table1.c.name)
        ).subquery()

        stmt = select(subq.c.name)

        # if we aren't fetching it, then render it
        self.assert_compile(
            stmt,
            "SELECT anon_1.name FROM (SELECT mytable.name AS name, "
            "(mytable.myid, mytable.name) AS anon_2 FROM mytable) AS anon_1",
        )

    @testing.combinations(

3 Source : test_selectable.py
with MIT License
from sqlalchemy

    def test_clone_col_list_changes_then_proxy(self):
        t = table("t", column("q"), column("p"))
        stmt = select(t.c.q).subquery()

        def add_column(stmt):
            stmt.add_columns.non_generative(stmt, t.c.p)

        stmt2 = visitors.cloned_traverse(stmt, {}, {"select": add_column})
        eq_(list(stmt.c.keys()), ["q"])
        eq_(list(stmt2.c.keys()), ["q", "p"])

    def test_clone_col_list_changes_then_schema_proxy(self):

3 Source : test_selectable.py
with MIT License
from sqlalchemy

    def test_clone_col_list_changes_then_schema_proxy(self):
        t = Table("t", MetaData(), Column("q", Integer), Column("p", Integer))
        stmt = select(t.c.q).subquery()

        def add_column(stmt):
            stmt.add_columns.non_generative(stmt, t.c.p)

        stmt2 = visitors.cloned_traverse(stmt, {}, {"select": add_column})
        eq_(list(stmt.c.keys()), ["q"])
        eq_(list(stmt2.c.keys()), ["q", "p"])

    @testing.combinations(

3 Source : test_selectable.py
with MIT License
from sqlalchemy

    def test_const_object_correspondence(self, c):
        """test #7154"""

        stmt = select(c).subquery()

        stmt2 = select(stmt)

        is_(
            stmt2.selected_columns.corresponding_column(c),
            stmt2.selected_columns[0],
        )

    def test_append_column_after_visitor_replace(self):

3 Source : test_selectable.py
with MIT License
from sqlalchemy

    def test_against_cloned_non_table(self):
        # test that corresponding column digs across
        # clone boundaries with anonymous labeled elements
        col = func.count().label("foo")
        sel = select(col).subquery()

        sel2 = visitors.ReplacingCloningVisitor().traverse(sel)
        assert sel2.corresponding_column(col) is sel2.c.foo

        sel3 = visitors.ReplacingCloningVisitor().traverse(sel2)
        assert sel3.corresponding_column(col) is sel3.c.foo

    def test_with_only_generative(self):

3 Source : test_selectable.py
with MIT License
from sqlalchemy

    def test_column_labels(self):
        a = select(
            table1.c.col1.label("acol1"),
            table1.c.col2.label("acol2"),
            table1.c.col3.label("acol3"),
        ).subquery()
        j = join(a, table2)
        criterion = a.c.acol1 == table2.c.col2
        self.assert_(criterion.compare(j.onclause))

    def test_labeled_select_corresponding(self):

3 Source : test_selectable.py
with MIT License
from sqlalchemy

    def test_unusual_column_elements_text(self):
        """test that .c excludes text()."""

        s = select(table1.c.col1, text("foo")).subquery()
        eq_(list(s.c), [s.c.col1])

    def test_unusual_column_elements_clauselist(self):

3 Source : test_selectable.py
with MIT License
from sqlalchemy

    def test_unusual_column_elements_clauselist(self):
        """Test that raw ClauseList is expanded into .c."""

        from sqlalchemy.sql.expression import ClauseList

        s = select(
            table1.c.col1, ClauseList(table1.c.col2, table1.c.col3)
        ).subquery()
        eq_(list(s.c), [s.c.col1, s.c.col2, s.c.col3])

    def test_unusual_column_elements_boolean_clauselist(self):

3 Source : test_selectable.py
with MIT License
from sqlalchemy

    def test_unusual_column_elements_boolean_clauselist(self):
        """test that BooleanClauseList is placed as single element in .c."""

        c2 = and_(table1.c.col2 == 5, table1.c.col3 == 4)
        s = select(table1.c.col1, c2).subquery()
        eq_(list(s.c), [s.c.col1, s.corresponding_column(c2)])

    def test_from_list_deferred_constructor(self):

3 Source : test_text.py
with MIT License
from sqlalchemy

    def test_over(self):
        stmt = select(column("foo"), column("bar")).subquery()
        stmt = select(
            func.row_number().over(order_by="foo", partition_by="bar")
        ).select_from(stmt)

        self.assert_compile(
            stmt,
            "SELECT row_number() OVER "
            "(PARTITION BY anon_2.bar ORDER BY anon_2.foo) "
            "AS anon_1 FROM (SELECT foo, bar) AS anon_2",
        )

    def test_union_column(self):

3 Source : test_utils.py
with MIT License
from sqlalchemy

    def test_find_tables_aliases(self):
        metadata = MetaData()
        common = Table(
            "common",
            metadata,
            Column("id", Integer, primary_key=True),
            Column("data", Integer),
            Column("extra", String(45)),
        )

        calias = common.alias()
        subset_select = select(common.c.id, calias.c.data).subquery()

        eq_(
            set(sql_util.find_tables(subset_select, include_aliases=True)),
            {common, calias, subset_select},
        )

    def test_incompatible_options_add_clslevel(self):

0 Source : test_compiler.py
with MIT License
from sqlalchemy

    def test_no_compile_for_col_label(self):
        class MyThingy(FunctionElement):
            inherit_cache = True

        @compiles(MyThingy)
        def visit_thingy(thingy, compiler, **kw):
            raise Exception(
                "unfriendly exception, dont catch this, dont run this"
            )

        @compiles(MyThingy, "postgresql")
        def visit_thingy_pg(thingy, compiler, **kw):
            return "mythingy"

        subq = select(MyThingy("text")).subquery()

        stmt = select(subq)

        self.assert_compile(
            stmt,
            "SELECT anon_2.anon_1 FROM (SELECT mythingy AS anon_1) AS anon_2",
            dialect="postgresql",
        )

    def test_stateful(self):

0 Source : test_core_compilation.py
with MIT License
from sqlalchemy

    def test_no_joinedload_in_subquery_select_rows(self, joinedload_fixture):
        User, Address = joinedload_fixture

        sess = fixture_session()
        stmt1 = sess.query(User).subquery()
        stmt1 = sess.query(stmt1)

        stmt2 = select(User).subquery()

        stmt2 = select(stmt2)

        expected = (
            "SELECT anon_1.id, anon_1.name FROM "
            "(SELECT users.id AS id, users.name AS name "
            "FROM users) AS anon_1"
        )
        self.assert_compile(
            stmt1._final_statement(legacy_query_style=False),
            expected,
        )

        self.assert_compile(stmt2, expected)

    def test_no_joinedload_in_subquery_select_entity(self, joinedload_fixture):

0 Source : test_core_compilation.py
with MIT License
from sqlalchemy

    def test_no_joinedload_in_subquery_select_entity(self, joinedload_fixture):
        User, Address = joinedload_fixture

        sess = fixture_session()
        stmt1 = sess.query(User).subquery()
        ua = aliased(User, stmt1)
        stmt1 = sess.query(ua)

        stmt2 = select(User).subquery()

        ua = aliased(User, stmt2)
        stmt2 = select(ua)

        expected = (
            "SELECT anon_1.id, anon_1.name, addresses_1.id AS id_1, "
            "addresses_1.user_id, addresses_1.email_address FROM "
            "(SELECT users.id AS id, users.name AS name FROM users) AS anon_1 "
            "LEFT OUTER JOIN addresses AS addresses_1 "
            "ON anon_1.id = addresses_1.user_id"
        )

        self.assert_compile(
            stmt1._final_statement(legacy_query_style=False),
            expected,
        )

        self.assert_compile(stmt2, expected)

    def test_deferred_subq_one(self, deferred_fixture):

0 Source : test_core_compilation.py
with MIT License
from sqlalchemy

    def test_deferred_subq_one(self, deferred_fixture):
        """test for #6661"""
        User = deferred_fixture

        subq = select(User).subquery()

        u1 = aliased(User, subq)
        q = select(u1)

        self.assert_compile(
            q,
            "SELECT anon_1.id "
            "FROM (SELECT users.name AS name, "
            "users.id AS id FROM users) AS anon_1",
        )

        # testing deferred opts separately for deterministic SQL generation

        q = select(u1).options(undefer(u1.name))

        self.assert_compile(
            q,
            "SELECT anon_1.name, anon_1.id "
            "FROM (SELECT users.name AS name, "
            "users.id AS id FROM users) AS anon_1",
        )

        q = select(u1).options(undefer(u1.name_upper))

        self.assert_compile(
            q,
            "SELECT upper(anon_1.name) AS upper_1, anon_1.id "
            "FROM (SELECT users.name AS name, "
            "users.id AS id FROM users) AS anon_1",
        )

    def test_non_deferred_subq_one(self, non_deferred_fixture):

0 Source : test_core_compilation.py
with MIT License
from sqlalchemy

    def test_non_deferred_subq_one(self, non_deferred_fixture):
        """test for #6661

        cols that aren't deferred go into subqueries.  1.3 did this also.

        """
        User = non_deferred_fixture

        subq = select(User).subquery()

        u1 = aliased(User, subq)
        q = select(u1)

        self.assert_compile(
            q,
            "SELECT upper(anon_1.name) AS upper_1, anon_1.id, anon_1.name "
            "FROM (SELECT upper(users.name) AS upper_2, users.id AS id, "
            "users.name AS name FROM users) AS anon_1",
        )

    def test_deferred_subq_two(self, deferred_fixture):

0 Source : test_core_compilation.py
with MIT License
from sqlalchemy

    def test_deferred_subq_two(self, deferred_fixture):
        """test for #6661

        in this test, we are only confirming the current contract of ORM
        subqueries which is that deferred + derived column_property's don't
        export themselves into the .c. collection of a subquery.
        We might want to revisit this in some way.

        """
        User = deferred_fixture

        subq = select(User).subquery()

        assert not hasattr(subq.c, "name_upper")

        # "undefer" it by including it
        subq = select(User, User.name_upper).subquery()

        assert hasattr(subq.c, "name_upper")

    def test_non_deferred_col_prop_targetable_in_subq(

0 Source : test_core_compilation.py
with MIT License
from sqlalchemy

    def test_subquery_col_expressions_wpoly_one(self):
        Person, Manager, Engineer = self.classes(
            "Person", "Manager", "Engineer"
        )

        wp1 = with_polymorphic(Person, [Manager, Engineer])

        subq1 = select(wp1).subquery()

        wp2 = with_polymorphic(Person, [Engineer, Manager])
        subq2 = select(wp2).subquery()

        # first thing we see, is that when we go through with_polymorphic,
        # the entities that get placed into the aliased class go through
        # Mapper._mappers_from_spec(), which matches them up to the
        # existing Mapper.self_and_descendants collection, meaning,
        # the order is the same every time.   Assert here that's still
        # happening.  If a future internal change modifies this assumption,
        # that's not necessarily bad, but it would change things.

        eq_(
            subq1.c.keys(),
            [
                "person_id",
                "company_id",
                "name",
                "type",
                "person_id_1",
                "status",
                "engineer_name",
                "primary_language",
                "person_id_1",
                "status_1",
                "manager_name",
            ],
        )
        eq_(
            subq2.c.keys(),
            [
                "person_id",
                "company_id",
                "name",
                "type",
                "person_id_1",
                "status",
                "engineer_name",
                "primary_language",
                "person_id_1",
                "status_1",
                "manager_name",
            ],
        )

    def test_subquery_col_expressions_wpoly_two(self):

0 Source : test_core_compilation.py
with MIT License
from sqlalchemy

    def test_subquery_col_expressions_wpoly_two(self):
        Person, Manager, Engineer = self.classes(
            "Person", "Manager", "Engineer"
        )

        wp1 = with_polymorphic(Person, [Manager, Engineer])

        subq1 = select(wp1).subquery()

        stmt = select(subq1).where(
            or_(
                subq1.c.engineer_name == "dilbert",
                subq1.c.manager_name == "dogbert",
            )
        )

        self.assert_compile(
            stmt,
            "SELECT anon_1.person_id, anon_1.company_id, anon_1.name, "
            "anon_1.type, anon_1.person_id AS person_id_1, anon_1.status, "
            "anon_1.engineer_name, anon_1.primary_language, "
            "anon_1.person_id AS person_id_2, anon_1.status AS status_1, "
            "anon_1.manager_name FROM "
            "%s WHERE "
            "anon_1.engineer_name = :engineer_name_1 "
            "OR anon_1.manager_name = :manager_name_1" % (self.default_punion),
        )


class ImplicitWithPolymorphicTest(

0 Source : test_froms.py
with MIT License
from sqlalchemy

    def test_nested_alias_none_to_subquery(self):
        """test #7576"""

        User = self.classes.User

        subq = select(User.id, User.name).subquery()

        u1 = aliased(User, subq)

        self.assert_compile(
            select(u1),
            "SELECT anon_1.id, anon_1.name FROM "
            "(SELECT users.id AS id, users.name AS name FROM users) AS anon_1",
        )

        u2 = aliased(u1)

        self.assert_compile(
            select(u2),
            "SELECT anon_1.id, anon_1.name FROM "
            "(SELECT users.id AS id, users.name AS name FROM users) AS anon_1",
        )

    def test_nested_alias_subquery_to_subquery_w_replace(self):

0 Source : test_froms.py
with MIT License
from sqlalchemy

    def test_nested_alias_subquery_to_subquery_w_replace(self):
        """test #7576"""

        User = self.classes.User

        subq = select(User.id, User.name).subquery()

        u1 = aliased(User, subq)

        self.assert_compile(
            select(u1),
            "SELECT anon_1.id, anon_1.name FROM "
            "(SELECT users.id AS id, users.name AS name FROM users) AS anon_1",
        )

        u2 = aliased(u1, subq)

        self.assert_compile(
            select(u2),
            "SELECT anon_1.id, anon_1.name FROM "
            "(SELECT users.id AS id, users.name AS name FROM users) AS anon_1",
        )

    def test_nested_alias_subquery_to_subquery_w_adaption(self):

0 Source : test_froms.py
with MIT License
from sqlalchemy

    def test_nested_alias_subquery_to_subquery_w_adaption(self):
        """test #7576"""

        User = self.classes.User

        inner_subq = select(User.id, User.name).subquery()

        u1 = aliased(User, inner_subq)

        self.assert_compile(
            select(u1),
            "SELECT anon_1.id, anon_1.name FROM "
            "(SELECT users.id AS id, users.name AS name FROM users) AS anon_1",
        )

        outer_subq = select(u1.id, u1.name).subquery()

        u2 = aliased(u1, outer_subq)

        self.assert_compile(
            select(u2),
            "SELECT anon_1.id, anon_1.name FROM "
            "(SELECT anon_2.id AS id, anon_2.name AS name FROM "
            "(SELECT users.id AS id, users.name AS name FROM users) "
            "AS anon_2) AS anon_1",
        )

        outer_subq = (
            select(u1.id, u1.name, User.id, User.name)
            .where(u1.id > User.id)
            .subquery()
        )
        u2 = aliased(u1, outer_subq)

        # query here is:
        # SELECT derived_from_inner_subq.id, derived_from_inner_subq.name
        # FROM (
        #    SELECT ... FROM inner_subq, users WHERE inner_subq.id > users.id
        # ) as outer_subq
        self.assert_compile(
            select(u2),
            "SELECT anon_1.id, anon_1.name FROM "
            "(SELECT anon_2.id AS id, anon_2.name AS name, users.id AS id_1, "
            "users.name AS name_1 FROM "
            "(SELECT users.id AS id, users.name AS name FROM users) "
            "AS anon_2, users "
            "WHERE anon_2.id > users.id) AS anon_1",
        )

    def test_nested_alias_subquery_w_alias_to_none(self):

0 Source : test_froms.py
with MIT License
from sqlalchemy

    def test_multiple_with_column_entities_oldstyle(self):
        # this is now very awkward and not very useful
        User = self.classes.User

        subq = select(User.id).subquery()

        uq = aliased(User, subq)

        subq2 = (
            select(uq.id)
            .add_columns(func.count().label("foo"))
            .group_by(uq.id)
            .order_by(uq.id)
            .subquery()
        )

        uq2 = aliased(User, subq2)
        sess = fixture_session()

        eq_(
            sess.query(uq2.id, subq2.c.foo).all(),
            [(7, 1), (8, 1), (9, 1), (10, 1)],
        )

    def test_multiple_with_column_entities_newstyle(self):

0 Source : test_compare.py
with MIT License
from sqlalchemy

    def _statements_w_anonymous_col_names():
        def one():
            c = column("q")

            l = c.label(None)

            # new case as of Id810f485c5f7ed971529489b84694e02a3356d6d
            subq = select(l).subquery()

            # this creates a ColumnClause as a proxy to the Label() that has
            # an anonymous name, so the column has one too.
            anon_col = subq.c[0]

            # then when BindParameter is created, it checks the label
            # and doesn't double up on the anonymous name which is uncachable
            return anon_col > 5

        def two():
            c = column("p")

            l = c.label(None)

            # new case as of Id810f485c5f7ed971529489b84694e02a3356d6d
            subq = select(l).subquery()

            # this creates a ColumnClause as a proxy to the Label() that has
            # an anonymous name, so the column has one too.
            anon_col = subq.c[0]

            # then when BindParameter is created, it checks the label
            # and doesn't double up on the anonymous name which is uncachable
            return anon_col > 5

        def three():

            l1, l2 = table_a.c.a.label(None), table_a.c.b.label(None)

            stmt = select(table_a.c.a, table_a.c.b, l1, l2)

            subq = stmt.subquery()
            return select(subq).where(subq.c[2] == 10)

        return (
            one(),
            two(),
            three(),
        )

    fixtures.append(_statements_w_anonymous_col_names)

0 Source : test_compare.py
with MIT License
from sqlalchemy

    def test_compare_annotated_wo_annotations(self):
        t = table("t", column("x"), column("y"))
        x_a = t.c.x._annotate({})
        x_b = t.c.x._annotate({"foo": True})

        is_true(t.c.x.compare(x_a))
        is_true(x_b.compare(x_a))

        is_true(x_a.compare(t.c.x))
        is_false(x_a.compare(t.c.y))
        is_false(t.c.y.compare(x_a))
        is_true((t.c.x == 5).compare(x_a == 5))
        is_false((t.c.y == 5).compare(x_a == 5))

        s = select(t).subquery()
        x_p = s.c.x
        is_false(x_a.compare(x_p))
        is_false(t.c.x.compare(x_p))
        x_p_a = x_p._annotate({})
        is_true(x_p_a.compare(x_p))
        is_true(x_p.compare(x_p_a))
        is_false(x_p_a.compare(x_a))


class ExecutableFlagsTest(fixtures.TestBase):

0 Source : test_compiler.py
with MIT License
from sqlalchemy

    def test_percent_names_collide_with_anonymizing(self, name):
        table1 = table("t1", column(name))

        jj = select(table1.c[name]).subquery()
        jjj = join(table1, jj, table1.c[name] == jj.c[name])

        j2 = (
            jjj.select()
            .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)
            .subquery("foo")
        )

        self.assert_compile(
            j2.select(),
            'SELECT foo."t1_%(name)s", foo."anon_1_%(name)s" FROM '
            '(SELECT t1."%(name)s" AS "t1_%(name)s", anon_1."%(name)s" '
            'AS "anon_1_%(name)s" FROM t1 JOIN (SELECT t1."%(name)s" AS '
            '"%(name)s" FROM t1) AS anon_1 ON t1."%(name)s" = '
            'anon_1."%(name)s") AS foo' % {"name": name},
        )

    def test_exists(self):

0 Source : test_compiler.py
with MIT License
from sqlalchemy

    def test_bind_given_anon_name_dont_double(self):
        c = column("id")
        l = c.label(None)

        # new case as of Id810f485c5f7ed971529489b84694e02a3356d6d
        subq = select(l).subquery()

        # this creates a ColumnClause as a proxy to the Label() that has
        # an anonymous name, so the column has one too.
        anon_col = subq.c[0]
        assert isinstance(anon_col.name, elements._anonymous_label)

        # then when BindParameter is created, it checks the label
        # and doesn't double up on the anonymous name which is uncachable
        expr = anon_col > 5

        self.assert_compile(
            expr, "anon_1.id_1 > :param_1", checkparams={"param_1": 5}
        )

        # see also test_compare.py -> _statements_w_anonymous_col_names
        # fixture for cache key

    def test_bind_as_col(self):

0 Source : test_compiler.py
with MIT License
from sqlalchemy

    def test_nested_api(self):
        from sqlalchemy.engine.cursor import CursorResultMetaData

        stmt2 = select(table2).subquery()

        stmt1 = select(table1).select_from(stmt2)

        contexts = {}

        int_ = Integer()

        class MyCompiler(compiler.SQLCompiler):
            def visit_select(self, stmt, *arg, **kw):

                if stmt is stmt2.element:
                    with self._nested_result() as nested:
                        contexts[stmt2.element] = nested
                        text = super(MyCompiler, self).visit_select(
                            stmt2.element,
                        )
                        self._add_to_result_map("k1", "k1", (1, 2, 3), int_)
                else:
                    text = super(MyCompiler, self).visit_select(
                        stmt, *arg, **kw
                    )
                    self._add_to_result_map("k2", "k2", (3, 4, 5), int_)
                return text

        comp = MyCompiler(default.DefaultDialect(), stmt1)
        eq_(
            CursorResultMetaData._create_description_match_map(
                contexts[stmt2.element][0]
            ),
            {
                "otherid": (
                    "otherid",
                    (
                        table2.c.otherid,
                        "otherid",
                        "otherid",
                        "myothertable_otherid",
                    ),
                    table2.c.otherid.type,
                    0,
                ),
                "othername": (
                    "othername",
                    (
                        table2.c.othername,
                        "othername",
                        "othername",
                        "myothertable_othername",
                    ),
                    table2.c.othername.type,
                    1,
                ),
                "k1": ("k1", (1, 2, 3), int_, 2),
            },
        )
        eq_(
            comp._create_result_map(),
            {
                "myid": (
                    "myid",
                    (table1.c.myid, "myid", "myid", "mytable_myid"),
                    table1.c.myid.type,
                    0,
                ),
                "k2": ("k2", (3, 4, 5), int_, 3),
                "name": (
                    "name",
                    (table1.c.name, "name", "name", "mytable_name"),
                    table1.c.name.type,
                    1,
                ),
                "description": (
                    "description",
                    (
                        table1.c.description,
                        "description",
                        "description",
                        "mytable_description",
                    ),
                    table1.c.description.type,
                    2,
                ),
            },
        )

    def test_select_wraps_for_translate_ambiguity(self):

0 Source : test_external_traversal.py
with MIT License
from sqlalchemy

    def test_traversal_size(self):
        """Test :ticket:`6304`.

        Testing that _iterate_from_elements returns only unique FROM
        clauses; overall traversal should be short and all items unique.

        """

        t = table("t", *[column(x) for x in "pqrxyz"])

        s1 = select(t.c.p, t.c.q, t.c.r, t.c.x, t.c.y, t.c.z).subquery()

        s2 = (
            select(s1.c.p, s1.c.q, s1.c.r, s1.c.x, s1.c.y, s1.c.z)
            .select_from(s1)
            .subquery()
        )

        s3 = (
            select(s2.c.p, s2.c.q, s2.c.r, s2.c.x, s2.c.y, s2.c.z)
            .select_from(s2)
            .subquery()
        )

        tt = list(s3.element._iterate_from_elements())
        eq_(tt, [s2])

        total = list(visitors.iterate(s3))
        # before the bug was fixed, this was 750
        eq_(len(total), 25)

        seen = set()
        for elem in visitors.iterate(s3):
            assert elem not in seen
            seen.add(elem)

        eq_(len(seen), 25)

    def test_change_in_place(self):

0 Source : test_from_linter.py
with MIT License
from sqlalchemy

    def test_lateral_subqueries(self, control):
        """
        ::

            test=> create table a (id integer);
            CREATE TABLE
            test=> create table b (id integer);
            CREATE TABLE
            test=> insert into a(id) values (1), (2), (3);
            INSERT 0 3
            test=> insert into b(id) values (1), (2), (3);
            INSERT 0 3

            test=> select * from (select id from a) as a1,
            lateral (select id from b where id=a1.id) as b1;
            id | id
            ----+----
            1 |  1
            2 |  2
            3 |  3
            (3 rows)

        """
        p1 = select(self.a).subquery()

        p2 = select(self.b).where(self.b.c.col_b == p1.c.col_a).subquery()

        if control == "lateral":
            p2 = p2.lateral()

        query = select(p1, p2)

        if control == "join":
            query = query.join_from(p1, p2, p1.c.col_a == p2.c.col_b)

        froms, start = find_unmatching_froms(query, p1)

        if control == "cartesian":
            assert start is p1
            assert froms == {p2}
        else:
            assert start is None
            assert froms is None

        froms, start = find_unmatching_froms(query, p2)

        if control == "cartesian":
            assert start is p2
            assert froms == {p1}
        else:
            assert start is None
            assert froms is None

    def test_lateral_subqueries_w_joins(self):

0 Source : test_from_linter.py
with MIT License
from sqlalchemy

    def test_lateral_subqueries_w_joins(self):
        p1 = select(self.a).subquery()
        p2 = (
            select(self.b)
            .where(self.b.c.col_b == p1.c.col_a)
            .subquery()
            .lateral()
        )
        p3 = (
            select(self.c)
            .where(self.c.c.col_c == p1.c.col_a)
            .subquery()
            .lateral()
        )

        query = select(p1, p2, p3).join_from(p1, p2, true()).join(p3, true())

        for p in (p1, p2, p3):
            froms, start = find_unmatching_froms(query, p)
            assert start is None
            assert froms is None

    def test_lateral_subqueries_ok_do_we_still_find_cartesians(self):

0 Source : test_from_linter.py
with MIT License
from sqlalchemy

    def test_lateral_subqueries_ok_do_we_still_find_cartesians(self):
        p1 = select(self.a).subquery()

        p3 = select(self.a).subquery()

        p2 = select(self.b).where(self.b.c.col_b == p3.c.col_a).subquery()

        p2 = p2.lateral()

        query = select(p1, p2, p3)

        froms, start = find_unmatching_froms(query, p1)

        assert start is p1
        assert froms == {p2, p3}

        froms, start = find_unmatching_froms(query, p2)

        assert start is p2
        assert froms == {p1}

        froms, start = find_unmatching_froms(query, p3)

        assert start is p3
        assert froms == {p1}

    def test_count_non_eq_comparison_operators(self):

0 Source : test_functions.py
with MIT License
from sqlalchemy

    def test_named_table_valued_subquery(self):

        fn = (
            func.json_to_recordset(  # noqa
                '[{"a":1,"b":"foo"},{"a":"2","c":"bar"}]'
            )
            .table_valued(column("a", Integer), column("b", String))
            .render_derived(with_types=True)
        )

        stmt = select(fn.c.a, fn.c.b).subquery()

        stmt = select(stmt)

        self.assert_compile(
            stmt,
            "SELECT anon_1.a, anon_1.b FROM "
            "(SELECT anon_2.a AS a, anon_2.b AS b "
            "FROM json_to_recordset(:json_to_recordset_1) "
            "AS anon_2(a INTEGER, b VARCHAR)"
            ") AS anon_1",
        )

    def test_named_table_valued_alias(self):

0 Source : test_selectable.py
with MIT License
from sqlalchemy

    def test_indirect_correspondence_on_labels(self):
        # this test depends upon 'distance' to
        # get the right result

        # same column three times

        s = select(
            table1.c.col1.label("c2"),
            table1.c.col1,
            table1.c.col1.label("c1"),
        ).subquery()

        # this tests the same thing as
        # test_direct_correspondence_on_labels below -
        # that the presence of label() affects the 'distance'
        assert s.corresponding_column(table1.c.col1) is s.c.col1

        assert s.corresponding_column(s.c.col1) is s.c.col1
        assert s.corresponding_column(s.c.c1) is s.c.c1

    def test_labeled_select_twice(self):

0 Source : test_selectable.py
with MIT License
from sqlalchemy

    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.select()
            .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)
            .subquery("foo")
        )
        assert j2.corresponding_column(table1.c.col1) is j2.c.table1_col1

    def test_clone_append_column(self):

0 Source : test_selectable.py
with MIT License
from sqlalchemy

    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_schema(self):

0 Source : test_selectable.py
with MIT License
from sqlalchemy

    def test_reduce_only_synonym_lineage(self):
        m = MetaData()
        t1 = Table(
            "t1",
            m,
            Column("x", Integer, primary_key=True),
            Column("y", Integer),
            Column("z", Integer),
        )
        # test that the first appearance in the columns clause
        # wins - t1 is first, t1.c.x wins
        s1 = select(t1).subquery()
        s2 = select(t1, s1).where(t1.c.x == s1.c.x).where(s1.c.y == t1.c.z)
        eq_(
            set(s2.reduce_columns().selected_columns),
            set([t1.c.x, t1.c.y, t1.c.z, s1.c.y, s1.c.z]),
        )

        # reverse order, s1.c.x wins
        s1 = select(t1).subquery()
        s2 = select(s1, t1).where(t1.c.x == s1.c.x).where(s1.c.y == t1.c.z)
        eq_(
            set(s2.reduce_columns().selected_columns),
            set([s1.c.x, t1.c.y, t1.c.z, s1.c.y, s1.c.z]),
        )

    def test_reduce_aliased_join(self):

0 Source : test_selectable.py
with MIT License
from sqlalchemy

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

        class MyColumn(Column):
            def __init__(self):
                Column.__init__(self, "foo", Integer)

            _constructor = Column

        t1 = Table("t1", MetaData(), MyColumn())
        s1 = t1.select().subquery()
        assert isinstance(t1.c.foo, MyColumn)
        assert isinstance(s1.c.foo, Column)

        annot_1 = t1.c.foo._annotate({})
        s2 = select(annot_1).subquery()
        assert isinstance(s2.c.foo, Column)
        annot_2 = s1._annotate({})
        assert isinstance(annot_2.c.foo, Column)

    def test_custom_construction_correct_anno_subclass(self):

See More Examples