def define_tables(cls, metadata):
t2 = Table("t2", metadata, Column("nextid", Integer))
Table(
"t1",
metadata,
Column(
"id",
Integer,
primary_key=True,
default=sa.select([func.max(t2.c.nextid)]).as_scalar(),
),
Column("data", String(30)),
)
Table(
"date_table",
metadata,
Column(
"date_id",
DateTime,
default=text("current_timestamp"),
primary_key=True,
),
)
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_delete_invalid_evaluation(self):
User = self.classes.User
sess = Session()
john, jack, jill, jane = sess.query(User).order_by(User.id).all()
assert_raises(
exc.InvalidRequestError,
sess.query(User).filter(
User.name == select([func.max(User.name)])).delete,
synchronize_session="evaluate",
)
sess.query(User).filter(
User.name == select([func.max(User.name)])).delete(
synchronize_session="fetch")
assert john not in sess
eq_(sess.query(User).order_by(User.id).all(), [jack, jill, jane])
def test_funcfilter_fromobj_fromfunc(self):
# test from_obj generation.
# from func:
self.assert_compile(
select(
[
func.max(table1.c.name).filter(
literal_column("description") != None # noqa
)
]
),
"SELECT max(mytable.name) FILTER (WHERE description "
"IS NOT NULL) AS anon_1 FROM mytable",
)
def test_delete_from_joined_subq_test(self):
Document = self.classes.Document
s = Session()
subq = (s.query(func.max(Document.title).label("title")).group_by(
Document.user_id).subquery())
s.query(Document).filter(Document.title == subq.c.title).delete(
synchronize_session=False)
eq_(
set(s.query(Document.id, Document.flag)),
set([(2, None), (3, None), (6, None)]),
)
def test_aggregate(self):
foo, Foo = self.tables.foo, self.classes.Foo
sess = create_session()
query = sess.query(Foo)
assert query.count() == 100
assert sess.query(func.min(
foo.c.bar)).filter(foo.c.bar < 30).one() == (0, )
assert sess.query(func.max(
foo.c.bar)).filter(foo.c.bar < 30).one() == (29, )
assert (next(
query.filter(foo.c.bar < 30).values(sa.func.max(
foo.c.bar)))[0] == 29)
assert (next(
query.filter(foo.c.bar < 30).values(sa.func.max(
foo.c.bar)))[0] == 29)
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_update_using_in(self):
Document = self.classes.Document
s = Session()
subq = (s.query(func.max(Document.title).label("title")).group_by(
Document.user_id).subquery())
s.query(Document).filter(Document.title.in_(subq)).update(
{"flag": True}, synchronize_session=False)
eq_(
set(s.query(Document.id, Document.flag)),
set([
(1, True),
(2, None),
(3, None),
(4, True),
(5, True),
(6, None),
]),
)
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_update_using_case(self):
Document = self.classes.Document
s = Session()
subq = (s.query(func.max(Document.title).label("title")).group_by(
Document.user_id).subquery())
# this would work with Firebird if you do literal_column('1')
# instead
case_stmt = case([(Document.title.in_(subq), True)], else_=False)
s.query(Document).update({"flag": case_stmt},
synchronize_session=False)
eq_(
set(s.query(Document.id, Document.flag)),
set([
(1, True),
(2, False),
(3, False),
(4, True),
(5, True),
(6, False),
]),
)
