Flask-Migrate sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column
You are letting Flask-SQLAlchemy choose the names for your tables. If I remember correctly, for a class called ListType
the table name will be list_type
(or something similar), not the listtype
that you specified in your foreign key.
My recommendation is that you specify your own table names using __tablename__
, that way they are explicit in the code and not magically determined for you. For example:
class Job(db.Model):
__tablename__ = 'jobs'
id = db.Column(db.Integer, primary_key=True)
list_type_id = db.Column(db.Integer, db.ForeignKey('list_types.id'),
nullable=False)
# ...
class ListType(db.Model):
__tablename__ = 'list_types'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(80), unique=True, nullable=False)
# ...
Mark Richman
https://markrichman.com Mark offers a unique combination of deep technical and business expertise, and a strong track record of success. Mark has built, grown, and driven software engineering teams for organizations of all sizes. Mark has developed courses on a wide range of AWS topics for Linux Academy and Pluralsight. Mark is the coauthor of the 2001 book Professional XML Web Services. He’s a former contributor to XML Journal and Web Services Journal. Mark has been routinely quoted and mentioned in publications like the Palm Beach Post and more. Mark frequently mentors executive leadership in cloud technology concepts and techniques. He is an avid writer and speaker, delivering engaging seminars and webinars on a range of topics. Mark has held key roles in the IT industry, spanning development, project management, product management, and marketing. Mark holds a BS in Computer Science and an MBA with a specialization in Technology Management. Organizations that work with Mark can expect dramatic results such as: Successful migrations of on-premises applications into AWS Significant reduction in existing AWS costs Further cost reduction, faster execution, and risk reduction using infrastructure-as-code Improved cloud security posture with automated incident response procedures Fault-tolerant and highly available infrastructure that scales to meet demand Elimination of undifferentiated heavy lifting, such as application and cloud operations Ability continuously adapt your applications to reduce costs, increase uptimes, respond to business events, and exploit the pace of innovation in AWS. Areas of Expertise Include: Application Architecture Performance Optimization Migrations Security, Governance & Compliance Cloud Operations Cost Savings
Updated on July 10, 2022Comments
-
Mark Richman almost 2 years
I am using Flask-Migrate in my application, with the following models:
listpull/models.py
from datetime import datetime from listpull import db class Job(db.Model): id = db.Column(db.Integer, primary_key=True) list_type_id = db.Column(db.Integer, db.ForeignKey('listtype.id'), nullable=False) list_type = db.relationship('ListType', backref=db.backref('jobs', lazy='dynamic')) record_count = db.Column(db.Integer, nullable=False) status = db.Column(db.Integer, nullable=False) sf_job_id = db.Column(db.Integer, nullable=False) created_at = db.Column(db.DateTime, nullable=False) compressed_csv = db.Column(db.LargeBinary) def __init__(self, list_type, created_at=None): self.list_type = list_type if created_at is None: created_at = datetime.utcnow() self.created_at = created_at def __repr__(self): return '<Job {}>'.format(self.id) class ListType(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(80), unique=True, nullable=False) def __init__(self, name): self.name = name def __repr__(self): return '<ListType {}>'.format(self.name)
run.py
#!/usr/bin/env python # -*- coding: utf-8 -*- from listpull import app, manager manager.run()
listpull/__init__.py
from flask import Flask from flask.ext.sqlalchemy import SQLAlchemy from flask.ext.script import Manager from flask.ext.migrate import Migrate, MigrateCommand from mom.client import SQLClient from smartfocus.restclient import RESTClient app = Flask(__name__) app.config.from_object('config') db = SQLAlchemy(app) migrate = Migrate(app, db) manager = Manager(app) manager.add_command('db', MigrateCommand) ... import listpull.models import listpull.views
I initialized the database using
./run.py db init
and then I run./run.py db migrate
and I get the following error:sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'job.list_type_id' could not find table 'listtype' with which to generate a foreign key to target column 'id'
What am I doing wrong here?