On Fri, Jun 26, 2015 at 11:49 AM, Mike Bayer <mike...@zzzcomputing.com>
wrote:
>
>
> On 6/26/15 12:23 PM, Jonathon Nelson wrote:
>
> Greetings!
>
> I am trying to use alembic to drop and re-create a PL/pgSQL function, and
> the function has embedded percentile (for use in RAISE but also present in
> comments, etc...).
>
> When rendering the upgrade as an 'offline' sql upgrade script, the
> percentiles end up escaped (with percentile):
>
> Thus:
>
> RAISE 'Param foo (%) has some badness to it.', foo;
>
> turns into:
>
> RAISE 'Param foo (%%) has some badness to it.', foo;
>
> I'm creating the function like this:
>
> op.execute( sa.text( sql_function_body ) )
>
> What am I doing wrong?
>
>
> the compiler for the Psycopg2 backend doubles up percent signs because
> they aren't otherwise accepted by the DBAPI.
>
> you can have your offline SQL use the generic Postgresql backend instead
> which won't do this. But it requires a monkeypatch:
>
> Where your env.py says something like:
>
> context.configure(
> url=url, target_metadata=target_metadata, literal_binds=True)
>
>
> for the url, do this:
>
> from sqlalchemy.engine import url
> from sqlalchemy.dialects.postgresql import PGDialect
> u = url.make_url("postgresql://")
> u.get_dialect = PGDialect
>
> context.configure(
> url=u, target_metadata=target_metadata, literal_binds=True)
>
>
> Alternatively you can make a compile rule on text() that reverses the
> escaping:
>
> from sqlalchemy.ext.compiler import compiles
> from sqlalchemy.sql.expression import TextClause
>
> @compiles(TextClause, "postgresql")
> def _reverse_escaping(element, compiler, **kw):
> text = compiler.process_text(element, **kw)
> text = text.replace("%%", "%")
> return text
>
>
I had to make some modifications to the first approach to make it work:
...
from sqlalchemy.engine import url
from sqlalchemy.dialects.postgresql.base import PGDialect
my_url = config.get_main_option("sqlalchemy.url")
u = url.make_url(my_url)
u.get_dialect = lambda: PGDialect
context.configure(
url=u, target_metadata=target_metadata, literal_binds=True)
with context.begin_transaction():
context.run_migrations()
...
For completeness, I also tried the patch to text-clause compiling, but I
couldn't get it to work:
did you mean 'post_process_text' in place of 'process_text'? If so, then
post_process_text doesn't take any keyword args. The other thing is that
post_process_text returns an instance of TextClause and it's at this point
I get lost as to what I should have done.
Still, the first approach is working just great!
--
Jon Nelson
Dyn / Senior Software Engineer
p. +1 (603) 263-8029
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
