def test_union_label(self):
s1 = select([func.foo("hoho").label("x")])
s2 = select([func.foo("Bar").label("y")])
stmt = union(s1, s2).order_by("x")
self.assert_compile(
stmt,
"SELECT foo(:foo_1) AS x UNION SELECT foo(:foo_2) AS y ORDER BY x",
)
def test_function(self):
self.assert_compile(func.foo(1, 2), 'foo(:foo_1, :foo_2)')
self.assert_compile(func.current_time(), 'CURRENT_TIME')
self.assert_compile(func.foo(), 'foo()')
m = MetaData()
t = Table(
'sometable', m, Column('col1', Integer), Column('col2', Integer))
self.assert_compile(select([func.max(t.c.col1)]),
'SELECT max(sometable.col1) AS max_1 FROM '
'sometable')
def test_function(self):
self.assert_compile(func.foo(1, 2), "foo(:foo_1, :foo_2)")
self.assert_compile(func.current_time(), "CURRENT_TIME")
self.assert_compile(func.foo(), "foo()")
m = MetaData()
t = Table(
"sometable", m, Column("col1", Integer), Column("col2", Integer)
)
self.assert_compile(
select([func.max(t.c.col1)]),
"SELECT max(sometable.col1) AS max_1 FROM " "sometable",
)
def test_columnadapter_anonymized(self):
"""test issue #3148
Testing the anonymization applied from the ColumnAdapter.columns
collection, typically as used in eager loading.
"""
exprs = [
table1.c.myid,
table1.c.name.label('t1name'),
func.foo("hoho").label('x')]
ta = table1.alias()
adapter = sql_util.ColumnAdapter(ta, anonymize_labels=True)
s1 = select([adapter.columns[expr] for expr in exprs]).\
apply_labels().order_by("myid", "t1name", "x")
def go():
# the labels here are anonymized, so label naming
# can't catch these.
self.assert_compile(
s1,
"SELECT mytable_1.myid AS mytable_1_myid, "
"mytable_1.name AS name_1, foo(:foo_2) AS foo_1 "
"FROM mytable AS mytable_1 ORDER BY mytable_1.myid, t1name, x"
)
assert_warnings(
go,
["Can't resolve label reference 't1name'",
"Can't resolve label reference 'x'"], regex=True)
def test_columnadapter_non_anonymized(self):
"""test issue #3148
Testing the anonymization applied from the ColumnAdapter.columns
collection, typically as used in eager loading.
"""
exprs = [
table1.c.myid,
table1.c.name.label("t1name"),
func.foo("hoho").label("x"),
]
ta = table1.alias()
adapter = sql_util.ColumnAdapter(ta)
s1 = (
select([adapter.columns[expr] for expr in exprs])
.apply_labels()
.order_by("myid", "t1name", "x")
)
# labels are maintained
self.assert_compile(
s1,
"SELECT mytable_1.myid AS mytable_1_myid, "
"mytable_1.name AS t1name, foo(:foo_1) AS x "
"FROM mytable AS mytable_1 ORDER BY mytable_1.myid, t1name, x",
)
def test_order_by_func_label_desc(self):
stmt = select([func.foo('bar').label('fb'), table1]).\
order_by(desc('fb'))
self.assert_compile(
stmt,
"SELECT foo(:foo_1) AS fb, mytable.myid, mytable.name, "
"mytable.description FROM mytable ORDER BY fb DESC"
)
def _join_fixture_o2m_to_oldstyle_func(self, **kw):
return relationships.JoinCondition(
self.left,
self.right,
self.left,
self.right,
primaryjoin=self.left.c.id == func.foo(self.right.c.lid),
consider_as_foreign_keys=[self.right.c.lid],
**kw)
def _join_fixture_o2m_to_annotated_func(self, **kw):
return relationships.JoinCondition(
self.left,
self.right,
self.left,
self.right,
primaryjoin=self.left.c.id == foreign(func.foo(self.right.c.lid)),
**kw
)
def test_order_by_func_label_desc(self):
stmt = select([func.foo("bar").label("fb"),
table1]).order_by(desc("fb"))
self.assert_compile(
stmt,
"SELECT foo(:foo_1) AS fb, mytable.myid, mytable.name, "
"mytable.description FROM mytable ORDER BY fb DESC",
)
def _join_fixture_o2m_to_oldstyle_func(self, **kw):
return relationships.JoinCondition(
self.left,
self.right,
self.left,
self.right,
primaryjoin=self.left.c.id == func.foo(self.right.c.lid),
consider_as_foreign_keys=[self.right.c.lid],
**kw
)
def _join_fixture_m2o_sub_to_joined_sub_func(self, **kw):
# see test.orm.test_mapper:MapperTest.test_add_column_prop_deannotate,
right = self.base.join(self.right_w_base_rel, self.base.c.id == self.right_w_base_rel.c.id)
return relationships.JoinCondition(
self.right_w_base_rel,
right,
self.right_w_base_rel,
self.right_w_base_rel,
primaryjoin=self.right_w_base_rel.c.base_id == func.foo(self.base.c.id),
)
def _join_fixture_m2o_sub_to_joined_sub_func(self, **kw):
# see test.orm.test_mapper:MapperTest.test_add_column_prop_deannotate,
right = self.base.join(self.right_w_base_rel,
self.base.c.id == self.right_w_base_rel.c.id)
return relationships.JoinCondition(
self.right_w_base_rel,
right,
self.right_w_base_rel,
self.right_w_base_rel,
primaryjoin=self.right_w_base_rel.c.base_id == func.foo(
self.base.c.id))
def test_offset_dont_misapply_labelreference(self):
m = MetaData()
t = Table('t', m, Column('x', Integer))
expr1 = func.foo(t.c.x).label('x')
expr2 = func.foo(t.c.x).label('y')
stmt1 = select([expr1]).order_by(expr1.desc()).offset(1)
stmt2 = select([expr2]).order_by(expr2.desc()).offset(1)
self.assert_compile(
stmt1, "SELECT anon_1.x FROM (SELECT foo(t.x) AS x, "
"ROW_NUMBER() OVER (ORDER BY foo(t.x) DESC) AS mssql_rn FROM t) "
"AS anon_1 WHERE mssql_rn > :param_1")
self.assert_compile(
stmt2, "SELECT anon_1.y FROM (SELECT foo(t.x) AS y, "
"ROW_NUMBER() OVER (ORDER BY foo(t.x) DESC) AS mssql_rn FROM t) "
"AS anon_1 WHERE mssql_rn > :param_1")
def _join_fixture_o2m_composite_selfref_func_annotated(self, **kw):
return relationships.JoinCondition(
self.composite_selfref,
self.composite_selfref,
self.composite_selfref,
self.composite_selfref,
primaryjoin=and_(
remote(self.composite_selfref.c.group_id) == func.foo(self.composite_selfref.c.group_id),
remote(self.composite_selfref.c.parent_id) == self.composite_selfref.c.id,
),
**kw
)
def test_exec_options(self):
f = func.foo()
eq_(f._execution_options, {})
f = f.execution_options(foo="bar")
eq_(f._execution_options, {"foo": "bar"})
s = f.select()
eq_(s._execution_options, {"foo": "bar"})
ret = testing.db.execute(func.now().execution_options(foo="bar"))
eq_(ret.context.execution_options, {"foo": "bar"})
ret.close()
def test_exec_options(self):
f = func.foo()
eq_(f._execution_options, {})
f = f.execution_options(foo="bar")
eq_(f._execution_options, {"foo": "bar"})
s = f.select()
eq_(s._execution_options, {"foo": "bar"})
ret = testing.db.execute(func.now().execution_options(foo="bar"))
eq_(ret.context.execution_options, {"foo": "bar"})
ret.close()
def test_exec_options(self):
f = func.foo()
eq_(f._execution_options, {})
f = f.execution_options(foo='bar')
eq_(f._execution_options, {'foo': 'bar'})
s = f.select()
eq_(s._execution_options, {'foo': 'bar'})
ret = testing.db.execute(func.now().execution_options(foo='bar'))
eq_(ret.context.execution_options, {'foo': 'bar'})
ret.close()
def _join_fixture_o2m_composite_selfref_func_annotated(self, **kw):
return relationships.JoinCondition(
self.composite_selfref,
self.composite_selfref,
self.composite_selfref,
self.composite_selfref,
primaryjoin=and_(
remote(self.composite_selfref.c.group_id) == func.foo(
self.composite_selfref.c.group_id),
remote(self.composite_selfref.c.parent_id) ==
self.composite_selfref.c.id),
**kw)
def test_functional_index_w_string_cols_combo(self):
metadata = MetaData()
x = Table(
"x",
metadata,
Column("q", String(50)),
Column("p", Integer),
Column("z", Integer),
)
for idx, ddl in [
(
Index("y", func.lower(x.c.q), "p", x.c.z),
"CREATE INDEX y ON x (lower(q), p, z)",
),
(
Index("y", "p", func.lower(x.c.q), "z"),
"CREATE INDEX y ON x (p, lower(q), z)",
),
(
Index("y", "p", "z", func.lower(x.c.q)),
"CREATE INDEX y ON x (p, z, lower(q))",
),
(
Index("y", func.foo("foob"), x.c.p, "z"),
"CREATE INDEX y ON x (foo('foob'), p, z)",
),
(
Index("y", x.c.p, func.foo("foob"), "z"),
"CREATE INDEX y ON x (p, foo('foob'), z)",
),
(
Index("y", func.foo("foob"), "p", "z"),
"CREATE INDEX y ON x (foo('foob'), p, z)",
),
]:
x.append_constraint(idx)
self.assert_compile(schema.CreateIndex(idx), ddl)
x.to_metadata(MetaData())
def test_update_to_expression(self):
"""test update from an expression.
this logic is triggered currently by a left side that doesn't
have a key. The current supported use case is updating the index
of a PostgreSQL ARRAY type.
"""
table1 = self.tables.mytable
expr = func.foo(table1.c.myid)
eq_(expr.key, None)
self.assert_compile(table1.update().values({expr: 'bar'}),
'UPDATE mytable SET foo(myid)=:param_1')
def _join_fixture_o2m_composite_selfref_func_remote_side(self, **kw):
return relationships.JoinCondition(
self.composite_selfref,
self.composite_selfref,
self.composite_selfref,
self.composite_selfref,
primaryjoin=and_(
self.composite_selfref.c.group_id == func.foo(
self.composite_selfref.c.group_id),
self.composite_selfref.c.parent_id ==
self.composite_selfref.c.id),
remote_side=set([self.composite_selfref.c.parent_id]),
**kw)
def test_update_to_expression(self):
"""test update from an expression.
this logic is triggered currently by a left side that doesn't
have a key. The current supported use case is updating the index
of a PostgreSQL ARRAY type.
"""
table1 = self.tables.mytable
expr = func.foo(table1.c.myid)
eq_(expr.key, None)
self.assert_compile(table1.update().values({expr: 'bar'}),
'UPDATE mytable SET foo(myid)=:param_1')
def _join_fixture_o2m_composite_selfref_func_remote_side(self, **kw):
return relationships.JoinCondition(
self.composite_selfref,
self.composite_selfref,
self.composite_selfref,
self.composite_selfref,
primaryjoin=and_(
self.composite_selfref.c.group_id ==
func.foo(self.composite_selfref.c.group_id),
self.composite_selfref.c.parent_id ==
self.composite_selfref.c.id
),
remote_side=set([self.composite_selfref.c.parent_id]),
**kw
)
def test_offset_dont_misapply_labelreference(self):
m = MetaData()
t = Table('t', m, Column('x', Integer))
expr1 = func.foo(t.c.x).label('x')
expr2 = func.foo(t.c.x).label('y')
stmt1 = select([expr1]).order_by(expr1.desc()).offset(1)
stmt2 = select([expr2]).order_by(expr2.desc()).offset(1)
self.assert_compile(
stmt1,
"SELECT anon_1.x FROM (SELECT foo(t.x) AS x, "
"ROW_NUMBER() OVER (ORDER BY foo(t.x) DESC) AS mssql_rn FROM t) "
"AS anon_1 WHERE mssql_rn > :param_1"
)
self.assert_compile(
stmt2,
"SELECT anon_1.y FROM (SELECT foo(t.x) AS y, "
"ROW_NUMBER() OVER (ORDER BY foo(t.x) DESC) AS mssql_rn FROM t) "
"AS anon_1 WHERE mssql_rn > :param_1"
)
def test_inline_defaults(self):
m = MetaData()
foo = Table('foo', m, Column('id', Integer))
t = Table(
'test', m, Column('col1', Integer, onupdate=func.foo(1)),
Column('col2',
Integer,
onupdate=select([func.coalesce(func.max(foo.c.id))])),
Column('col3', String(30)))
self.assert_compile(
t.update(inline=True, values={'col3': 'foo'}),
"UPDATE test SET col1=foo(:foo_1), col2=(SELECT "
"coalesce(max(foo.id)) AS coalesce_1 FROM foo), "
"col3=:col3")
def test_inline_defaults(self):
m = MetaData()
foo = Table('foo', m, Column('id', Integer))
t = Table(
'test',
m,
Column('col1', Integer, default=func.foo(1)),
Column('col2',
Integer,
default=select([func.coalesce(func.max(foo.c.id))])),
)
self.assert_compile(
t.insert(inline=True, values={}),
"INSERT INTO test (col1, col2) VALUES (foo(:foo_1), "
"(SELECT coalesce(max(foo.id)) AS coalesce_1 FROM "
"foo))")
def test_inline_defaults(self):
m = MetaData()
foo = Table("foo", m, Column("id", Integer))
t = Table(
"test",
m,
Column("col1", Integer, default=func.foo(1)),
Column(
"col2",
Integer,
default=select([func.coalesce(func.max(foo.c.id))]),
),
)
self.assert_compile(
t.insert(inline=True, values={}),
"INSERT INTO test (col1, col2) VALUES (foo(:foo_1), "
"(SELECT coalesce(max(foo.id)) AS coalesce_1 FROM "
"foo))",
)
def test_inline_defaults(self):
m = MetaData()
foo = Table("foo", m, Column("id", Integer))
t = Table(
"test",
m,
Column("col1", Integer, onupdate=func.foo(1)),
Column(
"col2",
Integer,
onupdate=select([func.coalesce(func.max(foo.c.id))]),
),
Column("col3", String(30)),
)
self.assert_compile(
t.update(inline=True, values={"col3": "foo"}),
"UPDATE test SET col1=foo(:foo_1), col2=(SELECT "
"coalesce(max(foo.id)) AS coalesce_1 FROM foo), "
"col3=:col3",
)
def test_inline_defaults(self):
m = MetaData()
foo = Table("foo", m, Column("id", Integer))
t = Table(
"test",
m,
Column("col1", Integer, onupdate=func.foo(1)),
Column(
"col2",
Integer,
onupdate=select([func.coalesce(func.max(foo.c.id))]),
),
Column("col3", String(30)),
)
self.assert_compile(
t.update(inline=True, values={"col3": "foo"}),
"UPDATE test SET col1=foo(:foo_1), col2=(SELECT "
"coalesce(max(foo.id)) AS coalesce_1 FROM foo), "
"col3=:col3",
)
def test_columnadapter_anonymized(self):
"""test issue #3148
Testing the anonymization applied from the ColumnAdapter.columns
collection, typically as used in eager loading.
"""
exprs = [
table1.c.myid,
table1.c.name.label("t1name"),
func.foo("hoho").label("x"),
]
ta = table1.alias()
adapter = sql_util.ColumnAdapter(ta, anonymize_labels=True)
s1 = (select([adapter.columns[expr] for expr in exprs
]).apply_labels().order_by("myid", "t1name", "x"))
def go():
# the labels here are anonymized, so label naming
# can't catch these.
self.assert_compile(
s1,
"SELECT mytable_1.myid AS mytable_1_myid, "
"mytable_1.name AS name_1, foo(:foo_2) AS foo_1 "
"FROM mytable AS mytable_1 ORDER BY mytable_1.myid, t1name, x",
)
assert_warnings(
go,
[
"Can't resolve label reference 't1name'",
"Can't resolve label reference 'x'",
],
regex=True,
)
def test_columnadapter_non_anonymized(self):
"""test issue #3148
Testing the anonymization applied from the ColumnAdapter.columns
collection, typically as used in eager loading.
"""
exprs = [
table1.c.myid,
table1.c.name.label('t1name'),
func.foo("hoho").label('x')
]
ta = table1.alias()
adapter = sql_util.ColumnAdapter(ta)
s1 = select([adapter.columns[expr] for expr in exprs]).\
apply_labels().order_by("myid", "t1name", "x")
# labels are maintained
self.assert_compile(
s1, "SELECT mytable_1.myid AS mytable_1_myid, "
"mytable_1.name AS t1name, foo(:foo_1) AS x "
"FROM mytable AS mytable_1 ORDER BY mytable_1.myid, t1name, x")
def t_dml_insert() -> None:
s1 = insert(User).returning(User.id, User.name)
r1 = session.execute(s1)
# EXPECTED_TYPE: Result[Tuple[int, str]]
reveal_type(r1)
s2 = insert(User).returning(User)
r2 = session.execute(s2)
# EXPECTED_TYPE: Result[Tuple[User]]
reveal_type(r2)
s3 = insert(User).returning(func.foo(), column("q"))
# EXPECTED_TYPE: ReturningInsert[Any]
reveal_type(s3)
r3 = session.execute(s3)
# EXPECTED_TYPE: Result[Any]
reveal_type(r3)
def value(cls):
"This is a class-level docstring"
return func.foo(cls._value) + cls.bar_value
def value(cls, value):
"This is a class-level docstring"
return func.foo(cls._value, value) + value
def value(cls):
return func.foo(cls._value) + cls.bar_value
def value(cls, value):
return func.foo(cls._value, value) + value
def value(cls):
"This is a class-level docstring"
return func.foo(cls._value) + cls.bar_value
def test_use_labels(self):
self.assert_compile(
select(func.foo()).apply_labels(), "SELECT foo() AS foo_1"
)
def value(cls):
return func.foo(cls._value) + cls.bar_value
def test_use_labels(self):
self.assert_compile(select([func.foo()], use_labels=True),
"SELECT foo() AS foo_1"
)
def foo_value(self):
return func.foo(self.value)
def value(cls, value):
"This is a class-level docstring"
return func.foo(cls._value, value) + value
def other_value(cls, value):
return func.foo(cls._value, value) + value
