diff options
author | Diana Clarke <diana.joan.clarke@gmail.com> | 2015-10-03 20:47:40 -0400 |
---|---|---|
committer | Diana Clarke <diana.joan.clarke@gmail.com> | 2015-10-03 20:47:40 -0400 |
commit | 2ae084f638a95ccd6c709528de2c214d3232ebef (patch) | |
tree | 7cd97c02b1b53fafa07aad2e27383f3f054c520a | |
parent | d84dea62de3a9c83ad539a5cf2ff4be2c0685a94 (diff) | |
download | sqlalchemy-pr/207.tar.gz |
add autoescape option to startswith, endswith, and containspr/207
- fixes #2694
-rw-r--r-- | lib/sqlalchemy/sql/operators.py | 99 | ||||
-rw-r--r-- | test/sql/test_operators.py | 125 |
2 files changed, 203 insertions, 21 deletions
diff --git a/lib/sqlalchemy/sql/operators.py b/lib/sqlalchemy/sql/operators.py index da3576466..6ba4605a6 100644 --- a/lib/sqlalchemy/sql/operators.py +++ b/lib/sqlalchemy/sql/operators.py @@ -197,7 +197,7 @@ class Operators(object): class custom_op(object): """Represent a 'custom' operator. - :class:`.custom_op` is normally instantitated when the + :class:`.custom_op` is normally instantiated when the :meth:`.ColumnOperators.op` method is used to create a custom operator callable. The class can also be used directly when programmatically constructing expressions. E.g. @@ -506,6 +506,29 @@ class ColumnOperators(Operators): In a column context, produces the clause ``LIKE '<other>%'`` + E.g.:: + + select([sometable]).where(sometable.c.column.startswith("foobar")) + + :param other: expression to be compared, with SQL wildcard + matching (``%`` and ``_``) enabled, e.g.:: + + somecolumn.startswith("foo%bar") + + :param escape: optional escape character, renders the ``ESCAPE`` + keyword allowing that escape character to be used to manually + disable SQL wildcard matching (``%`` and ``_``) in the expression, + e.g.:: + + somecolumn.startswith("foo/%bar", escape="/") + + :param autoescape: optional escape character, renders the ``ESCAPE`` + keyword and uses that escape character to auto escape the + expression, disabling all SQL wildcard matching (``%`` and ``_``), + e.g.:: + + somecolumn.startswith("foo%bar", autoescape="/") + """ return self.operate(startswith_op, other, **kwargs) @@ -514,6 +537,29 @@ class ColumnOperators(Operators): In a column context, produces the clause ``LIKE '%<other>'`` + E.g.:: + + select([sometable]).where(sometable.c.column.endswith("foobar")) + + :param other: expression to be compared, with SQL wildcard + matching (``%`` and ``_``) enabled, e.g.:: + + somecolumn.endswith("foo%bar") + + :param escape: optional escape character, renders the ``ESCAPE`` + keyword allowing that escape character to be used to manually + disable SQL wildcard matching (``%`` and ``_``) in the expression, + e.g.:: + + somecolumn.endswith("foo/%bar", escape="/") + + :param autoescape: optional escape character, renders the ``ESCAPE`` + keyword and uses that escape character to auto escape the + expression, disabling all SQL wildcard matching (``%`` and ``_``), + e.g.:: + + somecolumn.endswith("foo%bar", autoescape="/") + """ return self.operate(endswith_op, other, **kwargs) @@ -522,6 +568,29 @@ class ColumnOperators(Operators): In a column context, produces the clause ``LIKE '%<other>%'`` + E.g.:: + + select([sometable]).where(sometable.c.column.contains("foobar")) + + :param other: expression to compare, with SQL wildcard + matching (``%`` and ``_``) enabled, e.g.:: + + somecolumn.contains("foo%bar") + + :param escape: optional escape character, renders the ``ESCAPE`` + keyword allowing that escape character to be used to manually + disable SQL wildcard matching (``%`` and ``_``) in the expression, + e.g.:: + + somecolumn.contains("foo/%bar", escape="/") + + :param autoescape: optional escape character, renders the ``ESCAPE`` + keyword and uses that escape character to auto escape the + expression, disabling all SQL wildcard matching (``%`` and ``_``), + e.g.:: + + somecolumn.contains("foo%bar", autoescape="/") + """ return self.operate(contains_op, other, **kwargs) @@ -701,6 +770,10 @@ class ColumnOperators(Operators): return self.reverse_operate(truediv, other) +def _escaped(value, escape): + return value.replace('%', escape + '%').replace('_', escape + '_') + + def from_(): raise NotImplementedError() @@ -781,27 +854,39 @@ def all_op(a): return a.all_() -def startswith_op(a, b, escape=None): +def startswith_op(a, b, escape=None, autoescape=None): + if autoescape: + return a.startswith(_escaped(b, autoescape), escape=autoescape) return a.startswith(b, escape=escape) -def notstartswith_op(a, b, escape=None): +def notstartswith_op(a, b, escape=None, autoescape=None): + if autoescape: + return ~a.startswith(_escaped(b, autoescape), escape=autoescape) return ~a.startswith(b, escape=escape) -def endswith_op(a, b, escape=None): +def endswith_op(a, b, escape=None, autoescape=None): + if autoescape: + return a.endswith(_escaped(b, autoescape), escape=autoescape) return a.endswith(b, escape=escape) -def notendswith_op(a, b, escape=None): +def notendswith_op(a, b, escape=None, autoescape=None): + if autoescape: + return ~a.endswith(_escaped(b, autoescape), escape=autoescape) return ~a.endswith(b, escape=escape) -def contains_op(a, b, escape=None): +def contains_op(a, b, escape=None, autoescape=None): + if autoescape: + return a.contains(_escaped(b, autoescape), escape=autoescape) return a.contains(b, escape=escape) -def notcontains_op(a, b, escape=None): +def notcontains_op(a, b, escape=None, autoescape=None): + if autoescape: + return ~a.contains(_escaped(b, autoescape), escape=autoescape) return ~a.contains(b, escape=escape) diff --git a/test/sql/test_operators.py b/test/sql/test_operators.py index 03c0f89be..584fb12d5 100644 --- a/test/sql/test_operators.py +++ b/test/sql/test_operators.py @@ -15,7 +15,7 @@ from sqlalchemy.sql.elements import _literal_as_text from sqlalchemy.schema import Column, Table, MetaData from sqlalchemy.sql import compiler from sqlalchemy.types import TypeEngine, TypeDecorator, UserDefinedType, \ - Boolean, NullType, MatchType, Indexable, Concatenable, Array + Boolean, MatchType, Indexable, Concatenable, Array from sqlalchemy.dialects import mysql, firebird, postgresql, oracle, \ sqlite, mssql from sqlalchemy import util @@ -1995,9 +1995,16 @@ class ComposedLikeOperatorsTest(fixtures.TestBase, testing.AssertsCompiledSQL): def test_contains_escape(self): self.assert_compile( - column('x').contains('y', escape='\\'), + column('x').contains('a%b_c', escape='\\'), "x LIKE '%%' || :x_1 || '%%' ESCAPE '\\'", - checkparams={'x_1': 'y'} + checkparams={'x_1': 'a%b_c'} + ) + + def test_contains_autoescape(self): + self.assert_compile( + column('x').contains('a%b_c', autoescape='\\'), + "x LIKE '%%' || :x_1 || '%%' ESCAPE '\\'", + checkparams={'x_1': 'a\\%b\\_c'} ) def test_contains_literal(self): @@ -2023,9 +2030,16 @@ class ComposedLikeOperatorsTest(fixtures.TestBase, testing.AssertsCompiledSQL): def test_not_contains_escape(self): self.assert_compile( - ~column('x').contains('y', escape='\\'), + ~column('x').contains('a%b_c', escape='\\'), "x NOT LIKE '%%' || :x_1 || '%%' ESCAPE '\\'", - checkparams={'x_1': 'y'} + checkparams={'x_1': 'a%b_c'} + ) + + def test_not_contains_autoescape(self): + self.assert_compile( + ~column('x').contains('a%b_c', autoescape='\\'), + "x NOT LIKE '%%' || :x_1 || '%%' ESCAPE '\\'", + checkparams={'x_1': 'a\\%b\\_c'} ) def test_contains_concat(self): @@ -2060,6 +2074,62 @@ class ComposedLikeOperatorsTest(fixtures.TestBase, testing.AssertsCompiledSQL): dialect=mysql.dialect() ) + def test_like(self): + self.assert_compile( + column('x').like('y'), + "x LIKE :x_1", + checkparams={'x_1': 'y'} + ) + + def test_like_escape(self): + self.assert_compile( + column('x').like('a%b_c', escape='\\'), + "x LIKE :x_1 ESCAPE '\\'", + checkparams={'x_1': 'a%b_c'} + ) + + def test_ilike(self): + self.assert_compile( + column('x').ilike('y'), + "lower(x) LIKE lower(:x_1)", + checkparams={'x_1': 'y'} + ) + + def test_ilike_escape(self): + self.assert_compile( + column('x').ilike('a%b_c', escape='\\'), + "lower(x) LIKE lower(:x_1) ESCAPE '\\'", + checkparams={'x_1': 'a%b_c'} + ) + + def test_notlike(self): + self.assert_compile( + column('x').notlike('y'), + "x NOT LIKE :x_1", + checkparams={'x_1': 'y'} + ) + + def test_notlike_escape(self): + self.assert_compile( + column('x').notlike('a%b_c', escape='\\'), + "x NOT LIKE :x_1 ESCAPE '\\'", + checkparams={'x_1': 'a%b_c'} + ) + + def test_notilike(self): + self.assert_compile( + column('x').notilike('y'), + "lower(x) NOT LIKE lower(:x_1)", + checkparams={'x_1': 'y'} + ) + + def test_notilike_escape(self): + self.assert_compile( + column('x').notilike('a%b_c', escape='\\'), + "lower(x) NOT LIKE lower(:x_1) ESCAPE '\\'", + checkparams={'x_1': 'a%b_c'} + ) + def test_startswith(self): self.assert_compile( column('x').startswith('y'), @@ -2069,9 +2139,16 @@ class ComposedLikeOperatorsTest(fixtures.TestBase, testing.AssertsCompiledSQL): def test_startswith_escape(self): self.assert_compile( - column('x').startswith('y', escape='\\'), + column('x').startswith('a%b_c', escape='\\'), "x LIKE :x_1 || '%%' ESCAPE '\\'", - checkparams={'x_1': 'y'} + checkparams={'x_1': 'a%b_c'} + ) + + def test_startswith_autoescape(self): + self.assert_compile( + column('x').startswith('a%b_c', autoescape='\\'), + "x LIKE :x_1 || '%%' ESCAPE '\\'", + checkparams={'x_1': 'a\\%b\\_c'} ) def test_not_startswith(self): @@ -2083,9 +2160,16 @@ class ComposedLikeOperatorsTest(fixtures.TestBase, testing.AssertsCompiledSQL): def test_not_startswith_escape(self): self.assert_compile( - ~column('x').startswith('y', escape='\\'), + ~column('x').startswith('a%b_c', escape='\\'), "x NOT LIKE :x_1 || '%%' ESCAPE '\\'", - checkparams={'x_1': 'y'} + checkparams={'x_1': 'a%b_c'} + ) + + def test_not_startswith_autoescape(self): + self.assert_compile( + ~column('x').startswith('a%b_c', autoescape='\\'), + "x NOT LIKE :x_1 || '%%' ESCAPE '\\'", + checkparams={'x_1': 'a\\%b\\_c'} ) def test_startswith_literal(self): @@ -2159,9 +2243,16 @@ class ComposedLikeOperatorsTest(fixtures.TestBase, testing.AssertsCompiledSQL): def test_endswith_escape(self): self.assert_compile( - column('x').endswith('y', escape='\\'), + column('x').endswith('a%b_c', escape='\\'), "x LIKE '%%' || :x_1 ESCAPE '\\'", - checkparams={'x_1': 'y'} + checkparams={'x_1': 'a%b_c'} + ) + + def test_endswith_autoescape(self): + self.assert_compile( + column('x').endswith('a%b_c', autoescape='\\'), + "x LIKE '%%' || :x_1 ESCAPE '\\'", + checkparams={'x_1': 'a\\%b\\_c'} ) def test_not_endswith(self): @@ -2173,9 +2264,16 @@ class ComposedLikeOperatorsTest(fixtures.TestBase, testing.AssertsCompiledSQL): def test_not_endswith_escape(self): self.assert_compile( - ~column('x').endswith('y', escape='\\'), + ~column('x').endswith('a%b_c', escape='\\'), "x NOT LIKE '%%' || :x_1 ESCAPE '\\'", - checkparams={'x_1': 'y'} + checkparams={'x_1': 'a%b_c'} + ) + + def test_not_endswith_autoescape(self): + self.assert_compile( + ~column('x').endswith('a%b_c', autoescape='\\'), + "x NOT LIKE '%%' || :x_1 ESCAPE '\\'", + checkparams={'x_1': 'a\\%b\\_c'} ) def test_endswith_literal(self): @@ -2413,4 +2511,3 @@ class AnyAllTest(fixtures.TestBase, testing.AssertsCompiledSQL): "FROM tab1 WHERE tab1.data < :data_1)", checkparams={'data_1': 10, 'param_1': 5} ) - |