From 6bbc7dd157faf5b513852286ba656fa6723cd2d6 Mon Sep 17 00:00:00 2001 From: Ants Aasma Date: Tue, 16 Oct 2007 22:57:05 +0000 Subject: change the in_ API to accept a sequence or a selectable [ticket:750] --- test/sql/query.py | 30 ++++++++++++------------ test/sql/select.py | 69 +++++++++++++++++++++++++++++++++--------------------- 2 files changed, 57 insertions(+), 42 deletions(-) (limited to 'test/sql') diff --git a/test/sql/query.py b/test/sql/query.py index 77e1421a5..ba29d6a8f 100644 --- a/test/sql/query.py +++ b/test/sql/query.py @@ -597,46 +597,46 @@ class QueryTest(PersistTest): users.insert().execute(user_id = 8, user_name = 'fred') users.insert().execute(user_id = 9, user_name = None) - s = users.select(users.c.user_name.in_()) + s = users.select(users.c.user_name.in_([])) r = s.execute().fetchall() # No username is in empty set assert len(r) == 0 - s = users.select(not_(users.c.user_name.in_())) + s = users.select(not_(users.c.user_name.in_([]))) r = s.execute().fetchall() # All usernames with a value are outside an empty set assert len(r) == 2 - s = users.select(users.c.user_name.in_('jack','fred')) + s = users.select(users.c.user_name.in_(['jack','fred'])) r = s.execute().fetchall() assert len(r) == 2 - s = users.select(not_(users.c.user_name.in_('jack','fred'))) + s = users.select(not_(users.c.user_name.in_(['jack','fred']))) r = s.execute().fetchall() # Null values are not outside any set assert len(r) == 0 u = bindparam('search_key') - s = users.select(u.in_()) + s = users.select(u.in_([])) r = s.execute(search_key='john').fetchall() assert len(r) == 0 r = s.execute(search_key=None).fetchall() assert len(r) == 0 - s = users.select(not_(u.in_())) + s = users.select(not_(u.in_([]))) r = s.execute(search_key='john').fetchall() assert len(r) == 3 r = s.execute(search_key=None).fetchall() assert len(r) == 0 - s = users.select(users.c.user_name.in_() == True) + s = users.select(users.c.user_name.in_([]) == True) r = s.execute().fetchall() assert len(r) == 0 - s = users.select(users.c.user_name.in_() == False) + s = users.select(users.c.user_name.in_([]) == False) r = s.execute().fetchall() assert len(r) == 2 - s = users.select(users.c.user_name.in_() == None) + s = users.select(users.c.user_name.in_([]) == None) r = s.execute().fetchall() assert len(r) == 1 @@ -690,9 +690,9 @@ class CompoundTest(PersistTest): def test_union(self): (s1, s2) = ( select([t1.c.col3.label('col3'), t1.c.col4.label('col4')], - t1.c.col2.in_("t1col2r1", "t1col2r2")), + t1.c.col2.in_(["t1col2r1", "t1col2r2"])), select([t2.c.col3.label('col3'), t2.c.col4.label('col4')], - t2.c.col2.in_("t2col2r2", "t2col2r3")) + t2.c.col2.in_(["t2col2r2", "t2col2r3"])) ) u = union(s1, s2) @@ -707,9 +707,9 @@ class CompoundTest(PersistTest): def test_union_ordered(self): (s1, s2) = ( select([t1.c.col3.label('col3'), t1.c.col4.label('col4')], - t1.c.col2.in_("t1col2r1", "t1col2r2")), + t1.c.col2.in_(["t1col2r1", "t1col2r2"])), select([t2.c.col3.label('col3'), t2.c.col4.label('col4')], - t2.c.col2.in_("t2col2r2", "t2col2r3")) + t2.c.col2.in_(["t2col2r2", "t2col2r3"])) ) u = union(s1, s2, order_by=['col3', 'col4']) @@ -720,9 +720,9 @@ class CompoundTest(PersistTest): def test_union_ordered_alias(self): (s1, s2) = ( select([t1.c.col3.label('col3'), t1.c.col4.label('col4')], - t1.c.col2.in_("t1col2r1", "t1col2r2")), + t1.c.col2.in_(["t1col2r1", "t1col2r2"])), select([t2.c.col3.label('col3'), t2.c.col4.label('col4')], - t2.c.col2.in_("t2col2r2", "t2col2r3")) + t2.c.col2.in_(["t2col2r2", "t2col2r3"])) ) u = union(s1, s2, order_by=['col3', 'col4']) diff --git a/test/sql/select.py b/test/sql/select.py index 8096b0c97..5c8b570d7 100644 --- a/test/sql/select.py +++ b/test/sql/select.py @@ -946,58 +946,61 @@ EXISTS (select yay from foo where boo = lar)", assert [str(c) for c in s.c] == ["id", "hoho"] def testin(self): - self.assert_compile(select([table1], table1.c.myid.in_('a')), - "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid = :mytable_myid") + self.assert_compile(select([table1], table1.c.myid.in_(['a'])), + "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:mytable_myid)") - self.assert_compile(select([table1], ~table1.c.myid.in_('a')), - "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid != :mytable_myid") + self.assert_compile(select([table1], ~table1.c.myid.in_(['a'])), + "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid NOT IN (:mytable_myid)") - self.assert_compile(select([table1], table1.c.myid.in_('a', 'b')), + self.assert_compile(select([table1], table1.c.myid.in_(['a', 'b'])), "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:mytable_myid, :mytable_myid_1)") - self.assert_compile(select([table1], table1.c.myid.in_(literal('a'))), - "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid = :literal") + self.assert_compile(select([table1], table1.c.myid.in_(iter(['a', 'b']))), + "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:mytable_myid, :mytable_myid_1)") + + self.assert_compile(select([table1], table1.c.myid.in_([literal('a')])), + "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:literal)") - self.assert_compile(select([table1], table1.c.myid.in_(literal('a'), 'b')), + self.assert_compile(select([table1], table1.c.myid.in_([literal('a'), 'b'])), "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:literal, :mytable_myid)") - self.assert_compile(select([table1], table1.c.myid.in_(literal('a'), literal('b'))), + self.assert_compile(select([table1], table1.c.myid.in_([literal('a'), literal('b')])), "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:literal, :literal_1)") - self.assert_compile(select([table1], table1.c.myid.in_('a', literal('b'))), + self.assert_compile(select([table1], table1.c.myid.in_(['a', literal('b')])), "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:mytable_myid, :literal)") - self.assert_compile(select([table1], table1.c.myid.in_(literal(1) + 'a')), - "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid = :literal + :literal_1") + self.assert_compile(select([table1], table1.c.myid.in_([literal(1) + 'a'])), + "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:literal + :literal_1)") - self.assert_compile(select([table1], table1.c.myid.in_(literal('a') +'a', 'b')), + self.assert_compile(select([table1], table1.c.myid.in_([literal('a') +'a', 'b'])), "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:literal || :literal_1, :mytable_myid)") - self.assert_compile(select([table1], table1.c.myid.in_(literal('a') + literal('a'), literal('b'))), + self.assert_compile(select([table1], table1.c.myid.in_([literal('a') + literal('a'), literal('b')])), "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:literal || :literal_1, :literal_2)") - self.assert_compile(select([table1], table1.c.myid.in_(1, literal(3) + 4)), + self.assert_compile(select([table1], table1.c.myid.in_([1, literal(3) + 4])), "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:mytable_myid, :literal + :literal_1)") - self.assert_compile(select([table1], table1.c.myid.in_(literal('a') < 'b')), - "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid = (:literal < :literal_1)") + self.assert_compile(select([table1], table1.c.myid.in_([literal('a') < 'b'])), + "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:literal < :literal_1)") - self.assert_compile(select([table1], table1.c.myid.in_(table1.c.myid)), - "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid = mytable.myid") + self.assert_compile(select([table1], table1.c.myid.in_([table1.c.myid])), + "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (mytable.myid)") - self.assert_compile(select([table1], table1.c.myid.in_('a', table1.c.myid)), + self.assert_compile(select([table1], table1.c.myid.in_(['a', table1.c.myid])), "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:mytable_myid, mytable.myid)") - self.assert_compile(select([table1], table1.c.myid.in_(literal('a'), table1.c.myid)), + self.assert_compile(select([table1], table1.c.myid.in_([literal('a'), table1.c.myid])), "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:literal, mytable.myid)") - self.assert_compile(select([table1], table1.c.myid.in_(literal('a'), table1.c.myid +'a')), + self.assert_compile(select([table1], table1.c.myid.in_([literal('a'), table1.c.myid +'a'])), "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:literal, mytable.myid + :mytable_myid)") - self.assert_compile(select([table1], table1.c.myid.in_(literal(1), 'a' + table1.c.myid)), + self.assert_compile(select([table1], table1.c.myid.in_([literal(1), 'a' + table1.c.myid])), "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:literal, :mytable_myid + mytable.myid)") - self.assert_compile(select([table1], table1.c.myid.in_(1, 2, 3)), + self.assert_compile(select([table1], table1.c.myid.in_([1, 2, 3])), "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:mytable_myid, :mytable_myid_1, :mytable_myid_2)") self.assert_compile(select([table1], table1.c.myid.in_(select([table2.c.otherid]))), @@ -1028,9 +1031,21 @@ UNION SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE ) # test empty in clause - self.assert_compile(select([table1], table1.c.myid.in_()), + self.assert_compile(select([table1], table1.c.myid.in_([])), "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE (CASE WHEN (mytable.myid IS NULL) THEN NULL ELSE 0 END = 1)") + def test_in_deprecated_api(self): + self.assert_compile(select([table1], table1.c.myid.in_('abc')), + "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:mytable_myid)") + + self.assert_compile(select([table1], table1.c.myid.in_(1)), + "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:mytable_myid)") + + self.assert_compile(select([table1], table1.c.myid.in_(1,2)), + "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:mytable_myid, :mytable_myid_1)") + + self.assert_compile(select([table1], table1.c.myid.in_()), + "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE (CASE WHEN (mytable.myid IS NULL) THEN NULL ELSE 0 END = 1)") def testcast(self): tbl = table('casttest', @@ -1081,9 +1096,9 @@ UNION SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE "SELECT op.field FROM op WHERE (op.field + :op_field) * :literal") self.assert_compile(table.select((table.c.field * 5) + 6), "SELECT op.field FROM op WHERE op.field * :op_field + :literal") - self.assert_compile(table.select(5 + table.c.field.in_(5,6)), + self.assert_compile(table.select(5 + table.c.field.in_([5,6])), "SELECT op.field FROM op WHERE :literal + (op.field IN (:op_field, :op_field_1))") - self.assert_compile(table.select((5 + table.c.field).in_(5,6)), + self.assert_compile(table.select((5 + table.c.field).in_([5,6])), "SELECT op.field FROM op WHERE :op_field + op.field IN (:literal, :literal_1)") self.assert_compile(table.select(not_(and_(table.c.field == 5, table.c.field == 7))), "SELECT op.field FROM op WHERE NOT (op.field = :op_field AND op.field = :op_field_1)") -- cgit v1.2.1