def test_empty_insert(self, connection):
t1 = Table(
"t1",
self.metadata,
Column("is_true", Boolean, server_default=("1")),
)
self.metadata.create_all(connection)
connection.execute(t1.insert())
eq_(
1,
connection.scalar(select([func.count(text("*"))]).select_from(t1)),
)
eq_(True, connection.scalar(t1.select()))
def test_funcfilter_chaining(self):
# test chaining:
self.assert_compile(
select(
[
func.count(1)
.filter(table1.c.name == "name")
.filter(table1.c.description == "description")
]
),
"SELECT count(:count_1) FILTER (WHERE "
"mytable.name = :name_1 AND mytable.description = :description_1) "
"AS anon_1 FROM mytable",
)
def test_col_w_nonoptional_sequence_non_autoinc_no_firing(
self, dataset_no_autoinc, connection):
"""When the sequence is not optional and sequences are supported,
the test fails because we didn't create the sequence.
"""
dataset_no_autoinc.c.set_id.default.optional = False
connection.execute(dataset_no_autoinc.insert())
eq_(
connection.scalar(
select([func.count("*")]).select_from(dataset_no_autoinc)),
1,
)
def test_delete(self):
KeywordAssociation = self.classes.KeywordAssociation
Item = self.classes.Item
item_keywords = self.tables.item_keywords
Keyword = self.classes.Keyword
sess = create_session()
item1 = Item("item1")
item2 = Item("item2")
item1.keywords.append(
KeywordAssociation(Keyword("blue"), "blue_assoc")
)
item1.keywords.append(KeywordAssociation(Keyword("red"), "red_assoc"))
item2.keywords.append(
KeywordAssociation(Keyword("green"), "green_assoc")
)
sess.add_all((item1, item2))
sess.flush()
eq_(select([func.count("*")]).select_from(item_keywords).scalar(), 3)
sess.delete(item1)
sess.delete(item2)
sess.flush()
eq_(select([func.count("*")]).select_from(item_keywords).scalar(), 0)
def test_w_new_entities(self):
"""Test that the query can have its entities modified in
an arbitrary callable, and that this new entity list is preserved
when the query is invoked.
"""
User = self.classes.User
bq = self.bakery(lambda s: s.query(User.id, User.name))
bq += lambda q: q.from_self().with_entities(func.count(User.id))
for i in range(3):
session = Session(autocommit=True)
eq_(bq(session).all(), [(4, )])
def test_col_w_optional_sequence_non_autoinc_no_firing(
self, dataset_no_autoinc, connection):
"""this is testing that a Table which includes a Sequence, when
run against a DB that does not support sequences, the Sequence
does not get in the way.
"""
dataset_no_autoinc.c.set_id.default.optional = True
connection.execute(dataset_no_autoinc.insert())
eq_(
connection.scalar(
select([func.count("*")]).select_from(dataset_no_autoinc)),
1,
)
def test_query_one(self):
q = (
Session.query(User)
.filter(User.name == "ed")
.options(joinedload(User.addresses))
)
q2 = serializer.loads(serializer.dumps(q, -1), users.metadata, Session)
def go():
eq_(
q2.all(),
[
User(
name="ed",
addresses=[
Address(id=2),
Address(id=3),
Address(id=4),
],
)
],
)
self.assert_sql_count(testing.db, go, 1)
eq_(
q2.join(User.addresses)
.filter(Address.email == "*****@*****.**")
.value(func.count(literal_column("*"))),
1,
)
u1 = Session.query(User).get(8)
q = (
Session.query(Address)
.filter(Address.user == u1)
.order_by(desc(Address.email))
)
q2 = serializer.loads(serializer.dumps(q, -1), users.metadata, Session)
eq_(
q2.all(),
[
Address(email="*****@*****.**"),
Address(email="*****@*****.**"),
Address(email="*****@*****.**"),
],
)
def test_strlen(self):
metadata = self.metadata
# On FB the length() function is implemented by an external UDF,
# strlen(). Various SA tests fail because they pass a parameter
# to it, and that does not work (it always results the maximum
# string length the UDF was declared to accept). This test
# checks that at least it works ok in other cases.
t = Table(
"t1",
metadata,
Column("id", Integer, Sequence("t1idseq"), primary_key=True),
Column("name", String(10)),
)
metadata.create_all()
t.insert(values=dict(name="dante")).execute()
t.insert(values=dict(name="alighieri")).execute()
select([func.count(t.c.id)],
func.length(t.c.name) == 5).execute().first()[0] == 1
def test_funcfilter_criterion(self):
self.assert_compile(
func.count(1).filter(table1.c.name != None), # noqa
"count(:count_1) FILTER (WHERE mytable.name IS NOT NULL)",
)
def test_funcfilter_empty(self):
self.assert_compile(func.count(1).filter(), "count(:count_1)")
def test_subquery_eagerloading(self):
User = self.classes.User
Address = self.classes.Address
Order = self.classes.Order
# Override the default bakery for one with a smaller size. This used to
# trigger a bug when unbaking subqueries.
self.bakery = baked.bakery(size=3)
base_bq = self.bakery(lambda s: s.query(User))
base_bq += lambda q: q.options(subqueryload(User.addresses),
subqueryload(User.orders))
base_bq += lambda q: q.order_by(User.id)
assert_result = [
User(
id=7,
addresses=[Address(id=1, email_address="*****@*****.**")],
orders=[Order(id=1), Order(id=3),
Order(id=5)],
),
User(
id=8,
addresses=[
Address(id=2, email_address="*****@*****.**"),
Address(id=3, email_address="*****@*****.**"),
Address(id=4, email_address="*****@*****.**"),
],
),
User(
id=9,
addresses=[Address(id=5)],
orders=[Order(id=2), Order(id=4)],
),
User(id=10, addresses=[]),
]
for i in range(4):
for cond1, cond2 in itertools.product(*[(False, True)
for j in range(2)]):
bq = base_bq._clone()
sess = Session()
if cond1:
bq += lambda q: q.filter(User.name == "jack")
else:
bq += lambda q: q.filter(User.name.like("%ed%"))
if cond2:
ct = func.count(Address.id).label("count")
subq = (sess.query(ct, Address.user_id).group_by(
Address.user_id).having(ct > 2).subquery())
bq += lambda q: q.join(subq)
if cond2:
if cond1:
def go():
result = bq(sess).all()
eq_([], result)
self.assert_sql_count(testing.db, go, 1)
else:
def go():
result = bq(sess).all()
eq_(assert_result[1:2], result)
self.assert_sql_count(testing.db, go, 3)
else:
if cond1:
def go():
result = bq(sess).all()
eq_(assert_result[0:1], result)
self.assert_sql_count(testing.db, go, 3)
else:
def go():
result = bq(sess).all()
eq_(assert_result[1:3], result)
self.assert_sql_count(testing.db, go, 3)
sess.close()
def test_conditional_step(self):
"""Test a large series of conditionals and assert that
results remain correct between all of them within a series
of loops.
"""
User = self.classes.User
base_bq = self.bakery(lambda s: s.query(User.id, User.name))
base_bq += lambda q: q.order_by(User.id)
for i in range(4):
for cond1, cond2, cond3, cond4 in itertools.product(
*[(False, True) for j in range(4)]):
bq = base_bq._clone()
if cond1:
bq += lambda q: q.filter(User.name != "jack")
if cond2:
bq += lambda q: q.join(User.addresses)
else:
bq += lambda q: q.outerjoin(User.addresses)
elif cond3:
bq += lambda q: q.filter(User.name.like("%ed%"))
else:
bq += lambda q: q.filter(User.name == "jack")
if cond4:
bq += lambda q: q.from_self().with_entities(
func.count(User.id))
sess = Session(autocommit=True)
result = bq(sess).all()
if cond4:
if cond1:
if cond2:
eq_(result, [(4, )])
else:
eq_(result, [(5, )])
elif cond3:
eq_(result, [(2, )])
else:
eq_(result, [(1, )])
else:
if cond1:
if cond2:
eq_(
result,
[(8, "ed"), (8, "ed"), (8, "ed"), (9, "fred")],
)
else:
eq_(
result,
[
(8, "ed"),
(8, "ed"),
(8, "ed"),
(9, "fred"),
(10, "chuck"),
],
)
elif cond3:
eq_(result, [(8, "ed"), (9, "fred")])
else:
eq_(result, [(7, "jack")])
sess.close()
def _test_baked_lazy_loading(self, set_option):
User, Address = self.classes.User, self.classes.Address
base_bq = self.bakery(lambda s: s.query(User))
if set_option:
base_bq += lambda q: q.options(lazyload(User.addresses))
base_bq += lambda q: q.order_by(User.id)
assert_result = self.static.user_address_result
for i in range(4):
for cond1, cond2 in itertools.product(*[(False, True)
for j in range(2)]):
bq = base_bq._clone()
sess = Session()
if cond1:
bq += lambda q: q.filter(User.name == "jack")
else:
bq += lambda q: q.filter(User.name.like("%ed%"))
if cond2:
ct = func.count(Address.id).label("count")
subq = (sess.query(ct, Address.user_id).group_by(
Address.user_id).having(ct > 2).subquery())
bq += lambda q: q.join(subq)
if cond2:
if cond1:
def go():
result = bq(sess).all()
eq_([], result)
self.assert_sql_count(testing.db, go, 1)
else:
def go():
result = bq(sess).all()
eq_(assert_result[1:2], result)
self.assert_sql_count(testing.db, go, 2)
else:
if cond1:
def go():
result = bq(sess).all()
eq_(assert_result[0:1], result)
self.assert_sql_count(testing.db, go, 2)
else:
def go():
result = bq(sess).all()
eq_(assert_result[1:3], result)
self.assert_sql_count(testing.db, go, 3)
sess.close()