autogenerate should resolve literal bind parameters

[sqlalchemy-bot]

Migrated issue, originally created by Lie Ryan (@lieryan)

Suppose that you have a table like this:

my_table = Table('my_table', metadata,
  Column('status', String(8), nullable=False),
)

status_list = ('statusA', 'statusB', 'statusC')
CheckConstraint(my_table.c.status.in_(status_list),           
  name='ck_my_table_status')

alembic autogenerate currently produces a create check constraint migration that looks like this:

sa.CheckConstraint('my_table.status IN (%(status_1)s, %(status_2)s, %(status_3)s)', name='ck_my_table_status')

I would expect autogenerate to resolve the placeholders when creating the constraints and produce something like:

sa.CheckConstraint('my_table.status IN ("statusA", "statusB", "statusC")', name='ck_my_table_status')

[sqlalchemy-bot]

Changes by Lie Ryan (@lieryan):

• changed title from "Alembic produces incorrect migration" to "autogenerate should resolve literal bind parameter"

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

please try this patch

--- a/alembic/autogenerate/render.py
+++ b/alembic/autogenerate/render.py
@@ -444,7 +444,8 @@ def _render_check_constraint(constraint, autogen_context):
                             for k, v in opts)) if opts else "",
             "sqltext": str(
                 constraint.sqltext.compile(
-                    dialect=autogen_context['dialect']
+                    dialect=autogen_context['dialect'],
+                    compile_kwargs={"literal_binds": True}
                 )
             )
         }

[sqlalchemy-bot]

Changes by Lie Ryan (@lieryan):

• edited description

[sqlalchemy-bot]

Lie Ryan (@lieryan) wrote:

The patch does seem to resolve the problem.

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• set milestone to "fasttrack"

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

• Bound parameters are now resolved as "literal" values within the
  SQL expression inside of a CheckConstraint(), when rendering the SQL
  as a text string; supported for SQLAlchemy 0.8.0 and forward.
  fixes autogenerate should resolve literal bind parameters #219

→ 0fa7716

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed status to closed