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))]) ])