def test_select_composition_three(self):
self.assert_compile(
select([column("column1"), column("column2")],
from_obj=table1).alias("somealias").select(),
"SELECT somealias.column1, somealias.column2 FROM "
"(SELECT column1, column2 FROM mytable) AS somealias",
)
def test_noorderby_parameters_insubquery(self):
"""test that the ms-sql dialect does not include ORDER BY
positional parameters in subqueries"""
table1 = table(
"mytable",
column("myid", Integer),
column("name", String),
column("description", String),
)
q = select(
[table1.c.myid, sql.literal("bar").label("c1")],
order_by=[table1.c.name + "-"],
).alias("foo")
crit = q.c.myid == table1.c.myid
dialect = mssql.dialect()
dialect.paramstyle = "qmark"
dialect.positional = True
self.assert_compile(
select(["*"], crit),
"SELECT * FROM (SELECT mytable.myid AS "
"myid, ? AS c1 FROM mytable) AS foo, mytable WHERE "
"foo.myid = mytable.myid",
dialect=dialect,
checkparams={"param_1": "bar"},
# if name_1 is included, too many parameters are passed to dbapi
checkpositional=("bar", ),
)
def test_insert_returning(self):
table1 = table(
"mytable",
column("myid", Integer),
column("name", String(128)),
column("description", String(128)),
)
i = insert(table1,
values=dict(name="foo")).returning(table1.c.myid,
table1.c.name)
self.assert_compile(
i,
"INSERT INTO mytable (name) VALUES (:name) "
"RETURNING mytable.myid, mytable.name",
)
i = insert(table1, values=dict(name="foo")).returning(table1)
self.assert_compile(
i,
"INSERT INTO mytable (name) VALUES (:name) "
"RETURNING mytable.myid, mytable.name, "
"mytable.description",
)
i = insert(table1, values=dict(name="foo")).returning(
func.length(table1.c.name))
self.assert_compile(
i,
"INSERT INTO mytable (name) VALUES (:name) "
"RETURNING char_length(mytable.name) AS "
"length_1",
)
def test_update_returning(self):
table1 = table(
"mytable",
column("myid", Integer),
column("name", String(128)),
column("description", String(128)),
)
u = update(table1,
values=dict(name="foo")).returning(table1.c.myid,
table1.c.name)
self.assert_compile(
u,
"UPDATE mytable SET name=:name RETURNING "
"mytable.myid, mytable.name",
)
u = update(table1, values=dict(name="foo")).returning(table1)
self.assert_compile(
u,
"UPDATE mytable SET name=:name RETURNING "
"mytable.myid, mytable.name, "
"mytable.description",
)
u = update(table1, values=dict(name="foo")).returning(
func.length(table1.c.name))
self.assert_compile(
u,
"UPDATE mytable SET name=:name RETURNING "
"char_length(mytable.name) AS length_1",
)
def test_delete_extra_froms_alias(self):
a1 = table("t1", column("c1")).alias("a1")
t2 = table("t2", column("c1"))
q = sql.delete(a1).where(a1.c.c1 == t2.c.c1)
self.assert_compile(
q, "DELETE FROM a1 USING t1 AS a1, t2 WHERE a1.c1 = t2.c1")
self.assert_compile(sql.delete(a1), "DELETE FROM t1 AS a1")
def test_limit_offset_with_correlated_order_by(self):
t1 = table("t1", column("x", Integer), column("y", Integer))
t2 = table("t2", column("x", Integer), column("y", Integer))
order_by = select([t2.c.y]).where(t1.c.x == t2.c.x).as_scalar()
s = (select(
[t1]).where(t1.c.x == 5).order_by(order_by).limit(10).offset(20))
self.assert_compile(
s,
"SELECT anon_1.x, anon_1.y "
"FROM (SELECT t1.x AS x, t1.y AS y, "
"ROW_NUMBER() OVER (ORDER BY "
"(SELECT t2.y FROM t2 WHERE t1.x = t2.x)"
") AS mssql_rn "
"FROM t1 "
"WHERE t1.x = :x_1) AS anon_1 "
"WHERE mssql_rn > :param_1 AND mssql_rn <= :param_2 + :param_1",
checkparams={
"param_1": 20,
"param_2": 10,
"x_1": 5
},
)
c = s.compile(dialect=mssql.dialect())
eq_(len(c._result_columns), 2)
assert t1.c.x in set(c._create_result_map()["x"][1])
assert t1.c.y in set(c._create_result_map()["y"][1])
def test_returning_insert(self):
t1 = table("t1", column("c1"), column("c2"), column("c3"))
self.assert_compile(
t1.insert().values(c1=1).returning(t1.c.c2, t1.c.c3),
"INSERT INTO t1 (c1) VALUES (:c1) RETURNING "
"t1.c2, t1.c3 INTO :ret_0, :ret_1",
)
def test_use_binds_for_limits_disabled(self):
t = table("sometable", column("col1"), column("col2"))
dialect = oracle.OracleDialect(use_binds_for_limits=False)
self.assert_compile(
select([t]).limit(10),
"SELECT col1, col2 FROM (SELECT sometable.col1 AS col1, "
"sometable.col2 AS col2 FROM sometable) WHERE ROWNUM <= 10",
dialect=dialect,
)
self.assert_compile(
select([t]).offset(10),
"SELECT col1, col2 FROM (SELECT col1, col2, ROWNUM AS ora_rn "
"FROM (SELECT sometable.col1 AS col1, sometable.col2 AS col2 "
"FROM sometable)) WHERE ora_rn > 10",
dialect=dialect,
)
self.assert_compile(
select([t]).limit(10).offset(10),
"SELECT col1, col2 FROM (SELECT col1, col2, ROWNUM AS ora_rn "
"FROM (SELECT sometable.col1 AS col1, sometable.col2 AS col2 "
"FROM sometable) WHERE ROWNUM <= 20) WHERE ora_rn > 10",
dialect=dialect,
)
def test_nonansi_nested_right_join(self):
a = table("a", column("a"))
b = table("b", column("b"))
c = table("c", column("c"))
j = a.join(b.join(c, b.c.b == c.c.c), a.c.a == b.c.b)
self.assert_compile(
select([j]),
"SELECT a.a, b.b, c.c FROM a, b, c "
"WHERE a.a = b.b AND b.b = c.c",
dialect=oracle.OracleDialect(use_ansi=False),
)
j = a.outerjoin(b.join(c, b.c.b == c.c.c), a.c.a == b.c.b)
self.assert_compile(
select([j]),
"SELECT a.a, b.b, c.c FROM a, b, c "
"WHERE a.a = b.b(+) AND b.b = c.c",
dialect=oracle.OracleDialect(use_ansi=False),
)
j = a.join(b.outerjoin(c, b.c.b == c.c.c), a.c.a == b.c.b)
self.assert_compile(
select([j]),
"SELECT a.a, b.b, c.c FROM a, b, c "
"WHERE a.a = b.b AND b.b = c.c(+)",
dialect=oracle.OracleDialect(use_ansi=False),
)
def test_use_binds_for_limits_enabled(self):
t = table("sometable", column("col1"), column("col2"))
dialect = oracle.OracleDialect(use_binds_for_limits=True)
self.assert_compile(
select([t]).limit(10),
"SELECT col1, col2 FROM (SELECT sometable.col1 AS col1, "
"sometable.col2 AS col2 FROM sometable) WHERE ROWNUM "
"<= :param_1",
dialect=dialect,
)
self.assert_compile(
select([t]).offset(10),
"SELECT col1, col2 FROM (SELECT col1, col2, ROWNUM AS ora_rn "
"FROM (SELECT sometable.col1 AS col1, sometable.col2 AS col2 "
"FROM sometable)) WHERE ora_rn > :param_1",
dialect=dialect,
)
self.assert_compile(
select([t]).limit(10).offset(10),
"SELECT col1, col2 FROM (SELECT col1, col2, ROWNUM AS ora_rn "
"FROM (SELECT sometable.col1 AS col1, sometable.col2 AS col2 "
"FROM sometable) WHERE ROWNUM <= :param_1 + :param_2) "
"WHERE ora_rn > :param_2",
dialect=dialect,
checkparams={
"param_1": 10,
"param_2": 10
},
)
def test_union(self):
orders = table("orders", column("region"), column("amount"))
regional_sales = select([orders.c.region,
orders.c.amount]).cte("regional_sales")
s = select([regional_sales.c.region
]).where(regional_sales.c.amount > 500)
self.assert_compile(
s,
"WITH regional_sales AS "
"(SELECT orders.region AS region, "
"orders.amount AS amount FROM orders) "
"SELECT regional_sales.region "
"FROM regional_sales WHERE "
"regional_sales.amount > :amount_1",
)
s = s.union_all(
select([regional_sales.c.region
]).where(regional_sales.c.amount < 300))
self.assert_compile(
s,
"WITH regional_sales AS "
"(SELECT orders.region AS region, "
"orders.amount AS amount FROM orders) "
"SELECT regional_sales.region FROM regional_sales "
"WHERE regional_sales.amount > :amount_1 "
"UNION ALL SELECT regional_sales.region "
"FROM regional_sales WHERE "
"regional_sales.amount < :amount_2",
)
def test_insert_returning(self):
table1 = table(
"mytable",
column("myid", Integer),
column("name", String(128)),
column("description", String(128)),
)
i = insert(table1,
values=dict(name="foo")).returning(table1.c.myid,
table1.c.name)
self.assert_compile(
i,
"INSERT INTO mytable (name) OUTPUT "
"inserted.myid, inserted.name VALUES "
"(:name)",
)
i = insert(table1, values=dict(name="foo")).returning(table1)
self.assert_compile(
i,
"INSERT INTO mytable (name) OUTPUT "
"inserted.myid, inserted.name, "
"inserted.description VALUES (:name)",
)
i = insert(table1, values=dict(name="foo")).returning(
func.length(table1.c.name))
self.assert_compile(
i,
"INSERT INTO mytable (name) OUTPUT "
"LEN(inserted.name) AS length_1 VALUES "
"(:name)",
)
def test_limit_offset_w_ambiguous_cols(self):
t = table("t", column("x", Integer), column("y", Integer))
cols = [t.c.x, t.c.x.label("q"), t.c.x.label("p"), t.c.y]
s = select(cols).where(t.c.x == 5).order_by(t.c.y).limit(10).offset(20)
self.assert_compile(
s,
"SELECT anon_1.x, anon_1.q, anon_1.p, anon_1.y "
"FROM (SELECT t.x AS x, t.x AS q, t.x AS p, t.y AS y, "
"ROW_NUMBER() OVER (ORDER BY t.y) AS mssql_rn "
"FROM t "
"WHERE t.x = :x_1) AS anon_1 "
"WHERE mssql_rn > :param_1 AND mssql_rn <= :param_2 + :param_1",
checkparams={
"param_1": 20,
"param_2": 10,
"x_1": 5
},
)
c = s.compile(dialect=mssql.dialect())
eq_(len(c._result_columns), 4)
result_map = c._create_result_map()
for col in cols:
is_(result_map[col.key][1][0], col)
def test_pg_example_three(self):
parts = table("parts", column("part"), column("sub_part"))
included_parts = (select([
parts.c.sub_part, parts.c.part
]).where(parts.c.part == "our part").cte("included_parts",
recursive=True))
pr = included_parts.alias("pr")
p = parts.alias("p")
included_parts = included_parts.union_all(
select([p.c.sub_part, p.c.part]).where(p.c.part == pr.c.sub_part))
stmt = (parts.delete().where(
parts.c.part.in_(select([included_parts.c.part
]))).returning(parts.c.part))
# the outer RETURNING is a bonus over what PG's docs have
self.assert_compile(
stmt,
"WITH RECURSIVE included_parts(sub_part, part) AS "
"(SELECT parts.sub_part AS sub_part, parts.part AS part "
"FROM parts "
"WHERE parts.part = :part_1 "
"UNION ALL SELECT p.sub_part AS sub_part, p.part AS part "
"FROM parts AS p, included_parts AS pr "
"WHERE p.part = pr.sub_part) "
"DELETE FROM parts WHERE parts.part IN "
"(SELECT included_parts.part FROM included_parts) "
"RETURNING parts.part",
)
def test_cte_refers_to_aliased_cte_twice(self):
# test issue #4204
a = table("a", column("id"))
b = table("b", column("id"), column("fid"))
c = table("c", column("id"), column("fid"))
cte1 = select([a.c.id]).cte(name="cte1")
aa = cte1.alias("aa")
cte2 = (select([b.c.id]).select_from(b.join(
aa, b.c.fid == aa.c.id)).cte(name="cte2"))
cte3 = (select([c.c.id]).select_from(c.join(
aa, c.c.fid == aa.c.id)).cte(name="cte3"))
stmt = select([cte3.c.id, cte2.c.id
]).select_from(cte2.join(cte3, cte2.c.id == cte3.c.id))
self.assert_compile(
stmt,
"WITH cte1 AS (SELECT a.id AS id FROM a), "
"cte2 AS (SELECT b.id AS id FROM b "
"JOIN cte1 AS aa ON b.fid = aa.id), "
"cte3 AS (SELECT c.id AS id FROM c "
"JOIN cte1 AS aa ON c.fid = aa.id) "
"SELECT cte3.id, cte2.id FROM cte2 JOIN cte3 ON cte2.id = cte3.id",
)
def test_delete_extra_froms(self):
t1 = sql.table("t1", sql.column("c1"))
t2 = sql.table("t2", sql.column("c1"))
q = sql.delete(t1).where(t1.c.c1 == t2.c.c1)
self.assert_compile(
q, "DELETE FROM t1 FROM t1, t2 WHERE t1.c1 = t2.c1"
)
def test_full_outer_join(self):
t1 = table("t1", column("x"))
t2 = table("t2", column("y"))
self.assert_compile(
t1.outerjoin(t2, t1.c.x == t2.c.y, full=True),
"t1 FULL OUTER JOIN t2 ON t1.x = t2.y",
)
def test_outer_join(self):
t1 = table("t1", column("x"))
t2 = table("t2", column("y"))
self.assert_compile(
t1.outerjoin(t2, t1.c.x == t2.c.y),
"t1 LEFT OUTER JOIN t2 ON t1.x = t2.y",
)
def test_recursive(self):
parts = table("parts", column("part"), column("sub_part"),
column("quantity"))
included_parts = (select([
parts.c.sub_part, parts.c.part, parts.c.quantity
]).where(parts.c.part == "our part").cte(recursive=True))
incl_alias = included_parts.alias()
parts_alias = parts.alias()
included_parts = included_parts.union(
select([
parts_alias.c.sub_part,
parts_alias.c.part,
parts_alias.c.quantity,
]).where(parts_alias.c.part == incl_alias.c.sub_part))
s = (select([
included_parts.c.sub_part,
func.sum(included_parts.c.quantity).label("total_quantity"),
]).select_from(
included_parts.join(
parts, included_parts.c.part == parts.c.part)).group_by(
included_parts.c.sub_part))
self.assert_compile(
s,
"WITH RECURSIVE anon_1(sub_part, part, quantity) "
"AS (SELECT parts.sub_part AS sub_part, parts.part "
"AS part, parts.quantity AS quantity FROM parts "
"WHERE parts.part = :part_1 UNION "
"SELECT parts_1.sub_part AS sub_part, "
"parts_1.part AS part, parts_1.quantity "
"AS quantity FROM parts AS parts_1, anon_1 AS anon_2 "
"WHERE parts_1.part = anon_2.sub_part) "
"SELECT anon_1.sub_part, "
"sum(anon_1.quantity) AS total_quantity FROM anon_1 "
"JOIN parts ON anon_1.part = parts.part "
"GROUP BY anon_1.sub_part",
)
# quick check that the "WITH RECURSIVE" varies per
# dialect
self.assert_compile(
s,
"WITH anon_1(sub_part, part, quantity) "
"AS (SELECT parts.sub_part AS sub_part, parts.part "
"AS part, parts.quantity AS quantity FROM parts "
"WHERE parts.part = :part_1 UNION "
"SELECT parts_1.sub_part AS sub_part, "
"parts_1.part AS part, parts_1.quantity "
"AS quantity FROM parts AS parts_1, anon_1 AS anon_2 "
"WHERE parts_1.part = anon_2.sub_part) "
"SELECT anon_1.sub_part, "
"sum(anon_1.quantity) AS total_quantity FROM anon_1 "
"JOIN parts ON anon_1.part = parts.part "
"GROUP BY anon_1.sub_part",
dialect=mssql.dialect(),
)
def test_cast_grouped_expression_pre_4(self):
dialect = mysql.dialect()
dialect.server_version_info = (3, 2, 3)
with expect_warnings("Current MySQL version does not support CAST;"):
self.assert_compile(
cast(sql.column("x") + sql.column("y"), Integer),
"(x + y)",
dialect=dialect,
)
def test_column_collection_pos_plus_bykey(self):
# overlapping positional names + type names
t = text("select a, b, c from foo").columns(column("a"),
column("b"),
b=Integer,
c=String)
eq_(t.c.keys(), ["a", "b", "c"])
eq_(t.c.b.type._type_affinity, Integer)
eq_(t.c.c.type._type_affinity, String)
def test_limit_using_top(self):
t = table("t", column("x", Integer), column("y", Integer))
s = select([t]).where(t.c.x == 5).order_by(t.c.y).limit(10)
self.assert_compile(
s,
"SELECT TOP 10 t.x, t.y FROM t WHERE t.x = :x_1 ORDER BY t.y",
checkparams={"x_1": 5},
)
def test_for_update_of_w_limit_adaption_col_unpresent(self):
table1 = table("mytable", column("myid"), column("name"))
self.assert_compile(
select([table1.c.myid]).where(table1.c.myid == 7).with_for_update(
nowait=True, of=table1.c.name).limit(10),
"SELECT myid FROM "
"(SELECT mytable.myid AS myid, mytable.name AS name "
"FROM mytable WHERE mytable.myid = :myid_1) "
"WHERE ROWNUM <= :param_1 FOR UPDATE OF name NOWAIT",
)
def test_over(self):
stmt = select([column("foo"), column("bar")])
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 bar ORDER BY foo) "
"AS anon_1 FROM (SELECT foo, bar)",
)
def test_subquery(self):
t = table("sometable", column("col1"), column("col2"))
s = select([t])
s = select([s.c.col1, s.c.col2])
self.assert_compile(
s,
"SELECT col1, col2 FROM (SELECT "
"sometable.col1 AS col1, sometable.col2 "
"AS col2 FROM sometable)",
)
def test_assorted(self):
table1 = table("mytable", column("myid", Integer))
table2 = table("myothertable", column("otherid", Integer))
# test an expression with a function
self.assert_compile(
func.lala(3, 4, literal("five"), table1.c.myid) * table2.c.otherid,
"lala(:lala_1, :lala_2, :param_1, mytable.myid) * "
"myothertable.otherid",
)
# test it in a SELECT
self.assert_compile(
select([func.count(table1.c.myid)]),
"SELECT count(mytable.myid) AS count_1 FROM mytable",
)
# test a "dotted" function name
self.assert_compile(
select([func.foo.bar.lala(table1.c.myid)]),
"SELECT foo.bar.lala(mytable.myid) AS lala_1 FROM mytable",
)
# test the bind parameter name with a "dotted" function name is
# only the name (limits the length of the bind param name)
self.assert_compile(
select([func.foo.bar.lala(12)]),
"SELECT foo.bar.lala(:lala_2) AS lala_1",
)
# test a dotted func off the engine itself
self.assert_compile(func.lala.hoho(7), "lala.hoho(:hoho_1)")
# test None becomes NULL
self.assert_compile(
func.my_func(1, 2, None, 3),
"my_func(:my_func_1, :my_func_2, NULL, :my_func_3)",
)
# test pickling
self.assert_compile(
util.pickle.loads(util.pickle.dumps(func.my_func(1, 2, None, 3))),
"my_func(:my_func_1, :my_func_2, NULL, :my_func_3)",
)
# assert func raises AttributeError for __bases__ attribute, since
# its not a class fixes pydoc
try:
func.__bases__
assert False
except AttributeError:
assert True
def test_array_agg_array_literal_explicit_type(self):
from sqlalchemy_1_3.dialects.postgresql import array
expr = array([column("data", Integer), column("d2", Integer)])
agg_expr = func.array_agg(expr, type_=ARRAY(Integer))
is_(agg_expr.type._type_affinity, ARRAY)
is_(agg_expr.type.item_type._type_affinity, Integer)
self.assert_compile(
agg_expr, "array_agg(ARRAY[data, d2])", dialect="postgresql"
)
def test_for_update_of_w_limit_offset_adaption_col_present(self):
table1 = table("mytable", column("myid"), column("name"))
self.assert_compile(
select([table1.c.myid,
table1.c.name]).where(table1.c.myid == 7).with_for_update(
nowait=True, of=table1.c.name).limit(10).offset(50),
"SELECT myid, name FROM (SELECT myid, name, ROWNUM AS ora_rn "
"FROM (SELECT mytable.myid AS myid, mytable.name AS name "
"FROM mytable WHERE mytable.myid = :myid_1) "
"WHERE ROWNUM <= :param_1 + :param_2) WHERE ora_rn > :param_2 "
"FOR UPDATE OF name NOWAIT",
)
def test_select_composition_two(self):
s = select()
s.append_column(column("column1"))
s.append_column(column("column2"))
s.append_whereclause(text("column1=12"))
s.append_whereclause(text("column2=19"))
s = s.order_by("column1")
s.append_from(text("table1"))
self.assert_compile(
s,
"SELECT column1, column2 FROM table1 WHERE "
"column1=12 AND column2=19 ORDER BY column1",
)
def test_backslash_escaping(self):
self.assert_compile(
sql.column("foo").like("bar", escape="\\"),
"foo LIKE %s ESCAPE '\\\\'",
)
dialect = mysql.dialect()
dialect._backslash_escapes = False
self.assert_compile(
sql.column("foo").like("bar", escape="\\"),
"foo LIKE %s ESCAPE '\\'",
dialect=dialect,
)
