How can I use ExecuteSQLOp?

[Jacob Magnusson]

Hi,

I have a use-case where I need to execute a function called audit_table after a new table is created in my migrations. So I thought I would be able to use alembic.operations.ops.ExecuteSQLOp, but I can't find any code that shows me how to actually use it. By reading the API documentation I thought this would be the way:

from alembic.autogenerate import rewriter

from alembic.operations import ops

writer = rewriter.Rewriter()

@writer.rewrites(ops.CreateTableOp)

def create_table(context, revision, op):

    return [

        op,

        ops.ExecuteSQLOp(

            sa.select([sa.func.audit_table(op.table_name)])

        )

    ]

def run_migrations_online():

    ...

    context.configure(

        ...,

        process_revision_directives=writer,

But then I get the following traceback:

Traceback (most recent call last):

  File "/venv/bin/alembic", line 11, in <module>

    sys.exit(main())

  File "/venv/lib/python3.5/site-packages/alembic/config.py", line 450, in main

    CommandLine(prog=prog).main(argv=argv)

  File "/venv/lib/python3.5/site-packages/alembic/config.py", line 444, in main

    self.run_cmd(cfg, options)

  File "/venv/lib/python3.5/site-packages/alembic/config.py", line 427, in run_cmd

    **dict((k, getattr(options, k)) for k in kwarg)

  File "/venv/lib/python3.5/site-packages/alembic/command.py", line 121, in revision

    revision_context.generate_scripts()

  File "/venv/lib/python3.5/site-packages/alembic/command.py", line 120, in <listcomp>

    script for script in

  File "/venv/lib/python3.5/site-packages/alembic/autogenerate/api.py", line 430, in generate_scripts

    yield self._to_script(generated_revision)

  File "/venv/lib/python3.5/site-packages/alembic/autogenerate/api.py", line 354, in _to_script

    autogen_context, migration_script, template_args

  File "/venv/lib/python3.5/site-packages/alembic/autogenerate/render.py", line 40, in _render_python_into_templatevars

    _render_cmd_body(upgrade_ops, autogen_context))

  File "/venv/lib/python3.5/site-packages/alembic/autogenerate/render.py", line 63, in _render_cmd_body

    lines = render_op(autogen_context, op)

  File "/venv/lib/python3.5/site-packages/alembic/autogenerate/render.py", line 74, in render_op

    renderer = renderers.dispatch(op)

  File "/venv/lib/python3.5/site-packages/alembic/util/langhelpers.py", line 309, in dispatch

    raise ValueError("no dispatch function for object: %s" % obj)

ValueError: no dispatch function for object: <alembic.operations.ops.ExecuteSQLOp object at 0x10f236860>

I've also tried to pass in the SQL command as a regular string to ExecuteSQLOp, but I still get the same exception.

Could someone tell me what the correct way to use it is? Perhaps I haven't understood correctly what ExecuteSQLOp is used for.

Thanks,

Jacob

[Mike Bayer]

On 10/28/15 1:57 PM, Jacob Magnusson wrote:
> Hi,
>
> I have a use-case where I need to execute a function called

> *audit_table* after a new table is created in my migrations. So I

> thought I would be able to use alembic.operations.ops.ExecuteSQLOp

> <http://alembic.readthedocs.org/en/latest/api/operations.html#alembic.operations.ops.ExecuteSQLOp>,

> haven't understood correctly what *ExecuteSQLOp* is used for.

ah, wow, OK. Well sure, ExecuteSQLOp is used for executing SQL but it
currently does not have an "autogenerate" render directive, that is, the
directive which writes it into a new migration script, since that would
require writing out all the Python code for
"sa.select([sa.func.audit_table(op.table_name)])" which is an immensely
difficult task.

You can build yourself a render function for ExecuteSQLOp following the
instructions at
http://alembic.readthedocs.org/en/latest/api/autogenerate.html#creating-a-render-function,
where you'd have to figure out what Python code you actually want to
render. Which means you might have an easier time just making yourself
a RunAuditTableOp object instead so that the way to render in an
autogenerate script is straightforward.



>
> Thanks,
> Jacob
>
> --
> 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-alem...@googlegroups.com
> <mailto:sqlalchemy-alem...@googlegroups.com>.
> For more options, visit https://groups.google.com/d/optout.

[Jacob Magnusson]

Thanks for pointing me in the right direction @zzzeek. For me it worked really well to just add these lines to alembic.autogenerate.render:

@renderers.dispatch_for(ops.ExecuteSQLOp)

def _execute_sql(autogen_context, op):

    assert isinstance(op.sqltext, str)

    return 'op.execute({})'.format(op.sqltext)

Is this perhaps something that could be included in alembic? I noticed that I couldn't make a PR at Bitbucket, so I made one at the github mirror: https://github.com/zzzeek/alembic/pull/20

> an email to sqlalchemy-alembic+unsub...@googlegroups.com
> <mailto:sqlalchemy-alembic+unsub...@googlegroups.com>.