如何使用sqlalchemy-migrate将列类型从字符变更为整数

Question

我正在使用sqlalchemy-migrate来修改Postgre SQL数据库中一个表的某个列的类型。我使用的升级脚本是：

# -*- 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')

升级的部分似乎运行正常，但降级时总是失败，出现以下错误：

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

现在，如果我使用普通的SQL，我可以用 ALTER TABLE courses ALTER COLUMN number TYPE INTEGER USING number::numeric 来把列的类型从 character varying 改回 integer，但我不知道怎么用sqlalchemy-migrate来做到这一点。

有没有办法强制sqlalchemy在 ALTER 语句中包含 USING number::numeric？或者有没有其他方法可以避免我上面提到的错误？

谢谢你的帮助。

Answer 1

看起来 sqlalchemy.migrate 这个工具在把 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')

顺便提一下，从字符串转换为整数可能会因为不同的原因失败，比如列中的某些值无法转换成数字。因此，我建议在应用程序的逻辑中加入一些额外的检查，以便将来如果需要降级迁移时能够顺利进行。