如何使用sqlalchemy-migrate将列类型从字符更改为整数 [英] How to alter column type from character varying to integer using sqlalchemy-migrate

问题描述

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

I'm using sqlalchemy-migrate to alter the type of one of the columns in a table in a Postgre SQL database. The upgrade script I'm 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')

升级部分似乎有效，但降级总是失败以下错误：

The upgrade part seems to work but the downgrade 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' {}

现在，如果我使用的是普通格式SQL我可以使用 ALTER TABLE课程ALTER COLUMN数字类型整数使用number :: numeric 来将列类型从个字符变化到 integer ，但是我不知道如何使用sqlalchemy-migrate实现。

Now, if I were using plain SQL I could use ALTER TABLE courses ALTER COLUMN number TYPE INTEGER USING number::numeric to alter the column type back from character varying to integer, but I don't know how to achieve that using sqlalchemy-migrate.

一种强制sqlalchemy在 ALTER 子句中包含 USING number :: numeric 的方法？还是有其他方法可以避免我在上面发布的错误？

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

感谢您的帮助。

推荐答案

看起来像sqlalchemy.migrate不支持呈现有效查询，以防将Postgresql的字符串类型从字符串更改为整数。

Looks like sqlalchemy.migrate doesn't have support for rendering valid query in case of altering from String to Integer column types for postgresql.

在您的情况下，我会将其实现为直接查询执行并继续。

In your case I would implement it as a direct query execution and move on.

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可能由于不同的原因而失败-当列值包含一些无法转换为数字的值时。因此，我将在应用程序逻辑中添加一些额外的验证，以确保以后可以进行降级迁移。

BTW migrating from String to Integer may fail for different reasons - when column value would contain some value that cannot be converted to number. So I would add some extra validation into application logic to keep downgrade migration possible later on.

这篇关于如何使用sqlalchemy-migrate将列类型从字符更改为整数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！