text() colon-escaping behaviour

[Sebastian Bank]

Using the postgres double colon shortcut for CAST(expression AS type), e.g.:

import sqlalchemy as sa
engine = sa.create_engine('postgresql://postgres@/')
engine.execute('SELECT * FROM pg_attribute WHERE attrelid = %(tab)s::regclass', tab='pg_class')

Trying this with text, I think the docs would suggest using \:s:

engine.execute(sa.text('SELECT * FROM pg_attribute WHERE attrelid = :tab\:\:regclass'), tab='pg_class')

But this raises ProgrammingError: (psycopg2.ProgrammingError) (rendered as SELECT * FROM pg_attribute WHERE attrelid = %(tab)s\::regclass).

As expected, this also raises:

engine.execute(sa.text('SELECT * FROM pg_attribute WHERE attrelid = :tab::regclass'), tab='pg_class')

(rendered as SELECT * FROM pg_attribute WHERE attrelid = :tab::regclass)

This finally works (extra space):

engine.execute(sa.text('SELECT * FROM pg_attribute WHERE attrelid = :tab ::regclass'), tab='pg_class')

But is this the intended way?

[Mike Bayer]

it's a bug. here's a patch:

diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index dbaa23a..cc9a49a 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -53,7 +53,7 @@ LEGAL_CHARACTERS = re.compile(r'^[A-Z0-9_$]+$', re.I)
 ILLEGAL_INITIAL_CHARACTERS = set([str(x) for x in range(0, 10)]).union(['$'])

 BIND_PARAMS = re.compile(r'(?<![:\w\$\x5c]):([\w\$]+)(?![:\w\$])', re.UNICODE)
-BIND_PARAMS_ESC = re.compile(r'\x5c(:[\w\$]+)(?![:\w\$])', re.UNICODE)
+BIND_PARAMS_ESC = re.compile(r'\x5c(:[\w\$]*)(?![:\w\$])', re.UNICODE)

 BIND_TEMPLATES = {
     'pyformat': "%%(%(name)s)s",

[Mike Bayer]

• Fixed bug in :func:.expression.text construct where a double-colon expression would not escape properly, e.g. some\:\:expr, as is most commonly required when rendering Postgresql-style CAST expressions. fixes #3644

→ <<cset 29dcaa2b0ae2>>

[Mike Bayer]

• Fixed bug in :func:.expression.text construct where a double-colon expression would not escape properly, e.g. some\:\:expr, as is most commonly required when rendering Postgresql-style CAST expressions. fixes #3644

(cherry picked from commit 29dcaa2b0ae2d26b36ec624be80f56e03ab9095e)

→ <<cset 498b0729b271>>

[Mike Bayer]

wow, what the crap, the compiler.py isn't in the commit

[Mike Bayer]

OK, it got inadvertently committed in c1316a299257fae8264c8038d83e415f4605fde7 for master, is not in 1.0

[Mike Bayer]

• repair the fix just cherry-picked for ref #3644 as compiler.py wasn't present in the commit for master, having been inadvertently committed on February 3. Source of this line is c1316a299257fae8264c8038d83e415f4605fde7.

→ <<cset 9a156c586ddf>>