How to change column type from character changing to integer using sqlalchemy-migrate

Question

I am using sqlalchemy-migrate to change the type of one of the columns in a table in a Postgre SQL database. The update script I am using is:

# -*- cofing: utf-8 -*-

from sqlalchemy import MetaData, Table, Column, String, Integer
from migrate import changeset


metadata = MetaData()


def upgrade(migrate_engine):
    # ALTER TABLE courses ALTER COLUMN number SET DATA TYPE character varying;
    metadata.bind = migrate_engine
    courses = Table('courses', metadata, Column("number", Integer), extend_existing=True)
    courses.c.number.alter(type=String)


def downgrade(migrate_engine):
    # ALTER TABLE courses ALTER COLUMN number SET DATA TYPE integer;
    metadata.bind = migrate_engine
    courses = Table('courses', metadata, Column("number", String), extend_existing=True)
    courses.c.number.alter(type=Integer, cast='numeric')

Part of the update seems to work, but downgrading always fails with the following error:

sqlalchemy.exc.ProgrammingError: (ProgrammingError) column "number" cannot be cast to type integer
'\nALTER TABLE courses ALTER COLUMN number TYPE INTEGER' {}

Now, if I used plain SQL, I could use ALTER TABLE courses ALTER COLUMN number TYPE INTEGER USING number::numericto change the type of the column from character varyingto integer, but I don't know how to achieve this using sqlalchemy-migrate.

Is there a way to force sqlalchemy to include USING number::numericin a sentence ALTER? or is there another way to avoid the error I posted above?

I appreciate your help.

Answer 1

, sqlalchemy.migrate String Integer postgresql.

.

def downgrade(migrate_engine):
    # ALTER TABLE courses ALTER COLUMN number SET DATA TYPE integer;
    migrate_engine.execute('ALTER TABLE courses ALTER COLUMN number TYPE INTEGER USING number::numeric')

BTW String Integer - , . , .