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_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_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 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_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 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_or_match(self): results1 = (matchtable.select().where( or_( matchtable.c.title.match("nutshells"), matchtable.c.title.match("rubies"), )).order_by(matchtable.c.id).execute().fetchall()) eq_([3, 5], [r.id for r in results1]) results2 = (matchtable.select().where( matchtable.c.title.match("nutshells | rubies")).order_by( matchtable.c.id).execute().fetchall()) eq_([3, 5], [r.id for r in results2])
def test_delete_rollback_with_fetch(self): User = self.classes.User sess = Session() john, jack, jill, jane = sess.query(User).order_by(User.id).all() sess.query(User).filter( or_(User.name == "john", User.name == "jill")).delete(synchronize_session="fetch") assert john not in sess and jill not in sess sess.rollback() assert john in sess and jill in sess
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 test_delete(self): User = self.classes.User sess = Session() john, jack, jill, jane = sess.query(User).order_by(User.id).all() sess.query(User).filter(or_(User.name == "john", User.name == "jill")).delete() assert john not in sess and jill not in sess eq_(sess.query(User).order_by(User.id).all(), [jack, jane])
def test_outer_join(self): """Query.outerjoin""" Order, User, Address = ( self.classes.Order, self.classes.User, self.classes.Address, ) session = create_session() q = (session.query(User).outerjoin("orders", "addresses").filter( sa.or_(Order.id == None, Address.id == 1))) # noqa eq_(set([User(id=7), User(id=8), User(id=10)]), set(q.all()))
def test_delete_without_session_sync(self): User = self.classes.User sess = Session() john, jack, jill, jane = sess.query(User).order_by(User.id).all() sess.query(User).filter( or_(User.name == "john", User.name == "jill")).delete(synchronize_session=False) assert john in sess and jill in sess eq_(sess.query(User).order_by(User.id).all(), [jack, jane])
def test_outer_join_count(self): """test the join and outerjoin functions on Query""" Order, User, Address = ( self.classes.Order, self.classes.User, self.classes.Address, ) session = create_session() q = (session.query(User).outerjoin("orders", "addresses").filter( sa.or_(Order.id == None, Address.id == 1))) # noqa eq_(q.count(), 4)
def test_from(self): users, Order, User, Address, orders, addresses = ( self.tables.users, self.classes.Order, self.classes.User, self.classes.Address, self.tables.orders, self.tables.addresses, ) session = create_session() sel = users.outerjoin(orders).outerjoin( addresses, orders.c.address_id == addresses.c.id) q = (session.query(User).select_from(sel).filter( sa.or_(Order.id == None, Address.id == 1))) # noqa eq_(set([User(id=7), User(id=8), User(id=10)]), set(q.all()))
def test_col_expression_base_plus_two_subs(self): sess = create_session() pa = with_polymorphic(Person, [Engineer, Manager]) eq_( sess.query( pa.name, pa.Engineer.primary_language, pa.Manager.manager_name ) .filter( or_( pa.Engineer.primary_language == "java", pa.Manager.manager_name == "dogbert", ) ) .order_by(pa.Engineer.type) .all(), [("dilbert", "java", None), ("dogbert", None, "dogbert")], )
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_exception_format_hide_parameters_nondbapi_round_trip(self): foo = Table("foo", MetaData(), Column("data", String)) with self.no_param_engine.connect() as conn: assert_raises_message( tsa.exc.StatementError, r"\(sqlalchemy_1_3.exc.InvalidRequestError\) A value is required " r"for bind parameter 'the_data_2'\n" r"\[SQL: SELECT foo.data \nFROM foo \nWHERE " r"foo.data = \? OR foo.data = \?\]\n" r"\[SQL parameters hidden due to hide_parameters=True\]", conn.execute, select([foo]).where( or_( foo.c.data == bindparam("the_data_1"), foo.c.data == bindparam("the_data_2"), ) ), {"the_data_1": "some data"}, )
def test_column_bind_labels_2(self): table1 = self.table1 s = table1.select( or_( table1.c.this_is_the_primarykey_column == 4, table1.c.this_is_the_primarykey_column == 2, ) ) self.assert_compile( s, "SELECT some_large_named_table.this_is_the_primarykey_column, " "some_large_named_table.this_is_the_data_column " "FROM some_large_named_table WHERE " "some_large_named_table.this_is_the_primarykey_column = " ":this_is_the_primarykey__1 OR " "some_large_named_table.this_is_the_primarykey_column = " ":this_is_the_primarykey__2", checkparams={ "this_is_the_primarykey__1": 4, "this_is_the_primarykey__2": 2, }, dialect=self._length_fixture(), ) self.assert_compile( s, "SELECT some_large_named_table.this_is_the_primarykey_column, " "some_large_named_table.this_is_the_data_column " "FROM some_large_named_table WHERE " "some_large_named_table.this_is_the_primarykey_column = " "%s OR " "some_large_named_table.this_is_the_primarykey_column = " "%s", checkparams={ "this_is_the_primarykey__1": 4, "this_is_the_primarykey__2": 2, }, checkpositional=(4, 2), dialect=self._length_fixture(positional=True), )
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 = create_session() q = s.query(Thing).options(sa.orm.joinedload("category")) result = q.filter( sa.and_( tests.c.owner_id == 1, sa.or_( options.c.someoption == None, options.c.someoption == False, # noqa ), )).outerjoin("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_outer_join(self): table1 = table( "mytable", column("myid", Integer), column("name", String), column("description", String), ) table2 = table( "myothertable", column("otherid", Integer), column("othername", String), ) table3 = table( "thirdtable", column("userid", Integer), column("otherstuff", String), ) query = select( [table1, table2], or_( table1.c.name == "fred", table1.c.myid == 10, table2.c.othername != "jack", text("EXISTS (select yay from foo where boo = lar)"), ), from_obj=[ outerjoin(table1, table2, table1.c.myid == table2.c.otherid) ], ) self.assert_compile( query, "SELECT mytable.myid, mytable.name, " "mytable.description, myothertable.otherid," " myothertable.othername FROM mytable, " "myothertable WHERE (mytable.name = " ":name_1 OR mytable.myid = :myid_1 OR " "myothertable.othername != :othername_1 OR " "EXISTS (select yay from foo where boo = " "lar)) AND mytable.myid = " "myothertable.otherid(+)", dialect=oracle.OracleDialect(use_ansi=False), ) query = table1.outerjoin(table2, table1.c.myid == table2.c.otherid).outerjoin( table3, table3.c.userid == table2.c.otherid) self.assert_compile( query.select(), "SELECT mytable.myid, mytable.name, " "mytable.description, myothertable.otherid," " myothertable.othername, " "thirdtable.userid, thirdtable.otherstuff " "FROM mytable LEFT OUTER JOIN myothertable " "ON mytable.myid = myothertable.otherid " "LEFT OUTER JOIN thirdtable ON " "thirdtable.userid = myothertable.otherid", ) self.assert_compile( query.select(), "SELECT mytable.myid, mytable.name, " "mytable.description, myothertable.otherid," " myothertable.othername, " "thirdtable.userid, thirdtable.otherstuff " "FROM mytable, myothertable, thirdtable " "WHERE thirdtable.userid(+) = " "myothertable.otherid AND mytable.myid = " "myothertable.otherid(+)", dialect=oracle.dialect(use_ansi=False), ) query = table1.join(table2, table1.c.myid == table2.c.otherid).join( table3, table3.c.userid == table2.c.otherid) self.assert_compile( query.select(), "SELECT mytable.myid, mytable.name, " "mytable.description, myothertable.otherid," " myothertable.othername, " "thirdtable.userid, thirdtable.otherstuff " "FROM mytable, myothertable, thirdtable " "WHERE thirdtable.userid = " "myothertable.otherid AND mytable.myid = " "myothertable.otherid", dialect=oracle.dialect(use_ansi=False), ) query = table1.join(table2, table1.c.myid == table2.c.otherid).outerjoin( table3, table3.c.userid == table2.c.otherid) self.assert_compile( query.select().order_by(table1.c.name).limit(10).offset(5), "SELECT myid, name, description, otherid, " "othername, userid, otherstuff FROM " "(SELECT myid, name, description, otherid, " "othername, userid, otherstuff, ROWNUM AS " "ora_rn FROM (SELECT mytable.myid AS myid, " "mytable.name AS name, mytable.description " "AS description, myothertable.otherid AS " "otherid, myothertable.othername AS " "othername, thirdtable.userid AS userid, " "thirdtable.otherstuff AS otherstuff FROM " "mytable, myothertable, thirdtable WHERE " "thirdtable.userid(+) = " "myothertable.otherid AND mytable.myid = " "myothertable.otherid ORDER BY mytable.name) " "WHERE ROWNUM <= :param_1 + :param_2) " "WHERE ora_rn > :param_2", checkparams={ "param_1": 10, "param_2": 5 }, dialect=oracle.dialect(use_ansi=False), ) subq = (select([table1]).select_from( table1.outerjoin(table2, table1.c.myid == table2.c.otherid)).alias()) q = select([table3]).select_from( table3.outerjoin(subq, table3.c.userid == subq.c.myid)) self.assert_compile( q, "SELECT thirdtable.userid, " "thirdtable.otherstuff FROM thirdtable " "LEFT OUTER JOIN (SELECT mytable.myid AS " "myid, mytable.name AS name, " "mytable.description AS description FROM " "mytable LEFT OUTER JOIN myothertable ON " "mytable.myid = myothertable.otherid) " "anon_1 ON thirdtable.userid = anon_1.myid", dialect=oracle.dialect(use_ansi=True), ) self.assert_compile( q, "SELECT thirdtable.userid, " "thirdtable.otherstuff FROM thirdtable, " "(SELECT mytable.myid AS myid, " "mytable.name AS name, mytable.description " "AS description FROM mytable, myothertable " "WHERE mytable.myid = myothertable.otherid(" "+)) anon_1 WHERE thirdtable.userid = " "anon_1.myid(+)", dialect=oracle.dialect(use_ansi=False), ) q = select([table1.c.name]).where(table1.c.name == "foo") self.assert_compile( q, "SELECT mytable.name FROM mytable WHERE " "mytable.name = :name_1", dialect=oracle.dialect(use_ansi=False), ) subq = (select([ table3.c.otherstuff ]).where(table3.c.otherstuff == table1.c.name).label("bar")) q = select([table1.c.name, subq]) self.assert_compile( q, "SELECT mytable.name, (SELECT " "thirdtable.otherstuff FROM thirdtable " "WHERE thirdtable.otherstuff = " "mytable.name) AS bar FROM mytable", dialect=oracle.dialect(use_ansi=False), )