使用SQLAlchemy-Migrate创建带外键约束的表时出错

Question

我正在用Python开发一个应用程序。我使用sqlalchemy-migrate来跟踪我的数据库结构。我有一个表叫做user_category，这个表有两列：id和name。我想创建一个用户表，并且这个用户表要有一个外键指向user_category表。为了创建用户表，我写了以下的变更脚本：

from sqlalchemy import *
from migrate import *
from migrate.changeset import *

meta = MetaData()
user_category = Table('user_category', meta)

user = Table('user', meta,
    Column('id', Integer, primary_key=True),
    Column('email', String(255)),
    Column('first_name', String(40)),
    Column('surname', String(40)),
    Column('password', String(255)),
    Column('user_category', Integer, ForeignKey("user_category.id")),
)

def upgrade(migrate_engine):
    # Upgrade operations go here. Don't create your own engine; bind migrate_engine
    # to your metadata
    meta.bind = migrate_engine
    user.create()

def downgrade(migrate_engine):
    # Operations to reverse the above upgrade go here.
    meta.bind = migrate_engine
    user.drop()

当我运行'manage.py test'时，出现了一个错误：

sqlalchemy.exc.NoReferencedColumnError: Could not create ForeignKey 'user_catego
ry.id' on table 'user': table 'user_category' has no column named 'id'

Answer 1

与其手动复制粘贴user_category表的定义，不如让SQLAlchemy自动从数据库中加载这个表的结构，这样会更方便。

from sqlalchemy import *
from migrate import *
from migrate.changeset import *

meta = MetaData()

user = Table('user', meta,
    Column('id', Integer, primary_key=True),
    Column('email', String(255)),
    Column('first_name', String(40)),
    Column('surname', String(40)),
    Column('password', String(255)),
    Column('user_category', Integer, ForeignKey("user_category.id")),
)

def upgrade(migrate_engine):
    _t = sa.Table('user_category', meta, autoload=True)
    meta.bind = migrate_engine
    user.create()

抱歉回复得晚了 :)

Answer 2

你说你的“用户类别”表里有名字和ID。但是“用户类别”的定义里根本没有任何列呢 :)