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?

Answer 1

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.