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