Table 'roles_users' is already defined for this MetaData instance

Question

I'm trying to get a current_user information in my views and I include from users.models import *

Then in my code return current_user;

@app.route('/panel')
@login_required
def access_panel():
    return current_user.email;

Once I run my server it says:

Traceback (most recent call last):
  File "manage.py", line 6, in <module>
    from nadel import app
  File "/Users/tbd/Desktop/Projects/nadel/__init__.py", line 27, in <module>
    from users import views
  File "/Users/tbd/Desktop/Projects/nadel/users/views.py", line 5, in <module>
    from users.models import *
  File "/Users/tbd/Desktop/Projects/nadel/users/models.py", line 8, in <module>
    db.Column('role_id', db.Integer(), db.ForeignKey('role.id')))
  File "/Library/Python/2.7/site-packages/flask_sqlalchemy/__init__.py", line 67, in _make_table
    return sqlalchemy.Table(*args, **kwargs)
  File "/Library/Python/2.7/site-packages/sqlalchemy/sql/schema.py", line 398, in __new__
    "existing Table object." % key)
sqlalchemy.exc.InvalidRequestError: Table 'roles_users' is already defined for this MetaData instance.  Specify 'extend_existing=True' to redefine options and columns on an existing Table object.

In my model I have:

from nadel import db
from flask.ext.security import UserMixin, RoleMixin

# Define models
roles_users = db.Table('roles_users',
        db.Column('user_id', db.Integer(), db.ForeignKey('user.id')),
        db.Column('role_id', db.Integer(), db.ForeignKey('role.id')))

class Role(db.Model, RoleMixin):
    id = db.Column(db.Integer(), primary_key=True)
    name = db.Column(db.String(80), unique=True)
    description = db.Column(db.String(255))

class User(db.Model, UserMixin):
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(255), unique=True)
    name = db.Column(db.String(255))
    password = db.Column(db.String(255))
    active = db.Column(db.Boolean())
    #confirmed_at = db.Column(db.DateTime())
    last_login_at = db.Column(db.DateTime())
    current_login_at = db.Column(db.DateTime())
    last_login_ip = db.Column(db.String(32))
    current_login_ip = db.Column(db.String(32))
    login_count = db.Column(db.Integer)

    roles = db.relationship('Role', secondary=roles_users,
                            backref=db.backref('users', lazy='dynamic'))

I don't understand why I am getting this error and how to solve it.

Answer 1

Try adding:

__table_args__ = {'extend_existing': True}

right below __tablename__

Reference: https://docs.sqlalchemy.org/en/13/orm/extensions/declarative/table_config.html?highlight=table_args

Answer 2

I had a similar error. My problem was importing the class that inherits db.Model from two different files using a relative import. Flask-SQLAlchemy mapped the two imports as two different table definitions and tried to create two tables. I fixed this issue by using the absolute import path in both files.

Answer 3

I had this error when I had created a new class by copy-pasting a previous class. It turned out I had forgotten to change the __tablename__, so I had two classes with the same __tablename__ property. This caused the error, and changing the property resolved it.

Answer 4

This depends on what you want to do, are you trying to:

1. delete the old class and repalce it with a new class mapping? Or,
2. Keep the old class and just restart your app?

In the first case using your example above, try running the following line before defining your classes:

db.metadata.clear()

The reason is the first time you declare a SQLAlchemy Mapping by defining a python class, the definition of the class is saved to the metadata object, in order to prevent conflicts caused by multiple definitions being mapped to the same table.

When you call the clear() method you clear all the table definitions held in memory by the Metadata object, which allows you to declare them again.

In the second case when you're just restarting the app I would write a test to see if the table already exists using the reflect method:

db.metadata.reflect(engine=engine)

Where engine is your db connection created using create_engine(), and see if your tables already exist and only define the class if the table is undefined.

Answer 5

Its one year late but if someone doesn't find the solution in the given answers may be this can solve the problem -

I had this same problem with my class which was inherited from db.Model (of flask_sqlalchemy).

My code looked like this -

class MasterDB(db.Model):
    __tablename__ = 'masterdb'
    __table_args__ = {'schema': 'schema_any'}
    ...
    ...

Resolved it by specifying the abstract property to True in the class variable list. Add it in the class variable list like this and it should work -

class MasterDB(db.Model):
    __tablename__ = 'masterdb'
    __table_args__ = {'schema': 'schema_any'}
    __abstract__ = True

Answer 6

My name is Jacob. I had the same situation.

The issue is this: this occurs when you are importing parent and child tables in the same code. Then you import CHILD table, the CHILD table brings the parent table's structure also into the metadata. So you don't need to import PARENT table again.

The solution is this: you would need to rearrange the order of importing the ORM structures in your code. The order is based on parent + child relation.

Example: The following yields an error:

import CHILD_ORM
import PARENT_ORM   

Rearrange the order as follows, to prevent the error:

import PARENT_ORM   
import CHILD_ORM

Answer 7

TL;DR

• If you encounter this error when trying to register flask blueprint to application, check if Model importing path is consistent across all blueprints where imports the Model

For example

• Blueprint 1: from A.B.C import Model A
• Blueprint 2: from .B.C import Model A (This raises error, importing path should be consistent across all blueprints that imports the same model. So it should be from A.B.C import Model A)

I haven't dig this issue to find out what is the cause, but I guess that SQLAlchemy manages Model with its imported namespace and raise error when different namespace is defined but the models are same.

Answer 8

I'm getting this error while running the flask app in development mode:

FLASK_ENV=development flask run

The dynamic reloading feature from flask apparently does reload the metadata instances as you might expect. Restarting the app as suggested by "Spcogg the second" resolves this issue for me!

Answer 9

Most likely a bug in imports.

It's just that the roles_users model is imported multiple times in one of your components.

This can happen if you imported into several components in which there is already an import of the required model

Examle:

from schemas.user import UserSchema # have `from models.user import User` inside
from blocs.users import UsersBLOC # have `from models.user import User` inside

Answer 10

I had this problem when I was importing and defining explicitly the models in foreign key relations (like in the examples of the official doc)

Bad:

ForeignKey(User.id)

Well (not import model):

ForeignKey("user.id")

Answer 11

I am not hundred percent sure, but this issue might be happening due to the fact that the same table is being created from the same module twice. In my situation, I realized I was importing the same module from two different locations and module.__init__ automatically gets executed, which imports the model.py twice. Therefore causing a metadata conflict, I guess the metadata/namespace is exactly same. I read other answers but none of them clearly explained which choices we do have here.

As far as I looked into the source code, we have two choices:

1. __table_args__ = {'extend_existing': True}
2. __table_args__ = {'keep_existing': True}

First one allows you to extend the table, as the name suggests, but that wouldn't be your preferred choice depending on the situation. I am not sure if that even creates any performance overhead but there is that possibility too.

Second option just keeps the table as it is and does not modify it. In the end the second option was my preferred choice thinking that it's just a simple check if the table exists.

Answer 12

I also had this problem. After reading this thread I was able to mange it. Like user N. Quest in his message from Feb 3, 2019 I forgot to change __tablename__ and had two classes with same value

Answer 13

I saw this same error using jupyter notebook. I was importing sqlalchemy table classes from a library stored on disk and there was a bug in the definition. After fixing the bug I re-ran the import and saw this error. Simply restarting the jupyter kernel and then rerunning the import resolved the issue.