CODEWITHSUNDEEP
pythonsqlalchemypython-3.6
Skyler Christian
Skyler Christian

Reputation: 21

SQLalchemy couldn't find the target column?

ticker_table = Table('ticker_data', MetaData(),
    Column('id', Integer, primary_key=True),
    Column('ticker', String(100), nullable=False),
    Column('adj_slope', Float(3), nullable=False),
    Column('sector', String(100), nullable=False),
    Column('sub_sector', String(100), nullable=False)
    )
price_data = Table('price_data', MetaData(),
    Column('id', Integer, primary_key=True),
    Column('ticker', String(100), ForeignKey('ticker_data.ticker'), nullable=False),
    Column('date', Date),
    Column('price', Float(3)),
    )
price_data.create(bind=engine)

I'm getting this error

Foreign key associated with column 'price_data.ticker' could not find table 'ticker_data' with which to generate a foreign key to target column 'ticker'

I'm using MySQL Workbench with Python 3.6 and the most up to date everything else. Not sure what would be causing this error? Any help would be much appreciated. Maybe because I'm using the mysqlclient since the actual plugin isn't updated to python 3.6? I'm relatively new to all things sqlalchemy so any feedback would greatly benefit me.

Upvotes: 2

Views: 160

Answers (2)

Bahman Engheta
Bahman Engheta

Reputation: 116

Try replacing the string 'ticker_data.ticker' with the column name ticker_table.c.ticker, as in:

Column('ticker', String(100), ForeignKey(ticker_table.c.ticker), nullable=False)

Upvotes: 0

jsbueno
jsbueno

Reputation: 110726

Your problem is that you are using two fresh, unrelated MetaData instances for each of your table. That way, inside sqlalchemy data structures, the second table won't "see" the other.

The correct thing to do is to use a single metadata object for both tables, and create both at once with a call to .create_all() on the MetaData instance:

...
metadata = MetaData()
ticker_table = Table('ticker_data', metadata,
    Column('id', Integer, primary_key=True),
    Column('ticker', String(100), nullable=False),
    Column('adj_slope', Float(3), nullable=False),
    Column('sector', String(100), nullable=False),
    Column('sub_sector', String(100), nullable=False)
    )
price_data = Table('price_data', metadata,
    Column('id', Integer, primary_key=True),
    Column('ticker', String(100), ForeignKey('ticker_data.ticker'), nullable=False),
    Column('date', Date),
    Column('price', Float(3)),
    )

metadata.create_all(bind=engine)

There is a way to ensure sqlalchemy "sees" the other table, even using distinct MetaData objects: just reference the foreign key column as the Python object, instead of a string. When you create a table, it gets the c attribute which is the column collection - so you could declare your second table like this:

price_data = Table('price_data', MetaData(),
    Column('id', Integer, primary_key=True),
    Column('ticker', String(100), ForeignKey(ticker_table.c.ticker), nullable=False),
    Column('date', Date),
    Column('price', Float(3)),
    )

Upvotes: 2

Related Questions