- changed milestone to blue sky
fix up argument escaping in .startswith() and .endswith()
now that ESCAPE support is in, the expr.startswith
and .endswith
operators can be adjusted to escape their argument. (i believe we had said the ultimate intention for these operators was to be no-brainer sugar methods mimicking the Python equivs, i.e. no wildcards.)
the twist is that the escaping will probably need to be SQL-side, so we can continue to emit sql like 'expr LIKE <stuff> + '%', where stuff can be a literal bind string or column or expression, etc.
previous SA versions haven't applied the escaping, so this is a behavior change. milestone TBD.
Comments (6)
-
repo owner -
repo owner - changed status to wontfix
startswith()
andendswith()
have anescape
argument which renderESCAPE
on the SQL side so I believe this has been in for some time now. -
I found this ticket while checking whether somebody already reported that .startswith("a_b_c") finds "a/b/c/d". So let me note that:
a) escape argument of startswith seems completely undocumented ( various parts of doc refer to http://docs.sqlalchemy.org/en/rel_0_8/core/expression_api.html#sqlalchemy.sql.operators.ColumnOperators.startswith which shows what it shows )
b) as I understand, I must write sth like qry = qry.where(tab.c.column.startswith("a#_b#_c", escape="#")) (at least this is the syntax which works for postgresql) what is not the most pleasant way
c) in general I feel that .startswith should perform some kind of escaping internally, if I wanted % and _ handling, I would use like
-
repo owner escape argument of startswith seems completely undocumented
fine
(at least this is the syntax which works for postgresql) what is not the most pleasant way
the "_" and "%" characters in a LIKE are special. They need to be escaped. The default escape character is a backslash, so there's no need to set a special escape character unless you actually need to search for backslashes. (see http://www.postgresql.org/docs/8.3/static/functions-matching.html)
in general I feel that .startswith should perform some kind of escaping internally, if I wanted % and _ handling,
startswith/endswith would hide the fact that LIKE is in use and auto-escape _ and %, could be useful, though we could never make this a default at this point. feel free to open a separate enhancement for that.
-
I got used to the fact that backslashes are risky as they are config-parameter dependant (see caution below http://www.postgresql.org/docs/8.3/static/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS ). Not sure whether/how this applies to bound params (and sqlalchemy generated SQL in general).
OK, I will open another ticket.
-
See
#2694 - Log in to comment