def test_distinct_count(self):
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()
self.assertEqual(res.count(), 1)
def test_noorm(self):
"""test the control case"""
# I want to display a list of tests owned by owner 1
# if someoption is false or he hasn't specified it yet (null)
# but not if he 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,
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, u'Some Category'), (3, u'Some Category')])
def test_noorm(self):
"""test the control case"""
# I want to display a list of tests owned by owner 1
# if someoption is false or he hasn't specified it yet (null)
# but not if he 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, 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, u"Some Category"), (3, u"Some Category")])
def test_distinct_count(self):
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()
self.assertEqual(res.count(), 1)
def test_distinct_count(self):
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_double(self):
"""tests lazy loading with two relations simulatneously, from the same table, using aliases. """
openorders = sa.alias(orders, 'openorders')
closedorders = sa.alias(orders, 'closedorders')
mapper(Address, addresses)
mapper(Order, orders)
open_mapper = mapper(Order, openorders, non_primary=True)
closed_mapper = mapper(Order, closedorders, non_primary=True)
mapper(User,
users,
properties=dict(addresses=relation(Address, lazy=True),
open_orders=relation(
open_mapper,
primaryjoin=sa.and_(
openorders.c.isopen == 1,
users.c.id == openorders.c.user_id),
lazy=True),
closed_orders=relation(
closed_mapper,
primaryjoin=sa.and_(
closedorders.c.isopen == 0,
users.c.id == closedorders.c.user_id),
lazy=True)))
q = create_session().query(User)
assert [
User(id=7,
addresses=[Address(id=1)],
open_orders=[Order(id=3)],
closed_orders=[Order(id=1), Order(id=5)]),
User(id=8,
addresses=[Address(id=2),
Address(id=3),
Address(id=4)],
open_orders=[],
closed_orders=[]),
User(id=9,
addresses=[Address(id=5)],
open_orders=[Order(id=4)],
closed_orders=[Order(id=2)]),
User(id=10)
] == q.all()
sess = create_session()
user = sess.query(User).get(7)
assert [Order(id=1), Order(id=5)
] == create_session().query(closed_mapper).with_parent(
user, property='closed_orders').all()
assert [Order(id=3)
] == create_session().query(open_mapper).with_parent(
user, property='open_orders').all()
def test_distinct_count(self):
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_withouteagerload(self):
s = create_session()
l = (s.query(Test).
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))))
result = ["%d %s" % ( t.id,t.category.name ) for t in l]
eq_(result, [u'1 Some Category', u'3 Some Category'])
def test_withouteagerload(self):
s = create_session()
l = (s.query(Test).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))))
result = ["%d %s" % (t.id, t.category.name) for t in l]
eq_(result, [u'1 Some Category', u'3 Some Category'])
def testone(self):
"""A lazy load which has multiple join conditions.
Including two that are against the same column in the child table.
"""
class Information(object):
pass
class Relation(object):
pass
class Data(object):
pass
session = create_session()
mapper(Data, data)
mapper(Relation,
rels,
properties={
'datas':
relation(Data,
primaryjoin=sa.and_(
rels.c.info_pk == data.c.info_pk,
data.c.timeval >= rels.c.start,
data.c.timeval <= rels.c.finish),
foreign_keys=[data.c.info_pk])
})
mapper(Information, infos, properties={'rels': relation(Relation)})
info = session.query(Information).get(1)
assert info
assert len(info.rels) == 2
assert len(info.rels[0].datas) == 3
def test_basic(self):
class C(object):
pass
class A(object):
pass
class B(object):
pass
mapper(B, b)
mapper(A,
a,
properties={
'tbs':
relation(B,
primaryjoin=sa.and_(b.c.a1 == a.c.a1,
b.c.b2 == True),
lazy=False)
})
mapper(C,
c,
properties={
'a1s': relation(A, secondary=c2a1, lazy=False),
'a2s': relation(A, secondary=c2a2, lazy=False)
})
assert create_session().query(C).with_labels().statement
def test_correlated_lazyload(self):
class User(_base.ComparableEntity):
pass
class Stuff(_base.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':
relation(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 test_witheagerload(self):
"""
Test that an eagerload locates the correct "from" clause with which to
attach to, when presented with a query that already has a complicated
from clause.
"""
s = create_session()
q = s.query(Test).options(sa.orm.eagerload("category"))
l = 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)))
result = ["%d %s" % (t.id, t.category.name) for t in l]
eq_(result, [u"1 Some Category", u"3 Some Category"])
def test_double(self):
"""tests lazy loading with two relations simulatneously, from the same table, using aliases. """
openorders = sa.alias(orders, 'openorders')
closedorders = sa.alias(orders, 'closedorders')
mapper(Address, addresses)
mapper(Order, orders)
open_mapper = mapper(Order, openorders, non_primary=True)
closed_mapper = mapper(Order, closedorders, non_primary=True)
mapper(User, users, properties = dict(
addresses = relation(Address, lazy = True),
open_orders = relation(open_mapper, primaryjoin = sa.and_(openorders.c.isopen == 1, users.c.id==openorders.c.user_id), lazy=True),
closed_orders = relation(closed_mapper, primaryjoin = sa.and_(closedorders.c.isopen == 0, users.c.id==closedorders.c.user_id), lazy=True)
))
q = create_session().query(User)
assert [
User(
id=7,
addresses=[Address(id=1)],
open_orders = [Order(id=3)],
closed_orders = [Order(id=1), Order(id=5)]
),
User(
id=8,
addresses=[Address(id=2), Address(id=3), Address(id=4)],
open_orders = [],
closed_orders = []
),
User(
id=9,
addresses=[Address(id=5)],
open_orders = [Order(id=4)],
closed_orders = [Order(id=2)]
),
User(id=10)
] == q.all()
sess = create_session()
user = sess.query(User).get(7)
assert [Order(id=1), Order(id=5)] == create_session().query(closed_mapper).with_parent(user, property='closed_orders').all()
assert [Order(id=3)] == create_session().query(open_mapper).with_parent(user, property='open_orders').all()
def test_dslish(self):
"""test the same as witheagerload except using generative"""
s = create_session()
q = s.query(Test).options(sa.orm.eagerload("category"))
l = q.filter(
sa.and_(tests.c.owner_id == 1, sa.or_(options.c.someoption == None, options.c.someoption == False))
).outerjoin("owner_option")
result = ["%d %s" % (t.id, t.category.name) for t in l]
eq_(result, [u"1 Some Category", u"3 Some Category"])
def test_witheagerload(self):
"""
Test that an eagerload locates the correct "from" clause with which to
attach to, when presented with a query that already has a complicated
from clause.
"""
s = create_session()
q = s.query(Test).options(sa.orm.eagerload('category'))
l = (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))))
result = ["%d %s" % (t.id, t.category.name) for t in l]
eq_(result, [u'1 Some Category', u'3 Some Category'])
def test_dslish(self):
"""test the same as witheagerload except using generative"""
s = create_session()
q = s.query(Test).options(sa.orm.eagerload('category'))
l = q.filter(
sa.and_(
tests.c.owner_id == 1,
sa.or_(
options.c.someoption == None,
options.c.someoption == False))).outerjoin('owner_option')
result = ["%d %s" % (t.id, t.category.name) for t in l]
eq_(result, [u'1 Some Category', u'3 Some Category'])
def testsequence(self):
cartitems.insert().execute(description='hi')
cartitems.insert().execute(description='there')
r = cartitems.insert().execute(description='lala')
assert r.last_inserted_ids() and r.last_inserted_ids()[0] is not None
id_ = r.last_inserted_ids()[0]
eq_(1,
sa.select([func.count(cartitems.c.cart_id)],
sa.and_(cartitems.c.description == 'lala',
cartitems.c.cart_id == id_)).scalar())
cartitems.select().execute().fetchall()
def testsequence(self):
cartitems.insert().execute(description='hi')
cartitems.insert().execute(description='there')
r = cartitems.insert().execute(description='lala')
assert r.last_inserted_ids() and r.last_inserted_ids()[0] is not None
id_ = r.last_inserted_ids()[0]
eq_(
1,
sa.select([func.count(cartitems.c.cart_id)],
sa.and_(cartitems.c.description == 'lala',
cartitems.c.cart_id == id_)).scalar())
cartitems.select().execute().fetchall()
def test_basic(self):
class C(object):pass
class A(object):pass
class B(object):pass
mapper(B, b)
mapper(A, a, properties={
'tbs': relation(B, primaryjoin=sa.and_(b.c.a1 == a.c.a1,
b.c.b2 == True),
lazy=False)})
mapper(C, c, properties={
'a1s': relation(A, secondary=c2a1, lazy=False),
'a2s': relation(A, secondary=c2a2, lazy=False)})
assert create_session().query(C).with_labels().statement
def setup_mappers(self):
mapper(Owner, owners)
mapper(Category, categories)
mapper(Option, options, properties=dict(
owner=relation(Owner),
test=relation(Test)))
mapper(Test, tests, properties=dict(
owner=relation(Owner, backref='tests'),
category=relation(Category),
owner_option=relation(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 setup_mappers(self):
mapper(Owner, owners)
mapper(Category, categories)
mapper(Option,
options,
properties=dict(owner=relation(Owner), test=relation(Test)))
mapper(Test,
tests,
properties=dict(
owner=relation(Owner, backref='tests'),
category=relation(Category),
owner_option=relation(
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 testone(self):
"""A lazy load which has multiple join conditions.
Including two that are against the same column in the child table.
"""
class Information(object):
pass
class Relation(object):
pass
class Data(object):
pass
session = create_session()
mapper(Data, data)
mapper(
Relation,
rels,
properties={
"datas": relation(
Data,
primaryjoin=sa.and_(
rels.c.info_pk == data.c.info_pk,
data.c.timeval >= rels.c.start,
data.c.timeval <= rels.c.finish,
),
foreign_keys=[data.c.info_pk],
)
},
)
mapper(Information, infos, properties={"rels": relation(Relation)})
info = session.query(Information).get(1)
assert info
assert len(info.rels) == 2
assert len(info.rels[0].datas) == 3
def test_correlated_lazyload(self):
class User(_base.ComparableEntity):
pass
class Stuff(_base.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':relation(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))])
])
