summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorDiana Clarke <diana.joan.clarke@gmail.com>2015-10-03 20:47:40 -0400
committerDiana Clarke <diana.joan.clarke@gmail.com>2015-10-03 20:47:40 -0400
commit2ae084f638a95ccd6c709528de2c214d3232ebef (patch)
tree7cd97c02b1b53fafa07aad2e27383f3f054c520a
parentd84dea62de3a9c83ad539a5cf2ff4be2c0685a94 (diff)
downloadsqlalchemy-pr/207.tar.gz
add autoescape option to startswith, endswith, and containspr/207
- fixes #2694
-rw-r--r--lib/sqlalchemy/sql/operators.py99
-rw-r--r--test/sql/test_operators.py125
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}
)
-