SQL Alchemy and generating ALTER TABLE statements

I want to programatically generate ALTER TABLE statements in SQL Alchemy to add a new column to a table. The column to be added should take its definition from an existing mapped class.

So, given an SQL Alchemy Column instance, can I generate the SQL schema definition(s) I would need for ALTER TABLE ... ADD COLUMN ... and CREATE INDEX ...?

I开发者_如何学运维've played at a Python prompt and been able to see a human-readable description of the data I'm after:

>>> DBChain.__table__.c.rName
Column('rName', String(length=40, convert_unicode=False, assert_unicode=None, unicode_error=None, _warn_on_bytestring=False), table=<Chain>)

When I call engine.create_all() the debug log includes the SQL statements I'm looking to generate:

CREATE TABLE "Chain" (
        ...
        "rName" VARCHAR(40),
        ...
)
CREATE INDEX "ix_Chain_rName" ON "Chain" ("rName")

I've heard of sqlalchemy-migrate, but that seems to be built around static changes and I'm looking to dynamically generate schema-changes.

(I'm not interested in defending this design, I'm just looking for a dialect-portable way to add a column to an existing table.)

After tracing engine.create_all() with a debugger I've discovered a possible answer:

>>> engine.dialect.ddl_compiler(
...    engine.dialect,
...    DBChain.__table__.c.rName ) \
... .get_column_specification(
...    DBChain.__table__.c.rName )
'"rName" VARCHAR(40)'

The index can be created with:

sColumnElement = DBChain.__table__.c.rName
if sColumnElement.index:                                   
    sIndex = sa.schema.Index(                              
            "ix_%s_%s" % (rTableName, sColumnElement.name),
            sColumnElement,                                
            unique=sColumnElement.unique)                  
    sIndex.create(engine)