Autogenerated postgresql.ExcludeConstraint() escaping

Question

Migrated issue, originally created by jahs ()

There appears to be an inconsistency within SQL Alchemy which autogenerated ExcludeConstraint migrations trigger, and an easy workaround. This is only seen for column names that need escaping, such as those containing upper case characters.

I'm using: alembic==0.9.7, SQLAlchemy==1.2.1

Adding an ExcludeConstraint to a Table results in a migration having a line such as:

python
postgresql.ExcludeConstraint(('"JB_NAM_JB_ID"', '='),
                             ('"JB_NAM_During"', '&&'),
                             using='gist', name='hkJB_NAM_Job_Name')

which appears to be correct following the ExcludeConstraint documentation, namely that the column names should be raw, escaped SQL strings.

However, at sqlalchemy/sql/schema.py(2668)_set_parent() we have

2665        def _set_parent(self, table):
2666            for col in self._pending_colargs:
2667                if isinstance(col, util.string_types):
2668 ->                 col = table.c[col]
2669                self.columns.add(col)

and so we get a KeyError as col is assumed not to be escaped.

Following the suggestion in the ExcludeConstraint docs, wrapping the column names in Column() rather than double quotes fixes it:

python
postgresql.ExcludeConstraint((sa.Column('JB_NAM_JB_ID'), '='),
                             (sa.Column('JB_NAM_During'), '&&'),
                             using='gist', name='hkJB_NAM_Job_Name')

This seems to be a better fix than trying to unescape in SQL Alchemy.

Many thanks, and thank you for Alembic.

该提问来源于开源项目：sqlalchemy/alembic

Answer 1

Michael Bayer () wrote:

OK....I had to guess on the detail here, but apparently when you properly use the table Column in the model:

t = sa.Table(
    't', m,
    sa.Column('XColumn', sa.String),
    sa.Column('YColumn', sa.String),
)
ExcludeConstraint(
    (t.c.XColumn, ">"),
    using="gist",
    where='"XColumn" != 2',
    name="TExclX"
)

it still generates the string:

    op.create_table('t',
    sa.Column('XColumn', sa.String(), nullable=True),
    sa.Column('YColumn', sa.String(), nullable=True),
    postgresql.ExcludeConstraint((u'"XColumn"', '>'), where=sa.text(u'"XColumn" != 2'), using='gist', name='TExclX')
    )

fine, we use column().