On 6/30/15 6:40 PM, Jonathon Nelson wrote:
On Fri, Jun 26, 2015 at 11:49 AM, Mike Bayer <[email protected]
<mailto:[email protected]>> 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.
ah, what I meant here was visit_textclause(), in fact.
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 [email protected]
<mailto:[email protected]>.
For more options, visit https://groups.google.com/d/optout.
--
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 [email protected].
For more options, visit https://groups.google.com/d/optout.
