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
3
Source : test_compiler.py
with MIT License
from sqlalchemy
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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