Error creating table with foreign key constraint using SQLAlchemy-Migrate

Question

I'm building an app in python. I'm using sqlalchemy-migrate to track my database schema. I have a table, user_category, which has two columns: id and name. I'm trying to create a user table with a foreign key to the user_category table. My change script for creating the user table is as follows:

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()

When I run 'manage.py test' I get and error:

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

score 4 · Answer 1 · answered Jun 14 '12 at 03:28

Instead of copy-pasting the definition of user_category table it is possible to tell SQLAlchemy to auto-load the table structure from the database:

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()

Sorry for the late answer :)

A useful answer is always welcome no matter how late it is :) Thanks! — hellsgate, Jun 20 '12 at 11:42
I need define table after meta.bind assignment. After that it works. Thanks — farincz, Jan 23 '16 at 14:31

score 3 · Accepted Answer · answered Feb 13 '11 at 12:30

3

You claim your "user_category" table has name and id. Definition of "user_category" contains no columns at all :)

answered Feb 13 '11 at 12:30

iElectric

5,633
1
29
31

It turns out to be this simple. I thought that because the user_category table is defined in a previous change script then I didn't need to re-define it in the user change script. I was wrong. Thanks iElectric – hellsgate Feb 14 '11 at 09:59

Error creating table with foreign key constraint using SQLAlchemy-Migrate

2 Answers2