postgresql 如何使用 sqlalchemy-migrate 编写更改列名迁移？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7659957/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to write alter column name migrations with sqlalchemy-migrate?
提问by PEZ
I'm trying to alter a column name. First attempt was with this script:
我正在尝试更改列名。第一次尝试是使用这个脚本：
meta = MetaData()
users = Table('users', meta,
Column('id', Integer, primary_key=True),
Column('name', String(50), unique=True),
Column('email', String(120), unique=True)
)
def upgrade(migrate_engine):
meta.bind = migrate_engine
users.c.id.alter(name='id')
def downgrade(migrate_engine):
meta.bind = migrate_engine
users.c.id.alter(name='user_id')
Running migrate.py teston my dev database (sqlite) works and so does upgrading and downgrading. But when deploying it to my test environment on Heroku (where PostgreSQL 8.3 is used) I get a trace when I try to upgrade. Gist is this message:
跑migrate.py test在我的开发数据库（源码）的作品也是如此升级和降级。但是当我在 Heroku 上部署它到我的测试环境（使用 PostgreSQL 8.3）时，我在尝试升级时得到了一个跟踪。要点是这条消息：
sqlalchemy.exc.ProgrammingError: (ProgrammingError) column "id" does not exist
I then tried to use users.c.user_idin the upgrade method. That fails in both environments.:
然后我尝试users.c.user_id在升级方法中使用。这在两种环境中都失败了。：
AttributeError: user_id
The workaround I'm using now is this script:
我现在使用的解决方法是这个脚本：
meta_old = MetaData()
meta_new = MetaData()
users_old = Table('users', meta_old,
Column('user_id', Integer, primary_key=True),
Column('name', String(50), unique=True),
Column('email', String(120), unique=True)
)
users_new = Table('users', meta_new,
Column('id', Integer, primary_key=True),
Column('name', String(50), unique=True),
Column('email', String(120), unique=True)
)
def upgrade(migrate_engine):
meta_old.bind = migrate_engine
users_old.c.user_id.alter(name='id')
def downgrade(migrate_engine):
meta_new.bind = migrate_engine
users_new.c.id.alter(name='user_id')
It's already recommended practice to copy-paste the model to the sqlalchemy-migrate scripts. But this extra duplications gets a bit too much for me. Anyone knows how this should be done. Assuming it's a bug, I'd like suggestions on how to DRY up the workaround some.
已经推荐的做法是将模型复制粘贴到 sqlalchemy-migrate 脚本中。但是这种额外的重复对我来说有点太多了。任何人都知道这应该怎么做。假设这是一个错误，我想就如何解决一些解决方法提出建议。
回答by PEZ
Turns out there's an even DRY:er solution to this than I had hoped for. Introspection! Like so:
事实证明，有一个比我希望的更 DRY:er 的解决方案。内省！像这样：
def upgrade(migrate_engine):
meta = MetaData(bind=migrate_engine)
users = Table('users', meta, autoload=True)
users.c.user_id.alter(name='id')
def downgrade(migrate_engine):
meta = MetaData(bind=migrate_engine)
users = Table('users', meta, autoload=True)
users.c.id.alter(name='user_id')
Works like a charm!
奇迹般有效！
回答by Sr?an Popi?
This one also works:
这个也有效：
from alembic import op
....
def upgrade(migrate_engine):
op.alter_column('users', 'user_id', new_column_name='id')
def downgrade(migrate_engine):
op.alter_column('users', 'id', new_column_name='user_id')
回答by wberry
I bet that it can't generate any SQL because your metadata references are getting mixed up. You seem to be using two different metadata objects in your Tableclasses, and that's really not good. You only need one. The metadata tracks stale-ness of objects, whether it needs to issue queries for object updates, foreign key constraints, etc. and it needs to know about all your tables and relationships.
我敢打赌它不能生成任何 SQL，因为您的元数据引用被混淆了。您似乎在您的Table类中使用了两个不同的元数据对象，这真的不好。你只需要一个。元数据跟踪对象的陈旧性，是否需要发出对象更新、外键约束等查询，并且需要了解您的所有表和关系。
Change to use a single MetaDataobject, and pass echo=Trueto your sqlalchemy.create_enginecall and it will print the SQL query that it's using to standard output. Try executing that query yourself while logged in as the same role (user) to Postgres. You may find that it's a simple permissions issue.
更改为使用单个MetaData对象，并传递echo=True给您的sqlalchemy.create_engine调用，它会将它使用的 SQL 查询打印到标准输出。尝试在以相同角色（用户）登录 Postgres 时自己执行该查询。您可能会发现这是一个简单的权限问题。
Regarding copy-pasting: I think Django has a good convention of placing Tableand declarative classes in their own module and importing them. However, because you have to pass a MetaDataobject to the Tablefactory, that complicates matters. You can use a singleton/global metadata object, or just convert to declarative.
关于复制粘贴：我认为 Django 有一个很好的约定，即Table在自己的模块中放置和声明类并导入它们。但是，因为您必须将MetaData对象传递给Table工厂，所以事情变得复杂了。您可以使用单例/全局元数据对象，或者只是转换为声明性的。
For a while I chose to implement one-argument functions that returned Tableobjects given a metadata and cached the result--in effect implementing a singleton model class. Then I decided that was silly and switched to declarative.
有一段时间我选择实现单参数函数，该函数返回Table给定元数据的对象并缓存结果——实际上实现了一个单例模型类。然后我认为这很愚蠢并切换到声明式。
