diff options
| -rw-r--r-- | CHANGES | 5 | ||||
| -rw-r--r-- | lib/sqlalchemy/databases/postgres.py | 3 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/compiler.py | 2 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/expression.py | 4 | ||||
| -rw-r--r-- | test/sql/query.py | 39 | ||||
| -rw-r--r-- | test/sql/select.py | 7 |
6 files changed, 45 insertions, 15 deletions
@@ -4,6 +4,11 @@ CHANGES 0.4.2p4 ------ +- sql + - added "ilike()" operator to column operations. + compiles to ILIKE on postgres, lower(x) LIKE lower(y) + on all others [ticket:727] + - orm - proper error message is raised when trying to access expired instance attributes with no session present diff --git a/lib/sqlalchemy/databases/postgres.py b/lib/sqlalchemy/databases/postgres.py index 19db8b6b7..bab998d69 100644 --- a/lib/sqlalchemy/databases/postgres.py +++ b/lib/sqlalchemy/databases/postgres.py @@ -625,7 +625,8 @@ class PGCompiler(compiler.DefaultCompiler): operators = compiler.DefaultCompiler.operators.copy() operators.update( { - sql_operators.mod : '%%' + sql_operators.mod : '%%', + sql_operators.ilike_op: 'ILIKE' } ) diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py index 35a12efe3..545c0a1b4 100644 --- a/lib/sqlalchemy/sql/compiler.py +++ b/lib/sqlalchemy/sql/compiler.py @@ -79,7 +79,7 @@ OPERATORS = { operators.concat_op : '||', operators.like_op : 'LIKE', operators.notlike_op : 'NOT LIKE', - operators.ilike_op : 'ILIKE', + operators.ilike_op : lambda x, y: "lower(%s) LIKE lower(%s)" % (x, y), operators.notilike_op : 'NOT ILIKE', operators.between_op : 'BETWEEN', operators.in_op : 'IN', diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py index 0bf9bea10..187b38a2c 100644 --- a/lib/sqlalchemy/sql/expression.py +++ b/lib/sqlalchemy/sql/expression.py @@ -1113,6 +1113,9 @@ class ColumnOperators(Operators): def like(self, other): return self.operate(operators.like_op, other) + def ilike(self, other): + return self.operate(operators.ilike_op, other) + def in_(self, *other): return self.operate(operators.in_op, other) @@ -1205,6 +1208,7 @@ class _CompareMixin(ColumnOperators): operators.ge : (__compare, operators.lt), operators.eq : (__compare, operators.ne), operators.like_op : (__compare, operators.notlike_op), + operators.ilike_op : (__compare, operators.notilike_op), } def operate(self, op, *other): diff --git a/test/sql/query.py b/test/sql/query.py index 2d5076b9a..d1583dc57 100644 --- a/test/sql/query.py +++ b/test/sql/query.py @@ -28,7 +28,7 @@ class QueryTest(PersistTest): def tearDownAll(self): metadata.drop_all() - def testinsert(self): + def test_insert(self): users.insert().execute(user_id = 7, user_name = 'jack') assert users.count().scalar() == 1 @@ -40,7 +40,7 @@ class QueryTest(PersistTest): ) assert users.select().execute().fetchall() == [(7, 'jack'), (8, 'ed'), (9, None)] - def testupdate(self): + def test_update(self): users.insert().execute(user_id = 7, user_name = 'jack') assert users.count().scalar() == 1 @@ -128,7 +128,7 @@ class QueryTest(PersistTest): finally: table.drop() - def testrowiteration(self): + def test_row_iteration(self): users.insert().execute(user_id = 7, user_name = 'jack') users.insert().execute(user_id = 8, user_name = 'ed') users.insert().execute(user_id = 9, user_name = 'fred') @@ -148,6 +148,23 @@ class QueryTest(PersistTest): l.append(row) self.assert_(len(l) == 2, "fetchmany(size=2) got %s rows" % len(l)) + def test_ilike(self): + users.insert().execute( + {'user_id':1, 'user_name':'one'}, + {'user_id':2, 'user_name':'TwO'}, + {'user_id':3, 'user_name':'ONE'}, + {'user_id':4, 'user_name':'OnE'}, + ) + + self.assertEquals(select([users.c.user_id]).where(users.c.user_name.ilike('one')).execute().fetchall(), [(1, ), (3, ), (4, )]) + + self.assertEquals(select([users.c.user_id]).where(users.c.user_name.ilike('TWO')).execute().fetchall(), [(2, )]) + + if testing.against('postgres'): + self.assertEquals(select([users.c.user_id]).where(users.c.user_name.like('one')).execute().fetchall(), [(1, )]) + self.assertEquals(select([users.c.user_id]).where(users.c.user_name.like('TWO')).execute().fetchall(), []) + + def test_compiled_execute(self): users.insert().execute(user_id = 7, user_name = 'jack') s = select([users], users.c.user_id==bindparam('id')).compile() @@ -229,7 +246,7 @@ class QueryTest(PersistTest): a_eq(prep(r"(\:that$other)"), "(:that$other)") a_eq(prep(r".\:that$ :other."), ".:that$ ?.") - def testdelete(self): + def test_delete(self): users.insert().execute(user_id = 7, user_name = 'jack') users.insert().execute(user_id = 8, user_name = 'fred') print repr(users.select().execute().fetchall()) @@ -238,7 +255,7 @@ class QueryTest(PersistTest): print repr(users.select().execute().fetchall()) - def testselectlimit(self): + def test_select_limit(self): users.insert().execute(user_id=1, user_name='john') users.insert().execute(user_id=2, user_name='jack') users.insert().execute(user_id=3, user_name='ed') @@ -251,7 +268,7 @@ class QueryTest(PersistTest): @testing.unsupported('mssql') @testing.fails_on('maxdb') - def testselectlimitoffset(self): + def test_select_limit_offset(self): users.insert().execute(user_id=1, user_name='john') users.insert().execute(user_id=2, user_name='jack') users.insert().execute(user_id=3, user_name='ed') @@ -769,7 +786,9 @@ class JoinTest(PersistTest): def assertRows(self, statement, expected): """Execute a statement and assert that rows returned equal expected.""" - found = exec_sorted(statement) + found = sorted([tuple(row) + for row in statement.execute().fetchall()]) + self.assertEquals(found, sorted(expected)) def test_join_x1(self): @@ -1020,12 +1039,6 @@ class OperatorTest(PersistTest): ) -def exec_sorted(statement, *args, **kw): - """Executes a statement and returns a sorted list plain tuple rows.""" - - return sorted([tuple(row) - for row in statement.execute(*args, **kw).fetchall()]) - if __name__ == "__main__": testbase.main() diff --git a/test/sql/select.py b/test/sql/select.py index 70bf704ce..b922c5c22 100644 --- a/test/sql/select.py +++ b/test/sql/select.py @@ -451,6 +451,8 @@ sq.myothertable_othername AS sq_myothertable_othername FROM (" + sqstring + ") A clause = (table1.c.myid == 12) & table1.c.myid.between(15, 20) & table1.c.myid.like('hoho') assert str(clause) == str(util.pickle.loads(util.pickle.dumps(clause))) + + def testextracomparisonoperators(self): self.assert_compile( table1.select(table1.c.name.contains('jo')), @@ -523,6 +525,11 @@ sq.myothertable_othername AS sq_myothertable_othername FROM (" + sqstring + ") A "SELECT myothertable.othername, count(myothertable.otherid) AS count_1 FROM myothertable GROUP BY myothertable.othername ORDER BY myothertable.othername" ) + def testilike(self): + stmt = table1.select(table1.c.name.ilike('%something%')) + self.assert_compile(stmt, "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE lower(mytable.name) LIKE :mytable_name_1") + self.assert_compile(stmt, "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.name ILIKE %(mytable_name_1)s", dialect=postgres.PGDialect()) + def testforupdate(self): self.assert_compile(table1.select(table1.c.myid==7, for_update=True), "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid = :mytable_myid_1 FOR UPDATE") |