the autogenerate renderer should escape the quotes in the SQL comments

[garaud]

Hi,

I use recent versions of SQLAlchemy and alembic in order to insert some comments on columns and tables (cf. https://gerrit.sqlalchemy.org/#/c/sqlalchemy/alembic/+/932/).

When I add a column with a comment to a table in my SQLAlchemy schema, the autogenerate feature fails to load the Python migration script due to a single quote (in my comment). I've got a syntax error since the result of the renderer is malformed.

artist = Table(
    "artist",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("name", String, nullable=False, comment="artist name"),
    # added this column with a single quote in the comment
    Column("band", String, comment="Artist's band")
)

The _render_column in the autogenerate.render module returns something like comment='Artist's band'. There are three single quotes.

I think the renderer should escape the single quotes, e.g. here https://github.com/sqlalchemy/alembic/blob/master/alembic/autogenerate/render.py#L617

I can replace the "'%s'" by a '"%s"' or find a proper way to escape the single quote. There might be other lines in the code where the single quote in comment should be escaped.

In my case, I write the comments in French. So I've a lot of apostrophes! 🙂

I'll submit a PR. I would like to share with you the best way to fix that.

Thanks,
Damien

[zzzeek]

Ohhhh OK right I made sure SQL quoting applied in the generated DDL but we forgot to do it in the autogen python code, OK.

[sqla-tester]

Damien Garaud has proposed a fix for this issue in the master branch:

Fix single quote escaping for the SQL comments https://gerrit.sqlalchemy.org/1111

[zzzeek]

thanks!

[garaud]

you're welcome !! 😸

Thanks for this quick review!