def test_collection_move_commitfirst(self): User, Address = self.classes.User, self.classes.Address sess = fixture_session() a1 = Address(email_address="address1") u1 = User(name="jack", address=a1) u2 = User(name="ed") sess.add_all([u1, u2]) sess.commit() # everything is expired # load u1.address u1.address # reassign u2.address = a1 assert u2.address is a1 # the commit cancels out u1.addresses # being loaded, on next access its fine. sess.commit() assert u1.address is None assert u2.address is a1
def test_scalar_move_commitfirst(self): User, Address = self.classes.User, self.classes.Address sess = fixture_session() u1 = User(name="jack") u2 = User(name="ed") a1 = Address(email_address="a1") a1.user = u1 sess.add_all([u1, u2, a1]) sess.commit() # u1.addresses is loaded u1.addresses # direct set - the fetching of the # "old" u1 here allows the backref # to remove it from the addresses collection a1.user = u2 sess.commit() assert a1 not in u1.addresses assert a1 in u2.addresses
def test_scalar_move_preloaded(self): User, Address = self.classes.User, self.classes.Address sess = fixture_session() a1 = Address(email_address="address1") a2 = Address(email_address="address1") u1 = User(name="jack", address=a1) sess.add_all([u1, a1, a2]) sess.commit() # everything is expired # load a1.user a1.user # reassign a2.user = u1 # backref fires assert u1.address is a2 # stays on both sides assert a1.user is u1 assert a2.user is u1
def test_plain_load_passive(self): """test that many-to-one set doesn't load the old value.""" User, Address = self.classes.User, self.classes.Address sess = fixture_session() u1 = User(name="jack") u2 = User(name="ed") a1 = Address(email_address="a1") a1.user = u1 sess.add_all([u1, u2, a1]) sess.commit() # in this case, a lazyload would # ordinarily occur except for the # PASSIVE_NO_FETCH flag. def go(): a1.user = u2 self.assert_sql_count(testing.db, go, 0) assert a1 not in u1.addresses assert a1 in u2.addresses
def test_only_loads_relations_on_unpopulated_models(self): User = self.classes.User Address = self.classes.Address session = fixture_session() users = session.query(User).order_by(self.tables.users.c.id.asc()).all() address = session.query(Address).filter(self.tables.addresses.c.id == 1).first() # pre-load the address for the first user attributes.set_committed_value(users[0], "addresses", [address]) self.queries = [] # make sure no relations are loaded for user in users[1:]: model_dict = attributes.instance_dict(user) assert "addresses" not in model_dict # trigger a lazy load users[1].addresses # only 1 query should have been generated to load all the child relationships assert len(self.queries) == 1 unpopulated_user_ids = [user.id for user in users[1:]] assert self.queries[0]["parameters"] == tuple(unpopulated_user_ids)
def test_bind_arg(self): sess = fixture_session() assert_raises_message( sa.exc.ArgumentError, "Not an acceptable bind target: foobar", sess.bind_mapper, "foobar", testing.db, ) self.mapper_registry.map_imperatively( self.classes.User, self.tables.users ) u_object = self.classes.User() assert_raises_message( sa.exc.ArgumentError, "Not an acceptable bind target: User()", sess.bind_mapper, u_object, testing.db, )
def test_many_to_one(self): users, Address, addresses, User = ( self.tables.users, self.classes.Address, self.tables.addresses, self.classes.User, ) mapper( Address, addresses, properties=dict( user=relationship(mapper(User, users), lazy="select")), ) sess = fixture_session() q = sess.query(Address) a = q.filter(addresses.c.id == 1).one() assert a.user is not None u1 = sess.query(User).get(7) assert a.user is u1
def test_eager_terminate(self): """Eager query generation does not include the same mapper's table twice. Or, that bi-directional eager loads don't include each other in eager query generation. """ Middle, Right, Left = ( self.classes.Middle, self.classes.Right, self.classes.Left, ) p = Middle("m1") p.left.append(Left("l1")) p.right.append(Right("r1")) session = fixture_session() session.add(p) session.flush() session.expunge_all() session.query(Left).filter_by(data="l1").one()
def test_collection_set(self): addresses = self.tables.addresses User, Address = self._user_address_fixture( addresses_args={"order_by": addresses.c.email_address} ) sess = fixture_session( autoflush=True, ) u1 = User(name="jack") a1 = Address(email_address="a1") a2 = Address(email_address="a2") a3 = Address(email_address="a3") a4 = Address(email_address="a4") sess.add(u1) u1.addresses = [a1, a3] eq_(list(u1.addresses), [a1, a3]) u1.addresses = [a1, a2, a4] eq_(list(u1.addresses), [a1, a2, a4]) u1.addresses = [a2, a3] eq_(list(u1.addresses), [a2, a3]) u1.addresses = [] eq_(list(u1.addresses), [])
def test_labeling_for_unnamed_legacy(self, _unnamed_expr_fixture): A = _unnamed_expr_fixture sess = fixture_session() stmt = sess.query(A.id, A.name) self.assert_compile( stmt, "SELECT a.id AS a_id, a.firstname || " ":firstname_1 || a.lastname AS name FROM a", ) # for the subquery, we lose the "ORM-ness" from the subquery # so we have to carry it over using _proxy_key eq_(stmt.subquery().c.keys(), ["id", "name"]) self.assert_compile( sess.query(stmt.subquery()), "SELECT anon_1.id AS anon_1_id, anon_1.name AS anon_1_name " "FROM (SELECT a.id AS id, a.firstname || :firstname_1 || " "a.lastname AS name FROM a) AS anon_1", )
def test_query_two(self): BankAccount, Amount = self.BankAccount, self.Amount session = fixture_session() # alternatively we can do the calc on the DB side. query = (session.query(BankAccount).filter( BankAccount.balance.as_currency("cad") > Amount(9999, "cad") ).filter( BankAccount.balance.as_currency("cad") < Amount(10001, "cad"))) self.assert_compile( query, "SELECT bank_account.balance AS bank_account_balance, " "bank_account.id AS bank_account_id " "FROM bank_account " "WHERE :balance_1 * bank_account.balance > :param_1 " "AND :balance_2 * bank_account.balance < :param_2", checkparams={ "balance_1": Decimal("1.01152"), "balance_2": Decimal("1.01152"), "param_1": Decimal("9999"), "param_2": Decimal("10001"), }, )
def test_multi_bundle(self): Data = self.classes.Data Other = self.classes.Other d1 = aliased(Data) b1 = Bundle("b1", d1.d1, d1.d2) b2 = Bundle("b2", Data.d1, Other.o1) sess = fixture_session() q = (sess.query(b1, b2).join(Data.others).join( d1, d1.id == Data.id).filter(b1.c.d1 == "d3d1")) eq_( q.all(), [ (("d3d1", "d3d2"), ("d3d1", "d3o0")), (("d3d1", "d3d2"), ("d3d1", "d3o1")), (("d3d1", "d3d2"), ("d3d1", "d3o2")), (("d3d1", "d3d2"), ("d3d1", "d3o3")), (("d3d1", "d3d2"), ("d3d1", "d3o4")), ], )
def test_remove_orphans(self): addresses = self.tables.addresses User, Address = self._user_address_fixture( addresses_args={ "order_by": addresses.c.id, "backref": "user", "cascade": "all, delete-orphan", }) sess = fixture_session(autoflush=True, ) u = User(name="ed") u.addresses.extend( [Address(email_address=letter) for letter in "abcdef"]) sess.add(u) for a in u.addresses.filter(Address.email_address.in_(["c", "e", "f"])): u.addresses.remove(a) eq_( set(ad for ad, in sess.query(Address.email_address)), set(["a", "b", "d"]), )
def test_query_class_custom_method(self): class MyClass(Query): def my_filter(self, arg): return self.filter(Address.email_address == arg) User, Address = self._user_address_fixture(addresses_args=dict( query_class=MyClass)) sess = fixture_session() q = sess.query(User) u = q.filter(User.id == 7).first() assert isinstance(u.addresses, MyClass) self.assert_compile( u.addresses.my_filter("x").statement, "SELECT addresses.id, addresses.user_id, addresses.email_address " "FROM " "addresses WHERE :param_1 = addresses.user_id AND " "addresses.email_address = :email_address_1", use_default_dialect=True, )
def test_needs_parent(self): """test the error raised when parent object is not bound.""" users, Address, addresses, User = ( self.tables.users, self.classes.Address, self.tables.addresses, self.classes.User, ) mapper( User, users, properties={ "addresses": relationship(mapper(Address, addresses), lazy="select") }, ) sess = fixture_session() q = sess.query(User) u = q.filter(users.c.id == 7).first() sess.expunge(u) assert_raises(orm_exc.DetachedInstanceError, getattr, u, "addresses")
def test_not_none(self): Edge = self.classes.Edge # current contract. the composite is None # when hasn't been populated etc. on a # pending/transient object. e1 = Edge() assert e1.end is None sess = fixture_session() sess.add(e1) # however, once it's persistent, the code as of 0.7.3 # would unconditionally populate it, even though it's # all None. I think this usage contract is inconsistent, # and it would be better that the composite is just # created unconditionally in all cases. # but as we are just trying to fix [ticket:2308] and # [ticket:2309] without changing behavior we maintain # that only "persistent" gets the composite with the # Nones sess.flush() assert e1.end is not None
def test_custom_comparator_factory(self): self._fixture(True) Edge, Point = (self.classes.Edge, self.classes.Point) edge_1, edge_2 = ( Edge(Point(0, 0), Point(3, 5)), Edge(Point(0, 1), Point(3, 5)), ) sess = fixture_session() sess.add_all([edge_1, edge_2]) sess.commit() near_edges = (sess.query(Edge).filter(Edge.start.near(Point(1, 1), 1)).all()) assert edge_1 not in near_edges assert edge_2 in near_edges near_edges = (sess.query(Edge).filter(Edge.start.near(Point(0, 1), 1)).all()) assert edge_1 in near_edges and edge_2 in near_edges
def test_dslish(self): """test the same as withjoinedload except using generative""" Thing, tests, options = ( self.classes.Thing, self.tables.tests, self.tables.options, ) s = fixture_session() q = s.query(Thing).options(sa.orm.joinedload(Thing.category)) result = q.filter( sa.and_( tests.c.owner_id == 1, sa.or_( options.c.someoption == None, options.c.someoption == False, # noqa ), ) ).outerjoin(Thing.owner_option) result_str = ["%d %s" % (t.id, t.category.name) for t in result] eq_(result_str, ["1 Some Category", "3 Some Category"])
def test_join_explicit_wpoly_full_alias(self): DataContainer, Job, SubJob = ( self.classes.DataContainer, self.classes.Job, self.classes.SubJob, ) Job_P = with_polymorphic(Job, SubJob, aliased=True) s = fixture_session() q = s.query(DataContainer).join(Job_P, DataContainer.jobs) self.assert_compile( q, "SELECT data_container.id AS data_container_id, " "data_container.name AS data_container_name " "FROM data_container JOIN " "(SELECT job.id AS job_id, job.type AS job_type, " "job.widget_id AS job_widget_id, " "job.container_id AS job_container_id, " "subjob.id AS subjob_id, subjob.attr AS subjob_attr " "FROM job LEFT OUTER JOIN subjob ON job.id = subjob.id) " "AS anon_1 ON data_container.id = anon_1.job_container_id", )
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 = fixture_session() q = s.query(Thing).options(sa.orm.joinedload(Thing.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_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 = fixture_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_evaluate_non_hybrid_attr(self): # this is a control case Person = self.classes.Person s = fixture_session() jill = s.get(Person, 3) s.query(Person).update( {Person.first_name: "moonbeam"}, synchronize_session="evaluate" ) eq_(jill.first_name, "moonbeam") eq_( s.scalar(select(Person.first_name).where(Person.id == 3)), "moonbeam", ) s.query(Person).update( {Person.first_name: "sunshine"}, synchronize_session="evaluate" ) eq_(jill.first_name, "sunshine") eq_( s.scalar(select(Person.first_name).where(Person.id == 3)), "sunshine", )
def test_bulk_save_return_defaults(self): (User,) = self.classes("User") s = fixture_session() objects = [User(name="u1"), User(name="u2"), User(name="u3")] assert "id" not in objects[0].__dict__ with self.sql_execution_asserter() as asserter: s.bulk_save_objects(objects, return_defaults=True) asserter.assert_( Conditional( testing.db.dialect.insert_executemany_returning, [ CompiledSQL( "INSERT INTO users (name) VALUES (:name)", [{"name": "u1"}, {"name": "u2"}, {"name": "u3"}], ), ], [ CompiledSQL( "INSERT INTO users (name) VALUES (:name)", [{"name": "u1"}], ), CompiledSQL( "INSERT INTO users (name) VALUES (:name)", [{"name": "u2"}], ), CompiledSQL( "INSERT INTO users (name) VALUES (:name)", [{"name": "u3"}], ), ], ) ) eq_(objects[0].__dict__["id"], 1)
def test_bulk_insert(self): (User,) = self.classes("User") s = fixture_session() with self.sql_execution_asserter() as asserter: s.bulk_insert_mappings( User, [ {"id": 1, "name": "u1new"}, {"id": 2, "name": "u2"}, {"id": 3, "name": "u3new"}, ], ) asserter.assert_( CompiledSQL( "INSERT INTO users (id, name) VALUES (:id, :name)", [ {"id": 1, "name": "u1new"}, {"id": 2, "name": "u2"}, {"id": 3, "name": "u3new"}, ], ) )
def test_many_to_many(self): keywords, items, item_keywords, Keyword, Item = ( self.tables.keywords, self.tables.items, self.tables.item_keywords, self.classes.Keyword, self.classes.Item, ) mapper(Keyword, keywords) mapper( Item, items, properties=dict(keywords=relationship( Keyword, secondary=item_keywords, lazy="select")), ) q = fixture_session().query(Item) assert self.static.item_keyword_result == q.all() eq_( self.static.item_keyword_result[0:2], q.join("keywords").filter(keywords.c.name == "red").all(), )
def test_many_to_one_binds(self): Address, addresses, users, User = ( self.classes.Address, self.tables.addresses, self.tables.users, self.classes.User, ) mapper( Address, addresses, primary_key=[addresses.c.user_id, addresses.c.email_address], ) mapper( User, users, properties=dict(address=relationship( Address, uselist=False, primaryjoin=sa.and_( users.c.id == addresses.c.user_id, addresses.c.email_address == "*****@*****.**", ), )), ) q = fixture_session().query(User) eq_( [ User(id=7, address=None), User(id=8, address=Address(id=3)), User(id=9, address=None), User(id=10, address=None), ], list(q), )
def test_collection_move_commitfirst(self): User, Address = self.classes.User, self.classes.Address sess = fixture_session() a1 = Address(email_address="address1") u1 = User(name="jack", addresses=[a1]) u2 = User(name="ed") sess.add_all([u1, u2]) sess.commit() # everything is expired # load u1.addresses collection u1.addresses u2.addresses.append(a1) # backref fires assert a1.user is u2 # everything expires, no changes in # u1.addresses, so all is fine sess.commit() assert a1 not in u1.addresses assert a1 in u2.addresses
def test_any_walias(self): ( DataContainer, Job, ) = (self.classes.DataContainer, self.classes.Job) Job_A = aliased(Job) s = fixture_session() q = (s.query(Job).join(DataContainer.jobs).filter( DataContainer.jobs.of_type(Job_A).any( and_(Job_A.id < Job.id, Job_A.type == "fred")))) self.assert_compile( q, "SELECT job.id AS job_id, job.type AS job_type, " "job.widget_id AS job_widget_id, " "job.container_id AS job_container_id " "FROM data_container JOIN job " "ON data_container.id = job.container_id " "WHERE EXISTS (SELECT 1 " "FROM job AS job_1 " "WHERE data_container.id = job_1.container_id " "AND job_1.id < job.id AND job_1.type = :type_1)", )
def test_scalar_move_preloaded(self): User, Address = self.classes.User, self.classes.Address sess = fixture_session() u1 = User(name="jack") u2 = User(name="ed") a1 = Address(email_address="a1") a1.user = u1 sess.add_all([u1, u2, a1]) sess.commit() # u1.addresses is loaded u1.addresses # direct set - the "old" is "fetched", # but only from the local session - not the # database, due to the PASSIVE_NO_FETCH flag. # this is a more fine grained behavior introduced # in 0.6 a1.user = u2 assert a1 not in u1.addresses assert a1 in u2.addresses
def test_orderby_secondary(self): """tests that a regular mapper select on a single table can order by a relationship to a second table""" Address, addresses, users, User = ( self.classes.Address, self.tables.addresses, self.tables.users, self.classes.User, ) mapper(Address, addresses) mapper( User, users, properties=dict(addresses=relationship(Address, lazy="select")), ) q = fixture_session().query(User) result = ( q.filter(users.c.id == addresses.c.user_id) .order_by(addresses.c.email_address) .all() ) assert [ 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)]), User(id=7, addresses=[Address(id=1)]), ] == result