mysql.INTEGER and autogenerate

[YKdvd]

I've got existing MySQL tables from a PHP codebase, any Python SQLAlchemy code slowly taking over.  Since the SQLa never had to create the tables I've been pretty loose about including or matching all the various VARCHAR lengths, indexes and whatnot for now.  I using Alembic autogenerate (and a bit of sqlacodegen) to help clean this up, and to help copy new additions into the multiple instances of the databases.  But it looks like alembic's autogenerate doesn't honor some of the dialect-specific column parameters?  So

from sqlalchemy.dialects import mysql

id = Column(mysql.INTEGER(unsigned=True), primary_key=True)

another = Column(mysql.INTEGER(unsigned=True), index=True)

seems to produce a "mysql.INTEGER(...) opcode inside a table creation without the unsigned, and no UNSIGNED in the DDL.  Is this just an autogenerate glitch?  The original tables were laid out with most integer key columns as UNSIGNED, and referencing existing ones from a new table with a foreign key fails if the referencing column isn't unsigned also (MySQL is very picky about matching types).  SQLAlchemy's CreateTable(t).compile(myEngine) does produce the "UNSIGNED".

Alembic also doesn't issue "create index" commands, but the docs warn about this.

[Michael Bayer]

On Aug 6, 2013, at 6:26 PM, YKdvd <david...@gmail.com> wrote:

> I've got existing MySQL tables from a PHP codebase, any Python SQLAlchemy code slowly taking over. Since the SQLa never had to create the tables I've been pretty loose about including or matching all the various VARCHAR lengths, indexes and whatnot for now. I using Alembic autogenerate (and a bit of sqlacodegen) to help clean this up, and to help copy new additions into the multiple instances of the databases. But it looks like alembic's autogenerate doesn't honor some of the dialect-specific column parameters? So
>
> from sqlalchemy.dialects import mysql
> id = Column(mysql.INTEGER(unsigned=True), primary_key=True)
> another = Column(mysql.INTEGER(unsigned=True), index=True)
>
> seems to produce a "mysql.INTEGER(...) opcode inside a table creation without the unsigned, and no UNSIGNED in the DDL. Is this just an autogenerate glitch?

these kinds of things are based on the __repr__() functionality of the type itself, so Alembic is involved but it would be more on the SQLAlchemy side. Alembic's autogenerate has many missing features in the area of rendering which is why you need to go in there and clean things up. types that you're using often, you can automate using the render_item callable specified to configure: https://alembic.readthedocs.org/en/latest/api.html#alembic.environment.EnvironmentContext.configure.