Configuring Alembic in SQL-only mode

[zsol...@gmail.com]

Hi,

I'm using SQLAlchemy 1.3 with PostgreSQL. I'm trying to use alembic in SQL-only mode.

I'm using a custom sql-diff script which takes the pg_dump's --schema output and compares it with a clean db setup's one and tells me exactly what changes do I need to make. This way I catch 100% of the db differences, something what no automated tool was able to do in my testing.

My question is how should I use Alembic for this scenario? I'd like to input SQL queries only, no Python at all.

Here is my concept which works but I'm not sure if it's the right way:

env.py:

def run_migrations_online():

    engine = engine_from_config(settings, prefix='sqlalchemy.')

    connection = engine.connect()

    context.configure(connection=connection)

    context.run_migrations()

    connection.close()

migration script:

commands = [

    'CREATE INDEX...',

]

def upgrade():

    for command in commands:

        try:

            op.execute(sa.text(command))

        except Exception as e:

            print(e)

def downgrade():

    pass

It's important that a failing query should never terminate a script, because I might apply some of them (like CREATE INDEX) to production servers in advance. I was also able to make this work by using "autocommit_block()" but this approach seems simpler to me. I'm surprised on the lack of rollback() in the except part though, but still it seems to work, the failed commands are not blocking any other.

Is this good like this?

Regards,
Zsolt

[Mike Bayer]

it should be fine though I'm surprised PostgreSQL isn't forcing you to ROLLBACK the transaction when one of those operations fails.    if it isn't then should be fine for now.

I'd probably make a function that does the command execution that you import from somewhere else though, that way your "run_command()" feature isn't hardcoded in every migration file and you can switch between the two approaches easily.

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/c8998719-7ea2-4ce9-bfb0-ef41128010c9n%40googlegroups.com.