Error creating flag-SQLAlchemy + create_all () when creating many-to-many relationship
I have the 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 try to create the original DB structure like this:
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 have an exception: NoReferencedTableError: The foreign key associated with the "users_to_roles.user_id" column could not find the table users with which to create a foreign key for the target "id" column
I also tried to do it like this:
@app.before_first_request
def initialize_database():
db.create_all()
without success
I tried to move Role
to the same file where the model is declared User
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 doesn't work.
This is how the model and relationship table are declared User
(the role model is 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 do I create the original DB structure using Flask-SQLAlchemy?
source to share
One of the features Flask-SQLAlchemy
is the automatic generation of the table name from the class name if the __tablename__
class attribute is not defined for the model class. From https://pythonhosted.org/Flask-SQLAlchemy/models.html :
Some parts that are required in SQLAlchemy are optional in Kolba-SQLAlchemy. For example, the table name is automatically set for you if not overridden. It is derived from the class name converted to lowercase and from "CamelCase" converted to "camel_case".
In your case, the class User
will have an attribute __tablename__
listed as User
, which means no table is defined users
.
You can fix this by either explicitly setting __tablename__
or changing the parameters ForeignKey
to match the autogenerated table names:
class User(db.Model, UserMixin):
__tablename__ = 'users'
...
class Role(db.Model):
__tablename__ = 'roles'
...
or leave the attribute __tablename__
undefined and change 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')))
source to share