Flask SQLAlchemy: many to many relationship error

Question

I am trying to set up many-to-many relationship in SQLAlchemy but I am getting the error:

from shopapp import db
db.create_all()

sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'shoppinglists_products.shoppinglist_id_v2' could not find table 'shoppinglist' with which to generate a foreign key to target column 'id'

My code:

from sqlalchemy import ForeignKey
from shopapp import db


shoppinglists_products = db.Table("shoppinglists_products",
                                  db.Column("shoppinglist_id", db.Integer, ForeignKey("shoppinglist.id")),
                                  db.Column("product_id", db.Integer, ForeignKey("product.id")))

class ShoppingList(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(20), unique=True, nullable=False)
    products = db.relationship('Product', back_populates="shoppinglists", secondary="shoppinglists_products")


class Product(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(20), unique=True, nullable=False)

Where is the problem?

Move the class Product before Shoppinglists. When creating the relationship the product class cannot be found — rfkortekaas
– rfkortekaas, Commented May 6, 2022 at 21:32

P0intMaN · Accepted Answer · 2022-05-07 06:13:35Z

It seems like Flask-SQLAlchemy has problem finding the table for foreign key reference. Based on your code, here are the two ways you can fix this:

1) Fix shoppinglists_products table:

Flask-SQLAlchemy often converts the CamelCased model names into a syntax similar to this: camel_cased. In your case, ShoppingList will be referred to as shopping_list. Therefore, changing the ForeignKey("shoppinglist.id") to ForeignKey("shopping_list.id") will do the trick.

shoppinglists_products = db.Table("shoppinglists_products",
             db.Column("shoppinglist_id", db.Integer, ForeignKey("shopping_list.id")), # <-- fixed

2) Change the model names:

If you'd like, you could go ahead and change the model name from ShoppingList to Shopping and later refer to this as shopping. This would prevent any confusion from rendering further. Usually, developers don't quite often go for a class name which is combined of two words, especially for the ORM cases. This is because various frameworks has different ways of interpreting the class names to create tables.

James_SO · Accepted Answer · 2022-06-22 15:23:16Z

Expanding on @P0intMaN's answer - explicitly providing the SQL Alchemy table name with __tablename__ = "ShoppingList" (for example) lets you use your preferred case style and prevents SQLAlchemy from 'helping' you by changing the name of something kind of important without telling you.

class ShoppingList(db.Model):
    __tablename__ = "ShoppingList"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(20), unique=True, nullable=False)
    products = db.relationship('Product', back_populates="shoppinglists", secondary="shoppinglists_products")

In many/most Flask tutorials and books, simplistic table names (e.g. posts, comments, users) are used, which elide this issue. Thus a trap awaits for those of us who insist on meaningful CamelCased class names. This is mentioned somewhat casually in the documentation here: https://flask-sqlalchemy.palletsprojects.com/en/2.x/models/

Some parts that are required in SQLAlchemy are optional in Flask-SQLAlchemy. For instance the table name is automatically set for you unless overridden. It’s derived from the class name converted to lowercase and with “CamelCase” converted to “camel_case”. To override the table name, set the tablename class attribute.

Collectives™ on Stack Overflow

Flask SQLAlchemy: many to many relationship error

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related