Alembic: Get SQL output from schema comparison within script

Question

I would like to compare the current db schema with my current model. While compare_metadata gives a nice list of the upgrade operations required, I would also like to print out the associated SQL statements (similar to the offline alembic upgrade head --sql command, but without actually generating a migration script file before).

from alembic.autogenerate import compare_metadata
from alembic.migration import MigrationContext
from myapp import models
from sqlalchemy import create_engine
engine = create_engine("db_url...")
diff_list = compare_metadata(
    MigrationContext.configure(engine.connect()),
    models.meta.metadata
)

Maybe the SQL statements can somehow be generated using the produce_migrations api command which returns a MigrationScript object and is used as an input for the compare_metadata command (see http://alembic.zzzcomputing.com/en/latest/api/autogenerate.html#customizing-revision). Thx.

score 1 · Answer 1 · answered Feb 28 '19 at 22:44

I don't think it can generate the SQL statements because they can be different depending on the DB engine you are using but you can generate the SQL Alchemy statements for this:

from alembic.migration import MigrationContext
from myapp import models
from sqlalchemy import create_engine
engine = create_engine("db_url...")

mc = MigrationContext.configure(engine.connect())
diff_list = compare_metadata(mc, models.meta.metadata) # not needed for this
migrations = produce_migrations(mc, models.meta.metadata) # we need this instead
sqlalchemy_statements = render_python_code(migrations.upgrade_ops))

print sqlalchemy_statements # just to see the result

Alembic: Get SQL output from schema comparison within script

1 Answers1