SQLAlchemy inheritance not working

Question

I'm using Flask and SQLAlchemy. I have used my own abstract base class and inheritance. When I try to use my models in the python shell I get the following error:

>>> from schedule.models import Task
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/home/teelf/projects/schedule/server/schedule/models.py", line 14, in <module>
    class User(Base):
  File "/home/teelf/projects/schedule/server/venv/lib/python3.4/site-packages/flask_sqlalchemy/__init__.py", line 536, in __init__
    DeclarativeMeta.__init__(self, name, bases, d)
  File "/home/teelf/projects/schedule/server/venv/lib/python3.4/site-packages/sqlalchemy/ext/declarative/api.py", line 55, in __init__
    _as_declarative(cls, classname, cls.__dict__)
  File "/home/teelf/projects/schedule/server/venv/lib/python3.4/site-packages/sqlalchemy/ext/declarative/base.py", line 254, in _as_declarative
    **table_kw)
  File "/home/teelf/projects/schedule/server/venv/lib/python3.4/site-packages/sqlalchemy/sql/schema.py", line 393, in __new__
    "existing Table object." % key)
sqlalchemy.exc.InvalidRequestError: Table 'user' is already defined for this MetaData instance.  Specify 'extend_existing=True' to redefine options and columns
 on an existing Table object.

How do I fix this?

Code:

manage.py:

#!/usr/bin/env python

import os, sys

sys.path.append(os.path.abspath(os.path.join(os.path.dirname(__file__), '..')))

from flask.ext.migrate import Migrate, MigrateCommand
from flask.ext.script import Manager

from server import create_app
from database import db


app = create_app("config")

migrate = Migrate(app, db)
manager = Manager(app)

manager.add_command("db", MigrateCommand)

if __name__ == "__main__":
    manager.run()

__init__.py:

from flask import Flask

from flask.ext.login import LoginManager

from database import db
from api import api
from server.schedule.controllers import mod_schedule


def create_app(config):
    # initialize Flask
    app = Flask(__name__)

    # load configuration file
    app.config.from_object(config)

    # initialize database
    db.init_app(app)

    api.init_app(app)

    # initialize flask-login
    login_manager = LoginManager(app)

    # register blueprints
    app.register_blueprint(mod_schedule)

    return app

database.py:

from flask.ext.sqlalchemy import SQLAlchemy


db = SQLAlchemy()

models.py:

from sqlalchemy.dialects.postgresql import UUID

from database import db


class Base(db.Model):
    __abstract__ = True

    id = db.Column(UUID, primary_key=True)


class User(Base):
    __tablename__ = "user"

    username = db.Column(db.String)
    password = db.Column(db.String)
    first_name = db.Column(db.String)
    last_name = db.Column(db.String)
    authenticated = db.Column(db.Boolean, default=False)

    def __init__(self, first_name, last_name, username):
        self.first_name = first_name
        self.last_name = last_name
        self.username = username

    def is_active(self):
        """ All users are active """
        return True

    def get_id(self):
        return self.username

    def is_authenticated(self):
        return self.authenticated

    def is_anonymous(self):
        """ Anonymous users are not supported"""
        return False

controllers.py:

from flask import Blueprint

from flask.ext.restful import reqparse, Resource

from api import api
from server.schedule.models import User


mod_schedule = Blueprint("schedule",  __name__, url_prefix="/schedule")


class Task(Resource):
    def put(self):
        pass

    def get(self):
        pass

    def delete(self):
        pass


api.add_resource(Task, "/tasks/<int:id>", endpoint="task")

Two things that might be helpful (although I haven't had the chance to test them): a) when initializing `SQLAlchemy()` use your Flask app (e.g. `db = SQLAlchemy(app)`; b) `users` is a [reserved key word in PostgreSQL](http://www.postgresql.org/docs/current/static/sql-keywords-appendix.html), so maybe `__tablename__ = "users"` might end up in some conflict. — cuducos, Jan 07 '15 at 11:00
@cuducos a) I have `db.init_app(app)` in `__init__.py`. b) the tablename in my code is `user` not `users`. — , Jan 07 '15 at 15:49
@teelf How are you managing migrations/creation of the tables? Is it possible to share your `__init__.py` or more bits of your application? — cuducos, Jan 09 '15 at 08:53
@cuducos I forgot manage.py, I use alembic for migration/table creation. Sorry about all the code. — , Jan 09 '15 at 23:14
@teelf, not good news: I replicated all your code locally and ran the `import` with no errors: `In [1]: from server.schedule.models import Task \n In [2]:` Probably the error is elsewhere… — cuducos, Jan 10 '15 at 19:28

score 62 · Answer 1 · answered Jan 11 '16 at 14:54

62

Try adding

 __table_args__ = {'extend_existing': True}

to your User class right under __tablename__=

cheers

answered Jan 11 '16 at 14:54

Jeff R.

963
7
11

7

Where did you learn this, and can you share in the SQLalchemy docs where is case is spoken about more? (You answer is just what I needed) – Alexander McNulty Aug 17 '17 at 08:06
20

While this is useful in some cases, in general this should not be needed and is a sign that the table definitions are created multiple times – usually by accident. Throwing it as a generic "just copy and paste this to your code" solution is misleading and just hides the real errors. – Ilja Everilä Feb 05 '18 at 07:20
3

Here's a [link](http://docs.sqlalchemy.org/en/latest/orm/extensions/declarative/table_config.html?highlight=__table_args__) to sqlalchemy docs, where \_\_table_args\_\_ dict is mentioned. – Alex Jun 23 '18 at 05:16

score 0 · Answer 2 · answered Oct 08 '21 at 13:23

Another option, if you don't already have data in your pre-existing database, is to drop it, recreate it without the tables. Then interactively run your "models.py" script (you would need to add a little code at the bottom to allow this), then in the interactive Python console do "db.create_all()" and it should create the tables based on your classes.

score 0 · Answer 3 · edited Feb 17 '23 at 03:49

0

I had the same problem and found the solution here: https://github.com/pallets-eco/flask-sqlalchemy/issues/672#issuecomment-478195961

So, basically we hit the union of two problems:

Having left over .pyc files on the disk.

Git Ignoring empty directories full of files in .gitignore

Deleting the directories and cleaning the .pyc files solved the problem.

edited Feb 17 '23 at 03:49

tdy

36,675
19
86
83

answered Feb 13 '23 at 15:23

Aleksey Koshkin

1

SQLAlchemy inheritance not working

3 Answers3

Linked