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, )