1

We are attempting to generate a SQL Migration script using Alembic (offline mode). Part of the view in the migration uses the MySQL function DATE_FORMAT, as in the example below. When the migration scripts are created, the percent characters are being doubled-up in the generated SQL.

We've been manually modifying the generated scripts to set it back to a single % character instead, but would like to get out of that practice and have our upgrades / downgrades generate SQL that can be piped into the db without manually modification.

From what I have been able to determine, running the Alembic migration in online mode with the no_parameters = True set in the execution_options allows, "...percent signs straight through without escaping, thus providing cross-compatible operation with DBAPI execution and static script generation."

(see: https://alembic.readthedocs.org/en/latest/changelog.html#change-1dff9e522d76403d9af885164fd284e9 )

Since the execution_options are set as a property of the Connection and no connection is present when running in offline mode, this does not seem to be an option for us.

Any help or advice would be most appreciated!

=======================

Alembic migration file:

def upgrade():
    my_view_sql = """
        CREATE VIEW my_view
        AS        
        SELECT DATE_FORMAT(m.my_date, '%m/%d/%Y') AS 'my_date'
        FROM    some_table as m
    """
    op.execute(my_view_sql)

======================

Generated SQL:

-- Running upgrade old_version -> new_version

CREATE VIEW my_view
        AS
        SELECT DATE_FORMAT(m.my_date, '%%m/%%d/%%Y') AS 'my_date'
        FROM    some_table as m;

UPDATE alembic_version SET version_num='new_version';
Code Enthusiastic
  • 2,827
  • 5
  • 25
  • 39
Scott Grussing
  • 11
  • 2

1 Answers1

0

inline_literal sounds like the thing you want.

fasouto
  • 4,386
  • 3
  • 30
  • 66
javex
  • 7,198
  • 7
  • 41
  • 60
  • I reviewed the linked documentation and have been unable to apply it in this context. I tried: (1) Passing the statement in to inline_literal(). Alembic spits it out incorrectly. (2) Concatenating it within the string as ...my_date,""" + op.inline_literal('%m/%d/%y') + """... Alembic produced: concat(concat(%s, '%m/%d/%Y'), %s); (3) Using .format(op.inline_literal('%m/%d/%y')), returned the CREATE statement as it originally was (with the double %%) If you could please link an example of inline_literal usage where it is being interjected into an existing string, I'd really appreciate it. – Scott Grussing Oct 01 '13 at 18:37