Flask-SQLAlchemy - assertionError when trying to reflect existing db when any relations exist

Question

I am having hard time figuring out how to create models based on existing db tables with flask-sqlalchemy. I managed to do it with pure sqlalchemy's automap, but I want to be able to benefit from features of flask-sqlalchemy. I googled quite a lot but didn't manage to find anything that would address my issue. The most intriguing part is that the error starts occurring the moment I add a foreign key to any table in the DB. I can generate models for all the tables successfully providing none of them have foreign keys.

When trying to run the app I get:

Traceback (most recent call last):
  File "C:\Users\Maciek\Documents\GitHub\stocker\run.py", line 2, in <module>
    from app import app, stock, products
  File "C:\Users\Maciek\Documents\GitHub\stocker\app\__init__.py", line 20, in <module>
    from app.models import Product
.
.
.
  File "C:\Users\Maciek\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\sql\schema.py", line 1952, in _link_to_col_by_colstring
assert self.constraint._referred_table is table
AssertionError

init.py

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql+psycopg2://postgres:password%@localhost:5432/stocker'

db = SQLAlchemy(app)
db.init_app(app)
db.reflect(app=app)
from app.models import Product
db.create_all()

models.py

from app import db

class Product(db.Model):
    __tablename__= db.Model.metadata.tables['Products']
    id = db.Column('Id', db.Integer, primary_key=True)
    name = db.Column('Name', db.String)
    categoryId = db.Column('CategoryId', db.Integer, db.ForeignKey('Categories.Id'))
    brandId = db.Column('BrandId', db.Integer, db.ForeignKey('Brands.Id'))
    unitId = db.Column('UnitId', db.Integer, db.ForeignKey('Units.Id'))
    brand = db.relationship("Brand", backref="products")
    category = db.relationship("Category", backref="products")
    unit = db.relationship("Unit", backref="products")

class Category(db.Model):
    __tablename__= db.Model.metadata.tables['Categories']
    id = db.Column('Id', db.Integer, primary_key=True)
    name = db.Column('Name', db.String)

class Brand(db.Model):
    __tablename__= db.Model.metadata.tables['Brands']
    id = db.Column('Id', db.Integer, primary_key=True)
    name = db.Column('Name', db.String)

class Unit(db.Model):
    __tablename__= db.Model.metadata.tables['Units']
    id = db.Column('Id', db.Integer, primary_key=True)
    name = db.Column('Name', db.String)

score 1 · Answer 1 · answered Apr 09 '20 at 20:40

Problem solved by redefining models

from app import db

class ProductModel(db.Model):
    __table__= db.Model.metadata.tables['products']
    name = db.Model.metadata.tables['products'].columns['Name']
    brand = db.relationship("BrandModel", backref="products")
    category = db.relationship("CategoryModel", backref="products")
    unit = db.relationship("UnitModel", backref="products")

class UnitModel(db.Model):
    __table__= db.Model.metadata.tables['units']

class BrandModel(db.Model):
    __table__= db.Model.metadata.tables['brands']

class CategoryModel(db.Model):
    __table__= db.Model.metadata.tables['categories']

Flask-SQLAlchemy - assertionError when trying to reflect existing db when any relations exist

1 Answers1