def test_withoutjoinedload(self):
Thing, tests, options = (
self.classes.Thing,
self.tables.tests,
self.tables.options,
)
s = create_session()
result = (
s.query(Thing).select_from(
tests.outerjoin(
options,
sa.and_(
tests.c.id == options.c.test_id,
tests.c.owner_id == options.c.owner_id,
),
)).filter(
sa.and_(
tests.c.owner_id == 1,
sa.or_(
options.c.someoption == None, # noqa
options.c.someoption == False,
),
)))
result_str = ["%d %s" % (t.id, t.category.name) for t in result]
eq_(result_str, ["1 Some Category", "3 Some Category"])
def test_withjoinedload(self):
"""
Test that an joinedload locates the correct "from" clause with which to
attach to, when presented with a query that already has a complicated
from clause.
"""
Thing, tests, options = (
self.classes.Thing,
self.tables.tests,
self.tables.options,
)
s = create_session()
q = s.query(Thing).options(sa.orm.joinedload("category"))
result = q.select_from(
tests.outerjoin(
options,
sa.and_(
tests.c.id == options.c.test_id,
tests.c.owner_id == options.c.owner_id,
),
)).filter(
sa.and_(
tests.c.owner_id == 1,
sa.or_(
options.c.someoption == None,
options.c.someoption == False, # noqa
),
))
result_str = ["%d %s" % (t.id, t.category.name) for t in result]
eq_(result_str, ["1 Some Category", "3 Some Category"])
def test_compare_clauselist_associative(self):
l1 = and_(self.a.c.x == self.b.c.y, self.a.c.y == self.b.c.z)
l2 = and_(self.a.c.y == self.b.c.z, self.a.c.x == self.b.c.y)
l3 = and_(self.a.c.x == self.b.c.z, self.a.c.y == self.b.c.y)
is_true(l1.compare(l1))
is_true(l1.compare(l2))
is_false(l1.compare(l3))
def test_nonansi_plusses_everthing_in_the_condition(self):
table1 = table(
"mytable",
column("myid", Integer),
column("name", String),
column("description", String),
)
table2 = table(
"myothertable",
column("otherid", Integer),
column("othername", String),
)
stmt = select([table1]).select_from(
table1.outerjoin(
table2,
and_(
table1.c.myid == table2.c.otherid,
table2.c.othername > 5,
table1.c.name == "foo",
),
))
self.assert_compile(
stmt,
"SELECT mytable.myid, mytable.name, mytable.description "
"FROM mytable, myothertable WHERE mytable.myid = "
"myothertable.otherid(+) AND myothertable.othername(+) > "
":othername_1 AND mytable.name = :name_1",
dialect=oracle.dialect(use_ansi=False),
)
stmt = select([table1]).select_from(
table1.outerjoin(
table2,
and_(
table1.c.myid == table2.c.otherid,
table2.c.othername == None,
table1.c.name == None,
),
))
self.assert_compile(
stmt,
"SELECT mytable.myid, mytable.name, mytable.description "
"FROM mytable, myothertable WHERE mytable.myid = "
"myothertable.otherid(+) AND myothertable.othername(+) IS NULL "
"AND mytable.name IS NULL",
dialect=oracle.dialect(use_ansi=False),
)
def test_join_to_join_entities(self):
sess = create_session()
pa = with_polymorphic(Person, [Engineer])
pa_alias = with_polymorphic(Person, [Engineer], aliased=True)
eq_(
[
(p1.name, type(p1), p2.name, type(p2))
for (p1, p2) in sess.query(pa, pa_alias)
.join(
pa_alias,
or_(
pa.Engineer.primary_language
== pa_alias.Engineer.primary_language,
and_(
pa.Engineer.primary_language == None, # noqa
pa_alias.Engineer.primary_language == None,
pa.person_id > pa_alias.person_id,
),
),
)
.order_by(pa.name, pa_alias.name)
],
[
("dilbert", Engineer, "dilbert", Engineer),
("dogbert", Manager, "pointy haired boss", Boss),
("vlad", Engineer, "vlad", Engineer),
("wally", Engineer, "wally", Engineer),
],
)
def test_join_to_join_columns(self):
sess = create_session()
pa = with_polymorphic(Person, [Engineer])
pa_alias = with_polymorphic(Person, [Engineer], aliased=True)
eq_(
[
row
for row in sess.query(
pa.name,
pa.Engineer.primary_language,
pa_alias.name,
pa_alias.Engineer.primary_language,
)
.join(
pa_alias,
or_(
pa.Engineer.primary_language
== pa_alias.Engineer.primary_language,
and_(
pa.Engineer.primary_language == None, # noqa
pa_alias.Engineer.primary_language == None,
pa.person_id > pa_alias.person_id,
),
),
)
.order_by(pa.name, pa_alias.name)
],
[
("dilbert", "java", "dilbert", "java"),
("dogbert", None, "pointy haired boss", None),
("vlad", "cobol", "vlad", "cobol"),
("wally", "c++", "wally", "c++"),
],
)
def _join_fixture_inh_selfref_w_entity(self, **kw):
fake_logger = mock.Mock(info=lambda *arg, **kw: None)
prop = mock.Mock(parent=mock.Mock(),
mapper=mock.Mock(),
logger=fake_logger)
local_selectable = self.base.join(self.sub)
remote_selectable = self.base.join(self.sub_w_sub_rel)
# note this test requires that "parentmapper" annotation is
# present in the columns ahead of time
sub_w_sub_rel__sub_id = self.sub_w_sub_rel.c.sub_id._annotate(
{"parentmapper": prop.mapper})
sub__id = self.sub.c.id._annotate({"parentmapper": prop.parent})
sub_w_sub_rel__flag = self.base.c.flag._annotate(
{"parentmapper": prop.mapper})
return relationships.JoinCondition(
local_selectable,
remote_selectable,
local_selectable,
remote_selectable,
primaryjoin=and_(
sub_w_sub_rel__sub_id == sub__id,
sub_w_sub_rel__flag == True, # noqa
),
prop=prop,
)
def setup_classes(cls):
Base = cls.DeclarativeBasic
class A(Base):
__tablename__ = "a"
id = Column(Integer, primary_key=True)
class B(Base):
__tablename__ = "b"
id = Column(Integer, primary_key=True)
a_id = Column(ForeignKey("a.id"))
cs = relationship("C")
class C(Base):
__tablename__ = "c"
id = Column(Integer, primary_key=True)
b_id = Column(ForeignKey("b.id"))
partition = select([
B,
func.row_number().over(order_by=B.id,
partition_by=B.a_id).label("index"),
]).alias()
partitioned_b = aliased(B, alias=partition)
A.partitioned_bs = relationship(
partitioned_b,
primaryjoin=and_(partitioned_b.a_id == A.id,
partition.c.index < 10),
)
def test_callable_bind(self):
Address, addresses, users, User = (
self.classes.Address,
self.tables.addresses,
self.tables.users,
self.classes.User,
)
mapper(
User,
users,
properties=dict(addresses=relationship(
mapper(Address, addresses),
lazy="select",
primaryjoin=and_(
users.c.id == addresses.c.user_id,
users.c.name == bindparam("name", callable_=lambda: "ed"),
),
)),
)
s = Session()
ed = s.query(User).filter_by(name="ed").one()
eq_(
ed.addresses,
[
Address(id=2, user_id=8),
Address(id=3, user_id=8),
Address(id=4, user_id=8),
],
)
fred = s.query(User).filter_by(name="fred").one()
eq_(fred.addresses, []) # fred is missing
def test_compare_clauselist_assoc_different_operator(self):
l1 = and_(self.a.c.x == self.b.c.y, self.a.c.y == self.b.c.z)
l2 = or_(self.a.c.y == self.b.c.z, self.a.c.x == self.b.c.y)
is_false(l1.compare(l2))
def test_distinct_count(self):
Table2, Obj1, Table1 = (
self.tables.Table2,
self.classes.Obj1,
self.tables.Table1,
)
q = create_session(bind=testing.db).query(Obj1)
assert q.count() == 4
res = q.filter(
sa.and_(Table1.c.ID == Table2.c.T1ID, Table2.c.T1ID == 1))
assert res.count() == 3
res = q.filter(
sa.and_(Table1.c.ID == Table2.c.T1ID,
Table2.c.T1ID == 1)).distinct()
eq_(res.count(), 1)
def test_distinct_count(self):
table2, Obj1, table1 = (
self.tables.table2,
self.classes.Obj1,
self.tables.table1,
)
query = create_session().query(Obj1)
eq_(query.count(), 4)
res = query.filter(
sa.and_(table1.c.id == table2.c.t1id, table2.c.t1id == 1))
eq_(res.count(), 3)
res = query.filter(
sa.and_(table1.c.id == table2.c.t1id,
table2.c.t1id == 1)).distinct()
eq_(res.count(), 1)
def test_custom_bind(self):
Address, addresses, users, User = (
self.classes.Address,
self.tables.addresses,
self.tables.users,
self.classes.User,
)
mapper(
User,
users,
properties=dict(addresses=relationship(
mapper(Address, addresses),
lazy="select",
primaryjoin=and_(
users.c.id == addresses.c.user_id,
users.c.name == bindparam("name"),
),
)),
)
canary = mock.Mock()
class MyOption(MapperOption):
propagate_to_loaders = True
def __init__(self, crit):
self.crit = crit
def process_query_conditionally(self, query):
"""process query during a lazyload"""
canary()
query._params = query._params.union(dict(name=self.crit))
s = Session()
ed = s.query(User).options(MyOption("ed")).filter_by(name="ed").one()
eq_(
ed.addresses,
[
Address(id=2, user_id=8),
Address(id=3, user_id=8),
Address(id=4, user_id=8),
],
)
eq_(canary.mock_calls, [mock.call()])
fred = (s.query(User).options(
MyOption("ed")).filter_by(name="fred").one())
eq_(fred.addresses, []) # fred is missing
eq_(canary.mock_calls, [mock.call(), mock.call()])
# the lazy query was not cached; the option is re-applied to the
# Fred object due to populate_existing()
fred = (s.query(User).populate_existing().options(
MyOption("fred")).filter_by(name="fred").one())
eq_(fred.addresses, [Address(id=5, user_id=9)]) # fred is there
eq_(canary.mock_calls, [mock.call(), mock.call(), mock.call()])
def test_where_empty(self):
table1 = self.tables.mytable
self.assert_compile(
table1.delete().where(and_()), "DELETE FROM mytable"
)
self.assert_compile(
table1.delete().where(or_()), "DELETE FROM mytable"
)
def _join_fixture_o2m_o_side_none(self, **kw):
return relationships.JoinCondition(
self.left,
self.right,
self.left,
self.right,
primaryjoin=and_(self.left.c.id == self.right.c.lid,
self.left.c.x == 5),
**kw)
def test_match_across_joins(self):
results = (matchtable.select().where(
and_(
cattable.c.id == matchtable.c.category_id,
or_(
cattable.c.description.match("Ruby"),
matchtable.c.title.match("nutshells"),
),
)).order_by(matchtable.c.id).execute().fetchall())
eq_([1, 3, 5], [r.id for r in results])
def test_noorm(self):
"""test the control case"""
tests, options, categories = (
self.tables.tests,
self.tables.options,
self.tables.categories,
)
# I want to display a list of tests owned by owner 1
# if someoption is false or they haven't specified it yet (null)
# but not if they set it to true (example someoption is for hiding)
# desired output for owner 1
# test_id, cat_name
# 1 'Some Category'
# 3 "
# not orm style correct query
print("Obtaining correct results without orm")
result = (
sa.select(
[tests.c.id, categories.c.name],
sa.and_(
tests.c.owner_id == 1,
sa.or_(
options.c.someoption == None, # noqa
options.c.someoption == False,
),
),
order_by=[tests.c.id],
from_obj=[
tests.join(categories).outerjoin(
options,
sa.and_(
tests.c.id == options.c.test_id,
tests.c.owner_id == options.c.owner_id,
),
)
],
).execute().fetchall())
eq_(result, [(1, "Some Category"), (3, "Some Category")])
def test_and_match(self):
results1 = (matchtable.select().where(
and_(
matchtable.c.title.match("python"),
matchtable.c.title.match("nutshells"),
)).execute().fetchall())
eq_([5], [r.id for r in results1])
results2 = (matchtable.select().where(
matchtable.c.title.match(
"python & nutshells")).execute().fetchall())
eq_([5], [r.id for r in results2])
def test_where_empty(self):
table1 = self.tables.mytable
self.assert_compile(
table1.update().where(and_()),
"UPDATE mytable SET myid=:myid, name=:name, "
"description=:description",
)
self.assert_compile(
table1.update().where(or_()),
"UPDATE mytable SET myid=:myid, name=:name, "
"description=:description",
)
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_correlated_lazyload(self):
stuff, user_t = self.tables.stuff, self.tables.user_t
class User(fixtures.ComparableEntity):
pass
class Stuff(fixtures.ComparableEntity):
pass
mapper(Stuff, stuff)
stuff_view = (sa.select([
stuff.c.id
]).where(stuff.c.user_id == user_t.c.id).correlate(user_t).order_by(
sa.desc(stuff.c.date)).limit(1))
mapper(
User,
user_t,
properties={
"stuff":
relationship(
Stuff,
primaryjoin=sa.and_(
user_t.c.id == stuff.c.user_id,
stuff.c.id == (stuff_view.as_scalar()),
),
)
},
)
sess = create_session()
eq_(
sess.query(User).all(),
[
User(
name="user1",
stuff=[Stuff(date=datetime.date(2007, 12, 15), id=2)],
),
User(
name="user2",
stuff=[Stuff(id=4, date=datetime.date(2008, 1, 15))],
),
User(
name="user3",
stuff=[Stuff(id=5, date=datetime.date(2007, 6, 15))],
),
],
)
def _baseline_5_aggregates(self):
Animal = self.metadata.tables["Animal"]
Zoo = self.metadata.tables["Zoo"]
# TODO: convert to ORM
engine = self.metadata.bind
for x in range(ITERATIONS):
# views
view = engine.execute(select([Animal.c.Legs])).fetchall()
legs = sorted([x[0] for x in view])
expected = {
"Leopard": 73.5,
"Slug": 0.75,
"Tiger": None,
"Lion": None,
"Bear": None,
"Ostrich": 103.2,
"Centipede": None,
"Emperor Penguin": None,
"Adelie Penguin": None,
"Millipede": None,
"Ape": None,
"Tick": None,
}
for species, lifespan in engine.execute(
select([Animal.c.Species, Animal.c.Lifespan])).fetchall():
assert lifespan == expected[species]
expected = ["Montr\xe9al Biod\xf4me", "Wild Animal Park"]
e = select(
[Zoo.c.Name],
and_(
Zoo.c.Founded != None, # noqa
Zoo.c.Founded <= func.current_timestamp(),
Zoo.c.Founded >= datetime.date(1990, 1, 1),
),
)
values = [val[0] for val in engine.execute(e).fetchall()]
assert set(values) == set(expected)
# distinct
legs = [
x[0] for x in engine.execute(
select([Animal.c.Legs], distinct=True)).fetchall()
]
legs.sort()
def test_visit_binary_product(self):
a, b, q, e, f, j, r = [column(chr_) for chr_ in "abqefjr"]
from sqlalchemy_1_3 import and_, func
from sqlalchemy_1_3.sql.util import visit_binary_product
expr = and_((a + b) == q + func.sum(e + f), j == r)
def visit(expr, left, right):
pass
@assert_cycles()
def go():
visit_binary_product(visit, expr)
go()
def _baseline_7_multiview(self):
Zoo = self.metadata.tables["Zoo"]
Animal = self.metadata.tables["Animal"]
engine = self.metadata.bind
def fulltable(select):
"""Iterate over the full result table."""
return [list(row) for row in engine.execute(select).fetchall()]
for x in range(ITERATIONS):
fulltable(
select(
[Zoo.c.ID] + list(Animal.c),
Zoo.c.Name == "San Diego Zoo",
from_obj=[join(Zoo, Animal)],
))
Zoo.select(Zoo.c.Name == "San Diego Zoo")
fulltable(
select(
[Zoo.c.ID, Animal.c.ID],
and_(
Zoo.c.Name == "San Diego Zoo",
Animal.c.Species == "Leopard",
),
from_obj=[join(Zoo, Animal)],
))
# Now try the same query with INNER, LEFT, and RIGHT JOINs.
fulltable(
select(
[Zoo.c.Name, Animal.c.Species],
from_obj=[join(Zoo, Animal)],
))
fulltable(
select(
[Zoo.c.Name, Animal.c.Species],
from_obj=[outerjoin(Zoo, Animal)],
))
fulltable(
select(
[Zoo.c.Name, Animal.c.Species],
from_obj=[outerjoin(Animal, Zoo)],
))
def setup_mappers(cls):
Person, City = cls.classes.Person, cls.classes.City
city, person = cls.tables.city, cls.tables.person
mapper(
Person,
person,
properties={
"city":
relationship(
City,
primaryjoin=and_(person.c.city_id == city.c.id,
city.c.deleted == False), # noqa
backref="people",
)
},
)
mapper(City, city)
def test_dont_use_get_pj_is_different(self):
mapper(self.classes.A, self.tables.a)
m_b = mapper(
self.classes.B,
self.tables.b_sameorder,
properties={
"a":
relationship(
self.classes.A,
primaryjoin=and_(
self.tables.a.c.id1 == self.tables.b_sameorder.c.a_id1,
self.tables.a.c.id2 == 12,
),
)
},
)
configure_mappers()
is_false(m_b.relationships.a.strategy.use_get)
def setup_mappers(cls):
Category, owners, Option, tests, Thing, Owner, options, categories = (
cls.classes.Category,
cls.tables.owners,
cls.classes.Option,
cls.tables.tests,
cls.classes.Thing,
cls.classes.Owner,
cls.tables.options,
cls.tables.categories,
)
mapper(Owner, owners)
mapper(Category, categories)
mapper(
Option,
options,
properties=dict(
owner=relationship(Owner, viewonly=True),
test=relationship(Thing, viewonly=True),
),
)
mapper(
Thing,
tests,
properties=dict(
owner=relationship(Owner, backref="tests"),
category=relationship(Category),
owner_option=relationship(
Option,
primaryjoin=sa.and_(
tests.c.id == options.c.test_id,
tests.c.owner_id == options.c.owner_id,
),
foreign_keys=[options.c.test_id, options.c.owner_id],
uselist=False,
),
),
)
def test_select_bundle_columns(self):
self.assert_compile(
select(
[
table1,
table2.c.otherid,
text("sysdate()"),
text("foo, bar, lala"),
],
and_(
text("foo.id = foofoo(lala)"),
text("datetime(foo) = Today"),
table1.c.myid == table2.c.otherid,
),
),
"SELECT mytable.myid, mytable.name, mytable.description, "
"myothertable.otherid, sysdate(), foo, bar, lala "
"FROM mytable, myothertable WHERE foo.id = foofoo(lala) AND "
"datetime(foo) = Today AND mytable.myid = myothertable.otherid",
)
def _join_fixture_overlapping_three_tables(self, **kw):
def _can_sync(*cols):
for c in cols:
if self.three_tab_c.c.contains_column(c):
return False
else:
return True
return relationships.JoinCondition(
self.three_tab_a,
self.three_tab_b,
self.three_tab_a,
self.three_tab_b,
support_sync=False,
can_be_synced_fn=_can_sync,
primaryjoin=and_(
self.three_tab_a.c.id == self.three_tab_b.c.aid,
self.three_tab_c.c.bid == self.three_tab_b.c.id,
self.three_tab_c.c.aid == self.three_tab_a.c.id,
),
)
def _u_ad_fixture(self, populate_user, dont_use_get=False):
users, Address, addresses, User = (
self.tables.users,
self.classes.Address,
self.tables.addresses,
self.classes.User,
)
mapper(
User,
users,
properties={
"addresses": relationship(Address, back_populates="user")
},
)
mapper(
Address,
addresses,
properties={
"user":
relationship(
User,
primaryjoin=and_(users.c.id == addresses.c.user_id,
users.c.id != 27)
if dont_use_get else None,
back_populates="addresses",
)
},
)
sess = create_session()
a1 = Address(email_address="a1")
sess.add(a1)
if populate_user:
a1.user = User(name="ed")
sess.flush()
if populate_user:
sess.expire_all()
return User, Address, sess, a1
