flask-sqlalchemy db.Model._decl_class_registry.values() and db.metadata.tables are inconsistent

Question

I want to dynamically generate a class based on the unit field like this:

def gm_a(unit):
    tname = '%s_a' % unit

    for c in db.Model._decl_class_registry.values():
        if hasattr(c, '__table__') and c.__table__.fullname == tname:
        return c

    class A(DynamicBase):
        __tablename__ = '%s_a' % unit
        id = db.Column(db.Integer, primary_key=True)
        ......# other fields
    return A

You can see if I want to use table_a or desk_a table in ORM operations, I can do this:

@app.route('/<unit>/a')
def a(unit):
    obj_table  = gm_a('table').query.filter_by(xxx).all()
    obj_desk = gm_a('desk').query.filter_by(xxx).all()

In this way, we can operate on tables with different table names of the same structure. There is a problem that if we have 3 gm_* functions(gm_a,gm_b,gm_c) and 3 routes (/<unit>/a,/<unit>/b,/<unit>/c), each template like this:

<ul>
  <li><a href="a">A</a></li>
  <li><a href="b">B</a></li>
  <li><a href="c">C</a></li>
</ul>

If we click on these links at random, we expect to generate 3 classes in db.Model._decl_class_registry.values() and 3 tables in db.metadata.tables.

Strange phenomenon

---
[<class '__main__.gm_a.<locals>.A'>, <class '__main__.gm_b.<locals>.B'>, <class '__main__.gm_c.<locals>.C'>]
immutabledict({'table_a': Table('table_a', MetaData(bind=None), Column('id', Integer(), table=<table_a>, primary_key=True, nullable=False), schema=None), 'table_b': Table('table_b', MetaData(bind=None), Column('id', Integer(), table=<table_b>, primary_key=True, nullable=False), schema=None), 'table_c': Table('table_c', MetaData(bind=None), Column('id', Integer(), table=<table_c>, primary_key=True, nullable=False), schema=None)})
127.0.0.1 - - [12/Jul/2018 09:58:03] "GET /table/b HTTP/1.1" 200 -
---
[<class '__main__.gm_a.<locals>.A'>, <class '__main__.gm_b.<locals>.B'>, <class '__main__.gm_c.<locals>.C'>]
immutabledict({'table_a': Table('table_a', MetaData(bind=None), Column('id', Integer(), table=<table_a>, primary_key=True, nullable=False), schema=None), 'table_b': Table('table_b', MetaData(bind=None), Column('id', Integer(), table=<table_b>, primary_key=True, nullable=False), schema=None), 'table_c': Table('table_c', MetaData(bind=None), Column('id', Integer(), table=<table_c>, primary_key=True, nullable=False), schema=None)})
127.0.0.1 - - [12/Jul/2018 09:58:04] "GET /table/c HTTP/1.1" 200 -
---
[<class '__main__.gm_a.<locals>.A'>, <class '__main__.gm_b.<locals>.B'>]
immutabledict({'table_a': Table('table_a', MetaData(bind=None), Column('id', Integer(), table=<table_a>, primary_key=True, nullable=False), schema=None), 'table_b': Table('table_b', MetaData(bind=None), Column('id', Integer(), table=<table_b>, primary_key=True, nullable=False), schema=None), 'table_c': Table('table_c', MetaData(bind=None), Column('id', Integer(), table=<table_c>, primary_key=True, nullable=False), schema=None)})
127.0.0.1 - - [12/Jul/2018 09:58:04] "GET /table/b HTTP/1.1" 200 -
---
[<class '__main__.gm_a.<locals>.A'>, <class '__main__.gm_b.<locals>.B'>]
immutabledict({'table_a': Table('table_a', MetaData(bind=None), Column('id', Integer(), table=<table_a>, primary_key=True, nullable=False), schema=None), 'table_b': Table('table_b', MetaData(bind=None), Column('id', Integer(), table=<table_b>, primary_key=True, nullable=False), schema=None), 'table_c': Table('table_c', MetaData(bind=None), Column('id', Integer(), table=<table_c>, primary_key=True, nullable=False), schema=None)})
127.0.0.1 - - [12/Jul/2018 09:58:04] "GET /table/a HTTP/1.1" 200 -

As you can see in pic or code, We have already clicked a, b, c before, so there are three in class and table. But once we clicked b, there is only a, b in .vales(). Since there is no table_c class, it will be regenerated in our program logic, but table_c does exist in Tables. So it will throw an exception:

sqlalchemy.exc.InvalidRequestError: Table 'table_c' is already defined for this MetaData instance.  Specify 'extend_existing=True' to redefine options and columns on an existing Table object.

I am very confused why the number in db.Model._decl_class_registry.values() will change randomly and the number of _decl_class_registry.values() and db.metadata.tables() will be different. I also use the get_class_by_table function in sqlalchemy-utils, but the principle is consistent with our method and doesn't work.

Does anyone know why?Thx.

Answer 1

I've been struggling with this exception yesterday and came up with a "solution". Kind of hacky but works.

My problem was that after flask server restart, pre-existing dynamically created tables were not loaded in db.Model._decl_class_registry but they were listed in db.metadata.tables. How you initialize your db is not included in the question, but I assume that the root cause of your problem was the same with mine, I have attempted to use a persistent db with dynamic table names and metadata.reflect on startup. Re-creating a table with a name which exists in db.metadata.tables but not in db.Model._decl_class_registry will result an exception above. The invalid state indicates that a persistent db was loaded and flask-sqalchemy did not find the model for a table during initialization.

This is just an assumption and I am kind of late the party, the question is almost 2 years old, probably you don't even have that piece of code anymore. Anyways, if anyone wants to use dynamic table names and persistent db: I hope this answer will help to not spend hours of googling and debugging.

So, the problematic code, which runs into

sqlalchemy.exc.InvalidRequestError: Table '{something}' is already defined for this MetaData instance.  Specify 'extend_existing=True' to redefine options and columns on an existing Table object.

after server restart:

app.py - original approach << WRONG

from flask import Flask, request, render_template

from config.config_test import ConfigCache, db

import logging
import os

app = Flask(__name__, static_url_path='/static')
basedir = os.path.abspath(os.path.dirname(__file__))
app.config['SQLALCHEMY_DATABASE_URI'] =  'sqlite:///' + os.path.join(basedir, 'data.sqlite')
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] =  False
app.config['SECRET_KEY'] = 'absolutely secret'

# !!!!!
# https://stackoverflow.com/questions/28789063/associate-external-class-model-with-flask-sqlalchemy
db.init_app(app)
with app.app_context():
    # !!!!
    # https://stackoverflow.com/a/40984584/8375783
    db.metadata.reflect(bind=db.engine)

configs.py - original approach << WRONG

from flask_sqlalchemy import SQLAlchemy, declarative_base

Base = declarative_base()
db = SQLAlchemy(model_class=Base)


class ServerConfig(db.Model):
    __tablename__ = 'server_config'
    __abstract__ = True
    name = db.Column(db.String, unique=True, primary_key=True)
    location = db.Column(db.String)
    config_type = db.Column(db.String)
    descriptor = ['name', 'location', 'config_type']


class ConfigCache:

    def _is_server_config_cached(self, config_name):
        return db.metadata.tables.get(config_name, False)

    def _create_server_config_table(self, name):
        new_table = type(name, (ServerConfig,), {'__tablename__': name})
        db.create_all()
        return new_table

    def add_server_config(self, data):
        table = self._is_server_config_cached(data['config_name']) 
        if table is False:
            table = self._create_server_config_table(data['config_name'])
        for d in data["data_batch"]:
            entry = table(name=d['name'], location=d['location'], config_type=d['config_type'])
            db.session.add(entry)
        db.session.commit()

After spending a few hours with this issue I noticed that all non-abstract, non-dynamic named tables worked fine. All the models were properly loaded afterdb.metadata.reflect(bind=db.engine) both into db.Model._decl_class_registry and db.metadata.tables. It seemed to me that the key is the local scope (regarding available Models) when db = SQLAlchemy(model_class=Base) is called. So I came up with this:

Lets tweak the startup sequence to provide all the models:

1. load db (physical sqlite db)
2. from the connection, load all the table names
3. add proper Models to locals
4. re-initialize SqlAlchemy with locals containing all the models
5. call reflect
6. all previously generated dynamic named tables are properly loaded

app.py - with updated init sequence << WORKING

from flask import Flask, request, render_template

from config.config_test import db, dirty_factory

import logging
import os

app = Flask(__name__, static_url_path='/static')
basedir = os.path.abspath(os.path.dirname(__file__))
app.config['SQLALCHEMY_DATABASE_URI'] =  'sqlite:///' + os.path.join(basedir, 'data.sqlite')
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] =  False
app.config['SECRET_KEY'] = 'absolutely secret'

# !!!!!
# https://stackoverflow.com/questions/28789063/associate-external-class-model-with-flask-sqlalchemy
db.init_app(app)
with app.app_context():
    # !!!!
    # https://stackoverflow.com/a/40984584/8375783
    dirty_factory(config_db.engine.engine.table_names())
    db.metadata.reflect(bind=config_db.engine)
    db.create_all(app=app)

configs.py - with the locals() hack << WORKING

from flask_sqlalchemy import SQLAlchemy, declarative_base

Base = declarative_base()
db = SQLAlchemy(model_class=Base)


class ServerConfig(db.Model):
    __tablename__ = 'server_config'
    __abstract__ = True
    name = db.Column(db.String, unique=True, primary_key=True)
    location = db.Column(db.String)
    config_type = db.Column(db.String)
    descriptor = ['name', 'location', 'config_type']

def dirty_factory(config_names):
    for config_name in config_names:
        if 'server-config' in config_name: #pattern in data['config_name']
            locals().update({config_name: type(config_name, (ServerConfig,), {'__tablename__': config_name, })})
    db = SQLAlchemy(model_class=Base)

class ConfigCache:

    def _is_server_config_cached(self, config_name):
        return db.metadata.tables.get(config_name, False)

    def _create_server_config_table(self, name):
        new_table = type(name, (ServerConfig,), {'__tablename__': name})
        db.create_all()
        return new_table

    def add_server_config(self, data):
        table = self._is_server_config_cached(data['config_name']) 
        if table is False:
            table = self._create_server_config_table(data['config_name'])
        for d in data["data_batch"]:
            entry = table(name=d['name'], location=d['location'], config_type=d['config_type'])
            db.session.add(entry)
        db.session.commit()

TLDR;

If you use dynamic table names and persistent db, make sure that you pay attention to load your models before you initialize SQLAlchemy. If your models are not available to be loaded when db = SQLAlchemy(model_class=Base) is called, tables will be loaded into db.metadata.tables but the related model won't be loaded into db.Model._decl_class_registry. Re-declaring the table/model will result the exception above. If you place your re-delcaration into a try/except block (yes I have tried), you won't be able to query, because the table and the model won't be connected properly.