CODEWITHSUNDEEP
flaskflask-sqlalchemy
Alex G.P.
Alex G.P.

Reputation: 10008

Flask-SQLAlchemy + create_all() failed on many-to-many relationship creation

I have following application structure:

run.py
|->app
   |->models
      |-> user.py (declares role-to-user relationship table and User model)
      |-> role.py (declares Role model)
   |-> main.py (contains initialization and all required imports)
   |-> extensions (here sqlalchemy variable declared to be imported later)

When I trying to create initial DB structure following way:

from app.models import *
from app.extensions import db
from app.main import myapp #app is initialized with all packages  like assets, db, security, etc.

with myapp.test_request_context():
    db.create_all()

I've got an exception: NoReferencedTableError: Foreign key associated with column 'users_to_roles.user_id' could not find table 'users' with which to generate a foreign key to target column 'id'

I also tried to dow it following way:

@app.before_first_request
def initialize_database():
    db.create_all()

with no success

I tried to move Role to the same file where User model is declared with the same result. I read the docs here: https://pythonhosted.org/Flask-SQLAlchemy/quickstart.html but it says 'you just need to import db' and it does not works.

Here is how User model and relationship table are declared (Role model looks similar to User):

users_to_roles_association_table = db.Table('users_to_roles',
    db.Column('user_id', db.Integer, db.ForeignKey('users.id')),
    db.Column('role_id', db.Integer, db.ForeignKey('roles.id')))

class User(db.Model, UserMixin):
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(255), unique=True)
    .......
    roles = db.relationship('Role', secondary=users_to_roles_association_table,
                            backref=db.backref('users', lazy='dynamic'))

How to create initial DB structure using Flask-SQLAlchemy?

Upvotes: 3

Views: 1214

Answers (1)

dgilland
dgilland

Reputation: 2748

One of the features of Flask-SQLAlchemy is automatic table name generation from the class name when the __tablename__ class attribute isn't defined for a model class. From https://pythonhosted.org/Flask-SQLAlchemy/models.html:

Some parts that are required in SQLAlchemy are optional in Flask-SQLAlchemy. For instance the table name is automatically set for you unless overridden. It’s derived from the class name converted to lowercase and with “CamelCase” converted to “camel_case”.

In your case, the User class will have it's __tablename__ attribute inferred as user which means there is no users table defined.

You can fix this by either explicitly setting __tablename__ or by changing the ForeignKey parameters to match the autogenerated table names:

class User(db.Model, UserMixin):
    __tablename__ = 'users'
    ...

class Role(db.Model):
    __tablename__ = 'roles'
    ...

or leave the __tablename__ attribute undefined and modify the association table:

users_to_roles_association_table = db.Table('users_to_roles',
    db.Column('user_id', db.Integer, db.ForeignKey('user.id')),
    db.Column('role_id', db.Integer, db.ForeignKey('role.id')))

Upvotes: 5

Related Questions