def test_positional_binds_2(self):
orders = table("orders", column("order"))
s = select([orders.c.order, literal("x")]).cte("regional_sales")
s = select([s.c.order, literal("y")])
dialect = default.DefaultDialect()
dialect.positional = True
dialect.paramstyle = "numeric"
s1 = (
select([orders.c.order])
.where(orders.c.order == "x")
.cte("regional_sales_1")
)
s1a = s1.alias()
s2 = (
select(
[
orders.c.order == "y",
s1a.c.order,
orders.c.order,
s1.c.order,
]
)
.where(orders.c.order == "z")
.cte("regional_sales_2")
)
s3 = select([s2])
self.assert_compile(
s3,
'WITH regional_sales_1 AS (SELECT orders."order" AS "order" '
'FROM orders WHERE orders."order" = :1), regional_sales_2 AS '
'(SELECT orders."order" = :2 AS anon_1, '
'anon_2."order" AS "order", '
'orders."order" AS "order", '
'regional_sales_1."order" AS "order" FROM orders, '
"regional_sales_1 "
"AS anon_2, regional_sales_1 "
'WHERE orders."order" = :3) SELECT regional_sales_2.anon_1, '
'regional_sales_2."order", regional_sales_2."order", '
'regional_sales_2."order" FROM regional_sales_2',
checkpositional=("x", "y", "z"),
dialect=dialect,
)
def test_unicode_warnings_dialectlevel(self):
unicodedata = self.data
with testing.expect_deprecated(
"The create_engine.convert_unicode parameter and "
"corresponding dialect-level"):
dialect = default.DefaultDialect(convert_unicode=True)
dialect.supports_unicode_binds = False
s = String()
uni = s.dialect_impl(dialect).bind_processor(dialect)
uni(util.b("x"))
assert isinstance(uni(unicodedata), util.binary_type)
eq_(uni(unicodedata), unicodedata.encode("utf-8"))
def test_insert_from_select_cte_follows_insert_two(self):
dialect = default.DefaultDialect()
dialect.cte_follows_insert = True
table1 = self.tables.mytable
cte = table1.select().cte("c")
stmt = cte.select()
ins = table1.insert().from_select(table1.c, stmt)
self.assert_compile(
ins,
"INSERT INTO mytable (myid, name, description) "
"WITH c AS (SELECT mytable.myid AS myid, mytable.name AS name, "
"mytable.description AS description FROM mytable) "
"SELECT c.myid, c.name, c.description FROM c",
dialect=dialect,
)
def test_named(self):
table1 = self.tables.mytable
values = [
{
"myid": 1,
"name": "a",
"description": "b"
},
{
"myid": 2,
"name": "c",
"description": "d"
},
{
"myid": 3,
"name": "e",
"description": "f"
},
]
checkparams = {
"myid_m0": 1,
"myid_m1": 2,
"myid_m2": 3,
"name_m0": "a",
"name_m1": "c",
"name_m2": "e",
"description_m0": "b",
"description_m1": "d",
"description_m2": "f",
}
dialect = default.DefaultDialect()
dialect.supports_multivalues_insert = True
self.assert_compile(
table1.insert().values(values),
"INSERT INTO mytable (myid, name, description) VALUES "
"(:myid_m0, :name_m0, :description_m0), "
"(:myid_m1, :name_m1, :description_m1), "
"(:myid_m2, :name_m2, :description_m2)",
checkparams=checkparams,
dialect=dialect,
)
def test_unformat(self):
prep = compiler.IdentifierPreparer(default.DefaultDialect())
unformat = prep.unformat_identifiers
def a_eq(have, want):
if have != want:
print("Wanted %s" % want)
print("Received %s" % have)
self.assert_(have == want)
a_eq(unformat("foo"), ["foo"])
a_eq(unformat('"foo"'), ["foo"])
a_eq(unformat("'foo'"), ["'foo'"])
a_eq(unformat("foo.bar"), ["foo", "bar"])
a_eq(unformat('"foo"."bar"'), ["foo", "bar"])
a_eq(unformat('foo."bar"'), ["foo", "bar"])
a_eq(unformat('"foo".bar'), ["foo", "bar"])
a_eq(unformat('"foo"."b""a""r"."baz"'), ["foo", 'b"a"r', "baz"])
class TableSampleTest(fixtures.TablesTest, AssertsCompiledSQL):
__dialect__ = default.DefaultDialect(supports_native_boolean=True)
run_setup_bind = None
run_create_tables = None
@classmethod
def define_tables(cls, metadata):
Table('people', metadata,
Column('people_id', Integer, primary_key=True),
Column('age', Integer),
Column('name', String(30)))
def test_standalone(self):
table1 = self.tables.people
# no special alias handling even though clause is not in the
# context of a FROM clause
self.assert_compile(
tablesample(table1, 1, name='alias'),
'people AS alias TABLESAMPLE system(:system_1)'
)
self.assert_compile(
table1.tablesample(1, name='alias'),
'people AS alias TABLESAMPLE system(:system_1)'
)
self.assert_compile(
tablesample(table1, func.bernoulli(1), name='alias',
seed=func.random()),
'people AS alias TABLESAMPLE bernoulli(:bernoulli_1) '
'REPEATABLE (random())'
)
def test_select_from(self):
table1 = self.tables.people
self.assert_compile(
select([table1.tablesample(text('1'), name='alias').c.people_id]),
'SELECT alias.people_id FROM '
'people AS alias TABLESAMPLE system(1)'
)
def test_named_with_column_objects(self):
table1 = self.tables.mytable
values = [
{
table1.c.myid: 1,
table1.c.name: 'a',
table1.c.description: 'b'
},
{
table1.c.myid: 2,
table1.c.name: 'c',
table1.c.description: 'd'
},
{
table1.c.myid: 3,
table1.c.name: 'e',
table1.c.description: 'f'
},
]
checkparams = {
'myid_m0': 1,
'myid_m1': 2,
'myid_m2': 3,
'name_m0': 'a',
'name_m1': 'c',
'name_m2': 'e',
'description_m0': 'b',
'description_m1': 'd',
'description_m2': 'f',
}
dialect = default.DefaultDialect()
dialect.supports_multivalues_insert = True
self.assert_compile(
table1.insert().values(values),
'INSERT INTO mytable (myid, name, description) VALUES '
'(:myid_m0, :name_m0, :description_m0), '
'(:myid_m1, :name_m1, :description_m1), '
'(:myid_m2, :name_m2, :description_m2)',
checkparams=checkparams,
dialect=dialect)
def factory(dialect: str) -> interfaces.Dialect:
"""
Factories the given SQL dialect instance by name.
Args:
dialect: The SQL dialect name
Returns:
The SQL dialect instance
"""
if dialect == 'default':
return default.DefaultDialect()
if dialect in ('firebird', 'mssql', 'mysql', 'oracle', 'postgresql',
'sqlite', 'sybase'):
return dialects.registry.load(dialect)()
raise UnsupportedDialectError(
'Unsupported dialect: {}'.format(dialect))
def test_adjustable_result_schema_column_1(self):
table1 = self.table1
q = table1.select(
table1.c.this_is_the_primarykey_column == 4).apply_labels().\
alias('foo')
dialect = default.DefaultDialect(label_length=10)
compiled = q.compile(dialect=dialect)
assert set(compiled.result_map['some_2'][1]).issuperset([
table1.c.this_is_the_data_column,
'some_large_named_table_this_is_the_data_column', 'some_2'
])
assert set(compiled.result_map['some_1'][1]).issuperset([
table1.c.this_is_the_primarykey_column,
'some_large_named_table_this_is_the_primarykey_column', 'some_1'
])
def test_adjustable_result_schema_column_2(self):
table1 = self.table1
q = table1.select(
table1.c.this_is_the_primarykey_column == 4).alias('foo')
x = select([q])
dialect = default.DefaultDialect(label_length=10)
compiled = x.compile(dialect=dialect)
assert set(compiled.result_map['this_2'][1]).issuperset([
q.corresponding_column(table1.c.this_is_the_data_column),
'this_is_the_data_column', 'this_2'
])
assert set(compiled.result_map['this_1'][1]).issuperset([
q.corresponding_column(table1.c.this_is_the_primarykey_column),
'this_is_the_primarykey_column', 'this_1'
])
def test_table_alias_names(self):
self.assert_compile(
table2.alias().select(),
"SELECT table_with_exactly_29_c_1.this_is_the_primarykey_column, table_with_exactly_29_c_1.this_is_the_data_column FROM table_with_exactly_29_characs AS table_with_exactly_29_c_1"
)
ta = table2.alias()
dialect = default.DefaultDialect()
dialect.max_identifier_length = IDENT_LENGTH
self.assert_compile(
select([table1, ta]).select_from(table1.join(ta, table1.c.this_is_the_data_column==ta.c.this_is_the_data_column)).\
where(ta.c.this_is_the_data_column=='data3'),
"SELECT some_large_named_table.this_is_the_primarykey_column, some_large_named_table.this_is_the_data_column, "
"table_with_exactly_29_c_1.this_is_the_primarykey_column, table_with_exactly_29_c_1.this_is_the_data_column FROM "
"some_large_named_table JOIN table_with_exactly_29_characs AS table_with_exactly_29_c_1 ON "
"some_large_named_table.this_is_the_data_column = table_with_exactly_29_c_1.this_is_the_data_column "
"WHERE table_with_exactly_29_c_1.this_is_the_data_column = :this_is_the_data_column_1",
dialect=dialect
)
table2.insert().execute(
{
"this_is_the_primarykey_column": 1,
"this_is_the_data_column": "data1"
},
{
"this_is_the_primarykey_column": 2,
"this_is_the_data_column": "data2"
},
{
"this_is_the_primarykey_column": 3,
"this_is_the_data_column": "data3"
},
{
"this_is_the_primarykey_column": 4,
"this_is_the_data_column": "data4"
},
)
r = table2.alias().select().execute()
assert r.fetchall() == [(x, "data%d" % x) for x in range(1, 5)]
def test_positional_binds(self):
orders = table("orders", column("order"))
s = select([orders.c.order, literal("x")]).cte("regional_sales")
s = select([s.c.order, literal("y")])
dialect = default.DefaultDialect()
dialect.positional = True
dialect.paramstyle = "numeric"
self.assert_compile(
s,
'WITH regional_sales AS (SELECT orders."order" '
'AS "order", :1 AS anon_2 FROM orders) SELECT '
'regional_sales."order", :2 AS anon_1 FROM regional_sales',
checkpositional=("x", "y"),
dialect=dialect,
)
self.assert_compile(
s.union(s),
'WITH regional_sales AS (SELECT orders."order" '
'AS "order", :1 AS anon_2 FROM orders) SELECT '
'regional_sales."order", :2 AS anon_1 FROM regional_sales '
'UNION SELECT regional_sales."order", :3 AS anon_1 '
"FROM regional_sales",
checkpositional=("x", "y", "y"),
dialect=dialect,
)
s = (
select([orders.c.order])
.where(orders.c.order == "x")
.cte("regional_sales")
)
s = select([s.c.order]).where(s.c.order == "y")
self.assert_compile(
s,
'WITH regional_sales AS (SELECT orders."order" AS '
'"order" FROM orders WHERE orders."order" = :1) '
'SELECT regional_sales."order" FROM regional_sales '
'WHERE regional_sales."order" = :2',
checkpositional=("x", "y"),
dialect=dialect,
)
def test_ignoring_unicode_error(self):
"""checks String(unicode_error='ignore') is passed to
underlying codec."""
unicodedata = self.data
with testing.expect_deprecated(
"The String.convert_unicode parameter is deprecated and "
"will be removed in a future release.",
"The String.unicode_errors parameter is deprecated and "
"will be removed in a future release.",
):
type_ = String(248,
convert_unicode="force",
unicode_error="ignore")
dialect = default.DefaultDialect(encoding="ascii")
proc = type_.result_processor(dialect, 10)
utfdata = unicodedata.encode("utf8")
eq_(proc(utfdata), unicodedata.encode("ascii", "ignore").decode())
def test_positional(self):
table1 = self.tables.mytable
values = [
{'myid': 1, 'name': 'a', 'description': 'b'},
{'myid': 2, 'name': 'c', 'description': 'd'},
{'myid': 3, 'name': 'e', 'description': 'f'}
]
checkpositional = (1, 'a', 'b', 2, 'c', 'd', 3, 'e', 'f')
dialect = default.DefaultDialect()
dialect.supports_multivalues_insert = True
dialect.paramstyle = 'format'
dialect.positional = True
self.assert_compile(table1.insert().values(values),
'INSERT INTO mytable (myid, name, description) VALUES '
'(%s, %s, %s), (%s, %s, %s), (%s, %s, %s)',
checkpositional=checkpositional, dialect=dialect)
def test_label_overlap_unlabeled(self):
"""test that an anon col can't overlap with a fixed name, #3396"""
table1 = table("tablename", column("columnname_one"),
column("columnn_1"))
stmt = select(table1).set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)
dialect = default.DefaultDialect(label_length=23)
self.assert_compile(
stmt,
"SELECT tablename.columnname_one AS tablename_columnn_1, "
"tablename.columnn_1 AS tablename_columnn_2 FROM tablename",
dialect=dialect,
)
compiled = stmt.compile(dialect=dialect)
eq_(
set(compiled._create_result_map()),
set(["tablename_columnn_1", "tablename_columnn_2"]),
)
def test_adjustable_result_schema_column_1(self):
table1 = self.table1
q = (table1.select(table1.c.this_is_the_primarykey_column ==
4).apply_labels().alias("foo"))
dialect = default.DefaultDialect(label_length=10)
compiled = q.compile(dialect=dialect)
assert set(compiled._create_result_map()["some_2"][1]).issuperset([
table1.c.this_is_the_data_column,
"some_large_named_table_this_is_the_data_column",
"some_2",
])
assert set(compiled._create_result_map()["some_1"][1]).issuperset([
table1.c.this_is_the_primarykey_column,
"some_large_named_table_this_is_the_primarykey_column",
"some_1",
])
def test_table_plus_column_exceeds_length(self):
"""test that the truncation only occurs when tablename + colname are
concatenated, if they are individually under the label length.
"""
compile_dialect = default.DefaultDialect(label_length=30)
a_table = table(
'thirty_characters_table_xxxxxx',
column('id')
)
other_table = table(
'other_thirty_characters_table_',
column('id'),
column('thirty_characters_table_id')
)
anon = a_table.alias()
j1 = other_table.outerjoin(
anon,
anon.c.id == other_table.c.thirty_characters_table_id)
self.assert_compile(
select([other_table, anon]).
select_from(j1).apply_labels(),
'SELECT '
'other_thirty_characters_table_.id '
'AS other_thirty_characters__1, '
'other_thirty_characters_table_.thirty_characters_table_id '
'AS other_thirty_characters__2, '
'thirty_characters_table__1.id '
'AS thirty_characters_table__3 '
'FROM '
'other_thirty_characters_table_ '
'LEFT OUTER JOIN '
'thirty_characters_table_xxxxxx AS thirty_characters_table__1 '
'ON thirty_characters_table__1.id = '
'other_thirty_characters_table_.thirty_characters_table_id',
dialect=compile_dialect)
def test_adjustable_result_schema_column_2(self):
table1 = self.table1
q = (table1.select().where(
table1.c.this_is_the_primarykey_column == 4).alias("foo"))
x = select(q)
dialect = default.DefaultDialect(label_length=10)
compiled = x.compile(dialect=dialect)
assert set(compiled._create_result_map()["this_2"][1]).issuperset([
q.corresponding_column(table1.c.this_is_the_data_column),
"this_is_the_data_column",
"this_2",
])
assert set(compiled._create_result_map()["this_1"][1]).issuperset([
q.corresponding_column(table1.c.this_is_the_primarykey_column),
"this_is_the_primarykey_column",
"this_1",
])
def test_table_plus_column_exceeds_length(self):
"""test that the truncation only occurs when tablename + colname are
concatenated, if they are individually under the label length.
"""
compile_dialect = default.DefaultDialect(label_length=30)
a_table = table("thirty_characters_table_xxxxxx", column("id"))
other_table = table(
"other_thirty_characters_table_",
column("id"),
column("thirty_characters_table_id"),
)
anon = a_table.alias()
j1 = other_table.outerjoin(
anon, anon.c.id == other_table.c.thirty_characters_table_id
)
self.assert_compile(
select(other_table, anon)
.select_from(j1)
.set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL),
"SELECT "
"other_thirty_characters_table_.id "
"AS other_thirty_characters__1, "
"other_thirty_characters_table_.thirty_characters_table_id "
"AS other_thirty_characters__2, "
"thirty_characters_table__1.id "
"AS thirty_characters_table__3 "
"FROM "
"other_thirty_characters_table_ "
"LEFT OUTER JOIN "
"thirty_characters_table_xxxxxx AS thirty_characters_table__1 "
"ON thirty_characters_table__1.id = "
"other_thirty_characters_table_.thirty_characters_table_id",
dialect=compile_dialect,
)
def test_positional_binds_2_asliteral(self):
orders = table("orders", column("order"))
s = select(orders.c.order, literal("x")).cte("regional_sales")
s = select(s.c.order, literal("y"))
dialect = default.DefaultDialect()
dialect.positional = True
dialect.paramstyle = "numeric"
s1 = (select(orders.c.order).where(
orders.c.order == "x").cte("regional_sales_1"))
s1a = s1.alias()
s2 = (select(
orders.c.order == "y",
s1a.c.order,
orders.c.order,
s1.c.order,
).where(orders.c.order == "z").cte("regional_sales_2"))
s3 = select(s2)
self.assert_compile(
s3,
"WITH regional_sales_1 AS "
'(SELECT orders."order" AS "order" '
"FROM orders "
"WHERE orders.\"order\" = 'x'), "
"regional_sales_2 AS "
"(SELECT orders.\"order\" = 'y' AS anon_1, "
'anon_2."order" AS "order", orders."order" AS "order", '
'regional_sales_1."order" AS "order" '
"FROM orders, regional_sales_1 AS anon_2, regional_sales_1 "
"WHERE orders.\"order\" = 'z') "
"SELECT regional_sales_2.anon_1, "
'regional_sales_2."order", regional_sales_2."order", '
'regional_sales_2."order" FROM regional_sales_2',
checkpositional=(),
dialect=dialect,
literal_binds=True,
)
def test_named(self):
table1 = self.tables.mytable
values = [{
'myid': 1,
'name': 'a',
'description': 'b'
}, {
'myid': 2,
'name': 'c',
'description': 'd'
}, {
'myid': 3,
'name': 'e',
'description': 'f'
}]
checkparams = {
'myid_0': 1,
'myid_1': 2,
'myid_2': 3,
'name_0': 'a',
'name_1': 'c',
'name_2': 'e',
'description_0': 'b',
'description_1': 'd',
'description_2': 'f',
}
dialect = default.DefaultDialect()
dialect.supports_multivalues_insert = True
self.assert_compile(
table1.insert().values(values),
'INSERT INTO mytable (myid, name, description) VALUES '
'(:myid_0, :name_0, :description_0), '
'(:myid_1, :name_1, :description_1), '
'(:myid_2, :name_2, :description_2)',
checkparams=checkparams,
dialect=dialect)
def test_adjustable_1(self):
table1 = self.table1
q = table1.select(
table1.c.this_is_the_primarykey_column == 4).alias('foo')
x = select([q])
compile_dialect = default.DefaultDialect(label_length=10)
self.assert_compile(
x, 'SELECT '
'foo.this_1, foo.this_2 '
'FROM ('
'SELECT '
'some_large_named_table.this_is_the_primarykey_column '
'AS this_1, '
'some_large_named_table.this_is_the_data_column '
'AS this_2 '
'FROM '
'some_large_named_table '
'WHERE '
'some_large_named_table.this_is_the_primarykey_column '
'= :this_1'
') '
'AS foo', dialect=compile_dialect)
def _test_colnames_longer_than_labels(self, t1):
dialect = default.DefaultDialect(label_length=4)
a1 = t1.alias(name='asdf')
# 'abcde' is longer than 4, but rendered as itself
# needs to have all characters
s = select([a1])
self.assert_compile(select([a1]),
'SELECT asdf.abcde FROM a AS asdf',
dialect=dialect)
compiled = s.compile(dialect=dialect)
assert set(compiled.result_map['abcde'][1]).issuperset(
['abcde', a1.c.abcde, 'abcde'])
# column still there, but short label
s = select([a1]).apply_labels()
self.assert_compile(s,
'SELECT asdf.abcde AS _1 FROM a AS asdf',
dialect=dialect)
compiled = s.compile(dialect=dialect)
assert set(compiled.result_map['_1'][1]).issuperset(
['asdf_abcde', a1.c.abcde, '_1'])
def test_insert_from_select_cte_follows_insert_one(self):
dialect = default.DefaultDialect()
dialect.cte_follows_insert = True
table1 = self.tables.mytable
cte = select([table1.c.name]).where(table1.c.name == 'bar').cte()
sel = select([table1.c.myid,
table1.c.name]).where(table1.c.name == cte.c.name)
ins = self.tables.myothertable.insert().\
from_select(("otherid", "othername"), sel)
self.assert_compile(
ins, "INSERT INTO myothertable (otherid, othername) "
"WITH anon_1 AS "
"(SELECT mytable.name AS name FROM mytable "
"WHERE mytable.name = :name_1) "
"SELECT mytable.myid, mytable.name FROM mytable, anon_1 "
"WHERE mytable.name = anon_1.name",
checkparams={"name_1": "bar"},
dialect=dialect)
def _test_colnames_longer_than_labels(self, t1):
dialect = default.DefaultDialect(label_length=4)
a1 = t1.alias(name="asdf")
# 'abcde' is longer than 4, but rendered as itself
# needs to have all characters
s = select(a1)
self.assert_compile(select(a1),
"SELECT asdf.abcde FROM a AS asdf",
dialect=dialect)
compiled = s.compile(dialect=dialect)
assert set(compiled._create_result_map()["abcde"][1]).issuperset(
["abcde", a1.c.abcde, "abcde"])
# column still there, but short label
s = select(a1).set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)
self.assert_compile(s,
"SELECT asdf.abcde AS _1 FROM a AS asdf",
dialect=dialect)
compiled = s.compile(dialect=dialect)
assert set(compiled._create_result_map()["_1"][1]).issuperset(
["asdf_abcde", a1.c.abcde, "_1"])
def test_query_crit(self):
Child1, Child2 = self.classes.Child1, self.classes.Child2
sess = create_session()
c11, c12, c13 = Child1(), Child1(), Child1()
c21, c22, c23 = Child2(), Child2(), Child2()
c11.left_child2 = c22
c12.left_child2 = c22
c13.left_child2 = c23
sess.add_all([c11, c12, c13, c21, c22, c23])
sess.flush()
# test that the join to Child2 doesn't alias Child1 in the select
eq_(set(sess.query(Child1).join(Child1.left_child2)),
set([c11, c12, c13]))
eq_(set(sess.query(Child1, Child2).join(Child1.left_child2)),
set([(c11, c22), (c12, c22), (c13, c23)]))
# test __eq__() on property is annotating correctly
eq_(
set(
sess.query(Child2).join(
Child2.right_children).filter(Child1.left_child2 == c22)),
set([c22]))
# test the same again
self.assert_compile(
sess.query(Child2).join(Child2.right_children).filter(
Child1.left_child2 == c22).with_labels().statement,
"SELECT child2.id AS child2_id, parent.id AS parent_id, "
"parent.cls AS parent_cls FROM secondary AS secondary_1, "
"parent JOIN child2 ON parent.id = child2.id JOIN secondary AS "
"secondary_2 ON parent.id = secondary_2.left_id JOIN (SELECT "
"parent.id AS parent_id, parent.cls AS parent_cls, child1.id AS "
"child1_id FROM parent JOIN child1 ON parent.id = child1.id) AS "
"anon_1 ON anon_1.parent_id = secondary_2.right_id WHERE "
"anon_1.parent_id = secondary_1.right_id AND :param_1 = "
"secondary_1.left_id",
dialect=default.DefaultDialect())
def test_positional_binds_2_asliteral(self):
orders = table(
'orders',
column('order'),
)
s = select([orders.c.order, literal("x")]).cte("regional_sales")
s = select([s.c.order, literal("y")])
dialect = default.DefaultDialect()
dialect.positional = True
dialect.paramstyle = 'numeric'
s1 = select([orders.c.order]).where(orders.c.order == 'x').\
cte("regional_sales_1")
s1a = s1.alias()
s2 = select([orders.c.order == 'y', s1a.c.order,
orders.c.order, s1.c.order]).\
where(orders.c.order == 'z').\
cte("regional_sales_2")
s3 = select([s2])
self.assert_compile(
s3, 'WITH regional_sales_1 AS '
'(SELECT orders."order" AS "order" '
'FROM orders '
'WHERE orders."order" = \'x\'), '
'regional_sales_2 AS '
'(SELECT orders."order" = \'y\' AS anon_1, '
'anon_2."order" AS "order", orders."order" AS "order", '
'regional_sales_1."order" AS "order" '
'FROM orders, regional_sales_1 AS anon_2, regional_sales_1 '
'WHERE orders."order" = \'z\') '
'SELECT regional_sales_2.anon_1, regional_sales_2."order" '
'FROM regional_sales_2',
checkpositional=(),
dialect=dialect,
literal_binds=True)
def test_anon_alias(self):
table1 = self.tables.table1
compile_dialect = default.DefaultDialect()
compile_dialect.max_identifier_length = IDENT_LENGTH
q = table1.select(table1.c.this_is_the_primarykey_column == 4).alias()
x = select([q], use_labels=True)
self.assert_compile(
x, "SELECT anon_1.this_is_the_primarykey_column AS "
"anon_1_this_is_the_prim_1, anon_1.this_is_the_data_column "
"AS anon_1_this_is_the_data_2 "
"FROM (SELECT some_large_named_table."
"this_is_the_primarykey_column AS "
"this_is_the_primarykey_column, "
"some_large_named_table.this_is_the_data_column "
"AS this_is_the_data_column "
"FROM some_large_named_table "
"WHERE some_large_named_table.this_is_the_primarykey_column "
"= :this_is_the_primarykey__1) AS anon_1",
dialect=compile_dialect)
eq_(list(testing.db.execute(x)), [(4, u'data4')])
def test_adjustable_5(self):
table1 = self.table1
q = table1.select(table1.c.this_is_the_primarykey_column == 4).alias()
x = select([q], use_labels=True)
compile_dialect = default.DefaultDialect(label_length=4)
self.assert_compile(
x, 'SELECT '
'_1._2 AS _1, '
'_1._4 AS _3 '
'FROM ('
'SELECT '
'some_large_named_table.this_is_the_primarykey_column '
'AS _2, '
'some_large_named_table.this_is_the_data_column '
'AS _4 '
'FROM '
'some_large_named_table '
'WHERE '
'some_large_named_table.this_is_the_primarykey_column '
'= :_1'
') '
'AS _1', dialect=compile_dialect)
def test_values_in_cte_params(self):
cte1 = select(
Values(
column("col1", String),
column("col2", Integer),
name="temp_table",
).data([("a", 2), ("b", 3)])).cte("cte1")
cte2 = select(cte1.c.col1).where(cte1.c.col1 == "q").cte("cte2")
stmt = select(cte2.c.col1)
dialect = default.DefaultDialect()
dialect.positional = True
dialect.paramstyle = "numeric"
self.assert_compile(
stmt,
"WITH cte1 AS (SELECT temp_table.col1 AS col1, "
"temp_table.col2 AS col2 FROM (VALUES (:1, :2), (:3, :4)) AS "
"temp_table (col1, col2)), "
"cte2 AS "
"(SELECT cte1.col1 AS col1 FROM cte1 WHERE cte1.col1 = :5) "
"SELECT cte2.col1 FROM cte2",
checkpositional=("a", 2, "b", 3, "q"),
dialect=dialect,
)
self.assert_compile(
stmt,
"WITH cte1 AS (SELECT temp_table.col1 AS col1, "
"temp_table.col2 AS col2 FROM (VALUES ('a', 2), ('b', 3)) "
"AS temp_table (col1, col2)), "
"cte2 AS "
"(SELECT cte1.col1 AS col1 FROM cte1 WHERE cte1.col1 = 'q') "
"SELECT cte2.col1 FROM cte2",
literal_binds=True,
dialect=dialect,
)